Full-text Query very slow.

Full-text Query very slow.

Post by dairec via » Thu, 09 Oct 2008 00:01:09


Hi there,

I have a poorly performing full-text query and I'm wondering if there is
anything I can do to try and improve it or if the current performance is the
best I can hope for.

The query is a relatively simple CONTAINSTABLE which joins to the base table.
Here it is:

SELECT WorkCode, WorkName, RANK
FROM [Work.WorkName] wname
INNER JOIN CONTAINSTABLE ([Work.WorkName], WorkName,
'"heavenly*" OR "day*"', 1000) AS Title_TBL
ON wname.WorkNameID = Title_TBL.[KEY]

It queries a table of song titles for songs with the words "heavenly" and
"day", limiting the results to the best 1000 matches.
The table has about 9million rows.
The database server has 4 CPUs and 4GB RAM. The SQL server max RAM is set to
2GB. The SQL Server version is 9.0.1399.
The table has it's own FT Catalog, on a different disk than the data file and
has recently been repopulated.

Currently the query takes about 10s. I would have thought that even for a
large table like this the query would be of the order of a second or so?

Thanks in advance for any suggestions.

Daire

--
Message posted via SQLMonster.com
http://www.yqcomputer.com/
 
 
 

Full-text Query very slow.

Post by Hilary Cot » Thu, 09 Oct 2008 00:44:11

Do you have a covering index?

Create a unique index on Work.WorkNameID

and then create this covering index

create index test Work_WorkName_1on [Work].[WorkName] (WorkNameID)
include(WorkdCode, WorkName)

 
 
 

Full-text Query very slow.

Post by dairec via » Thu, 09 Oct 2008 17:23:28

Hi Hilary, thanks for your reply.
WorkNameID is the PK for the table and WorkName has its own non-clustered
index.
If tried creating another index to include both columns but it did not
improve the query. Note that the following query executes in 1s so I think
the bottleneck is definitely in the FT Query:

SELECT TOP 1000 *
FROM [Work.WorkName] wname
Where WorkName like 'heavenly%' OR WorkName like 'day%'

Daire



--
Message posted via http://www.yqcomputer.com/
 
 
 

Full-text Query very slow.

Post by Hilary Cot » Thu, 09 Oct 2008 20:32:06

Can you post the execution plan either offline or online? A unique index
generally gives better performance than a clustered index for scans.
 
 
 

Full-text Query very slow.

Post by dairec via » Thu, 09 Oct 2008 21:31:53

xecution 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.1399.06"
xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">
<BatchSequence>
<Batch>
<Statements>
<StmtSimple StatementCompId="4" StatementEstRows="1" StatementId="1"
StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound"
StatementSubTreeCost="0.0333206" StatementText="SELECT WorkCode, WorkName,
RANK FROM [Work.WorkName] wname INNER JOIN CONTAINSTABLE ([Work.
WorkName], WorkName, '"heavenly*" OR "day*"',
1000) AS Title_TBL ON wname.WorkNameID = Title_TBL.[KEY]
" 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="0" CachedPlanSize="11">
<RelOp AvgRowSize="169" EstimateCPU="4.18E-06" EstimateIO="0"
EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Inner
Join" NodeId="0" Parallel="false" PhysicalOp="Nested Loops"
EstimatedTotalSubtreeCost="0.0333206">
<OutputList>
<ColumnReference Database="[IMRO]" Schema="[dbo]" Table="
[Work.WorkName]" Alias="[wname]" Column="WorkCode" />
<ColumnReference Database="[IMRO]" Schema="[dbo]" Table="
[Work.WorkName]" Alias="[wname]" Column="WorkName" />
<ColumnReference Table="[Full-text Search Engine]" Alias="
[Title_TBL]" Column="RANK" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1000"
ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<NestedLoops Optimized="false">
<OuterReferences>
<ColumnReference Table="[Full-text Search Engine]" Alias="
[Title_TBL]" Column="KEY" />
</OuterReferences>
<RelOp AvgRowSize="15" EstimateCPU="0.0300333" EstimateIO="0"
EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1" LogicalOp="Remote
Scan" NodeId="1" Parallel="false" PhysicalOp="Remote Scan"
EstimatedTotalSubtreeCost="0.0300333">
<OutputList>
<ColumnReference Table="[Full-text Search Engine]"
Alias="[Title_TBL]" Column="KEY" />
<ColumnReference Table="[Full-text Search Engine]"
Alias="[Title_TBL]" Column="RANK" />
</OutputList>
<RunTimeInformation>
<RunTimeCountersPerThread Thread="0" ActualRows="1000"
ActualEndOfScans="1" ActualExecutions="1" />
</RunTimeInformation>
<RemoteScan RemoteObject="CONTAINS" />
</RelOp>
<RelOp AvgRowSize="165" EstimateCPU="0.0001581" EstimateIO="0.
003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="1"
LogicalOp="Index Seek" NodeId="2" Parallel="false" PhysicalOp="Index Seek"
EstimatedTotalSubtreeCost="0.0032831">
<OutputList>