Alexander
Reputation: 20234
SELECT with RowNumber takes long to execute
I have a table containing debug messages defined as follows:
id INT NOT NULL IDENTITY
message TEXT NOT NULL
date DATETIME NOT NULL
Against that, I run the following query:
SELECT * FROM (
SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS RowNum, *
FROM debug WHERE message LIKE @Filter
) AS tbl
WHERE RowNum > (@Page*200-200)
AND RowNum <= (@Page * 200)
ORDER BY ID DESC
This gives me a single page of 200 results; but with 220k entries in the table (and counting!), the filtering and row numbering takes a considerable amount of time (>30 secs).
Is there anything I can do to speed up the process of getting that page of results?
EDIT: So I have switched to VARCHAR(MAX) as proposed by Gordon; but this didn't speed the things up. The resulting execution plan of the query is as follows (I try to repaint the graphics as ASCII-Art):
Sequence Project Clustered Index Scan
SELECT <- Sort <- Filter <- Top <- (Compute Scalar) <- Segment <- Filter <- [debug].[PK_debug]
Cost: 0% 26% 0% 0% 0% 0% 2% 71%
or in XML:
<?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.2" Build="12.0.2569.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="4" StatementEstRows="16.4317" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" CardinalityEstimationModelVersion="120" StatementSubTreeCost="0.0438369" StatementText="SELECT * FROM (SELECT ROW_NUMBER() OVER (ORDER BY ID DESC) AS RowNum, * FROM debug WHERE message LIKE @Filter) AS tbl WHERE RowNum > (@Page*200-200) AND RowNum <= (@Page * 200) ORDER BY ID DESC" StatementType="SELECT" QueryHash="0x535D30B424597DBB" QueryPlanHash="0x17ABB516DFF1A1A7" RetrievedFromCache="true">
<StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />
<QueryPlan DegreeOfParallelism="0" NonParallelPlanReason="EstimatedDOPIsOne" MemoryGrant="1024" CachedPlanSize="32" CompileTime="4" CompileCPU="4" CompileMemory="248">
<MemoryGrantInfo SerialRequiredMemory="512" SerialDesiredMemory="672" RequiredMemory="512" DesiredMemory="672" RequestedMemory="1024" GrantWaitTime="0" GrantedMemory="1024" MaxUsedMemory="56" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="256000" EstimatedPagesCached="16000" EstimatedAvailableDegreeOfParallelism="1" />
<RelOp AvgRowSize="4055" EstimateCPU="0.000207573" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="16.4317" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.0438369">
<OutputList>
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="id" />
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="message" />
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="date" />
<ColumnReference Column="Expr1002" />
</OutputList>
<MemoryFractions Input="1" Output="1" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="147" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="false">
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="id" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="4055" EstimateCPU="8.8E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="16.4317" LogicalOp="Filter" NodeId="1" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.0323681">
<OutputList>
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="id" />
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="message" />
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="date" />
<ColumnReference Column="Expr1002" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="147" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Filter StartupExpression="false">
<RelOp AvgRowSize="4055" EstimateCPU="1E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Top" NodeId="2" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="0.0322801">
<OutputList>
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="id" />
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="message" />
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="date" />
<ColumnReference Column="Expr1002" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="147" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="CASE WHEN CONVERT_IMPLICIT(bigint,[@Page]*(200),0) IS NULL OR CONVERT_IMPLICIT(bigint,[@Page]*(200),0)<(0) THEN (0) ELSE CONVERT_IMPLICIT(bigint,[@Page]*(200),0) END">
<IF>
<Condition>
<ScalarOperator>
<Logical Operation="OR">
<ScalarOperator>
<Compare CompareOp="IS">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1007">
<ScalarOperator>
<Convert DataType="bigint" Style="0" Implicit="true">
<ScalarOperator>
<Arithmetic Operation="MULT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@Page" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(200)" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="NULL" />
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="LT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1007">
<ScalarOperator>
<Convert DataType="bigint" Style="0" Implicit="true">
<ScalarOperator>
<Arithmetic Operation="MULT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@Page" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(200)" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Condition>
<Then>
<ScalarOperator>
<Const ConstValue="(0)" />
</ScalarOperator>
</Then>
<Else>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1007">
<ScalarOperator>
<Convert DataType="bigint" Style="0" Implicit="true">
<ScalarOperator>
<Arithmetic Operation="MULT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@Page" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(200)" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Else>
</IF>
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="4055" EstimateCPU="0.00284444" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Compute Scalar" NodeId="3" Parallel="false" PhysicalOp="Sequence Project" EstimatedTotalSubtreeCost="0.0322701">
<OutputList>
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="id" />
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="message" />
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="date" />
<ColumnReference Column="Expr1002" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="147" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<SequenceProject>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1002" />
<ScalarOperator ScalarString="row_number">
<Sequence FunctionName="row_number" />
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<RelOp AvgRowSize="4055" EstimateCPU="0.00071111" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Segment" NodeId="4" Parallel="false" PhysicalOp="Segment" EstimatedTotalSubtreeCost="0.0322621">
<OutputList>
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="id" />
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="message" />
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="date" />
<ColumnReference Column="Segment1008" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="147" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Segment>
<GroupBy />
<SegmentColumn>
<ColumnReference Column="Segment1008" />
</SegmentColumn>
<RelOp AvgRowSize="4047" EstimateCPU="0.347654" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="100" LogicalOp="Filter" NodeId="5" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.0322601">
<OutputList>
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="id" />
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="message" />
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="date" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="147" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<Filter StartupExpression="false">
<RelOp AvgRowSize="4047" EstimateCPU="0.434724" EstimateIO="9.53275" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1111.11" LogicalOp="Clustered Index Scan" NodeId="6" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.0312823" TableCardinality="395061">
<OutputList>
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="id" />
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="message" />
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="date" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="395061" ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<IndexScan Ordered="true" ScanDirection="BACKWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="message" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="date" />
</DefinedValue>
</DefinedValues>
<Object Database="[timeflex]" Schema="[dbo]" Table="[debug]" Index="[PK_debug]" IndexKind="Clustered" Storage="RowStore" />
</IndexScan>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="[timeflex].[dbo].[debug].[message] like [@Filter]">
<Intrinsic FunctionName="like">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[timeflex]" Schema="[dbo]" Table="[debug]" Column="message" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="@Filter" />
</Identifier>
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</Segment>
</RelOp>
</SequenceProject>
</RelOp>
</Top>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="[Expr1002]>CONVERT_IMPLICIT(bigint,[@Page]*(200)-(200),0) AND [Expr1002]<=CONVERT_IMPLICIT(bigint,[@Page]*(200),0)">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="GT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1002" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1006">
<ScalarOperator>
<Convert DataType="bigint" Style="0" Implicit="true">
<ScalarOperator>
<Arithmetic Operation="SUB">
<ScalarOperator>
<Arithmetic Operation="MULT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@Page" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(200)" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(200)" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="LE">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1002" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Column="ConstExpr1007">
<ScalarOperator>
<Convert DataType="bigint" Style="0" Implicit="true">
<ScalarOperator>
<Arithmetic Operation="MULT">
<ScalarOperator>
<Identifier>
<ColumnReference Column="@Page" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="(200)" />
</ScalarOperator>
</Arithmetic>
</ScalarOperator>
</Convert>
</ScalarOperator>
</ColumnReference>
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</Sort>
</RelOp>
<ParameterList>
<ColumnReference Column="@Page" ParameterRuntimeValue="(1)" />
<ColumnReference Column="@Filter" ParameterRuntimeValue="'%ZUser888%'" />
</ParameterList>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
Upvotes: 1
Views: 196
Answers (1)
Gordon Linoff
Reputation: 1270181
First, don't use text, use varchar(max) or nvarchar(max). It is officially deprecated in SQL Server 2016, so it might disappear in a future version.
For pagination, use OFFSET/FETCH. This was introduced in SQL Server 2012, so it is somewhat back compatible. There is no SQL Server 2010. Read about it here.
Let me eliminate table/page/row contention as the cause of the performance problem. My best guess is the like on a very wide column. If message really is a bunch of free form text, then use a full text index. The place to start is the documentation. For some reason, my links to Microsoft documentation aren't working well right now, but it is should be easy to find.
If the row width really is very wide, then you may have an additional problem with the select *. If you are doing paging, then this is likely for a user interface, so you might want to limit the columns to those that you really need -- and even shorten really long ones if you don't intend to show the entire column.
Upvotes: 5
Related Questions
- SQL Query slow only when Row_Number values are used in STUFF
- ROW_NUMBER() OVER is slow with ORDER BY on single column
- ROW_NUMBER Performance
- Alternative to using ROW_NUMBER for better performance
- How to get elements from table by row number in sql server
- ROW_NUMBER() Performance optimization
- Query with row_number() taking too much time
- SQL ROW_NUMBER() over performance problem
- ROW_NUMBER() OVER Not Fast Enough With Large Result Set, any good solution?
- ROW_NUMBER() column is invalid and trying to speed up query help