|
|
Posted by Erland Sommarskog on 02/27/07 22:56
Massimo (mastino@hotmail.it) writes:
> Sql 2K
> I have to keep in the database the data from the last 3 months.
> Every day I have to load 2 millions records in the database. So every
> day I have to export (in an other database as historical data container)
> and delete the 2 millions records inserted 3 month + one day ago.
>
> The main problem is that delete operation take a while...involving
> transaction log.
>
> The question are:
> 1) How can I improve this operation (export/delete)
> 2) If we decide to migrate to SQL 2005, may we use some feature, as
> "partitioning" to resolve the problems ? In oracle I can use the
> "truncate partition" statement, but in sql 2005, I'm reading, it cant be
> done. This becouse we can think to create a partition on the last three
> mounts to split data. The partitioning function can be dinamic or
> containing a function that says "last 3 months ?" I dont think so.
Permit me to start with SQL 2005. There you have partitioned tables,
and in a case like yours you would set up the table with let's say
four partitions, with the month as the partitioning column. To delete
old rows, you would simply take that table out of the partition
table, and then drop table that table. You in the same manner, shift
in a new table for the next month. Here I said month, but you have one
partition per day, and have 90 partitions if you like - whether this
is a good idea I don't know.
Note that partitioned tables are only available in the Enterprise
(and Developer) Edition of SQL 2005.
In SQL 2005, you would use partitioned views (and here 90 paritions
would definitely go beyond what is manageable). One table per month
and then they are united in a view with a UNION ALL statement. At
a new month you would run a job that dropped the table from four
months back, and create a new table. Notice that you can load directly
to the new, and data should turn in the right place.
See also Stefan Delmarco's article on partitioned views:
http://www.fotia.co.uk/fotia/FA.02.Sql2KPartitionedViews.01.aspx
--
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]
|