3

I have a query that is run once per minute, on multiple tables (with joins). The largest table has 31 million rows after being used for about 18 months. Generally the query is fast (< 1 second), except in cases where we have to go back a few months or more in time (order by datetime field descending, group by, top 1), then it can take up to 20 seconds.

I've started looking at partitioning. Now I read twice that partitioning does not increase query performance, instead it decreases performance, but it was twice by the same person (here and here). Is this correct?

Community
  • 1
  • 1
user247702
  • 23,641
  • 15
  • 110
  • 157

1 Answers1

2

No partitioning does not necessarily decrease query performance.

What you are describing sounds like being caused by parameter sniffing. Are your indexes and statistics up to date?

Update (in response to comment): Ensure you have a regular index maintenance task scheduled.

Mitch Wheat
  • 295,962
  • 43
  • 465
  • 541
  • For some reason the index fragmentation is up to nearly 100% again. I had rebuilt all indexes before starting query tests. Will fix that first then. – user247702 Aug 01 '11 at 10:18
  • Found a way to work around the slow performance (keep the value of the last available datetime per machine, and use it in the where clause). I'll accept your answer since it actually answers the original question. – user247702 Aug 01 '11 at 11:36