Re: Poor performance after upgrading to sql server 2005

I'm not sure how helpful this code will be without the database itself, but
here is a sample. I've included the SQL 2005 execution plan but I do not know
of a way to send the SQL 2000 execution plan. This query takes 0 milliseconds
in SQL 2000 and 233 milliseconds in SQL 2005, even after running it a few
times. The lines of code that cause the slow-down in SQL 20005 are:

AND 1 = CASE WHEN @FREQUENCY = 'W'
THEN ( SELECT 1 WHERE CURR.DATA_ID IN (SELECT DATA_ID FROM
fdINDDATA_DAILY D (NOLOCK)))
ELSE ( SELECT 1)
END


SQL Code:

DECLARE
@SEC_IDS VARCHAR(5000),
@DATE DATETIME,
@FREQUENCY CHAR(1),
@PREVIOUS_DAY DATETIME

DECLARE @SEC_LIST TABLE( SEC_ID INT)
DECLARE @IND_DATA TABLE( SEC_ID INT, DATE DATETIME, PRICE FLOAT, PRICE_PREV
FLOAT, DIVIDEND FLOAT)

SET @SEC_IDS = '2'
SET @DATE = '2006-02-17 08:39:20'
SET @FREQUENCY = 'D'

IF @SEC_IDS = ''
INSERT INTO @SEC_LIST
SELECT SEC_ID
FROM fdSecuritiesMasterList SML (NOLOCK)
WHERE TYPE_ID = 1
AND SEC_ID IN (SELECT ID_DEP_VAR FROM mNAV_REGRESSION WHERE FREQUENCY =
@FREQUENCY)
ELSE
INSERT INTO @SEC_LIST
SELECT ItemValue
FROM dbo.fn_SplitIn2Rows( @SEC_IDS, ',')


-- ** US Tickers ** --
SELECT TOP 1 @PREVIOUS_DAY = CDAY FROM calUSA (NOLOCK) WHERE WDC = (SELECT
WDC - 1 FROM calUSA (NOLOCK) WHERE CDAY = CONVERT(VARCHAR(10), @DATE, 101))
ORDER BY CDAY
-- Get data for indepent index variables
INSERT INTO @IND_DATA( SEC_ID, DATE, PRICE, PRICE_PREV, DIVIDEND)
SELECT DISTINCT R.ID_IND_VAR AS SEC_ID, CURR.[DATE], CURR.VALUE,
PREV.VALUE, ISNULL(RD.AMOUNT, 0)
FROM mvNAV_REGRESSION R (NOLOCK)
JOIN @SEC_LIST L ON R.ID_DEP_VAR = L.SEC_ID
JOIN fdSecuritiesMasterList SML (NOLOCK) ON R.ID_IND_VAR = SML.SEC_ID
JOIN fdEXCHANGES EXCH (NOLOCK) ON SML.EXCH_ID = EXCH.EXCH_ID
JOIN fdINDDATA CURR (NOLOCK) ON SML.SEC_ID = CURR.SEC_ID
JOIN fdINDDATA_DAILY PREV (NOLOCK) ON SML.SEC_ID = PREV.SEC_ID AND
PREV.[DATE] = @PREVIOUS_DAY
LEFT JOIN
(fdSECINFO SI (NOLOCK) JOIN fdDIV RD (NOLOCK) ON SI.SEC_ID = RD.SEC_ID)
ON SML.SEC_ID = SI.SEC_RELATED AND ISNULL(SI.SEC_RELATED, -1) != 1 AND
RD.XDATE = CONVERT(VARCHAR(10), @DATE, 101)
WHERE EXCH.CALENDAR = 'USA'
AND CURR.[DATE] = @DATE
AND CURR.VALUE IS NOT NULL AND PREV.VALUE IS NOT NULL
AND R.FREQUENCY = @FREQUENCY
AND 1 = CASE WHEN @FREQUENCY = 'W'
THEN ( SELECT 1 WHERE CURR.DATA_ID IN (SELECT DATA_ID FROM
fdINDDATA_DAILY D (NOLOCK)))
ELSE ( SELECT 1)
END



Execution Plan:

<?xml version="1.0" encoding="utf-16"?>
<ShowPlanXML xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance";
xmlns:xsd="http://www.w3.org/2001/XMLSchema"; Version="1.0"
Build="9.00.1399.06"
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan";>
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="10" StatementEstRows="1"
StatementId="2" StatementOptmLevel="FULL"
StatementOptmEarlyAbortReason="GoodEnoughPlanFound"
StatementSubTreeCost="0.0132875" StatementText="INSERT INTO @SEC_LIST
SELECT ItemValue
FROM dbo.fn_SplitIn2Rows( @SEC_IDS, ',')


-- ** US Tickers ** --
" StatementType="INSERT">
<StatementSetOptions ANSI_NULLS="false" ANSI_PADDING="false"
ANSI_WARNINGS="false" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="false"
NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="false" />
<QueryPlan DegreeOfParallelism="0" CachedPlanSize="20">
<RelOp AvgRowSize="9" EstimateCPU="2E-06" EstimateIO="0"
EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Sequence"
NodeId="0" Parallel="false" PhysicalOp="Sequence"
EstimatedTotalSubtreeCost="0.0132875">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1"
ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sequence>
<RelOp AvgRowSize="9" EstimateCPU="1.157E-06" EstimateIO="0"
EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1"
LogicalOp="Table-valued function" NodeId="1" Parallel="false"
PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="1.157E-06">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1"
ActualRewinds="0" ActualRows="0" ActualEndOfScans="0" ActualExecutions="1" />
</RunTimeInformation>
<TableValuedFunction>
<DefinedValues />
<Object Database="[FundData]" Schema="[dbo]"
Table="[fn_SplitIn2Rows]" />
<ParameterList>
<ScalarOperator ScalarString="[@SEC_IDS]">
<Identifier>
<ColumnReference Column="@SEC_IDS" />
</Identifier>
</ScalarOperator>
<ScalarOperator ScalarString="','">
<Const ConstValue="','" />
</ScalarOperator>
</ParameterList>
</TableValuedFunction>
</RelOp>
<RelOp AvgRowSize="9" EstimateCPU="1E-06" EstimateIO="0.01"
EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Insert"
NodeId="4" Parallel="false" PhysicalOp="Table Insert"
EstimatedTotalSubtreeCost="0.0132843">
<OutputList />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1"
ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Update>
<Object Table="[@SEC_LIST]" />
<SetPredicate>
<ScalarOperator ScalarString="[SEC_ID] = [Expr1006]">
<ScalarExpressionList />
</ScalarOperator>
</SetPredicate>
<RelOp AvgRowSize="11" EstimateCPU="1E-07"
EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1"
LogicalOp="Top" NodeId="5" Parallel="false" PhysicalOp="Top"
EstimatedTotalSubtreeCost="0.0032833">
<OutputList>
<ColumnReference Column="Expr1006" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1"
ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="true" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(0)">
<Const ConstValue="(0)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="11" EstimateCPU="1E-07"
EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1"
LogicalOp="Compute Scalar" NodeId="6" Parallel="false" PhysicalOp="Compute
Scalar" EstimatedTotalSubtreeCost="0.0032832">
<OutputList>
<ColumnReference Column="Expr1006" />
</OutputList>
<ComputeScalar>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1006" />
<ScalarOperator
ScalarString="CONVERT_IMPLICIT(int,[FundData].[dbo].[fn_SplitIn2Rows].[ItemValue],0)">
<Convert DataType="int" Style="0"
Implicit="true">
<ScalarOperator>
<Identifier>
<ColumnReference
Database="[FundData]" Schema="[dbo]" Table="[fn_SplitIn2Rows]"
Column="ItemValue" />
</Identifier>
</ScalarOperator>
</Convert>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="511" EstimateCPU="0.0001581"
EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0"
EstimateRows="1" LogicalOp="Table Scan" NodeId="7" Parallel="false"
PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831">
<OutputList>
<ColumnReference Database="[FundData]"
Schema="[dbo]" Table="[fn_SplitIn2Rows]" Column="ItemValue" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0"
ActualRows="1" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<TableScan Ordered="false" ForcedIndex="false"
NoExpandHint="false">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[FundData]"
Schema="[dbo]" Table="[fn_SplitIn2Rows]" Column="ItemValue" />
</DefinedValue>
</DefinedValues>
<Object Database="[FundData]" Schema="[dbo]"
Table="[fn_SplitIn2Rows]" />
</TableScan>
</RelOp>
</ComputeScalar>
</RelOp>
</Top>
</RelOp>
</Update>
</RelOp>
</Sequence>
</RelOp>
<ParameterList>
<ColumnReference Column="@SEC_IDS" ParameterRuntimeValue="'2'"
/>
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>



