Sunday, March 25, 2012
Books on Hierarchies
I would like learn Hierarchies in "SQL". I do have the Transact-SQL Cookbook
by Jonathan Gennick.
Are there any other book that talks about this topic that I should read.
Also, if anyone can point to some
links that would be great as well.
Thanks,
GopiTREES & HIERARCHIES IN SQL by Joe Celko|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117821936.831757.296570@.z14g2000cwz.googlegroups.com...
> TREES & HIERARCHIES IN SQL by Joe Celko
>
Not to toot Joe's horn, but that is an awesome book!!!
Check out SQL for Smarties by Joe Celko as well.
Great stuff.
Rick Sawtell
MCT, MCSD, MCDBA|||"Rick Sawtell" <r_sawtell@.hotmail.com> wrote in message
news:eOlDAiGaFHA.1040@.TK2MSFTNGP10.phx.gbl...
> "--CELKO--" <jcelko212@.earthlink.net> wrote in message
> news:1117821936.831757.296570@.z14g2000cwz.googlegroups.com...
> Not to toot Joe's horn, but that is an awesome book!!!
> Check out SQL for Smarties by Joe Celko as well.
> Great stuff.
Shame he's so rude to people in real life. I'd love to have a look at his
books as they seem so raved about but I don't think I could bring myself to
buy something from someone who's so needlessly rude to so many people.|||>> Shame he's so rude to people in real life. <<
Wrong. I am a pussy cat in person. I have a deliberately advosar
Wrong. I am a pussy cat in person. I have a deliberately adversarial
newsgroup personality. After ten or so years, you get tired whining
"do my homework/job for me", or "give me a kludge so I can kill
people" postings.
The first group of whiners is why Chris Date does not answer email
address. I turn them into their schools and have them expelled for
academic cheating (2 and maybe 3 so far)
The second group is more of a problem. In 2004, it was kid who threw
out 1NF on a medical supply system for African relief. He was more
bothered by the fact that I was rude to him than that his incompetent
was killing children in a war zone.
Grow up. See a few episodes of HOUSE. Then get professional.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1117848393.903206.188650@.g44g2000cwa.googlegroups.com...
> Wrong. I am a pussy cat in person. I have a deliberately adversarial
> newsgroup personality.
Well that's fine, but some people are coming on here to develop and learn
from other people, your style puts people off. You don't come across as a
friendly and helpful character. (and presumably you must be at heart if you
like looking into people's sql problems and solving them for free) Oh I
dunno, maybe I'm too patient with people myself... anyway self perception
analysis is a bit off topic so I'll shut up.sql
Thursday, March 22, 2012
Book recommendation on performance
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?php newbie (newtophp2000@.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, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp