December 9, 2010 at 3:33 pm
Hi,
We have an issue running a reports in that the query can take up to 4:30 minutes to run. we issue a report for each customer so currently the report process is taking 14 hours which is crazy!
We've traced this to an order_products table which is over 8GB and running the query through the execution plan didn't recommend any indexes we don't already have.
So we've done a test to create a copy of the order_products table but only containing the previous 3 months of data and the query came back in 7 seconds.
So now I am trying to think of a way I can have a copy of the order_products table on the report server but only containing the pervious 3 months of data. The new copy would have to be kept synchronised for all new updates too. The order_products table is replicated transactionally from our production server.
So not sure which way to go with this one. How do I keep the new table fully synchronized but with nothing more than 3 months of data.
was looking at triggers but maybe there is a better way. Is there anyway of partitioning the table into have latest 3 months in 1 partition and everything else in the other partition and having the query just scan the smaller partition?
Any help / suggestions greatly appreciated!
December 9, 2010 at 3:51 pm
Yes, partitioning is actually your best bet here, but you have to partition on the date column in the table...
... which leads me back to a different issue. When you said you reviewed the index plan, are you getting a seek or a scan against the main table with this date in it? Is the date part of your clustered index?
There's a few things you can do here to help deal with it, but partitioning is one choice... and it'll do the rest for you. However, I've never tried to partition a replicated table before, so I dunno what you're gonna end up with there.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
December 9, 2010 at 4:26 pm
I am thinking about getting the data for 3 months into a different table every night, or something, and then to query this table. Something like this:
After you create the "3MonthsTable"
a job would TRUNCATE TABLE "3MonthsTable" then populate it with the new data.
Of course you can delete the old records and insert the new ones.
This would be relatively easily to implement.
Partitioning looks idealy, but as Craig said I am also not sure how it would work in this specific environment. There is an article describing partitioning on msdn here:
http://msdn.microsoft.com/en-us/library/ms345146%28SQL.90%29.aspx
Regards,
Iulian
December 9, 2010 at 6:19 pm
Thanks for the replies......
I'm actually getting a clustered index scan on the table. The date for the orders is actually not contained in the order_products table
but a parent orders table. The orders table has a non clustered index on the OrderDate column.
The issue I would have if I created a order_products_3months table and just removed everything prior to 3 months previous is that the new 3month table has to be completely in sync with the existing order_products table. So all latest rows have to be the same.
I will a further look at partitoning and see what I can find!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply