SQL Server Performance

Improve query performance (sorting)

Discussion in 'SQL Server 2005 Performance Tuning for DBAs' started by samham, Feb 5, 2008.

  1. samham New Member

    Hello, i have the following query:SELECT
    splitText, FileCount, Frequency
    FROM (SELECT splitText FROM dbo.SplitString(@Words,'|')) AS tblKeys
    LEFT OUTER JOIN Keyword ON Keyword.word = tblKeys.splitText
    LEFT OUTER JOIN KeywordFrequency ON Keyword.ID = KeywordFrequency.KeywordID
    ORDER BY FileCount DESC, Frequency DESC
    I have an index on FileCount desc,Frequency Desc
    but the execution plan shows a sort on fileCount and Frequency.
    How can i make the query use indexes for sorting?

    (FileCount and Frequency are in the KeywordFrequency table)
    Thanks,
    Sam
  2. FrankKalis Moderator

    Can you post the execution plan?
  3. samham New Member

    Thanks, here's the 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.3054.00" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
    <BatchSequence>
    <Batch>
    <Statements>
    <StmtSimple StatementCompId="4" StatementEstRows="1.14409" StatementId="110" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.0246546" StatementText="SELECT splitText,FileCount, Frequency&#xD;&#xA;FROM (SELECT splitText FROM dbo.SplitString(@Words,'|')) AS tblKeys&#xD;&#xA;LEFT OUTER JOIN Keyword ON Keyword.word = tblKeys.splitText&#xD;&#xA;LEFT OUTER JOIN KeywordFrequency ON Keyword.ID = KeywordFrequency.KeywordID&#xD;&#xA;ORDER BY FileCount DESC, Frequency DESC&#xD;&#xA; &#xD;" StatementType="SELECT">
    <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="1" MemoryGrant="102" CachedPlanSize="32" CompileTime="24" CompileCPU="24" CompileMemory="224">
    <RelOp AvgRowSize="4019" EstimateCPU="2.14409E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.14409" LogicalOp="Sequence" NodeId="0" Parallel="false" PhysicalOp="Sequence" EstimatedTotalSubtreeCost="0.0246546">
    <OutputList>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[SplitString]" Column="splitText" />
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="Frequency" />
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="FileCount" />
    </OutputList>
    <RunTimeInformation>
    <RunTimeCountersPerThread Thread="0" ActualRows="108" 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="[MyDB]" Schema="[dbo]" Table="[SplitString]" />
    <ParameterList>
    <ScalarOperator ScalarString="[@Words]">
    <Identifier>
    <ColumnReference Column="@Words" />
    </Identifier>
    </ScalarOperator>
    <ScalarOperator ScalarString="'|'">
    <Const ConstValue="'|'" />
    </ScalarOperator>
    </ParameterList>
    </TableValuedFunction>
    </RelOp>
    <RelOp AvgRowSize="4019" EstimateCPU="0.000104366" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.14409" LogicalOp="Sort" NodeId="4" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0246513">
    <OutputList>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[SplitString]" Column="splitText" />
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="Frequency" />
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="FileCount" />
    </OutputList>
    <MemoryFractions Input="0.333333" Output="1" />
    <RunTimeInformation>
    <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="108" ActualEndOfScans="1" ActualExecutions="1" />
    </RunTimeInformation>
    <Sort Distinct="false">
    <OrderBy>
    <OrderByColumn Ascending="false">
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="FileCount" />
    </OrderByColumn>
    <OrderByColumn Ascending="false">
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="Frequency" />
    </OrderByColumn>
    </OrderBy>
    <RelOp AvgRowSize="4019" EstimateCPU="4.78229E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.14409" LogicalOp="Inner Join" NodeId="5" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.0132856">
    <OutputList>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[SplitString]" Column="splitText" />
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="Frequency" />
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="FileCount" />
    </OutputList>
    <RunTimeInformation>
    <RunTimeCountersPerThread Thread="0" ActualRows="108" ActualEndOfScans="1" ActualExecutions="1" />
    </RunTimeInformation>
    <NestedLoops Optimized="false">
    <PassThru>
    <ScalarOperator ScalarString="[IsBaseRow1006] IS NULL">
    <Compare CompareOp="IS">
    <ScalarOperator>
    <Identifier>
    <ColumnReference Column="IsBaseRow1006" />
    </Identifier>
    </ScalarOperator>
    <ScalarOperator>
    <Const ConstValue="NULL" />
    </ScalarOperator>
    </Compare>
    </ScalarOperator>
    </PassThru>
    <OuterReferences>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="ID" />
    </OuterReferences>
    <RelOp AvgRowSize="4020" EstimateCPU="4.78229E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.14409" LogicalOp="Left Outer Join" NodeId="6" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00985842">
    <OutputList>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[SplitString]" Column="splitText" />
    <ColumnReference Column="IsBaseRow1006" />
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="ID" />
    </OutputList>
    <RunTimeInformation>
    <RunTimeCountersPerThread Thread="0" ActualRows="108" ActualEndOfScans="1" ActualExecutions="1" />
    </RunTimeInformation>
    <NestedLoops Optimized="true">
    <OuterReferences>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Keyword]" Column="ID" />
    </OuterReferences>
    <RelOp AvgRowSize="4019" EstimateCPU="4.18E-06" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Left Outer Join" NodeId="8" Parallel="false" PhysicalOp="Nested Loops" EstimatedTotalSubtreeCost="0.00657038">
    <OutputList>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[SplitString]" Column="splitText" />
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Keyword]" Column="ID" />
    </OutputList>
    <RunTimeInformation>
    <RunTimeCountersPerThread Thread="0" ActualRows="108" ActualEndOfScans="1" ActualExecutions="1" />
    </RunTimeInformation>
    <NestedLoops Optimized="true">
    <OuterReferences>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[SplitString]" Column="splitText" />
    </OuterReferences>
    <RelOp AvgRowSize="4011" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Table Scan" NodeId="10" Parallel="false" PhysicalOp="Table Scan" EstimatedTotalSubtreeCost="0.0032831">
    <OutputList>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[SplitString]" Column="splitText" />
    </OutputList>
    <RunTimeInformation>
    <RunTimeCountersPerThread Thread="0" ActualRows="108" ActualEndOfScans="1" ActualExecutions="1" />
    </RunTimeInformation>
    <TableScan Ordered="false" ForcedIndex="false" NoExpandHint="false">
    <DefinedValues>
    <DefinedValue>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[SplitString]" Column="splitText" />
    </DefinedValue>
    </DefinedValues>
    <Object Database="[MyDB]" Schema="[dbo]" Table="[SplitString]" />
    </TableScan>
    </RelOp>
    <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Index Seek" NodeId="11" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.0032831">
    <OutputList>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Keyword]" Column="ID" />
    </OutputList>
    <RunTimeInformation>
    <RunTimeCountersPerThread Thread="0" ActualRows="35" ActualEndOfScans="73" ActualExecutions="108" />
    </RunTimeInformation>
    <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
    <DefinedValues>
    <DefinedValue>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Keyword]" Column="ID" />
    </DefinedValue>
    </DefinedValues>
    <Object Database="[MyDB]" Schema="[dbo]" Table="[Keyword]" Index="[IX_Keyword]" />
    <SeekPredicates>
    <SeekPredicate>
    <Prefix ScanType="EQ">
    <RangeColumns>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Keyword]" Column="Word" />
    </RangeColumns>
    <RangeExpressions>
    <ScalarOperator ScalarString="[MyDB].[dbo].[SplitString].[splitText]">
    <Identifier>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[SplitString]" Column="splitText" />
    </Identifier>
    </ScalarOperator>
    </RangeExpressions>
    </Prefix>
    </SeekPredicate>
    </SeekPredicates>
    </IndexScan>
    </RelOp>
    </NestedLoops>
    </RelOp>
    <RelOp AvgRowSize="16" EstimateCPU="0.000158258" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1.14409" LogicalOp="Index Seek" NodeId="12" Parallel="false" PhysicalOp="Index Seek" EstimatedTotalSubtreeCost="0.00328326">
    <OutputList>
    <ColumnReference Column="IsBaseRow1006" />
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="ID" />
    </OutputList>
    <RunTimeInformation>
    <RunTimeCountersPerThread Thread="0" ActualRows="35" ActualEndOfScans="108" ActualExecutions="108" />
    </RunTimeInformation>
    <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
    <DefinedValues>
    <DefinedValue>
    <ColumnReference Column="IsBaseRow1006" />
    </DefinedValue>
    <DefinedValue>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="ID" />
    </DefinedValue>
    </DefinedValues>
    <Object Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Index="[IX_KeywordFrequency]" />
    <SeekPredicates>
    <SeekPredicate>
    <Prefix ScanType="EQ">
    <RangeColumns>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="KeywordID" />
    </RangeColumns>
    <RangeExpressions>
    <ScalarOperator ScalarString="[MyDB].[dbo].[Keyword].[ID]">
    <Identifier>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[Keyword]" Column="ID" />
    </Identifier>
    </ScalarOperator>
    </RangeExpressions>
    </Prefix>
    </SeekPredicate>
    </SeekPredicates>
    </IndexScan>
    </RelOp>
    </NestedLoops>
    </RelOp>
    <RelOp AvgRowSize="15" EstimateCPU="0.0001581" EstimateIO="0.003125" EstimateRebinds="0.144089" EstimateRewinds="0" EstimateRows="1" LogicalOp="Clustered Index Seek" NodeId="14" Parallel="false" PhysicalOp="Clustered Index Seek" EstimatedTotalSubtreeCost="0.00342208">
    <OutputList>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="Frequency" />
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="FileCount" />
    </OutputList>
    <RunTimeInformation>
    <RunTimeCountersPerThread Thread="0" ActualRows="35" ActualEndOfScans="0" ActualExecutions="35" />
    </RunTimeInformation>
    <IndexScan Lookup="true" Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" NoExpandHint="false">
    <DefinedValues>
    <DefinedValue>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="Frequency" />
    </DefinedValue>
    <DefinedValue>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="FileCount" />
    </DefinedValue>
    </DefinedValues>
    <Object Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Index="[PK_KeywordFrequency]" TableReferenceId="-1" />
    <SeekPredicates>
    <SeekPredicate>
    <Prefix ScanType="EQ">
    <RangeColumns>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="ID" />
    </RangeColumns>
    <RangeExpressions>
    <ScalarOperator ScalarString="[MyDB].[dbo].[KeywordFrequency].[ID]">
    <Identifier>
    <ColumnReference Database="[MyDB]" Schema="[dbo]" Table="[KeywordFrequency]" Column="ID" />
    </Identifier>
    </ScalarOperator>
    </RangeExpressions>
    </Prefix>
    </SeekPredicate>
    </SeekPredicates>
    </IndexScan>
    </RelOp>
    </NestedLoops>
    </RelOp>
    </Sort>
    </RelOp>
    </Sequence>
    </RelOp>
    <ParameterList>
    <ColumnReference Column="@Words" ParameterCompiledValue="N'الاهو|اهؤ|اه|احو|احؤ|اح|آهو|آهؤ|آه|آحو|آحؤ|آح|هو|هؤ|ه|حو|حؤ|ح|آلاهو|آلاهؤ|آلاه|آلاحو|آلاحؤ|آلاح|آلآهو|آلآهؤ|آلآه|آلآحو|آلآحؤ|آلآح|آلهو|آلهؤ|آله|آلحو|آلحؤ|آلح|لاهو|لاهؤ|لاه|لاحو|لاحؤ|لاح|لآهو|لآهؤ|لآه|لآحو|لآحؤ|لآح|لهو|لهؤ|له|لحو|لحؤ|لح|لاهو|لاهؤ|لاه|لاحو|ل'" ParameterRuntimeValue="N'الاهو|اهؤ|اه|احو|احؤ|اح|آهو|آهؤ|آه|آحو|آحؤ|آح|هو|هؤ|ه|حو|حؤ|ح|آلاهو|آلاهؤ|آلاه|آلاحو|آلاحؤ|آلاح|آلآهو|آلآهؤ|آلآه|آلآحو|آلآحؤ|آلآح|آلهو|آلهؤ|آله|آلحو|آلحؤ|آلح|لاهو|لاهؤ|لاه|لاحو|لاحؤ|لاح|لآهو|لآهؤ|لآه|لآحو|لآحؤ|لآح|لهو|لهؤ|له|لحو|لحؤ|لح|لاهو|لاهؤ|لاه|لاحو|ل'" />
    </ParameterList>
    </QueryPlan>
    </StmtSimple>
    </Statements>
    </Batch>
    </BatchSequence>
    </ShowPlanXML>
    as for the indexes, i have primary keys on all tables + indexes on all foreign keys and 1 addition index on FileCount desc, Frequency desc
    Thanks,

  4. satya Moderator

    ...and how about INDEX on the table(s).
  5. mmarovic Active Member

    If you make query indexes you mentioned you would kill the query performance (assuming there is significant amount of data in tables). Query optimizer most probably uses index (or pk) on KeywordFrequency.keywordID.
  6. jagblue New Member

    Hi
    did you try
    SELECT Word, FileCount, Frequency
    Instaid of
    SELECT splitText, FileCount, Frequency
    also from this query I am assuming your function is trying to to generate list of words from your word string seperated by '|'
    http://www.sommarskog.se/arrays-in-sql-2005.html
    This is nice link which explains different methods to convert string in to lists and array
    Thank You
  7. samham New Member

    Hi jagblue,
    Thanks for the reply but i can't replace splitText by word since i will get NULL values because i am doing a left outer join
    I will check the link
    Thanks,
    Sam
  8. Adriaan New Member

    You have a UDF that returns a table. Does the table declaration include a PK on the column on which you're joining?
    And even if the UDF declares a PK, in general the use of a UDF means that SQL will ignore indexes. This is because of the unpredictable nature of the values returned by the UDF, so SQL assumes a table scan is required to match against those values.
    You might try declaring a table variable with a PK, or a temp table with an index, into which you insert the values from the UDF, then include this table in your main query.
    Try and stick with the proven SQL structures, and use UDFs only where they do not compromise performance.
  9. samham New Member

    Hi Adriaan
    Thanks for the reply, i added an index to the function but i still have a Sort in the execution plan, i tried an inner join instead of outer thinking maybe NULL values have any effect but no difference

    my goal behind this query is to sort the list sent as comma delimited list, any other ideas?
  10. samham New Member

    thanks all, but I will sort on app level since i have other app level params needed for sorting

Share This Page