I have a SQL Server database that only has a single table with a few columns. There are a few thousand rows currently. The columns are as follows:
id (nvarchar(35))
path (nvarchar(255))
body (ntext)
datetimecreated (datetime)
author (nvarchar(255))
version (int)
summary (nvarchar(255))
The search query is as follows:
SELECT [id],[path],[body],[datetimecreated],[author],[version],[summary]
FROM [dbo].[pages] p1
where ([version] =
(select max(p2.[version])
from [dbo].[pages] p2
where p1.[path] = p2.[path]))
and (body like '%outlook%'
or summary like '%outlook%'
or author like '%outlook%'
)
order by p1.[path] asc
A search like above will take 8-10 seconds to complete. I've tried copying the db and converting the ntext to nvarchar(max) and see no difference in query times. I've also tried creating a full-text index and searching with the following:
contains (body, '"outlook"') or CONTAINS (summary, '"outlook"')
This actually takes longer.
I'm looking for advice/help to speed up search queries. I did not design the database.
I've tried modifying the body column to nvarchar(max) and creating a full-text index to no avail.
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.589" Build="16.0.5564.30211" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="1" StatementEstRows="1" StatementId="1" StatementOptmLevel="FULL" CardinalityEstimationModelVersion="120" StatementSubTreeCost="1.90059" StatementText="SELECT TOP 1000 [id]
 ,[path]
 ,[body]
 ,[datetimecreated]
 ,[author]
 ,[version]
 ,[summary]
 FROM [dbo].[pages_1] p1
 where ([version] =
 (select max(p2.[version])
 from [dbo].[pages_1] p2
 where p1.[path] = p2.[path])) 
