Copy without Locks

[Ryan]

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_MISDATATe mp -- Temp Table
TRUNCATE TABLE MISGENERATE.dbo .CBFA_MISPIPDAT ATemp -- Temp Table

INSERT INTO MISGENERATE.dbo .CBFA_MISDATATe mp
SELECT * FROM MIS.dbo.CBFA_MI SDATA -- View

INSERT INTO MISGENERATE.dbo .CBFA_MISPIPDAT ATemp
SELECT * FROM MIS.dbo.CBFA_MI SPIPDATA -- View

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

TRUNCATE TABLE MISGENERATE.dbo .CBFA_MISDATA
TRUNCATE TABLE MISGENERATE.dbo .CBFA_MISPIPDAT A

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

INSERT INTO MISGENERATE.dbo .CBFA_MISPIPDAT A -- Final Table
SELECT * FROM MISGENERATE.dbo .CBFA_MISPIPDAT ATemp

[Erland Sommarskog]

Re: Copy without Locks

Ryan (ryanofford@hot mail.com) writes:[color=blue]
> 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.[/color]

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, sommar@algonet. se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp