January 16, 2008 at 9:26 am
I have a table which read by the web program and the web is up 24/7. But everyday I have to insert data (about 1 million rows) into the table by reading some text files. It will slow down the processing of the web. How do I keep the web running when I do the insert? If I use 'SELECT ...FROM Table with nolock' , I am afraid I will get dirty data for the web. Should I use data mirror to do the update and switch the table?
January 22, 2008 at 6:59 am
Have you considered using a staging table to load data and renaming tables once ready? Also, if you are using SQL Server 2005 ENT, you may want to look into using the new row versioning feature to mitigate this problem. Here's link:
http://www.microsoft.com/technet/prodtechnol/sql/2005/cncrrncy.mspx
Good luck,
Phillip Cox
January 22, 2008 at 7:09 am
One way, which requires a little work, but not too much, is to use a partitioned view instead of a table for the web page.
When you're ready to import the data, create a table for it, using the same structure as the main table, import the data into it, then add it to the partitioned view. (If not sure how to use partitioned views, Books Online has good data on it.)
Weekly, or thereabouts, move all the data into a master table, update the partitioned view to select from the master table, and drop/archive the daily tables. Or, when you add a new day's data, also move the prior day's data (or data from a week ago, whatever), to a master table. You can, of course, just keep adding tables, but that might become a bit much to administer. Of course, if the data becomes obsolete at some point (when it's a week old, or two days old, or yesterday's data, whatever the business rule is), then you can just add a new table, import into it, modify the view (add the new table, remove the obsolete), and then archive/drop the obsolete data.
Bit more admin, but it'll get the data into the table (view) with minimal disruption to the web page.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply