CODEWITHSUNDEEP
t-sqlsql-server-2008-r2query-performance
user2041076
user2041076

Reputation: 119

Sql Server Union Query Optimization

I have given a task to optimize the below sql query. Currently the query is timing out and causing a lot of blocking . I just started using t-sql, so please help me with optimizing the query. 

select ExcludedID 
from OfferConditions with (NoLock) 
where OfferID = 27251 
  and ExcludedID in (210,223,409,423,447,480,633,...lots and lots of these...,
  13346,13362,13380,13396,13407,1,2) 

union 

select CustomerGroupID as ExcludedID 
from CPE_IncentiveCustomerGroups ICG with (NoLock) 
inner join CPE_RewardOptions RO with (NoLock) 
on RO.RewardOptionID = ICG.RewardOptionID 
where RO.IncentiveID = 27251 
  AND ICG.Deleted = 0 and RO.Deleted = 0 and 
  and ExcludedUsers = 1 
  and CustomerGroupID in (210,223,409,423,447,480,633,...lots and lots of these...,
  13346,13362,13380,13396,13407,1,2);

Upvotes: 0

Views: 1726

Answers (2)

Natalia
Natalia

Reputation: 311

You can try to insert those IDs to temp table and join it instead of using IN statement.

Upvotes: 1

Steve
Steve

Reputation: 5545

The key to solving you problem is NOT to fix the SQL, but to fix indexes on your tables. For example, you should have a compound index on the OfferConditions table with OfferID and ExcludedID.

When you create the indexes on the other tables, remember that if the field is in the where OR the join filter, it should be part of your compound index.

Upvotes: 0

Related Questions