I have a ms sql 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>