Performance improvement for distinct clause

Question

0.00/5 (No votes)

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 6-Sep-19 0:10am

dinesh42

Updated 10-Sep-19 22:23pm

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

RickZeeland · Answer 1 · 2019-09-06T00:42:00

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[^]

Posted 6-Sep-19 0:42am

RickZeeland

Updated 6-Sep-19 3:07am

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 :)

MadMyche · Answer 2 · 2019-09-06T01:41:00

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®[^]

Aarti Meswania · Answer 3 · 2019-09-10T22:24:00

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!
:)

Posted 10-Sep-19 22:24pm

Aarti Meswania