SQL index needed?

Question

I have a query that searches a large table with around 1mln discount records and i'm having an index problem with this table.

SELECT TOP 1 D.Discount
FROM  Discount D
WHERE (D.ProductGroupId = @ProductGroupId OR D.ProductId = @ProductId)
AND   (D.CampaignId IS NULL AND (D.CustomerGroupId = @CustomerGroupId OR D.CustomerId = @CustomerId OR (D.CustomerId IS NULL AND D.CustomerGroupId IS NULL)))
AND   getDate() BETWEEN D.StartDate AND D.EndDate
AND   D.Quantity = 1
ORDER BY D.Discount DESC

The where clauses on the product / productgroup, the startdate / enddate and quantity all work well. Without any special indexes on this table, i'll find the correct discount in about 2 seconds. (which is not that fast, but nowhere near dramatic in this case).

The problem is with the campaignId/CustomerGroupId/CustomerId part. Adding this part makes the query to run for over 2 minutes. I'm sure this can be solved, perhaps using a correct index, but i have been trying to setup all kinds of indexes, but nothing worked.

Answer 1

You can try to turn it into dynamic SQL and build from parts. Those OR that you have in your query make it run slower. If you pass only one parameter at the time to your query, that will improve it.

Another thing that you may do if you only search by one parameter at the time is to create 2 separate stored procs as you will be able to optimize each one separately. Don't create one stored proc with IF at the beginning (if @param1 IS NOT NULL ... ELSE ...) as SQL Server has sometimes problems with producing the right execution plans for such stored procs.

Answer 2

Your problem is amount of data. Indexing can help to certain extent but here data will always make query slow.

You need to partition your table to limit the amount of data you deal with, this will make your query run faster.