"Darrick" wrote:

> I'm trying to determine why a process is failing, but one thing I noticed
> right away is that it takes 40 seconds in SQL 2005 and takes 11 in SQL 2000
> (both standard version), client is VB 6.  I haven't looked through all the
> lines of code, but do know of at least one performance issue in 2005 versus
> 2000 - using NOT IN with a derived table can be slow in 2005:
> SELECT * FROM t1 WHERE c1 NOT IN (SELECT c2 FROM t2...)
>
> This does much better in SQL 2000 in our dataset, than in SQL 2005. 
>
> Any other gotcha's I should know about?  I'm also trying to determine why a
> Tran count complaint is occurring in 2005 and not 2000.

"Darrick" <Misdirection@nospam.com> wrote in message
news:5C9B61BA-B563-4FA6-9EB8-25D5E815F4ED@microsoft.com...
> I'm trying to determine why a process is failing, but one thing I noticed
> right away is that it takes 40 seconds in SQL 2005 and takes 11 in SQL
> 2000
> (both standard version), client is VB 6.  I haven't looked through all the
> lines of code, but do know of at least one performance issue in 2005
> versus
> 2000 - using NOT IN with a derived table can be slow in 2005:
> SELECT * FROM t1 WHERE c1 NOT IN (SELECT c2 FROM t2...)
>
> This does much better in SQL 2000 in our dataset, than in SQL 2005.
>
> Any other gotcha's I should know about?  I'm also trying to determine why
> a
> Tran count complaint is occurring in 2005 and not 2000.

"Uri Dimant" wrote:

> Darrick
>
> Run EXEC sp_updatestats  on SQL Server 2005's database
>
> or
> USE dbname;
> GO
> UPDATE STATISTICS Schema.TableName;
> GO
>
>
>
>
>
>
> "Darrick" <Misdirection@nospam.com> wrote in message
> news:5C9B61BA-B563-4FA6-9EB8-25D5E815F4ED@microsoft.com...
> > I'm trying to determine why a process is failing, but one thing I noticed
> > right away is that it takes 40 seconds in SQL 2005 and takes 11 in SQL
> > 2000
> > (both standard version), client is VB 6.  I haven't looked through all the
> > lines of code, but do know of at least one performance issue in 2005
> > versus
> > 2000 - using NOT IN with a derived table can be slow in 2005:
> > SELECT * FROM t1 WHERE c1 NOT IN (SELECT c2 FROM t2...)
> >
> > This does much better in SQL 2000 in our dataset, than in SQL 2005.
> >
> > Any other gotcha's I should know about?  I'm also trying to determine why
> > a
> > Tran count complaint is occurring in 2005 and not 2000.
>
>
>

"Darrick" wrote:

> Thanks for the suggestion.  I tried this and re-ran the process. First time
> took 42 seconds, second time took 40: no change.
>
> For the past few years we've been learning about optimizing our SQL (at
> least I thought so), and at first we found that recompiles were the primary
> reason for slow throughput.  After reading a few papers on the topic, we did
> several things and got some drastic improvements - this all in SQL 2000 -
> including the following:
>
> NOTE:  We use temporary tables a lot to pass data around our store procedure
> tree.
>
> 1) Put DDL at the top of stored procedure
> 2) Created temporary tables in code before calling the stored procedures,
> and kept the temp tables around, truncating them, rather than letting them be
> destroyed and re-created.
> 3) Added OPTION (KEEP PLAN) or OPTION (KEEPFIXED PLAN) on applicable
> statements involving stored procedures.
> 4) When a statement refused to not recompile, we used sp_executesql to
> isolate the size of the recompile.
>
> Are any of these hurting our SQL 2005 performance?  I notice now that stored
> procedures that used to recompile 1-5 times, now have about 37 recompiles - I
> know they're line level, which are quicker, but I've also seen the same line
> get recompiled more than once for just one execution.  For example, the
> statement below recompiled twice for what sure looked like one execution.
>
> UPDATE wotranwrk
>   SET Quantity = (tt.Quantity + tt.ScrapPcs),
>      @RunHours = CASE WHEN (PcHrHrPc <> 'B' AND EffectPcsHr <> 0)
>                                  OR (PcHrHrPc = 'B' AND PcsHr <> 0)
>     THEN CASE WHEN PcHrHrPc = 'P' THEN (tt.Quantity +  
>                                             tt.ScrapPcs)/wodetl.EffectPcsHr
>           WHEN PcHrHrPc = 'B' THEN CEILING((tt.Quantity +
>                                             tt.ScrapPcs)/wodetl.PcsHr)                    
>        * wodetl.EffectPercent
>           ELSE (tt.Quantity + tt.ScrapPcs) * wodetl.EffectPcsHr
>                   END
>     ELSE 0 END,
>     RunHrs =     @RunHours,
>     RunCost =     CASE WHEN wodetl.OperCostToWip = 'Y'
>         THEN ROUND(@RunHours * wc.WCEstRunRate, @_lUCDecPlaces)
>     ELSE 0 END,
>     TOTHrs = @RunHours,
>     RunVar = CASE WHEN wodetl.OperCostToWip = 'Y'
>     THEN ROUND(@RunHours *(wc.WCVarRunRate + wc.WCStdRunLaborRate),
> @_lUCDecPlaces)
>     ELSE 0 END,
>     RunFix = CASE WHEN wodetl.OperCostToWip = 'Y'
>     THEN ROUND(@RunHours * wc.WCFixedRunRate, @_lUCDecPlaces)
>     ELSE 0 END
>     FROM #tWOTW wotranwrk     WITH (NOLOCK)
>     JOIN tWOD wodetl     WITH (NOLOCK)    
>                           ON (wodetl.WOSKey = wotranwrk.WOSKey)
>     JOIN #Transaction tt    WITH (NOLOCK)    ON               
>                          (wotranwrk.RefTransNum = tt.TransactionKey)
>     JOIN tWCntr wc WITH (NOLOCK)    ON (wodetl.WCKey = wc.WCKey)
>     WHERE wotranwrk.OperationType = 'L' 
>     OPTION (KEEP PLAN)
>
>
> "Uri Dimant" wrote:
>
> > Darrick
> >
> > Run EXEC sp_updatestats  on SQL Server 2005's database
> >
> > or
> > USE dbname;
> > GO
> > UPDATE STATISTICS Schema.TableName;
> > GO
> >
> >
> >
> >
> >
> >
> > "Darrick" <Misdirection@nospam.com> wrote in message
> > news:5C9B61BA-B563-4FA6-9EB8-25D5E815F4ED@microsoft.com...
> > > I'm trying to determine why a process is failing, but one thing I noticed
> > > right away is that it takes 40 seconds in SQL 2005 and takes 11 in SQL
> > > 2000
> > > (both standard version), client is VB 6.  I haven't looked through all the
> > > lines of code, but do know of at least one performance issue in 2005
> > > versus
> > > 2000 - using NOT IN with a derived table can be slow in 2005:
> > > SELECT * FROM t1 WHERE c1 NOT IN (SELECT c2 FROM t2...)
> > >
> > > This does much better in SQL 2000 in our dataset, than in SQL 2005.
> > >
> > > Any other gotcha's I should know about?  I'm also trying to determine why
> > > a
> > > Tran count complaint is occurring in 2005 and not 2000.
> >
> >
> >