Is is faster to filter and get data or filter then get data ?

Question

Hi

I have this kind of request :

SELECT myTable.ID,
myTable.Adress,
-- 20 more columns of all kind of type
FROM myTable
WHERE EXISTS(SELECT * FROM myLink 
    WHERE myLink.FID = myTable.ID 
    and myLink.FID2 = 666)

myLink has a lot of rows.

Do you think it's faster to do like this :

INSERT INTO @result(ID) SELECT myLink.FID 
FROM myLink 
WHERE myLink.FID2 = 666

UPDATE @result SET Adress = myTable.Adress,
    -- 20 more columns of all kind of type 
    FROM myTable 
    WHERE myTable.ID = @result.ID

Answer 1

You will want to select as few rows as possible. Using a where clause to filter data is a good idea, because then the server has to return less data. In the second version, you're selecting only one item of myLink, and then working on it, whereas in the first example youve got a select *, which is usually a bad idea.

Is there anything wrong with:

SELECT myTable.etc from myTable,myLink where myLink.FID2= 666 AND myLink.ID=myTable.ID 

This constructs a join and filters in one step. Perhaps a different kind of join would work more efficiently, but the code sure looks shorter.