[dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite Urgent.

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






More information about the dba-SQLServer mailing list