This View is Shocking ! Can it be Improved ??
-
Hi All,
I have a view which has the DDL as described below.
I have users running queries on it each table has around 10m records on it, I have users running queries on this view and the performance is totally shocking, once users run these queries on the database, the database is rendered useless as it grinds everything to a halt, the only thing you can run without getting annoyed is SP_WHO2.
Typical queries looks like this:
select JoinDete, PhoneNumber, client_id, count(personurn) as ContactCount, sum(elapsedtime) as totalduration
from CustomerProfile_July_200607
where route = '5304' and clientservice_id = '-1'
group by JoinDete, PhoneNumber, client_id
CREATE View CustomerProfile_July_200607 as
Select * from dbo.CustomerProfile20060731
Union all
select * from dbo.CustomerProfile20060730
Union all
select * from dbo.CustomerProfile20060729
Union all
select * from dbo.CustomerProfile20060728
Union all
select *
from dbo.CustomerProfile20060727
Union all
select * from dbo.CustomerProfile20060726
Union all
select * from dbo.CustomerProfile20060725
Union all
select * from dbo.CustomerProfile20060724
Union all
select * from dbo.CustomerProfile20060723
Union all
sele
ct * from dbo.CustomerProfile20060722
Union all
select * from dbo.CustomerProfile20060721
Union all
select * from dbo.CustomerProfile20060720
Union all
select * from dbo.CustomerProfile20060719
Union all
select * from dbo.CustomerProfile20060718
Union all
select * from dbo.CustomerProfile20060717
Union all
select * from dbo.CustomerProfile20060716
Union all
select * from dbo.CustomerProfile20060715
Union all
select * from dbo.CustomerProfile20060714
Union all
select * from dbo.CustomerProfile20060713
Unio
n all
select * from dbo.CustomerProfile20060712
Union all
select * from dbo.CustomerProfile20060711
Union all
select * from dbo.CustomerProfile20060710
Union all
select * from dbo.CustomerProfile20060709
Union all
select * from dbo.CustomerProfile20060708
Union all
select * from dbo.CustomerProfile20060707
Union all
select * from dbo.CustomerProfile20060706
Union all
select * from dbo.CustomerProfile20060705
Union all
select * from dbo.CustomerProfile20060704
Union all
select * from dbo.CustomerProfile200
60703
Union all
select * from dbo.CustomerProfile20060702
Union all
select * from dbo.CustomerProfile20060701
-
Your first bet might be to ask the users if they really need 10 million x (however many tables there are) records. I've never run into a situation where that amount of data is useful. If they just need aggregates on that, perhaps compiling these aggregated statistics in off hours and having a summary table they could query would be better. Look into managing it with Analysis Services and OLAP cubes.
If there aren't any other options you can try splitting the tables across multiple filegroups on multiple drive arrays. Identifying the required columns and creating indexes on them might help as well. I just haven't seen SQL Server ever perform that well with datasets greater than a few million records.
-
It will really help if you can summarize the data (specially if you have 10 mil + rows).
Thanks
Sreejith
-
Thanks all for your reply, I have tried summarising the information into tables, but what I find out is that there are so many users who can run different kind of queries, i.e they can decide to change a parameter etc
With disks arrays etc, that sounds like a good idea but looking at the cost, the idea would probably be throw out.
Thanks
John
-
Wouldn't this be a case where a cube might come in handy (sorry but I cna't offer more than that)??
-
Hello,
Can you post the estimated execution plan? I'd surely like to see it. I believe that looking at the plan will help answer your question about which is faster. (querying 1 table vs the monthly view).
I'd also suggest that you play around with the idea of materializing the view into a single table and adding the right indexes.
But first, run this with "results to text" turned on and post the results. Then modify it so that it performs the same select, but against a single table contained in the view.
set showplan_text on
GO
select JoinDete, PhoneNumber, client_id, count(personurn) as ContactCount, sum(elapsedtime) as totalduration
from CustomerProfile_July_200607
where route = '5304' and clientservice_id = '-1'
group by JoinDete, PhoneNumber, client_id
GO
set showplan_text off
GO
-
Do you really need every column of every row of every table in the view? Im sure that there must be a lot of redundant there. Some summary approach must be possible.
-
On each of the 31 under tables do you have an index on route and another one on clientservice_id? If not then write a script to create these. I often find single column indexs yeild better performance than complex indexes, but your milage may vary.
Oh wait. You have a separate table for each day of the month? OK. What populates these? It must know the table name. Your users are trying to summary data for the whole month, right? This is historical data, right?
Roll you 31 tables into one monthly table and let them querry that? I'll be that you dot not have transaction date as one of the columns in your tables. With the table name being the date i would hope that you don't have a date column. OK, add one.
One 10 million row table has got to perform better than 31 tables with 322 thousand, or so, rows each.
Good grief! One table for each day.
ATBCharles Kincaid
-
If I remember correctly, it's 10-15 M/day. (Phone company).
-
Oh! So 28 to 465 million per month.
You might have to solve this in code. The execution plan must be a horror show. UNION ALL is not going to be kind to you.
ATBCharles Kincaid
-
That's why I proposed the cude idea... (assuming it's possible)... solves the multiple queries part and the long wait time.
-
... and I would normally agree with Charles (if only 300 k /day) then you don't need more than one table / month but this is a monster database.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply