Sql Job runs Slow in 2008 compared to 2005

[callenr41]

The following job runs in under 10 seconds in 2005 but takes 3 minutes in
2008.

update Flavor.dbo.Inventory set Flavor.dbo.Inventory.user1 = 'PS' where
Flavor.dbo.Inventory.InvtID in(
select distinct FP.FormulaID
from Flavsys.dbo.FormulaProcedures FP, Flavor.dbo.Inventory I, Flavsys.
dbo.Formulas F
where FP.FormulaID = I.InvtID
and F.FormulaID = FP.FormulaID
and F.VersionNumber = FP.VersionNumber
and F.ActiveVersion = '1'
and F.PrimaryVersion = '1'
and FP.ComponentID in (select I.InvtID from Flavor.dbo.Inventory I where
User1 ='PS')
and FP.FormulaID not in (select I.InvtID from Flavor.dbo.Inventory I
where User1 = 'PS')
)

If i change it to a select statement it complete in less than a second. I
am not sure how to fix this or where to look. Any help would be appreciated

[sloan]

I would experiment with swapping out to EXISTS and NOT EXISTS
clauses...instead of IN and NOT IN.

............
(IN Example)

Select * from dbo.Product where ProductionID IN (Select ProductID from
dbo.Orders where CustomerID = 'ABC' )

.....
(EXISTS example)

Select * from dbo.Product outerPAlias where exists ( Select null from
dbo.Orders innerOAlias where
/* relationship to "outside" */
innerOAlias.ProductID = outerPAlias.ProductID
----------------
and /* filter the results /* innerOAlias.CustomerID = 'ABC')

........

95% of the time (in my experience) the EXISTS works better than the IN.
However, don't take my word for it. Learn how to write them and test them
for performance.

"callenr41" <u56146@uwe> wrote in message news:9f030269ccd81@uwe...

[TheSQLGuru]

1) is this repeatable?

2) did you update all statistics with fullscan when you upgraded to 2008?

3) did you check for blocking? IO stalls?

4) is the hardware and setting identical?

5) are you patched up completely on sql 2008?

6) did you try OPTION (RECOMPILE)? could be a bad plan in cache

7) have you shrunk the database, thus causing massive internal
fragmentation?

--
Kevin G. Boles
Indicium Resources, Inc.
SQL Server MVP
kgboles a earthlink dt net

"callenr41" <u56146@uwe> wrote in message news:9f030269ccd81@uwe...