and (body like '%outlook%'
or summary like '%outlook%'
/*or author like '%outlook%'*/
)
order by p1.[path] asc" StatementType="SELECT" QueryHash="0x91AAE73160CEF12C" QueryPlanHash="0xE33814DCBC42ED0F" RetrievedFromCache="true" StatementSqlHandle="0x09003CE69250B7708CCD87CBDE6C4A7A08DE0000000000000000000000000000000000000000000000000000" DatabaseContextSettingsId="12" ParentObjectId="0" StatementParameterizationType="0" SecurityPolicyApplied="false">
<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="1" MemoryGrant="1424" CachedPlanSize="48" CompileTime="399" CompileCPU="63" CompileMemory="512">
<MemoryGrantInfo SerialRequiredMemory="640" SerialDesiredMemory="1424" RequiredMemory="640" DesiredMemory="1424" RequestedMemory="1424" GrantWaitTime="0" GrantedMemory="1424" MaxUsedMemory="520" MaxQueryMemory="401408" />
<OptimizerHardwareDependentProperties EstimatedAvailableMemoryGrant="47815008" EstimatedPagesCached="5976876" EstimatedAvailableDegreeOfParallelism="2" MaxCompileMemory="1349624" />
<RelOp AvgRowSize="4855" EstimateCPU="1E-07" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Top" NodeId="0" Parallel="false" PhysicalOp="Top" EstimatedTotalSubtreeCost="1.90059">
<OutputList>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="id" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="path" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="body" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="datetimecreated" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="author" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="version" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="summary" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="8595" ActualCPUms="701" />
</RunTimeInformation>
<Top RowCount="false" IsPercent="false" WithTies="false">
<TopExpression>
<ScalarOperator ScalarString="(1000)">
<Const ConstValue="(1000)" />
</ScalarOperator>
</TopExpression>
<RelOp AvgRowSize="4855" EstimateCPU="0.00586661" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="1" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="1.90059">
<OutputList>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="id" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="path" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="body" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="datetimecreated" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="author" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="version" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="summary" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="4" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="8595" ActualCPUms="701" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<Merge ManyToMany="false">
<InnerSideJoinColumns>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="path" />
</InnerSideJoinColumns>
<OuterSideJoinColumns>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p2]" Column="path" />
</OuterSideJoinColumns>
<Residual>
<ScalarOperator ScalarString="[Expr1002]=[wiki].[dbo].[pages_1].[version] as [p1].[version] AND [wiki].[dbo].[pages_1].[path] as [p2].[path]=[wiki].[dbo].[pages_1].[path] as [p1].[path]">
<Logical Operation="AND">
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Column="Expr1002" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="version" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
<ScalarOperator>
<Compare CompareOp="EQ">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p2]" Column="path" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Identifier>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="path" />
</Identifier>
</ScalarOperator>
</Compare>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Residual>
<RelOp AvgRowSize="65" EstimateCPU="0.0016498" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="98" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.969562">
<OutputList>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p2]" Column="path" />
<ColumnReference Column="Expr1002" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="98" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="3" ActualCPUms="3" />
</RunTimeInformation>
<StreamAggregate>
<DefinedValues>
<DefinedValue>
<ColumnReference Column="Expr1002" />
<ScalarOperator ScalarString="MAX([wiki].[dbo].[pages_1].[version] as [p2].[version])">
<Aggregate AggType="MAX" Distinct="false">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p2]" Column="version" />
</Identifier>
</ScalarOperator>
</Aggregate>
</ScalarOperator>
</DefinedValue>
</DefinedValues>
<GroupBy>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p2]" Column="path" />
</GroupBy>
<RelOp AvgRowSize="65" EstimateCPU="0.047471" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2668" LogicalOp="Sort" NodeId="3" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.967912">
<OutputList>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p2]" Column="path" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p2]" Column="version" />
</OutputList>
<MemoryFractions Input="1" Output="0.734694" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="2668" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="3" ActualCPUms="3" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="1296" OutputMemoryGrant="704" UsedMemoryGrant="280" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p2]" Column="path" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="65" EstimateCPU="0.0030918" EstimateIO="0.906088" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2668" LogicalOp="Clustered Index Scan" NodeId="4" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.90918" TableCardinality="2668">
<OutputList>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p2]" Column="path" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p2]" Column="version" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2668" ActualRowsRead="2668" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="1" ActualCPUms="1" ActualScans="1" ActualLogicalReads="1247" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p2]" Column="path" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p2]" Column="version" />
</DefinedValue>
</DefinedValues>
<Object Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Index="[PK_pages_1]" Alias="[p2]" IndexKind="Clustered" Storage="RowStore" />
</IndexScan>
</RelOp>
</Sort>
</RelOp>
</StreamAggregate>
</RelOp>
<RelOp AvgRowSize="4855" EstimateCPU="0.000235967" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="19.5838" LogicalOp="Sort" NodeId="5" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.925159">
<OutputList>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="id" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="path" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="body" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="datetimecreated" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="author" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="version" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="summary" />
</OutputList>
<MemoryFractions Input="0.265306" Output="0.265306" />
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="79" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="8592" ActualCPUms="697" ActualScans="0" ActualLogicalReads="0" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" InputMemoryGrant="712" OutputMemoryGrant="328" UsedMemoryGrant="240" />
</RunTimeInformation>
<Sort Distinct="false">
<OrderBy>
<OrderByColumn Ascending="true">
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="path" />
</OrderByColumn>
</OrderBy>
<RelOp AvgRowSize="4855" EstimateCPU="0.00448224" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="19.5838" LogicalOp="Filter" NodeId="6" Parallel="false" PhysicalOp="Filter" EstimatedTotalSubtreeCost="0.913662">
<OutputList>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="id" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="path" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="body" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="datetimecreated" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="author" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="version" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="summary" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="79" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="8591" ActualCPUms="697" />
</RunTimeInformation>
<Filter StartupExpression="false">
<RelOp AvgRowSize="4855" EstimateCPU="0.0030918" EstimateIO="0.906088" EstimateRebinds="0" EstimateRewinds="0" EstimatedExecutionMode="Row" EstimateRows="2668" LogicalOp="Clustered Index Scan" NodeId="7" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.90918" TableCardinality="2668">
<OutputList>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="id" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="path" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="body" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="datetimecreated" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="author" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="version" />
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="summary" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="2668" ActualRowsRead="2668" Batches="0" ActualEndOfScans="1" ActualExecutions="1" ActualExecutionMode="Row" ActualElapsedms="653" ActualCPUms="3" ActualScans="1" ActualLogicalReads="1247" ActualPhysicalReads="0" ActualReadAheads="0" ActualLobLogicalReads="0" ActualLobPhysicalReads="0" ActualLobReadAheads="0" />
</RunTimeInformation>
<IndexScan Ordered="false" ForcedIndex="false" ForceScan="false" NoExpandHint="false" Storage="RowStore">
<DefinedValues>
<DefinedValue>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="id" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="path" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="body" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="datetimecreated" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="author" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="version" />
</DefinedValue>
<DefinedValue>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="summary" />
</DefinedValue>
</DefinedValues>
<Object Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Index="[PK_pages_1]" Alias="[p1]" IndexKind="Clustered" Storage="RowStore" />
</IndexScan>
</RelOp>
<Predicate>
<ScalarOperator ScalarString="[wiki].[dbo].[pages_1].[body] as [p1].[body] like N'%outlook%' OR [wiki].[dbo].[pages_1].[summary] as [p1].[summary] like N'%outlook%'">
<Logical Operation="OR">
<ScalarOperator>
<Intrinsic FunctionName="like">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="body" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'%outlook%'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
<ScalarOperator>
<Intrinsic FunctionName="like">
<ScalarOperator>
<Identifier>
<ColumnReference Database="[wiki]" Schema="[dbo]" Table="[pages_1]" Alias="[p1]" Column="summary" />
</Identifier>
</ScalarOperator>
<ScalarOperator>
<Const ConstValue="N'%outlook%'" />
</ScalarOperator>
</Intrinsic>
</ScalarOperator>
</Logical>
</ScalarOperator>
</Predicate>
</Filter>
</RelOp>
</Sort>
</RelOp>
</Merge>
</RelOp>
</Top>
</RelOp>
</QueryPlan>
</StmtSimple>
</Statements>
</Batch>
</BatchSequence>
</ShowPlanXML>
I first converted the body field to varchar(max) (which didn't yield any performance increase) and then broke this into two queries where the first gets the latest version of each page and the second is a query of queries that looks for the search phrase. This results in an average of 2 seconds per result, a 4-5x speed increase. Thanks!
The first query:
select [id], [path], [body], [datetimecreated], [author], [version], [summary]
from pages p1
where (version =
(select max(p2.version)
from pages p2
where p1.path = p2.path))
order by p1.path asc
The second query:
select id, path, datetimecreated, version
from latest
where (body like '%outlook%'
or summary like '%outlook%')
order by path asc