February 12, 2013 at 4:50 am
Dear All,
I have a database cut that I work on every day. It has a few million rows. It contains invoices - so the columns are things like invoice number, date, value etc. there are about 60 columns in total.
We have some quite complex queries that exist and are run against this database, but it appears to me that as the database is read only, we could prepare (overnight) it to perform much better during the day.
Soooo, I wonder if anyone knows of any technqiues/articles that exist to do this, so that SQL code changes were minimal/zero, and without resorting to SSAS.
A typical query might sum invoice values between two day dates (maybe over a year), and aggregating to the day would't really help that much. Aggregating to the month probably would, but if the query spans whole months and part months (a few days either end of the aggregations) - how would you handle that !?
Well, any ideas gratefully received.
Regards, Greg.
February 12, 2013 at 6:13 am
If it's read only during the day, then the first thing I'd do is make the database a readonly database. That will eliminate all locking, which will help performance some.
There's not going to be a way to modify this to get pre-aggregations and other performance enhancements without also rewriting your queries. If, for example, you were to create a materialized view based on one of your queries that summed up the invoices, then your queries would not have to run those sums any more. So substantial rewrites to the queries is just going to be assumed.
In general, look to the queries that do aggregates. Recognize patterns. Then turn those patterns into another table or into a materialized view. Since you're not updating the database during the day, this will work quite well.
Other than that, tune the queries, tune the indexes, avoid common code smells, all the stuff of tuning queries.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply