|
Posted by Enorme Vigenti on 10/25/07 07:45
Erland Sommarskog ha scritto:
> Enorme Vigenti (LSimon5@libero.it) writes:
>> It is more better to migrate at sqlserver2005 for the partitionet tables
>> and then use a single day table to create index? In that case I could
>> have only one day index! :-)
>
> The main advantage of partitioned tables is that it makes it easy to
> age out old data very quickly. I don't see any particular gain for
> querying in your case. For the queries a clustered index on the datetime
> column would be a good start.
>
> If there are many rows per day (say > 50000), you may need to add non-
> clustered indexes as well for the most important queries. Possibly with the
> datetime value as the first column.
>
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)
Navigation:
[Reply to this message]
|