Djabarov, Robert
Robert.Djabarov at usaa.com
Wed Feb 26 08:54:41 CST 2003
The "side effects" that I thought we are talking about here are performance and contingency...and you're talking about syntax errors? First of all, your first example in a sense deletes all records from TitleAuthor, because your IN clause actually selects AU_ID from TitleAuthor, not from Publishers, as it might seem (unfortunately, I don't see the purpose of this IN clause in this particular case at all). In fact, your IN clause will select AU_ID as many times as there are rows in Publishers... Would you ever consider doing it in real life?
What I was talking about was the impact of IN/NOT IN/NOT EXISTS vs. EXISTS/LEFT OUTER JOIN. Since you started withy IN vs. EXISTS, here's the sample that you can run in QA and see the difference in performance for yourself:
select * from authors where au_id in (
select au_id from titleauthor)
go
select * from authors a where exists (
select * from titleauthor t where a.au_id = t.au_id)
go
Just turn Statistics Time on in your Connection Options. In mine I see 19ms delay in the first query vs. the second.
______________________________________________________
Robert Djabarov
Certified MS SQL Server DBA
Certified MS SQL Server Programmer
Certified MS VB Programmer
? (210) 913-3148 - phone
( (210) 753-3148 - pager
-----Original Message-----
From: Francisco H Tapia [mailto:my.lists at verizon.net]
Sent: Tuesday, February 25, 2003 6:11 PM
To: dba-sqlserver at databaseadvisors.com
Subject: Re: [dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite Urgent.
NOT EXIST isn't exposed to the same side effects... Take the following for
an example...
BEGIN TRANSACTION
SELECT COUNT(*) FROM Titleauthor
DELETE Titleauthor
WHERE au_id IN ( SELECT au_id FROM Publishers)
SELECT COUNT(*) FROM Titleauthor
ROLLBACK
however this statement dosent error out, true it's the EXISTS not the NOT
EXISTS but the side-effects are the same...
BEGIN TRANSACTION
SELECT COUNT(*) FROM Titleauthor
DELETE Titleauthor
WHERE EXISTS(SELECT au_id FROM Publishers
WHERE Publishers.au_id = Titleauthor.au_id)
SELECT COUNT(*) FROM Titleauthor
ROLLBACK
note that you now get the invalid column au_id with the EXISTS
statement...which is at it should be... the first IN statement doesn't give
this feedback, a workaround is to declare the au_id in the IN statement as
Publisher.au_id in order to cause it to explicilty resolve in which case
you'd get the error..
-Francisco
http://rcm.netfirms.com
----- Original Message -----
From: "Djabarov, Robert" <Robert.Djabarov at usaa.com>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Tuesday, February 25, 2003 12:19 PM
Subject: RE: [dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite
Urgent.
: NOT IN has the same side effects as NOT EXISTS. It can be easily
re-written as follows:
:
: insert into tableA (Payroll, JobDate, JobType)
: select B.Payroll, B.JobDate, B.JobType
: from tableB B left outer join (
: select A.Payroll, A.JobDate, A.JobType
: from tableA A
: inner join tableB tb
: on A.Payroll = tb.Payroll
: AND A.Jobdate = tb.JobDate
: AND A.JobType = tb.JobType
: ) t on B.Payroll = t.Payroll
: and B.JobDate = t.JobDate
: and B.JobType = t.JobType
: where t.Payroll is null
: and t.JobDate is null
: and t.JobType is null
:
:
: ______________________________________________________
: Robert Djabarov
: Certified MS SQL Server DBA
: Certified MS SQL Server Programmer
: Certified MS VB Programmer
: ? (210) 913-3148 - phone
: ( (210) 753-3148 - pager
:
: -----Original Message-----
: From: Francisco H Tapia [mailto:my.lists at verizon.net]
: Sent: Tuesday, February 25, 2003 10:36 AM
: To: dba-sqlserver at databaseadvisors.com; AccessD;
dba-VB at databaseadvisors.com
: Subject: Re: [dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite
Urgent.
:
: How about ....
:
: Insert into tableA (Payroll, jobdate, jobtype)
: Select Payroll, jobdate, jobtype
: From tableB AS B
: WHERE NOT EXISTS( Select Payroll, jobdate, jobtype
: From tableA AS A
: Where B.Payroll = A.Payroll
AND
: B.Jobdate = A.JobDate AND B.JobType = A.JobType)
:
: I could have used a NOT IN clause but then NOT IN can have some serious
side
: effects...In General the above statement will be evaluated by the engine
: where as a NOT IN could miss evaluating the B alias in the where clause
and
: cause it to report bad data, using Exists (or NOT EXISTS) usually yields
: faster performance and the assurance that it evaluates correctly.
: -Francisco
: http://rcm.netfirms.com
: ----- Original Message -----
: From: <paul.hartland at fsmail.net>
: To: <dba-SQLServer at databaseadvisors.com>; <accessd at databaseadvisors.com>;
: <dba-vb at databaseadvisors.com>
: Sent: Tuesday, February 25, 2003 1:42 AM
: Subject: [dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite
Urgent.
:
:
: : To all,
: :
: : Watch for Word Wrap...............
: :
: : Being quite a novice with Stored Procedures on SQL Server 7.0, I'm
looking
: for some pretty urgent help at the moment. I have two tables, lets say
: Table A & Table B. Table A is my master table, and Table B is a temporary
: table. Before Table be gets populated with any data, every existing
record
: is deleted so I start with a fresh table so to speak. Table B consists of
3
: fields PayrollNo, JobDate, JobType (these fields are also in the master
: table, but the master table is used to contain other consting information,
: which is entered at a later date).
: :
: : What I would like is a Stored Procedure which would Insert Table B
: contents into Table A, but making sure that I don't insert any duplicates
: into Table A (something like an unmatched query in access but with an
Insert
: also). i.e.
: :
: : Table A Table B
: : payroll jobdate jobtype id
payroll
: jobdate jobtype
: : 999001 01/01/01 0 1 999001
: 01/01/01 0
: : 999001 01/01/01 2 2 999001
: 01/01/01 1
: : 999002 01/01/01 2 3 999001
: 01/01/01 2
: : 4 999002
: 01/01/01 0
: : 5 999002
: 01/01/01 1
: : 6 999002
: 01/01/01 2
: :
: : So when I invoke the stored procedure, it would insert records 2, 4 and
5
: from Table B into Table A........Anyone any idea what I'm on about, if so
: can anybody please help.......
: :
: : Thanks in advance
: :
: : Paul Hartland
: :
: :
__________________________________________________________________________
: : Freeserve AnyTime - Go online whenever you want for just £6.99 a month
for
: : your first 3 months, that's HALF PRICE! And then it's just £13.99 a
month
: : after that.
: :
: : For more information visit http://www.freeserve.com/time/ or call free
on
: : 0800 970 8890
: :
: :
: : _______________________________________________
: : dba-SQLServer mailing list
: : dba-SQLServer at databaseadvisors.com
: : http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: : http://www.databaseadvisors.com
: :
:
:
: _______________________________________________
: dba-SQLServer mailing list
: dba-SQLServer at databaseadvisors.com
: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: http://www.databaseadvisors.com
:
:
:
: _______________________________________________
: dba-SQLServer mailing list
: dba-SQLServer at databaseadvisors.com
: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: http://www.databaseadvisors.com
:
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com