5

Just remove the hint for SQL Server. The cardinality and parallel hints are designed to help control performance when you "know better" than the DBMS--they don't affect the results in any way. You can't know if you'll get acceptable performance in SQL Server until you run the query. The different platforms will almost certainly need tuning in ...


2

Using a window aggregate would presumably be substantially faster than the self-join that you currently have. This is available from version 3.25 Unfortunately, in SQLite, DISTINCT cannot be used in an OVER aggregate, but we can simulate it with MAX of DENSE_RANK (if file_id is nullable then it can be a little more complex) The calculation is quite simple: ...


2

Likely the majority of the performance issue you're seeing is just the normal constraints of the data limitations of SQLite. Your query is of sound mind, and I don't believe there's much you can do to optimize it other than re-write the WHERE predicate to something more efficiently relational with an INNER JOIN like this: WITH file_id_counts AS ( SELECT ...


2

Index: (rowtype, starttime, endtime, clientid, deletedat) Index usage: Index Cond: ((rowtype = ANY ('{1,2,3,4}'::integer[])) AND (clientid = client.id) AND (deletedat < '2000-01-01 00:00:00'::timestamp without time zone)) Notice how the index condition skips over starttime (as it not usable, being specified in the OR condition). So while it is ...


1

I found adding an index around po_status_id and name helps. I dont have a great explanation. I dont know this causes the planner to think it needs to build the entire join despite the limit. CREATE TABLE `px_po_status` ( `po_status_id` tinyint NOT NULL, `name` varchar(32) NOT NULL, PRIMARY KEY (`po_status_id`), key (`po_status_id`, `name`) ) ENGINE=...


1

Out of the box, you don't need be using query hints in SQL Server until you need to and probably even true in your case with Oracle, by the looks of that query. The cardinality hint in Oracle is for when you have predicates that are too complex for Oracle's Cardinality Estimator, but your query looks rather simple. Anyway, there's not exactly a similar hint ...


1

Usually it is most efficient to avoid the dreaded OR and replace it with a UNION: SELECT myTable.id, client.id, ... FROM myTable JOIN client ON myTable.clientid = client.id WHERE myTable.rowtype in (1, 2, 3, 4) AND myTable.starttime > '2021-03-04 19:33:26+00' AND myTable.starttime < '2021-03-05 00:40:28+00' AND myTable....


Only top voted, non community-wiki answers of a minimum length are eligible