Which one is better - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Which one is better

Author

Topic

CanadaDBA

583 Posts

Posted - 2003-05-15 : 13:20:52

Hi,

I have:

IF Exists ( SELECT Filed1 FROM Table1 WHERE Field1 = Something)
< do something>
IF Exists ( SELECT Filed2 FROM Table1 WHERE Field1 = Something)
< do something>
IF Exists ( SELECT Filed3 FROM Table1 WHERE Field1 = Something)
< do something>

Is it better to write as follow?
SELECT @F1=Filed1, @F2=Filed2, @F3=Filed3
FROM Table
WHERE Field1 = Something
IF Field1 <> Null
< do something>
IF Field2 <> Null
< do something>
IF Field3 <> Null
< do something>

I believe that in 1st situation, 3 times the SELECT is executed but only sets Exists or not Exists and the SELECT do not return values (So it runs faster). In second situation, One time the SELECT is executed but returns values.

Which one is faster? or better?

Regards,
Farhad

X002548
Not Just a Number

15586 Posts

Posted - 2003-05-15 : 13:28:11

uhhhh

That's not the way it works for the first example. Existance is for a row, not a null or not null column.

And what is the do something? It may be you can do this all as 1 SQL statement.

But in CASE (get the pun) you still have to "do something", your syntax for your second choice has to be altered.

Something Like:


SELECT    @F1=Field1
	, @F2=Field2
	, @F3=Field3 
  FROM    Table 
 WHERE    Field1 = Something 

IF @F1 IS NULL
	BEGIN
		< do something>
	END 
IF @F2 IS NULL
	BEGIN
		< do something>
	END 
IF @F3 IS NULL
	BEGIN
		< do something>
	END

Brett

8-)

Edited by - x002548 on 05/15/2003 13:29:11

nr
SQLTeam MVY

12543 Posts

Posted - 2003-05-15 : 14:09:38

You need
Depends on the indexes, what else is running, ...
If the table is large and not indexed for the query then the selects will all table scan and so be very slow.
If the table is indexed for the query and the data is retained in memory then the selects will be quick. If the data is not kept in memory then the selects will be slower as will require disk access.

IF Field1 <> Null s.b. IF Field1 is not Null

I suspect you need
IF Exists (SELECT * FROM Table1 WHERE Fieldx = Something and Filed1 is not null)
IF Exists (SELECT * FROM Table1 WHERE Fieldx = Something and Filed2 is not null)

and
SELECT @F1=Filed1, @F2=Filed2, @F3=Filed3
FROM Table
WHERE Fieldx = Something

To have comparitive queries.

I would do the second.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources