To speed up my stored procs, I use
SELECT * FROM table WITH (NOLOCK)
I've noticed that this saves a tremendous amount of time.
Does anyone know if there's any speedup at all by using NOLOCKs on temporary tables, created in the same SP? As in:
CREATE TABLE #temptable (tempitem int)
SELECT * FROM #temp WITH (NOLOCK)
The reason for asking is that I have *alot* of SP's, using *alot* of temporary tables, and if someone could give me a quick answer, I'd appreciate it.
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
My opinion (based on what I reda) is that you obtain a speed boost(not a big one if the SP is a small one), because this way the SQL Server uses no locks!!!
The same thing you can obtain by simply issuing a
set transaction isolation level read uncommitted
at the begining of each SP you have......
but be carefull maybe in certain situation you need to use the locking mechanims, and by using NOLOCK you may introduce inconsistent data....
good luck,
xenon
Business Accounts
Answer for Membership
by: adatheladPosted on 2003-06-03 at 04:09:41ID: 8637374
I would say that it wouldn't make a difference on temporary tables in stored procedures.
These temp tables are limited to the scope of that procedure so no other processes can interact with it...i.e. the stored proc that created it is the only process accessing it. Therefore other process will not be attempting to read or gain locks on the temp table.
Whereas in your original statement using standard tables, there is a probability that other process are using the table at the same time, which is why NOLOCK works in that scenario.
Cheers