I have a query that is taking too long to run.
I executed set statistics io on
and set statistics time on
, and I noticed that Scan count and logical reads are very high
Table 'TABLE_01'. Scan count 6497248, logical reads 26065220, physical
reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads
0, lob read-ahead reads 0.SQL Server parse and compile time: CPU time = 20 ms, elapsed time
= 20 ms.(138 row(s) affected)
SQL Server Execution Times: CPU time = 28969 ms, elapsed time =
30559 ms.
Here's the query:
SELECT Object1.Column1 AS Column2,
Object1.Column3 AS Column4,
Object1.Column5,
Object1.Column6,
Object1.Column7 AS Column8,
Object2.Column9 AS Column10,
Cast(Object2.Column11 AS VARCHAR) AS Column12,
Object2.Column13 AS Column14,
Object2.Column15 AS Column16,
Object2.Column17 AS Column18,
Object2.Column19 AS Column20,
Object3.Column21 AS Column22,
Object3.Column5 AS Column23,
Object2.Column24 AS Column25,
Object4.Column21 AS Column26,
Object4.Column5 AS Column27,
Object5.Column21 AS Column28,
Object5.Column29 AS
Column30,
Object6.Column31 AS Column32
FROM Object1
INNER JOIN (Object2
LEFT JOIN Object7 Object3
ON Object2.Column19 = Object3.Column19
INNER JOIN Object7 Object4
ON Object2.Column24 = Object4.Column19
INNER JOIN Object5
ON Object2.Column13 = Object5.Column15
INNER JOIN Object6
ON Object2.Column9 = Object6.Column9)
ON Object1.Column3 = Object2.Column3
WHERE Object1.Column3 IN (
43796020, 43795933, 43795931, 43795681,
43795672, 43793093, 43793090, 43793085,
43790358, 43790342, 43789167, 43789155,
43789154, 43788970, 43788928, 43788924,
43788776, 43788774, 43788712, 43788699,
43788674, 43788673, 43788626, 43788625,
43788227, 43788219, 43787557, 43787530,
43787088, 43787075, 43787063, 43787041,
43787002, 43786368, 43786351, 43786244,
43786243, 43786223, 43786222, 43786151,
43417954, 43417953, 43417929, 43417294,
43417293, 43417286, 43416500, 43416334,
43416304, 43416288, 43416089, 43416034,
43416016, 43416015, 43416014, 43416013,
43416012, 43416011, 43416010, 43416009,
43416008, 43416007, 43415753, 43401569,
43400701, 41524028, 41316604, 41311555,
41309174, 41308146, 41308136, 41286794,
41281375, 41281374, 41281294, 41263751,
41263749, 40960597, 38574969, 38133930,
38133868, 38133516, 38132482, 37630401,
37630400, 37630399, 37630398, 37630397,
37630396, 37630395, 37629851, 37629846,
37301414, 37298436, 37298433, 37298431,
37298429, 37298427, 37298424, 37298398
)
ORDER BY Object1.Column1 ASC
I would like to know how I can improve this query
The estimated execution plan and the actual execution plan
Best Answer
Hmm ? so as I mentioned in the comments, your actual execution plan reveals there is a severe cardinality estimate issue (screenshot of operation details for reference) on the Clustered Index Seek operation occuring on the
atividade
table. I'm not seeing much else in the execution plan or your query that jumps out at me explaining why you might be experiencing this type of issue other than the predicate in yourWHERE
clause being a decent size in theIN
list, which essentially is syntactical sugar for a bunch ofORs
. This also happens to be what the seek predicates are on for the aforementioned Clustered Index Seek operation, so it's a little suspect.Unfortunately I think this one might require a little trial and error of query re-writing to see what helps the optimizer in generating a better plan without a cardinality estimate issue. Could you please try re-writing your query like this logical equivalent query (if possible) and re-running it to see if there's any performance improvement?
The above query breaks out the logic of the filtering on the
id_atividade
field for theatributo_andamento
table and inserts the results into a temp table first. Then it creates an index on theidatividade
field that is used in theINNER JOIN
predicate to theatividade
table later on. I also am testing creating an index onidatributoandamento
which is what is used in theORDER BY
clause at the end of your query. Not sure if the query optimizer will use both indexes, but it's worth trying as a start.Please run the above and let me know how it turns out. We can try adjusting a couple more things and still end up with a logically equivalent query, to see if we can fix the cardinality estimate issue, and worst case we may need to recalculate the statistics or possibly use a query hint.