473,544 Members | 1,815 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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_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
Jul 20 '05 #1
1 3027
Ryan (ry********@hot mail.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

42
5722
by: Edward Diener | last post by:
Coming from the C++ world I can not understand the reason why copy constructors are not used in the .NET framework. A copy constructor creates an object from a copy of another object of the same kind. It sounds simple but evidently .NET has difficulty with this concept for some reason. I do understand that .NET objects are created on the GC...
0
3368
by: Bruce Pullen | last post by:
DB2 v7.2 (FP7 - DB2 v7.1.0.68) on AIX 5.2.0.0. We're seeing unexpected single row (then commit) insert locking behaviour. We're seeing Applications that already hold row-level W locks in lock-wait, waiting to acquire row-level X locks. The lock-waits are behind applications that have row-level X locks on different rows (honestly). Both...
4
1893
by: Alex Callea | last post by:
Hi there, We have a web application handling thousands of requests per seconds reading sql server data which is heavily updated. We are generally experiencing no performance problems. On some occasions we get an increase of the traffic of about 15% for short periods. In this case we observe something really strange: our webserver CPU goes...
7
6668
by: Ralf Gedrat | last post by:
Hello! I have some Vb.Net applications, which are terminated at indefinite times without message. If I call in the program regulated system.GC.Collect, then the program is terminated here sporadically without message. It's not possible to debug in visual studio, i get no exceptions (application is terminated unexpectedly without...
2
2233
by: Diffident | last post by:
Hello All, I just finished reading an interesting article by Scott about App Domains: http://odetocode.com/Articles/305.aspx Scott, I have a question about the section "Shadow Copies and Restarts". You talked about "Drain Stopped" and "Shadow Copy" concepts in this article. I maintain a web application which is in production. Every...
28
7345
by: robert | last post by:
In very rare cases a program crashes (hard to reproduce) : * several threads work on an object tree with dict's etc. in it. Items are added, deleted, iteration over .keys() ... ). The threads are "good" in such terms, that this core data structure is changed only by atomic operations, so that the data structure is always consistent regarding...
18
395
by: Anjana | last post by:
hi, can anyone illustrate a program to read and print a number without using standard library functions?? waiting for reply....
1
2870
by: shenanwei | last post by:
I have db2 v8.2.5 on AIX V5.3 with all the switches on Buffer pool (DFT_MON_BUFPOOL) = ON Lock (DFT_MON_LOCK) = ON Sort (DFT_MON_SORT) = ON Statement (DFT_MON_STMT) = ON Table (DFT_MON_TABLE)...
0
1337
by: SP | last post by:
Dear all! I have written a web service which should archive files in a server directory. The files are placed in a directory that the web server can access. In the first step I copy them to a temporary directory on the web server. Afterwards I put some data into a database table and put one of the files into a content management system by...
0
7434
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7371
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7781
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7388
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7716
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
1
5305
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
4925
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
1
1848
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
676
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.