Long running join in sql server is taking a lot of time

Question

2.00/5 (2 votes)

See more:

SQL-Server-2008R2

Hi All,

The below query is taking a lot of time in executing i.e. upto almost 2 hr and sometime more also. The Tables LAR_PRODUCTS and STORE_RANGE_GRP_MATCH are physical tables and contain a record count of 432837 and 103038 respectively. Any suggestion to reduce the query execution time is welcomed.

SQL

Select 1                                  
      From LAR_PRODUCTS prd    with (nolock)                              
      Join STORE_RANGE_GRP_MATCH srg with (nolock)                                 
      On  prd.Store_Range_Grp_Id = srg.Orig_Store_Range_Grp_ID                                  
      And  srg.Match_Flag  = 'Y'                                  
      And  prd.Range_Event_Id = srg.LAR_Range_Event_Id                                  
                                  
      Where srg.Range_Event_Id Not IN (Select Range_Event_Id                                  
           From Last_Authorised_Range

What I have tried:

I have tried using indexed on both the tables but still it's taking a lot of time

Posted 4-Feb-16 16:50pm

Member 11706445

Updated 24-Sep-19 19:49pm

PIEBALDconsult

v2

Add a Solution

Comments

PIEBALDconsult 4-Feb-16 23:11pm

Do not _ever_ use IN or NOT IN with a subquery; convert it to a JOIN of some sort. Same for EXISTS.

Richard Deeming 5-Feb-16 13:09pm

Do you have anything to back that up?

This blog[^] suggests that a correlated NOT EXISTS tends to be the best solution. The results also suggest that NOT IN performs better than a LEFT JOIN.

jgakenhe 5-Feb-16 0:18am

I'd run the Execution Plan (google it) and see where it is hanging up. I'd then put an index on Match_Flag, because this is queried in the WHERE clause for a large amount of the records. And if you must use the NOT IN, then put it last as the dataset should be small by the time it gets there.

Execution Plan and Indexes: http://www.codeproject.com/Articles/190263/Indexes-in-MS-SQL-Server

jaket-cp 5-Feb-16 5:22am

I was just wondering would there be a major benefit in having an index on Match_Flag if only two possible values of 'Y' and 'N' exist.
I would have suggested putting a clustered index on columns Store_Range_Grp_Id, Range_Event_Id.

As I said, just wondering that is all...

CHill60 5-Feb-16 8:30am

I agree - not worth it on Match_Flag, definitely index the Ids

Richard Deeming 5-Feb-16 13:06pm

However, if you're always querying for Match_Flag = 'Y', a filtered index might be useful.

CHill60 6-Feb-16 8:10am

Very good point and one I keep forgetting about. I've edited my solution (and credited you)

3 solutions

Solution 2

Hello All, I did used the Left join after removing the Not IN Clause, but this also didn't worked for me. Clustered Indexes are already there on ID columns on both the tables

Posted 8-Feb-16 20:29pm

Member 11706445

Solution 3

HI Write not exists and also check for join condition left side table must contain less records.

Posted 24-Sep-19 19:49pm

Member 14603062

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)

CHill60 · Accepted Answer · 2016-02-05T02:54:00

As suggested by PIEBALDconsult replace the WHERE with a join
e.g.

SQL

LEFT join Last_Authorised_Range lar on lar.Range_Event_Id = srg.Range_Event_Id
WHERE lar.Range_Event_Id is null

Indexes on the ID fields in the tables should also help.

As Member 10454138 points out, there is no major benefit in having an index on Match_Flag if there are only two values

[EDIT] In regard to my last point on the column that only has two values - @RichardDeeming made an excellent point in a comment above. If you are always going to search for Match_Flag='Y' then using a Filtered Index will bring performance improvement - reference Introduction to SQL Server Filtered Indexes[^]