March 24, 2011 at 5:39 am
Hi everyone,
I have inherited a dataware house database which contains 10 fact tables ( 50-60 millions of rows per table) and around 15 dimension tables (1.5 – 2 millions of rows per table) . Each of the fact tables has 4-5 measures such as premium, amounts etc.
On top this structure, 3 summary fact tables were created which collects measures from each of the fact tables, applies an aggregation and inserts into this roll up fact tables.
There are currently over 80 measures that are collected from corresponding fact tables and inserted into the roll up fact tables and as you can imagine this is a slow process. At present for each measure, it runs a large join query touching upon fact table(s) and dimension tables and repeats this for number of measures which is over 80 at the moment.
To improve the performance, something that is obvious is to create a view ( measures can be logically grouped which means that measures that in the same group can use the same fact and dimension table to fetch the data) for the same group ( it will cover 4-5 measures) .
I want to go a step further and run multiple views in parallel, but I am not sure how this can be achieved. Should I create a separate stored procedure and call them one after another? Or is there setting or switch at connection level or server level I can turn on and off?
As far I as I have gathered from MSDN and other sources, in order to run queries parallel, you need to have SMP computer with multiple CPUs and depending on some conditions it is done automatically by the server which monitors and decides which queries run simultaneously. These queries that I am talking about are certainly very good candidates to be run simultaneously.
Notes: There are maintenance jobs which maintains statistics, rebuild indices etc. Database is MS SQL 2005.
Has anyone had any experience of what I am talking about? All suggestions including design of the solutions , are most welcome.
Thank you for your time.
Enis
March 24, 2011 at 7:04 am
First, a view is not going to improve performance. A view is just a query. That's all.
To improve performance you need to understand how your database is being queried. If those queries are taking advantage of existing indexes. If the indexes in place are good enough for the queries or do you need more or different ones. Fundamental query performance tuning has to be done.
If this is a datawarehouse, can you set it to READ ONLY? If so, you'll get a drastic improvement in speed since SQL Server will not deal with locking.
As far as parallelism goes, you do have to have a machine that has multiple processors first. After that, it is managed automatically by SQL Server and you're better off letting it. The one thing you can do to ensure a higher degree of parallelism is to reduce the cost threshold for parallelism below it's default of 5. However, I would test this exhaustively to be sure you're getting better performance. While parallelism sounds like a cool thing, it comes with quite a bit of overhead to manage all the threads of data. Frequently that overhead is greater, sometimes by many factors, than the benefits from parallel processing.
"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
March 24, 2011 at 8:28 am
Hi Grant,
Thank you for your response.
The idea behind the view was to running one query to bring multiple measures rather than run the same query as many as number of measures.
As far as I can see indexes are used when the query runs. I think the main factor is the sheer number of rows that each tables has. Unfortunately we can’t as we are moving data among tables for this particular job. Database set it the read only after load/process ends in the production environment though.
That’s great. I don’t want to mess with multiple processor settings etc. I will look into cost threshold.
Much appreciated.
Enis
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply