How to copy a table containing a few million rows effciently?

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Karan (Karan_at_discussions.microsoft.com)
Date: 03/01/05 

Date: Tue, 1 Mar 2005 10:33:03 -0800

I have a table in SQL Server 2000 which contains about 5 millions rows and
keeps growing at the rate of 500,000 rows per month. This is a live
production table into which data is inserted/updated contantly. This table
contains a few indexes and keys. The activity on this table is extremely high
during the last few days of the month.

We use this table also to calculate a lot of reporting data currently. This
reporting job sometimes takes more than 20 hours to run especially during the
month end. We run this job about once a week. While this job is running there
are a lot of Timeouts and deadlocks since this table is being constantly
updated. Sometimes we have to even abort this job manually just to aviod
interruption to the regular processing. I am planning on copying this data (a
portion of the data) to a new table and run my reporting data queries on that
table. What is the best way to copy this data into a new table. It has to be
efficient and not affect the actual table much since it is being used
constantly. The Reporting queries may include data that are being currently
inserted as well.

We have a plan to replicate the entire database and use the replicated
database for the purpose of reporting. Until then, we need a temporary
solution to fix this problem.

Please advice.
Karan

Relevant Pages

  • Re: 3vl 2vl and NULL
    ... reporting, MV end-users were either churning out reports and ad hoc ... MV-database-independent applications as often as they do in the SQL ... where I cannot leave MV behind unless I can find a better data model. ... screen to be identical to the logical data model of the database. ...
    (comp.databases.theory)
  • Re: Freezing Over Start Menu.... Again
    ... James, thanks for reporting back to the group, it often helps others. ... I suspect that MSE keeps a database of what's where and things like file versions and sizes, perhaps in order to replace things that get damaged by malware or crashes, so it maybe has MSSQL Lite or something running. ...
    (microsoft.public.windows.vista.general)
  • Re: New Computwer with "Office with Access"
    ... Alpha 4 I found interesting at the time, ... Filemaker was a pretty good database from a user standpoint. ... Reporting was easy too, and one of it's ... Perhaps more familiarity with it would help. ...
    (misc.news.internet.discuss)
  • Re: Reporing service failure
    ... I understand that when you tried to configure your database for your SQL ... Server Reporting Services, you encountered the logon failure error. ... And then configure your SSRS databases via Reporting Services Configuration ...
    (microsoft.public.sqlserver.security)
  • RE: Reporing service failure
    ... I understand that when you tried to configure your database for your SQL ... Server Reporting Services, you encountered the logon failure error. ... it was strange that the audit failure was for another user account NT ... And then configure your SSRS databases via Reporting Services Configuration ...
    (microsoft.public.sqlserver.security)