Improve query performance (sorting)

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

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,

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.

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

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

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.

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?

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