Daily Reporting tables or Partitioned View ! Performance Crisis !
-
Hi All,
I am designing a reporting loader for a client project, at the moment, they have shown me the kind of design they have, at the moment they seem to have quite huge daily tables:
EG PersonProfile20070220 etc
The size of these tables including indexes is almost around 6GB. Moving forwards, I was wondering whether it made sense to design monthly tables rather and have a partitioned view looking at the monthly table, or is it better to have daily tables, and also I would like to know if there are any disadvantages of having daily tables.
I am also looking to reduce the size of the daily tables i.e removing unnecessary clutters, are there any guidelines to be followed when embarking on such tasks ?
Many thanks
-
This was removed by the editor as SPAM
-
Partitioning of data is always a good idea, but, the performance gains are lost if access to the partitioned data is not controlled. I've used partitioned views with daily,weekly, monthly data with great success , but I had control. SQL 2005 has builtin partitioning but i've not been able to test if some of the issues with partitioned views are still with the tables ( I suspect they will be )
As to which to use, weekly generate 52 per year, daily 365 per year - it's a case of what fits your requirements. Partitioning allows the efficient use of filegroups and of course federated databases/servers all of which can aid performance, backup etc. etc. However the more complex you make it the more likelyhood it is to break - so test, test and test and make sure you carefully document. I'd certainly advise against partitions if you're going to allow ad-hoc queries against the tables.
Data cleansing is a whole subject on its own, the answer is it just depends.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Hello Colin,
Thanks for reply, I am still a bit mystified.. The curent situation is that, I have a reporting loader which inserts into a big massive table 24 x 7 365 days a year.
My question is that, at the moment, maintenance tasks, ordinary dbcc showcontig takes ages on the table, simply because its a very large table, if I break the table down to monthly/weekly tables instead of the one massive table, and maybe have a view that looks at everything:
Eg
Create view PersonProfile
As
Select * from PersonProfile200703
union all Select * from PersonProfile200702
union all
Select * from PersonProfile200701
this way if i need to do maintenance tasks on the database, i.e backup/bcp, backup filegroup etc, it will be more simplified.
Will I gain/Suffer in terms of performance using this approach? The big table is used by a web front end and is heavily indexed, performance is Ok, but any new method (partitioned view) must not suffer from performance problems.
Please advice
-
I think what Colin is trying to say is that in 2005 you can use table partitioning to do the hard work for you.
If you partition on date then you can still query the main table and it gets the information from the relevent partitions.
You might want to look for kimberley tripp's article on table partitioning to get a better idea of how it works, can't remember the URL but google will return it pretty quick.
If you are using SQL 2000 then you might consider redGates SQL refactor application, from what i've read it allows you to split tables and amends SP's for you. I've never used this before but if it works then I think it would be a pretty cool solution
Mike
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply