April 20, 2020 at 5:37 pm
Hi folks,
Just wondering if anyone can give me some pointers as to some other options to try to improve performance on a query I'm running.
Its a flat select off one table with 23 fields to display along with a where clause for an ID column, which will have multiple values so using an IN condition, as well as another clause using a name <> 'MyValue' i.e.
WHERE ID IN (1,2,3.....47)
AND NAME <> 'MyName'
As per the code sample suggestion, I have, in this case, up to 47 values that will be contained within the IN condition.
I created a non clustered index on the table using the ID and Name fields as well as an INCLUDE on all the other fields returned by the query.
In this particular example I'm returning just over 1.2m results but it takes just over 6 minutes to return.
I've tried changing the query somewhat by using EXISTS as well as an INNER JOIN but none improve the performance of the query. I've also removed the NAME <> 'MyName' condition along with changing the index to just use the ID value instead but again no difference to the performance.
The execution plan shows that for all versions of the query an INDEX SEEK is being used with no obvious bottlenecks showing.
If I run the query for one ID, the data returns instantaneously which leads me to believe that there is room for improvement. However, even if I run the query for a smaller section of ID's it still doesn't run that much quicker.
Is this just a case of the query performing as expected or is there something else I could be doing to improve it?
Any help appreciated
April 20, 2020 at 6:27 pm
1.2M rows * 10KB bytes per row is a huge amount of data, ~12GB. I can imagine it could take 6 mins to transfer that much data.
It would be very helpful to see full DDL for all tables involved and get the entire query plan, not just a single, static screen shot from the query plan.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
April 21, 2020 at 8:56 am
Hi Scott
Table DDL attached along with execution plan (where using a EXISTS condition).
I did notice there is a big difference between the estimate and actual number of rows between the IN and EXISTS conditions. However, that doesn't make much difference to the time it takes to return the full result set.
Regards
April 21, 2020 at 11:55 am
you didn't tell us what you mean by "the data returns instantaneously" - if by this you mean it returns to your SSMS grid then do not use this as a benchmark - specially as your full query is trying to return 1.2 million rows.
when testing performance of a query do it by inserting onto a temporary table - that will normally give you a good indication of how fast the query is.
once you are happy with it then you can use the query on your "client" software - but that better not be SSMS for these volumes.
also be careful when creating indexes with so many includes - you are basically almost duplicating the table on this case and it may not be required at all.
and can you tell us what is the spec of the server - both CPU/Memory at os level and what is allocated to SQL Server - and values of maxdop and cost threshold for parallelism.
April 21, 2020 at 1:06 pm
Hi Federico
By "If I run the query for one ID, the data returns instantaneously" I meant that if I execute the query for just one ID value it returns the full result set to SSMS grid in less than 1 second.
Just as an FYI, the query is being used in a separate report application (Qlik) not within SSMS itself.
Database Server is SQL 2012 SP4
Maxdop is 4
Cost Threshold For Parallelism is 5
Minimum and maximum server memory is 500MB and 26GB respectively
Memory on server is 32GB, 8 logical processors
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply