Query optimize

can this be rewritten in more optimized way..
DELETE FROM A WHERE ID Not In (SELECT ID FROM @.ABC)DELETE FROM A T WHERE ID Not exists (SELECT ID FROM @.ABC where ID=T.ID)
Madhivanan|||You cannot use a variable table name. Indexes will help, but the
optimizer is msart enough to pick a good plan.|||You could possibly try:
DELETE def
WHERE NOT EXISTS
(
SELECT 1
FROM abc
WHERE abc.ID = def.ID
)
Frank
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:1BF75571-9841-41EC-95EF-16D6837095C9@.microsoft.com...
> can this be rewritten in more optimized way..
> DELETE FROM A WHERE ID Not In (SELECT ID FROM @.ABC)|||delete A
from A
left join B
on B.CommonKey = A.CommonKey
where (B.CommonKey is null)
ML|||On Thu, 11 Aug 2005 06:54:01 -0700, Sunny wrote:

> can this be rewritten in more optimized way..
> DELETE FROM A WHERE ID Not In (SELECT ID FROM @.ABC)
As Celko said, you can't use a variable table name directly. The best you
can do is dynamic SQL:
declare @.abc nvarchar(32)
declare @.sql nvarchar(500)
set @.abc='MyTable'
set @.sql = N'DELETE FROM A WHERE NOT EXISTS (SELECT * FROM ' + @.ABC + ' B
WHERE A.ID=B.ID)'
execute sp_executesql @.sql|||Ah, I must have just breezed through the DML statement. I didn't catch the
@.. Sunny, if you were using a declared table variable by design, you should
follow the advice in the posts that followed mine.
Frank Castora.
"Sunny" <Sunny@.discussions.microsoft.com> wrote in message
news:1BF75571-9841-41EC-95EF-16D6837095C9@.microsoft.com...
> can this be rewritten in more optimized way..
> DELETE FROM A WHERE ID Not In (SELECT ID FROM @.ABC)|||Before you issue blanket statements like the one below, you should check
your facts. You can indeed use a variable table name, provided the variable
table name refers to an existing table variable. Table variables are a
feature of SQL Server 2000. You refer to a table variable in the same way
as a scalar variable.
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1123769246.878870.169080@.g44g2000cwa.googlegroups.com...
> You cannot use a variable table name. Indexes will help, but the
> optimizer is msart enough to pick a good plan.
>

Query Optimization

IS there any way to rewrite this Query in optimized way?

SELECT dbo.Table1.EmpId E from dbo.Table1
where EmpId in(
SELECT dbo.Table1.EmpId
FROM (SELECT DISTINCT PersonID, MAX(dtmStatusDate) AS dtmStatusDate
FROM dbo.Table1
GROUP BY PersonID) derived_table INNER JOIN
dbo.Table1 ON derived_table.PersonID = dbo.Table1.PersonID AND
derived_table.dtmStatusDate = dbo.Table1.dtmStatusDate))

Thanks...jDon't know abiut being faster but I think this is what oyu are trying to do. (get the empid's with max(dtmStatusDate) from each person.

SELECT t1.EmpId
from dbo.Table1 t1
where t1.dtmStatusDate =
(select max(dtmStatusDate) from dbo.Table1 t2 where t1.PersonID = t2.PersonID)

also try

SELECT t1.EmpId
from dbo.Table1 t1
where not exists ( select * from dbo.Table1 t2 where t1.PersonID = t2.PersonID and t1.dtmStatusDate < t2.dtmStatusDate)