0

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.

Improve this question
3
  • 1
    These OR clauses aren't going to help performance. What is the actual usage of this? Do you only pass in one of @ProductGroupId/@ProductId and one of @CustomerGroupId/@CustomerId? Commented Oct 6, 2013 at 9:24
  • Do you always pass both params @ ProductGroupId and @ ProductId or only one at the time? Commented Oct 6, 2013 at 9:27
  • I pass in both. Discounts can ben either product specific or they are set on a productgroup. And also they can be specified for that specific customer but also for the group of customers where this customer belongs to. Commented Oct 6, 2013 at 11:26

2 Answers 2

Reset to default
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.

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

But.. i need records, with either this value set, or that value set.. Do you have a good suggestion on how to do that with an OR?
OK. Are you able to post the execution plan?
postimg.org/image/t12vojkdr This the plan, which uses the index proposed by SQL itself.
CREATE CLUSTERED INDEX [IX_1] ON [dbo].[Discount] ( [StartDate] ASC, [EndDate] ASC, [CustomerId] ASC, [CustomerGroupId] ASC, [CampaignId] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
Another thing you could try is to rewrite your query as a few separate selects and union them later. Try to get rid of those OR's. It's a bit hard to say what will be the best way exactly without knowing your data.
0

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.

Improve this answer

5 Comments

The primary benefit of partitioning is administrative (data loading and archiving) not performance. The table only has 1 million rows anyway.
I disagree that primary benefit is just administrative. Partition is big help when you have large data.
Regarding performance if you just make whatever the partitioning column(s) would be the leading column(s) in the index then it can seek into the relevant part easily anyway. It doesn't have to read a load of redundant rows.
Partitioning of big tables allows parallel processing of data from different file groups which definitely gives better query proformance I totally agree with Lokesh on this
1 million rows is not a lot. You should first and foremost look at optimising queries before looking at administrative tools like partitioning. You pretty much always have control over your code but you may not have much control over how the databases are physically structured and managed (e.g. when you have multiple clients who manage the databases themselves).

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.