October 25, 2007 at 5:15 am
Hi Guys,
Does the following give any benefits in execution time for queries. This is with thousands of rows of data and executed a large number of times within a batch job
IF EXISTS(
SELECT 'xdummy'
FROM dbo.Table
WHERE TableCode = @TableCode
AND OneValue = 'T'
AND AnotherValue= 'F'
)
over
IF EXISTS(
SELECT *
FROM dbo.Table
WHERE TableCode = @TableCode
AND OneValue = 'T'
AND AnotherValue= 'F' )
There is about 20 columns in this table so I was wondering does the SQL engine evaluate the * and try and return back the columns?
Thanks guys,
M
October 25, 2007 at 5:21 am
EXISTS will only check for the existence of rows in the sub-query and it will not return any rows....and also it doen't matter if you specify a single column or * in an EXISTS sub-query
--Ramesh
October 25, 2007 at 5:29 am
Oh OK so.
thanks Ramesh
October 25, 2007 at 5:30 am
Try this..... I'm not sure whether this will help in fine tuning your query... Still thought of sharing you the knowledge.
=======================================================================
Subqueries introduced with EXISTS and NOT EXISTS can be used for two set-theory operations: intersection and difference. The intersection of two sets contains all elements that belong to both of the original sets. The difference contains elements that belong only to the first of the two sets.
The intersection of authors and publishers over the city column is the set of cities in which both an author and a publisher are located.
USE pubs
SELECT DISTINCT city
FROM authors
WHERE EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)
Here is the result set:
city
--------
Berkeley
(1 row(s) affected)
Of course, this query could be written as a simple join.
USE pubs
SELECT DISTINCT authors.city
FROM authors INNER JOIN publishers
ON authors.city = publishers.city
The difference between authors and publishers over the city column is the set of cities where an author lives but no publisher is located, that is, all the cities except Berkeley.
USE pubs
SELECT DISTINCT city
FROM authors
WHERE NOT EXISTS
(SELECT *
FROM publishers
WHERE authors.city = publishers.city)
This query could also be written as:
USE pubs
SELECT DISTINCT city
FROM authors
WHERE city NOT IN
(SELECT city
FROM publishers)
October 25, 2007 at 6:51 am
Thanks for the input will keep it in mind!
Martin
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply