471,108 Members | 1,291 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,108 software developers and data experts.

Copy without Locks

I have a stored procedure which copies data from a view into a
temporary table (x2) and then from the temporary table into a table
which the users use. It takes 1 minute to get the data into the temp
table and seconds to update into the final one (hence the two stages).

When I do the initial copy from the view, it locks the various tables
used in the view and potentially blocks the users. It's a complex view
and uses plenty of other tables. We get massive performance issues
'generating' the data into a table as opposed to the view.

What I want to do is take all the data without locking it. I don't
want to modify the data, just read it and stick the data into a table.

Thanks

Ryan

SQL as follows :

/*Drop into temp tables first and then proper ones later as this
works out a lot less time when no data will be available*/

TRUNCATE TABLE MISGENERATE.dbo.CBFA_MISDATATemp -- Temp Table
TRUNCATE TABLE MISGENERATE.dbo.CBFA_MISPIPDATATemp -- Temp Table

INSERT INTO MISGENERATE.dbo.CBFA_MISDATATemp
SELECT * FROM MIS.dbo.CBFA_MISDATA -- View

INSERT INTO MISGENERATE.dbo.CBFA_MISPIPDATATemp
SELECT * FROM MIS.dbo.CBFA_MISPIPDATA -- View

/*Now drop this into full MIS tables for speed*/

TRUNCATE TABLE MISGENERATE.dbo.CBFA_MISDATA
TRUNCATE TABLE MISGENERATE.dbo.CBFA_MISPIPDATA

INSERT INTO MISGENERATE.dbo.CBFA_MISDATA -- Final Table
SELECT * FROM MISGENERATE.dbo.CBFA_MISDATATemp

INSERT INTO MISGENERATE.dbo.CBFA_MISPIPDATA -- Final Table
SELECT * FROM MISGENERATE.dbo.CBFA_MISPIPDATATemp
Jul 20 '05 #1
1 2955
Ryan (ry********@hotmail.com) writes:
What I want to do is take all the data without locking it. I don't
want to modify the data, just read it and stick the data into a table.


You can say things like:

SELECT * FROM tbl WITH (NOLOCK)

although, I am uncertain how this works with a view.

You should be very careful with NOLOCK. Using NOLOCK may save you from
users screaming because they are blocked, but since you are reading
uncommitted data, you may produce incorrect or incoherent results. The
users may not scream about this - they will just make incorrect decisions
because of bad input.

--
Erland Sommarskog, SQL Server MVP, so****@algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 20 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

42 posts views Thread by Edward Diener | last post: by
reply views Thread by Bruce Pullen | last post: by
4 posts views Thread by Alex Callea | last post: by
18 posts views Thread by Anjana | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.