I'm going to be looping round a stringgrid and depending on user selections will run different sql. One would be to delete all the calls linked to a project where a call hasn't been made. Where a call has been made an entry will be in CallStats. There may be many entries for any given call in CallStats. My first reaction  is to use DISTINCT but I'm not sure its going to be the most effective and an execution plan doesn't elp me much, neither does trying to spot the timings.

Any comments?

DELETE FROM Calls
WHERE
NOT _CallID IN (SELECT DISTINCT _fkCalls FROM CallStats WHERE _fkProjects = 122)
AND
_fkProjects = 122


DELETE FROM Calls
WHERE
NOT _CallID IN (SELECT _fkCalls FROM CallStats WHERE _fkProjects = 122)
AND
_fkProjects = 122

Roy Lambert

Roy
> I'm going to be looping round a stringgrid and depending on user
selections will run different sql. One would be to delete all the calls
linked to a project where a call hasn't been made. Where a call has been
made an entry will be in CallStats. There may be many entries for any given
call in CallStats. My first reaction is to use DISTINCT but I'm not sure its
going to be the most effective and an execution plan doesn't elp me much,
neither does trying to spot the timings.
>
> Any comments?

There probably isn't  much in it but intuitively "exists" seems the way to
go.

DELETE FROM Calls
WHERE
NOT EXISTS (SELECT _fkCalls FROM CallStats WHERE _fkProjects = 122)
AND
fkProjects = 122

John

John


It may be intuitive but it produces different results

Replacing the DELETE with SELECT * for purposes of data preservation <giggle> I get 122 rows using either version of my code and none with yours.

Roy Lambert

Roy

> It may be intuitive but it produces different results

oops - missed a bit (which will definitely make it slower)

SELECT * FROM Calls
WHERE
NOT EXISTS (SELECT _fkCalls FROM CallStats WHERE _fkProjects = 122 AND
Calls._Callid=CallStats._fkCalls)
AND
fkProjects = 122

John