"Andrew J. Kelly" wrote:

The optimizer has changed between 2000 and 2005 so there will certainly be
some types of queries that are better optimized and some that are not. If
either of you can provide the exact code and the execution plans for both
(preferably xml for 2005) I can give them to the dev team to look at. If
there is a problem with how the optimizer is dealing with these it they will
want to know about it and there will be a better chance of fixing it. That
is if it is broke to begin with. It is quite possible a matter of
implementation.

--
Andrew J. Kelly SQL MVP


"Damian" <damiank@xxxxxxxxxxxxxxx> wrote in message
news:1141354358.202211.278990@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
We have just upgraded our development server to sql sever 2005. Dual
processor, 4GB RAM, 64 bit etc.

We also noticed an incredible difference in the execution of stored
procedures that are using correlated subqueries. An interesing note is
that while rewriting one of these stored procedures I created an outer
join to a derived table (the replacement) and while verifying the
results, my performance was back, and the data was correct in both
instances. Comment out the derived table, performance deserts us.

Another interesting thing was pointed out to me today by a co-developer
(as I was going through and re-writing a stack of these) is that an ad
hoc query built and then executed with the EXEC command did not suffer
from the same performance problem.

Do these differences have to do with the statement level compilation
introduced in 2005?

Any thoughts would be appreciated.




.

Relevant Pages

  • Re: Poor performance after upgrading to sql server 2005
    ... Andrew J. Kelly SQL MVP ... I've included the SQL 2005 execution plan but I do not ... <RunTimeInformation> ...
    (microsoft.public.sqlserver.setup)
  • Re: Combining two fast queries produces a slow one
    ... you see that in the execution plan. ... there was no index on workstationid in the ... <RunTimeInformation> ... <DefinedValues> ...
    (microsoft.public.sqlserver.server)
  • Re: Poor performance after upgrading to sql server 2005
    ... SQL 2005 on a machine which is much, much faster in every way than the SQL ... I've included the SQL 2005 execution plan but I do not ... <RunTimeInformation> ... <DefinedValues> ...
    (microsoft.public.sqlserver.setup)
  • Re: Poor performance after upgrading to sql server 2005
    ... I've included the SQL 2005 execution plan but I do not know ... <RunTimeInformation> ... <DefinedValues> ...
    (microsoft.public.sqlserver.setup)
  • Re: Stored Procedures
    ... this is also happening for inline T-SQL, however with a SP this can in the case of a SQL batch and with optimizations much more efficient beside the advantage of encapsulation security etc etc ... ... However when is something called then "Compiled" as our .Net / Java assembly`s are JITTED we just create the SQL counterpart of an execution plan ... "A stored procedure is compiled at execution time, like any other Transact-SQL statement" ... "A stored procedure is not saved in its compiled version outside the cache, the same as any other Transact-SQL statement" ...
    (microsoft.public.dotnet.languages.vb)