Click here to Skip to main content
15,563,228 members
Please Sign up or sign in to vote.
2.00/5 (2 votes)
See more:
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.

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
Updated 24-Sep-19 20:49pm
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:
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

As suggested by PIEBALDconsult replace the WHERE with a join
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[^]
Share this answer
Member 11706445 6-Mar-16 10:02am    
how to use filtered index?
CHill60 6-Mar-16 12:09pm    
There are examples on the link in my solution
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
Share this answer
HI Write not exists and also check for join condition left side table must contain less records.
Share this answer

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

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900