RSS 2.0
Sign In
# Monday, October 29, 2012

Recently we had a discussion with DBA regarding optimization strategey we have selected for some queries.

We have a table in our database. These are facts about that table:

  • the table is partitioned by date;
  • each partition contains a month worth of data;
  • the table contains at present about 110 million rows;
  • the table ever grows;
  • the table is most accessed in the database;
  • the most accessed part of the data is related to last 2-3 days, which is about 150000 rows.

The way we have optimized access to that table was a core of the dispute.

We have created filtered index that includes data for the last 3 days.

To achieve desired effect we had to:

  • create a job that recreates that index once a day, as filter condition is moving;
  • adjust queries that access the table, as we had to use several access pathes to the table depending on date.

As result we can see that under the load, stored procedures that access that table became almost 50% faster. On the other hand maintainance became more complicated.

DBA who didn't like the database complications had to agree that there are speed improvements. He said that there should be a better way to achieve the same effect but could not find it.

Are there a better way to optimize access to this table?

Monday, October 29, 2012 8:34:39 AM UTC  #    Comments [0] -
SQL Server puzzle | Tips and tricks
All comments require the approval of the site owner before being displayed.
Home page

Comment (Some html is allowed: a@href@title, b, blockquote@cite, em, i, strike, strong, sub, super, u) where the @ means "attribute." For example, you can use <a href="" title=""> or <blockquote cite="Scott">.  

[Captcha]Enter the code shown (prevents robots):

Live Comment Preview
<June 2024>
Total Posts: 387
This Year: 3
This Month: 0
This Week: 0
Comments: 1270
Locations of visitors to this page
The opinions expressed herein are our own personal opinions and do not represent our employer's view in anyway.

© 2024, Nesterovsky bros
All Content © 2024, Nesterovsky bros
DasBlog theme 'Business' created by Christoph De Baene (delarou)