|
|
Posted by Erland Sommarskog on 10/25/07 21:14
Enorme Vigenti (LSimon5@libero.it) writes:
> Delete aged old data is most important for me: I have about 2 million
> rows every day for every customer table (and I have about 50 customers).
> For the delete operation for cut off the last day I need 4-5 hours!!!
> If I could to use truncate table (for delete last day) could be
> wonderfull :-)
> My problem is in calculate statistical day report too
> I must query only yesterday data table with where clause on 3-4 coloumn
> and sum/max function on decimal field, and group by clause on 6 coloums.
> This operation require 7/8 hours!!!
> My tables have 8 non clustered index (with data coloums like first
> field) and cluster primary key of course (only one coloumn integer type)
Boy, it seems that databasee keeps you busy a great part of the day.
It sounds that SQL 2005 and partitioned tables could help you at least
with the DELETE part. And once that is set up, you can add more indexes
to speed up the queries without getting problems with the updates. Well,
you still have the load part to consider, but I think you should be
able to load into an empty table, index it, and then switch it into the
partitioned table.
--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
Navigation:
[Reply to this message]
|