September 19, 2012 at 1:36 pm
Hi All,
There is a job which executes each day and copy some records from one table to other.
The query looks something like:
INSERT INTO DestTable
SELECT Field1, Field2, Field3 FROM SourceTable
There is a lot of records to be transferred, and during that process, destination table is locked and web pages can't read it. Thus, website is not accessible while job is running.
I solved it by adding with (nolock) part to all SELECTs on web pages, and it works fine now.
I wonder if there is a better, recommended way to solve problem like this?
How to keep table available while big INSERT INTO is executing?
Thanks!
September 19, 2012 at 2:06 pm
Boris Pazin (9/19/2012)
Hi All,There is a job which executes each day and copy some records from one table to other.
The query looks something like:
INSERT INTO DestTable
SELECT Field1, Field2, Field3 FROM SourceTable
There is a lot of records to be transferred, and during that process, destination table is locked and web pages can't read it. Thus, website is not accessible while job is running.
I solved it by adding with (nolock) part to all SELECTs on web pages, and it works fine now.
I wonder if there is a better, recommended way to solve problem like this?
How to keep table available while big INSERT INTO is executing?
Thanks!
Yes avoiding the NO LOCK hint would be a good idea. You could have your insert run in batches with a small delay in between so other queries can execute. You could also look into isolation levels instead.
http://msdn.microsoft.com/en-us/library/ms173763.aspx
NO LOCK is a very scary hint to use for a number of reasons.
http://www.jasonstrate.com/2012/06/the-side-effect-of-nolock/%5B/url%5D
http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply