Click here to Skip to main content
14,298,693 members
Rate this:
Please Sign up or sign in to vote.
See more:
I have a table with 2 million records and 6 columns in the table. I am executing below statement on it:

SELECT DISTINCT t1.ID
FROM CUSTOMER_ORDER t1
WHERE (t1.SERVER_NAME = 'mn')
AND (t1.STATUS = 1)


The value for "Status" column in all the 2 million rows is 1.
The all distinct values for "SERVER_NAME" are 'mn' and 'op'. I have a non clustered index on Server_Name and Status column with ID column as included column.
'ID' column is a varchar field.

The query execution plan shows index seek for the NonClustered. The total time it is taking is apprx 4 seconds.

However, I am looking for any suggestions where the total execution time can be reduced to much lesser than 4 sec.

What I have tried:

Created index to make sure it is seek. Gained performance but still this 4 seconds of execution time is pretty high as this query gets executed close to 1000 times during batch processing, which in turn increasing the job execution time.
Posted
Updated 5 days ago
Rate this:
Please Sign up or sign in to vote.

Solution 1

Maybe you can try a filtered index: An Essential Guide to SQL Server Filtered Indexes[^]

And here is another interesting article: Performance Surprises and Assumptions : GROUP BY vs. DISTINCT[^]
   
v2
Comments
dinesh42 6-Sep-19 7:55am
   
I tried implementing the filtered index, the optimizer is using the filtered index and the number of logical reads have come down to 6000. Earlier with normal non clustered index the total logical reads were 12000.

I am unsure if this reduce in logical reads will have impact in overall execution of the query when it runs for 1000 times during batch execution?
dinesh42 6-Sep-19 7:59am
   
upon rebuilding the existing index also the logical reads have come down to 6000 which is similar to filtered index !
RickZeeland 6-Sep-19 8:58am
   
It is a good thing to do reindexing on a regular basis, SQL Server needs a bit of help here. PostgreSQL needs less maintenance, one of the reasons we chose for PostgreSQL years ago :)
Rate this:
Please Sign up or sign in to vote.

Solution 2

Adding indexes (filtered or not) are helpful and the best way to approach this.

What I am going to add into this is to use SSMS and view the Query Execution Plan to identify what index is going to help you best.

Read through this article to help you with SSMS and using the Execution Plans
Management Studio Hides Missing Indexes From You. - Brent Ozar Unlimited®[^]
   
Rate this:
Please Sign up or sign in to vote.

Solution 3

You can try below approaches as well

1. Indexing will help, but you have already tried that.
2. define scope if possible, use "TOP" to fetch top X no. of rows.
3. try alternative, "group by" query.

Happy Coding!
:)
   

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100