Does the table have a clustered index ?

If it doesn't, then the 'shrinking' you might already have tried (like DBCC DbReindex() ) will not have done anything.

>>It only happens when it tries to run as a scheduled job??? Could you please tell me what can be the problem?

The scheduled job doesn't run as "you". Therefore it...

>>It means there is no need to put column name in Same order in which INDEX is created for the table ?

No, it doesn't mean that. Order of columns in...

What is the source of the DDQ, the target of the DDQ and the connection used for the lookup ?

If they are all tables/views/sprocs on the same SQL instance, then...

>>but these same tables have an index on the same column.

Are the other indexes single-column indexes that are identical to the PK, or are they composite indexes that include the...

Option 1 - use NOT EXISTS

SELECT * FROM Table1 As t1

WHERE NOT EXISTS

(

  SELECT * FROM Table2 As t2

  WHERE t2.FNAME = t1.FNAME

  AND     t2.LNAME = t1.LNAME

)

Option 2...

Why subtract 3 milliseconds (thereby generating a time component) if the stated goal is ... no time component ?

select DateAdd(dd, -1, DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))

Nope, that doesn't do it. HAVING needs aggregates to work with.

Join to a derived table that gives the dupes:

Select Distinct t.*

From YourTable As t

Inner Join

(

  Select FirstName, MiddleName, LastName, DateOfBirth

 ...

What is the criteria for deletion ? A date/time column older than a certain time period ?

Try deleting in smaller batches using the deletion criteria and using SET ROWCOUNT

-- Delete...

It is often (but not always) preferrable to get the data into the target table as a single INSERT, rather than creating the records needed then running multiple UPDATEs.

Reason for...

Use DTS features like Lookups and DataPumps as little as possible. Their performance is orders of magnitudes slower than a plain T-SQL INSERT INTO ... SELECT ... statement. Debuggging them is...

Use left join:

LEFT Join

-- Join to derived table that assembles QtyFail for each key

(

select  inmast.fpartno, inmast.frev , 

  rcitem.freceiver+rcitem.fitemno as Receiver,

  -- QtyFail comes from derived table, not sub-select

  dt.QtyFail,

  rcitem.fqtyrecv, rcitem.fucost

from inmast

inner Join rcitem

  -- Any particular reason why...

>>(Select Sum(rcinsp.fnqtyfail) from rcinsp Where rcinsp.fcreceiver+rcinsp.fcitemno = rcitem.freceiver+rcitem.fitemno) as QtyFail,

Get this sub-select out of the SELECT and move it into the FROM. Sub-selects within a SELECT create a cursor-like query...

>>I've run the simple sp_get_admin_email separately and it returns exactly what I want.

What is it returning ? Is it a resultset that you can view ?

To assign the results to...