473,372 Members | 1,234 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

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

Book recommendation on performance

I have the Transact-SQL Programming book from O'Reilly. It was
published in 1999. It states that "SELECT ... INTO" statements end up
locking the entire database of the target table. Since the tempdb is
also involved (in many cases), this creates major deadlocks for the
entire database and all users. It suggests using the "INSERT ...
SELECT" form instead.

Considering that the book is somehow dated, is this recommendation
still valid, especially on target sizes of up to 5 million records?
Jul 20 '05 #1
1 3610
php newbie (ne**********@yahoo.com) writes:
I have the Transact-SQL Programming book from O'Reilly. It was
published in 1999. It states that "SELECT ... INTO" statements end up
locking the entire database of the target table. Since the tempdb is
also involved (in many cases), this creates major deadlocks for the
entire database and all users. It suggests using the "INSERT ...
SELECT" form instead.

Considering that the book is somehow dated, is this recommendation
still valid, especially on target sizes of up to 5 million records?


To a large extent, no. The author seems to have had SQL 6.5 in mind, where
SELECT INTO a temptable, indeed to bring a server to a stand still. The
problem is that SELECT INTO creates the temp table, and then goes on to
fill it with data. Since the query is one transaction, it keeps a lock on
the system tables until the query has completed. This was fatal in SQL 6.5
which only had page locks. In SQL7 and SQL2000 where you have row locks,
the impact on other processes is much smaller.

The advantage of SELECT INTO is that is that is minimally logged, so
SELECT INTO #tmp for five million rows can be faster and take less
toll on the server than CREATE TABLE INSERT INTO.

Nevertheless, there are contexts where SELECT INTO is an inferior choice
over CREATE TABLE not talking about a table variable. Say that you already
have a transaction in progress, and you creating many small tables
repeatedly within this transaction. SELECT INTO takes out more locks on
than CREATE TABLE, so you acquire a whole lot more locks with SELECT
INTO, and this can have an impact on performance. Case in point: I had
a procedure which suddently started to perform much slower than before.
This procedure performs some complex data updating in an iterative
fashion. My profiling pointed to a seeminginly innocent query which
appeared to take longer and longer time as the procedure proceeded.
Eventually I found the answer in a trigger (which was not affected by
this query). I had replaced direct deferences to "inserted" with temp
table created through "SELECT * INTO #inserted FROM inserted".

To summarize: SELECT INTO is fine for single-time queries on large
tables. It is bad to have in triggers in tables which maninly are
updated one row at a time.

--
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

0
by: Phil Da Lick! | last post by:
Hi all, I am about to move into programming using an xml web services model. The first thing I need to know is whether you can use them privately within a corporate LAN, or whether you need to...
3
by: Alan | last post by:
I got a book : C# Primer Plus, this is about the C#. I am going to learn Visual C#, another book recommendation about this specific topic ? I don't want a book repeat the C# stuffs already in my...
13
by: Gabriel Reid | last post by:
Greetings, I'm looking for any books that the members of this group may have to recommend for C#. I have experience with a number of programming languages (probably most relevant would have to...
9
by: Reginald Blue | last post by:
I asked this before, although several months ago, and I want to make sure the recommendation hasn't changed. I'm a developer who is working on a Web application, but most of my work is at the...
16
by: Robert Zurer | last post by:
Can anyone suggest the best book or part of a book on this subject. I'm looking for an in-depth treatment with examples in C# TIA Robert Zurer robert@zurer.com
8
by: msnews.microsoft.com | last post by:
I am a C# programmer with a little C++ experience from a few years ago. I need to write some ISAPI filters and extensions to run on the latest version of IIS on .net Server 2003. Can anyone...
2
by: Trisha | last post by:
I am looking for book(s) recommendation on how to design asp.net/c# application from ground up using sound design pattern techniques and good object oriented practices. Examples of projects would...
6
by: ted | last post by:
Hi, I have been programming for about 5 years mainly on client apps that are standalone and n-tier. I have very little experience with web development. I need a recommendation on a book that...
4
by: Huy | last post by:
I am in need of a book recommendation to pick up C++ properly and elegantly. I program extensively within the Python programming language and have a decent grasp of OOP (possibly could be better)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: emmanuelkatto | last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud. Please let me know. Thanks! Emmanuel
0
BarryA
by: BarryA | last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
1
by: Sonnysonu | last post by:
This is the data of csv file 1 2 3 1 2 3 1 2 3 1 2 3 2 3 2 3 3 the lengths should be different i have to store the data by column-wise with in the specific length. suppose the i have to...

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.