SQL request performance problem

• CameleWeb

  Old Hand

  Points: 365

  More actions

  April 9, 2012 at 9:13 am

  #258907

  Hi!

  I have a performance problem on a SQL request when it's executed the first time, under a website on IIS 7. Request takes more than 40 seconds to execute. Here my request:

  SELECT CONT.TITRE, CONT.DESCRIPTION, CONT.URL, CONT.DATE_MODIF, CONT.IMAGE, PERT.RANK FROM RECHERCHE_CONTENU AS CONT INNER JOIN CONTAINSTABLE (RECHERCHE_CONTENU, CONTENU, 'FORMSOF(INFLECTIONAL, keywords)') AS PERT ON CONT.ID = PERT.WHERE id_site = 1 ORDER BY PERT.RANK DESC;

  If I execute it under MS SQL Server Management Studio, the first time, it takes more than 40 seconds, and then next, it executes instantly. So far, here what I tried as a solution, without succes :

  Run sp_fulltext_service 'verify_signature', 0; (according to http://support.microsoft.com/kb/915850)

  Check my full-text search service to automatic

  So, the problem is still there... realy dont know what to do!

  Thanks for any help you can provide on this problem.

  Stephane

  Stéphane Demers
  Web/ecommerce Consultant
  CaméléWeb - ecommerce Solution: CaméléShop
  www.cameleweb.com[/url]

• Jack Corbett

  SSC Guru

  Points: 184393

  More actions

  April 9, 2012 at 11:21 am

  #1470772

  I'm not a Full-Text search guy, but SQL Server caches data in memory the first time it is accessed so it could be as simple as the data not being in memory the first execution and then being in memory the next time so there is no physical disk access the second time the query is run.

  If this is it you probably need to check memory usage on the server to see if you need more memory.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  April 9, 2012 at 11:26 am

  #1470774

  I already suggested that (direct contact before sending him here).

  The problem doesn't happen only once, and there's too little data to be a problem unless it's hardware related (still in dev phase).

  Sql express. Don't know the rest.

  I already repeatedly requested the executin plan as I expect the join to be errandous on this query.

• CameleWeb

  Old Hand

  Points: 365

  More actions

  April 9, 2012 at 11:36 am

  #1470784

  Hi! Thanks for answering!

  As Ninja's_RGR'us said, it couldn't be a memory usage problem since it takes 40 seconds the first time, and less than a second the next time.

  Here's the execution plan. Let me know if you need anything else that could be helpful!

  <?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.1" Build="10.50.2500.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

  <BatchSequence>

  <Batch>

  <Statements>

  <StmtSimple StatementCompId="1" StatementEstRows="89.0897" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.257799" StatementText="SELECT CONT.TITRE, CONT.DESCRIPTION, CONT.URL, CONT.DATE_MODIF, CONT.IMAGE, PERT.RANK FROM RECHERCHE_CONTENU AS CONT WITH(index=PK_RECHERCHE_CONTENU)INNER JOIN CONTAINSTABLE (RECHERCHE_CONTENU, CONTENU, 'FORMSOF(INFLECTIONAL, couche)') AS PERT ON CONT.ID = PERT.WHERE id_site = 1 ORDER BY PERT.RANK DESC; " StatementType="SELECT" QueryHash="0x424531AA8413538B" QueryPlanHash="0xB930F8801101D0B7">

  <StatementSetOptions ANSI_NULLS="true" ANSI_PADDING="true" ANSI_WARNINGS="true" ARITHABORT="true" CONCAT_NULL_YIELDS_NULL="true" NUMERIC_ROUNDABORT="false" QUOTED_IDENTIFIER="true" />

  <QueryPlan CachedPlanSize="32" CompileTime="734" CompileCPU="734" CompileMemory="240">

  <MissingIndexes>

  <MissingIndexGroup Impact="75.0886">

  <MissingIndex Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]">

  <ColumnGroup Usage="EQUALITY">

  <Column Name="[ID_SITE]" ColumnId="1" />

  </ColumnGroup>

  <ColumnGroup Usage="INCLUDE">

  <Column Name="[ID]" ColumnId="2" />

  <Column Name="[TITRE]" ColumnId="3" />

  <Column Name="[DESCRIPTION]" ColumnId="4" />

  <Column Name="" ColumnId="6" />

  <Column Name="[DATE_MODIF]" ColumnId="7" />

  <Column Name="[IMAGE]" ColumnId="8" />

  </ColumnGroup>

  </MissingIndex>

  </MissingIndexGroup>

  </MissingIndexes>

  <RelOp AvgRowSize="1279" EstimateCPU="0.00100148" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="89.0897" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.257799">

  <OutputList>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="TITRE" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DESCRIPTION" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="URL" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DATE_MODIF" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="IMAGE" />

  <ColumnReference Column="Expr1003" />

  </OutputList>

  <MemoryFractions Input="1" Output="1" />

  <Sort Distinct="false">

  <OrderBy>

  <OrderByColumn Ascending="false">

  <ColumnReference Column="Expr1003" />

  </OrderByColumn>

  </OrderBy>

  <RelOp AvgRowSize="1279" EstimateCPU="0.00647974" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="89.0897" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.245536">

  <OutputList>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="TITRE" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DESCRIPTION" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="URL" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DATE_MODIF" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="IMAGE" />

  <ColumnReference Column="Expr1003" />

  </OutputList>

  <Merge ManyToMany="false">

  <InnerSideJoinColumns>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="ID" />

  </InnerSideJoinColumns>

  <OuterSideJoinColumns>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  </OuterSideJoinColumns>

  <Residual>

  <ScalarOperator ScalarString="FulltextMatch.[docid]=[cameleshop].[dbo].[RECHERCHE_CONTENU].[ID] as [CONT].[ID]">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="ID" />

  </Identifier>

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Residual>

  <RelOp AvgRowSize="15" EstimateCPU="0.000358997" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="325.594" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0075361">

  <OutputList>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  <ColumnReference Column="Expr1003" />

  </OutputList>

  <StreamAggregate>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1003" />

  <ScalarOperator ScalarString="MAX([Expr1002])">

  <Aggregate AggType="MAX" Distinct="false">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1002" />

  </Identifier>

  </ScalarOperator>

  </Aggregate>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <GroupBy>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  </GroupBy>

  <RelOp AvgRowSize="15" EstimateCPU="0.0003597" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="327" LogicalOp="Aggregate" NodeId="3" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0071771">

  <OutputList>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  <ColumnReference Column="Expr1002" />

  </OutputList>

  <StreamAggregate>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1002" />

  <ScalarOperator ScalarString="MAX([Expr1004])">

  <Aggregate AggType="MAX" Distinct="false">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1004" />

  </Identifier>

  </ScalarOperator>

  </Aggregate>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <GroupBy>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  <ColumnReference Table="[FulltextMatch]" Column="colid" />

  </GroupBy>

  <RelOp AvgRowSize="19" EstimateCPU="3.27E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="327" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0068174">

  <OutputList>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  <ColumnReference Table="[FulltextMatch]" Column="colid" />

  <ColumnReference Column="Expr1004" />

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1004" />

  <ScalarOperator ScalarString="containstablerank(FulltextMatch.[documentcount],FulltextMatch.[itemcount],FulltextMatch.[termfrequency],FulltextMatch.[documentlength])">

  <Intrinsic FunctionName="containstablerank">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="[FulltextMatch]" Column="documentcount" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="[FulltextMatch]" Column="itemcount" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="[FulltextMatch]" Column="termfrequency" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="[FulltextMatch]" Column="documentlength" />

  </Identifier>

  </ScalarOperator>

  </Intrinsic>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp AvgRowSize="43" EstimateCPU="0.0036597" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="327" LogicalOp="Table-valued function" NodeId="5" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="0.0067847">

  <OutputList>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  <ColumnReference Table="[FulltextMatch]" Column="colid" />

  <ColumnReference Table="[FulltextMatch]" Column="documentcount" />

  <ColumnReference Table="[FulltextMatch]" Column="documentlength" />

  <ColumnReference Table="[FulltextMatch]" Column="termfrequency" />

  <ColumnReference Table="[FulltextMatch]" Column="itemcount" />

  </OutputList>

  <TableValuedFunction>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="colid" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="documentcount" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="documentlength" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="termfrequency" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="itemcount" />

  </DefinedValue>

  </DefinedValues>

  <Object Table="[FulltextMatch]" />

  <ParameterList>

  <ScalarOperator ScalarString="(1)">

  <Const ConstValue="(1)" />

  </ScalarOperator>

  <ScalarOperator ScalarString="(1)">

  <Const ConstValue="(1)" />

  </ScalarOperator>

  <ScalarOperator ScalarString="(1)">

  <Const ConstValue="(1)" />

  </ScalarOperator>

  <ScalarOperator ScalarString="(0)">

  <Const ConstValue="(0)" />

  </ScalarOperator>

  <ScalarOperator ScalarString="NULL">

  <Const ConstValue="NULL" />

  </ScalarOperator>

  <ScalarOperator ScalarString="NULL">

  <Const ConstValue="NULL" />

  </ScalarOperator>

  </ParameterList>

  </TableValuedFunction>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  </StreamAggregate>

  </RelOp>

  </StreamAggregate>

  </RelOp>

  <RelOp AvgRowSize="1283" EstimateCPU="0.0012493" EstimateIO="0.229792" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="89.0897" LogicalOp="Clustered Index Scan" NodeId="21" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.231041" TableCardinality="993">

  <OutputList>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="ID" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="TITRE" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DESCRIPTION" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="URL" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DATE_MODIF" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="IMAGE" />

  </OutputList>

  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="ID" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="TITRE" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DESCRIPTION" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="URL" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DATE_MODIF" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="IMAGE" />

  </DefinedValue>

  </DefinedValues>

  <Object Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Index="[PK_RECHERCHE_CONTENU]" Alias="[CONT]" IndexKind="Clustered" />

  <Predicate>

  <ScalarOperator ScalarString="[cameleshop].[dbo].[RECHERCHE_CONTENU].[ID_SITE] as [CONT].[ID_SITE]=(1)">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="ID_SITE" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Const ConstValue="(1)" />

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Predicate>

  </IndexScan>

  </RelOp>

  </Merge>

  </RelOp>

  </Sort>

  </RelOp>

  </QueryPlan>

  </StmtSimple>

  </Statements>

  </Batch>

  </BatchSequence>

  </ShowPlanXML>

  Stéphane Demers
  Web/ecommerce Consultant
  CaméléWeb - ecommerce Solution: CaméléShop
  www.cameleweb.com[/url]

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  April 9, 2012 at 11:40 am

  #1470787

  This is an estimated plan, we need the actual plan.

  You can post as .sqlplan by hitting edit attachements

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  April 9, 2012 at 11:45 am

  #1470794

  Did you change the query since you shared it with me? I don't remember having seen this hint before!

  WITH(index=PK_RECHERCHE_CONTENU)

  Hints are absolute last resorts and you're not there yet. They will hurt you more often than not.

  As I already shared, I would save the results of the fts into a temp table and then join that to the base table to give the server the best possible changes of getting the right plan. The way this query is written, it will give you more headaches than you care for down the line.

• CameleWeb

  Old Hand

  Points: 365

  More actions

  April 9, 2012 at 11:46 am

  #1470795

  Oups, sorry!

  Is this the right 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.1" Build="10.50.2500.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

  <BatchSequence>

  <Batch>

  <Statements>

  <StmtSimple StatementCompId="1" StatementEstRows="89.0897" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.257799" StatementText="SELECT CONT.TITRE, CONT.DESCRIPTION, CONT.URL, CONT.DATE_MODIF, CONT.IMAGE, PERT.RANK FROM RECHERCHE_CONTENU AS CONT WITH(index=PK_RECHERCHE_CONTENU)INNER JOIN CONTAINSTABLE (RECHERCHE_CONTENU, CONTENU, 'FORMSOF(INFLECTIONAL, couche)') AS PERT ON CONT.ID = PERT.WHERE id_site = 1 ORDER BY PERT.RANK DESC; " StatementType="SELECT" QueryHash="0x424531AA8413538B" QueryPlanHash="0xB930F8801101D0B7">

  <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" MemoryGrant="1024" CachedPlanSize="32" CompileTime="734" CompileCPU="734" CompileMemory="240">

  <MissingIndexes>

  <MissingIndexGroup Impact="75.0886">

  <MissingIndex Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]">

  <ColumnGroup Usage="EQUALITY">

  <Column Name="[ID_SITE]" ColumnId="1" />

  </ColumnGroup>

  <ColumnGroup Usage="INCLUDE">

  <Column Name="[ID]" ColumnId="2" />

  <Column Name="[TITRE]" ColumnId="3" />

  <Column Name="[DESCRIPTION]" ColumnId="4" />

  <Column Name="" ColumnId="6" />

  <Column Name="[DATE_MODIF]" ColumnId="7" />

  <Column Name="[IMAGE]" ColumnId="8" />

  </ColumnGroup>

  </MissingIndex>

  </MissingIndexGroup>

  </MissingIndexes>

  <RelOp AvgRowSize="1279" EstimateCPU="0.00100148" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="89.0897" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.257799">

  <OutputList>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="TITRE" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DESCRIPTION" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="URL" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DATE_MODIF" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="IMAGE" />

  <ColumnReference Column="Expr1003" />

  </OutputList>

  <MemoryFractions Input="1" Output="1" />

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="80" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <Sort Distinct="false">

  <OrderBy>

  <OrderByColumn Ascending="false">

  <ColumnReference Column="Expr1003" />

  </OrderByColumn>

  </OrderBy>

  <RelOp AvgRowSize="1279" EstimateCPU="0.00647974" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="89.0897" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.245536">

  <OutputList>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="TITRE" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DESCRIPTION" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="URL" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DATE_MODIF" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="IMAGE" />

  <ColumnReference Column="Expr1003" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="80" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <Merge ManyToMany="false">

  <InnerSideJoinColumns>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="ID" />

  </InnerSideJoinColumns>

  <OuterSideJoinColumns>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  </OuterSideJoinColumns>

  <Residual>

  <ScalarOperator ScalarString="FulltextMatch.[docid]=[cameleshop].[dbo].[RECHERCHE_CONTENU].[ID] as [CONT].[ID]">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="ID" />

  </Identifier>

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Residual>

  <RelOp AvgRowSize="15" EstimateCPU="0.000358997" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="325.594" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0075361">

  <OutputList>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  <ColumnReference Column="Expr1003" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <StreamAggregate>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1003" />

  <ScalarOperator ScalarString="MAX([Expr1002])">

  <Aggregate AggType="MAX" Distinct="false">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1002" />

  </Identifier>

  </ScalarOperator>

  </Aggregate>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <GroupBy>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  </GroupBy>

  <RelOp AvgRowSize="15" EstimateCPU="0.0003597" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="327" LogicalOp="Aggregate" NodeId="3" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0071771">

  <OutputList>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  <ColumnReference Column="Expr1002" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <StreamAggregate>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1002" />

  <ScalarOperator ScalarString="MAX([Expr1004])">

  <Aggregate AggType="MAX" Distinct="false">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1004" />

  </Identifier>

  </ScalarOperator>

  </Aggregate>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <GroupBy>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  <ColumnReference Table="[FulltextMatch]" Column="colid" />

  </GroupBy>

  <RelOp AvgRowSize="19" EstimateCPU="3.27E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="327" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0068174">

  <OutputList>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  <ColumnReference Table="[FulltextMatch]" Column="colid" />

  <ColumnReference Column="Expr1004" />

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1004" />

  <ScalarOperator ScalarString="containstablerank(FulltextMatch.[documentcount],FulltextMatch.[itemcount],FulltextMatch.[termfrequency],FulltextMatch.[documentlength])">

  <Intrinsic FunctionName="containstablerank">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="[FulltextMatch]" Column="documentcount" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="[FulltextMatch]" Column="itemcount" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="[FulltextMatch]" Column="termfrequency" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="[FulltextMatch]" Column="documentlength" />

  </Identifier>

  </ScalarOperator>

  </Intrinsic>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp AvgRowSize="43" EstimateCPU="0.0036597" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="327" LogicalOp="Table-valued function" NodeId="5" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="0.0067847">

  <OutputList>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  <ColumnReference Table="[FulltextMatch]" Column="colid" />

  <ColumnReference Table="[FulltextMatch]" Column="documentcount" />

  <ColumnReference Table="[FulltextMatch]" Column="documentlength" />

  <ColumnReference Table="[FulltextMatch]" Column="termfrequency" />

  <ColumnReference Table="[FulltextMatch]" Column="itemcount" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="450" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <TableValuedFunction>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="colid" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="documentcount" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="documentlength" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="termfrequency" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="itemcount" />

  </DefinedValue>

  </DefinedValues>

  <Object Table="[FulltextMatch]" />

  <ParameterList>

  <ScalarOperator ScalarString="(1)">

  <Const ConstValue="(1)" />

  </ScalarOperator>

  <ScalarOperator ScalarString="(1)">

  <Const ConstValue="(1)" />

  </ScalarOperator>

  <ScalarOperator ScalarString="(1)">

  <Const ConstValue="(1)" />

  </ScalarOperator>

  <ScalarOperator ScalarString="(0)">

  <Const ConstValue="(0)" />

  </ScalarOperator>

  <ScalarOperator ScalarString="NULL">

  <Const ConstValue="NULL" />

  </ScalarOperator>

  <ScalarOperator ScalarString="NULL">

  <Const ConstValue="NULL" />

  </ScalarOperator>

  </ParameterList>

  </TableValuedFunction>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  </StreamAggregate>

  </RelOp>

  </StreamAggregate>

  </RelOp>

  <RelOp AvgRowSize="1283" EstimateCPU="0.0012493" EstimateIO="0.229792" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="89.0897" LogicalOp="Clustered Index Scan" NodeId="21" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.231041" TableCardinality="993">

  <OutputList>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="ID" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="TITRE" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DESCRIPTION" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="URL" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DATE_MODIF" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="IMAGE" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="90" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="true" ForceSeek="false" ForceScan="false" NoExpandHint="false">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="ID" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="TITRE" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DESCRIPTION" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="URL" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DATE_MODIF" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="IMAGE" />

  </DefinedValue>

  </DefinedValues>

  <Object Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Index="[PK_RECHERCHE_CONTENU]" Alias="[CONT]" IndexKind="Clustered" />

  <Predicate>

  <ScalarOperator ScalarString="[cameleshop].[dbo].[RECHERCHE_CONTENU].[ID_SITE] as [CONT].[ID_SITE]=(1)">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="ID_SITE" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Const ConstValue="(1)" />

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Predicate>

  </IndexScan>

  </RelOp>

  </Merge>

  </RelOp>

  </Sort>

  </RelOp>

  </QueryPlan>

  </StmtSimple>

  </Statements>

  </Batch>

  </BatchSequence>

  </ShowPlanXML>

  Stéphane Demers
  Web/ecommerce Consultant
  CaméléWeb - ecommerce Solution: CaméléShop
  www.cameleweb.com[/url]

• Jack Corbett

  SSC Guru

  Points: 184393

  More actions

  April 9, 2012 at 11:47 am

  #1470796

  It would also be helpful to have the table definitions and the creation statement for the FT Index.

  You probably want to get rid of the index hint on the query as well. You should let the optimizer decide the best access path. You are already getting a missing index suggestion as it is and SQL Server wouldn't use it because of the hint.

  Even with the right index that few rows returned should be sub-second unless you have some underlying IO issues with either the table access of the FT access.

  Take a look at the disk counters in PERFMON so see if you are having issues there.

  Jack Corbett
  Consultant - Straight Path Solutions
  Check out these links on how to get faster and more accurate answers:
  Forum Etiquette: How to post data/code on a forum to get the best help
  Need an Answer? Actually, No ... You Need a Question

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  April 9, 2012 at 11:48 am

  #1470800

  *Perfmon.

  He his a programmer, nice brain but I don't know if he has experience there. might need a lot more explainations on that front.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 9, 2012 at 11:49 am

  #1470801

  Looking at the estimated plan, however, it looks like there is a missing index as well. Please post the DDL for the table(s) involved including all indexes that are defined on the table(s).

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  April 9, 2012 at 11:51 am

  #1470802

  +1 on the tables def again. Looks like your clustered index is not optimal

• CameleWeb

  Old Hand

  Points: 365

  More actions

  April 9, 2012 at 12:09 pm

  #1470811

  Well, I don’t know what the hint does there... it's not there in my connexion script. So, I removed the "WITH(index=PK_RECHERCHE_CONTENU)" and here's the 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.1" Build="10.50.2500.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

  <BatchSequence>

  <Batch>

  <Statements>

  <StmtSimple StatementCompId="1" StatementEstRows="89.0897" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.257799" StatementText="SELECT CONT.TITRE, CONT.DESCRIPTION, CONT.URL, CONT.DATE_MODIF, CONT.IMAGE, PERT.RANK FROM RECHERCHE_CONTENU AS CONT INNER JOIN CONTAINSTABLE (RECHERCHE_CONTENU, CONTENU, 'FORMSOF(INFLECTIONAL, couche)') AS PERT ON CONT.ID = PERT.WHERE id_site = 1 ORDER BY PERT.RANK DESC; " StatementType="SELECT" QueryHash="0xBE9B5122D345BC15" QueryPlanHash="0x081882FFFD1B99C5">

  <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" MemoryGrant="1024" CachedPlanSize="32" CompileTime="46" CompileCPU="46" CompileMemory="240">

  <MissingIndexes>

  <MissingIndexGroup Impact="75.0886">

  <MissingIndex Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]">

  <ColumnGroup Usage="EQUALITY">

  <Column Name="[ID_SITE]" ColumnId="1" />

  </ColumnGroup>

  <ColumnGroup Usage="INCLUDE">

  <Column Name="[ID]" ColumnId="2" />

  <Column Name="[TITRE]" ColumnId="3" />

  <Column Name="[DESCRIPTION]" ColumnId="4" />

  <Column Name="" ColumnId="6" />

  <Column Name="[DATE_MODIF]" ColumnId="7" />

  <Column Name="[IMAGE]" ColumnId="8" />

  </ColumnGroup>

  </MissingIndex>

  </MissingIndexGroup>

  </MissingIndexes>

  <RelOp AvgRowSize="1279" EstimateCPU="0.00100148" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="89.0897" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.257799">

  <OutputList>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="TITRE" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DESCRIPTION" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="URL" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DATE_MODIF" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="IMAGE" />

  <ColumnReference Column="Expr1003" />

  </OutputList>

  <MemoryFractions Input="1" Output="1" />

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="80" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <Sort Distinct="false">

  <OrderBy>

  <OrderByColumn Ascending="false">

  <ColumnReference Column="Expr1003" />

  </OrderByColumn>

  </OrderBy>

  <RelOp AvgRowSize="1279" EstimateCPU="0.00647974" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="89.0897" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.245536">

  <OutputList>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="TITRE" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DESCRIPTION" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="URL" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DATE_MODIF" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="IMAGE" />

  <ColumnReference Column="Expr1003" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="80" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <Merge ManyToMany="false">

  <InnerSideJoinColumns>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="ID" />

  </InnerSideJoinColumns>

  <OuterSideJoinColumns>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  </OuterSideJoinColumns>

  <Residual>

  <ScalarOperator ScalarString="FulltextMatch.[docid]=[cameleshop].[dbo].[RECHERCHE_CONTENU].[ID] as [CONT].[ID]">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="ID" />

  </Identifier>

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Residual>

  <RelOp AvgRowSize="15" EstimateCPU="0.000358997" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="325.594" LogicalOp="Aggregate" NodeId="2" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0075361">

  <OutputList>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  <ColumnReference Column="Expr1003" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <StreamAggregate>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1003" />

  <ScalarOperator ScalarString="MAX([Expr1002])">

  <Aggregate AggType="MAX" Distinct="false">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1002" />

  </Identifier>

  </ScalarOperator>

  </Aggregate>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <GroupBy>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  </GroupBy>

  <RelOp AvgRowSize="15" EstimateCPU="0.0003597" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="327" LogicalOp="Aggregate" NodeId="3" Parallel="false" PhysicalOp="Stream Aggregate" EstimatedTotalSubtreeCost="0.0071771">

  <OutputList>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  <ColumnReference Column="Expr1002" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="450" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <StreamAggregate>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1002" />

  <ScalarOperator ScalarString="MAX([Expr1004])">

  <Aggregate AggType="MAX" Distinct="false">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Column="Expr1004" />

  </Identifier>

  </ScalarOperator>

  </Aggregate>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <GroupBy>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  <ColumnReference Table="[FulltextMatch]" Column="colid" />

  </GroupBy>

  <RelOp AvgRowSize="19" EstimateCPU="3.27E-05" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="327" LogicalOp="Compute Scalar" NodeId="4" Parallel="false" PhysicalOp="Compute Scalar" EstimatedTotalSubtreeCost="0.0068174">

  <OutputList>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  <ColumnReference Table="[FulltextMatch]" Column="colid" />

  <ColumnReference Column="Expr1004" />

  </OutputList>

  <ComputeScalar>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Column="Expr1004" />

  <ScalarOperator ScalarString="containstablerank(FulltextMatch.[documentcount],FulltextMatch.[itemcount],FulltextMatch.[termfrequency],FulltextMatch.[documentlength])">

  <Intrinsic FunctionName="containstablerank">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="[FulltextMatch]" Column="documentcount" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="[FulltextMatch]" Column="itemcount" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="[FulltextMatch]" Column="termfrequency" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Identifier>

  <ColumnReference Table="[FulltextMatch]" Column="documentlength" />

  </Identifier>

  </ScalarOperator>

  </Intrinsic>

  </ScalarOperator>

  </DefinedValue>

  </DefinedValues>

  <RelOp AvgRowSize="43" EstimateCPU="0.0036597" EstimateIO="0.003125" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="327" LogicalOp="Table-valued function" NodeId="5" Parallel="false" PhysicalOp="Table-valued function" EstimatedTotalSubtreeCost="0.0067847">

  <OutputList>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  <ColumnReference Table="[FulltextMatch]" Column="colid" />

  <ColumnReference Table="[FulltextMatch]" Column="documentcount" />

  <ColumnReference Table="[FulltextMatch]" Column="documentlength" />

  <ColumnReference Table="[FulltextMatch]" Column="termfrequency" />

  <ColumnReference Table="[FulltextMatch]" Column="itemcount" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRebinds="1" ActualRewinds="0" ActualRows="450" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <TableValuedFunction>

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="docid" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="colid" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="documentcount" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="documentlength" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="termfrequency" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Table="[FulltextMatch]" Column="itemcount" />

  </DefinedValue>

  </DefinedValues>

  <Object Table="[FulltextMatch]" />

  <ParameterList>

  <ScalarOperator ScalarString="(1)">

  <Const ConstValue="(1)" />

  </ScalarOperator>

  <ScalarOperator ScalarString="(1)">

  <Const ConstValue="(1)" />

  </ScalarOperator>

  <ScalarOperator ScalarString="(1)">

  <Const ConstValue="(1)" />

  </ScalarOperator>

  <ScalarOperator ScalarString="(0)">

  <Const ConstValue="(0)" />

  </ScalarOperator>

  <ScalarOperator ScalarString="NULL">

  <Const ConstValue="NULL" />

  </ScalarOperator>

  <ScalarOperator ScalarString="NULL">

  <Const ConstValue="NULL" />

  </ScalarOperator>

  </ParameterList>

  </TableValuedFunction>

  </RelOp>

  </ComputeScalar>

  </RelOp>

  </StreamAggregate>

  </RelOp>

  </StreamAggregate>

  </RelOp>

  <RelOp AvgRowSize="1283" EstimateCPU="0.0012493" EstimateIO="0.229792" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="89.0897" LogicalOp="Clustered Index Scan" NodeId="21" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.231041" TableCardinality="993">

  <OutputList>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="ID" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="TITRE" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DESCRIPTION" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="URL" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DATE_MODIF" />

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="IMAGE" />

  </OutputList>

  <RunTimeInformation>

  <RunTimeCountersPerThread Thread="0" ActualRows="90" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <IndexScan Ordered="true" ScanDirection="FORWARD" ForcedIndex="false" ForceSeek="false" ForceScan="false" NoExpandHint="false">

  <DefinedValues>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="ID" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="TITRE" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DESCRIPTION" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="URL" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="DATE_MODIF" />

  </DefinedValue>

  <DefinedValue>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="IMAGE" />

  </DefinedValue>

  </DefinedValues>

  <Object Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Index="[PK_RECHERCHE_CONTENU]" Alias="[CONT]" IndexKind="Clustered" />

  <Predicate>

  <ScalarOperator ScalarString="[cameleshop].[dbo].[RECHERCHE_CONTENU].[ID_SITE] as [CONT].[ID_SITE]=(1)">

  <Compare CompareOp="EQ">

  <ScalarOperator>

  <Identifier>

  <ColumnReference Database="[cameleshop]" Schema="[dbo]" Table="[RECHERCHE_CONTENU]" Alias="[CONT]" Column="ID_SITE" />

  </Identifier>

  </ScalarOperator>

  <ScalarOperator>

  <Const ConstValue="(1)" />

  </ScalarOperator>

  </Compare>

  </ScalarOperator>

  </Predicate>

  </IndexScan>

  </RelOp>

  </Merge>

  </RelOp>

  </Sort>

  </RelOp>

  </QueryPlan>

  </StmtSimple>

  </Statements>

  </Batch>

  </BatchSequence>

  </ShowPlanXML>

  Stéphane Demers
  Web/ecommerce Consultant
  CaméléWeb - ecommerce Solution: CaméléShop
  www.cameleweb.com[/url]

• Ninja's_RGR'us

  SSC Guru

  Points: 294069

  More actions

  April 9, 2012 at 12:13 pm

  #1470817

  We're all asking for the table's definition for a reason 😉

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 9, 2012 at 12:14 pm

  #1470818

  Please read and follow the instructions in these two articles:

  http://www.sqlservercentral.com/articles/Best+Practices/61537/

  http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  Doing so will get you the best answers possible to you problem.

• CameleWeb

  Old Hand

  Points: 365

  More actions

  April 9, 2012 at 12:16 pm

  #1470820

  As you can see, I’m not an SQL expert 😉

  Here what I found about the "CREATE TO" script for RECHERCHE_CONTENU:

  USE [cameleshop]

  GO

  /****** Object: Index [PK_RECHERCHE_CONTENU] Script Date: 04/09/2012 14:11:43 ******/

  ALTER TABLE [dbo].[RECHERCHE_CONTENU] ADD CONSTRAINT [PK_RECHERCHE_CONTENU] PRIMARY KEY CLUSTERED

  (

  [ID] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  GO

  And here's the table "CREATE TO" script :

  USE [cameleshop]

  GO

  /****** Object: Table [dbo].[RECHERCHE_CONTENU] Script Date: 04/09/2012 14:14:35 ******/

  SET ANSI_NULLS ON

  GO

  SET QUOTED_IDENTIFIER ON

  GO

  CREATE TABLE [dbo].[RECHERCHE_CONTENU](

  [ID_SITE] [int] NULL,

  [ID] [int] IDENTITY(1,1) NOT NULL,

  [TITRE] [nvarchar](100) NULL,

  [DESCRIPTION] [nvarchar](500) NULL,

  [CONTENU] [nvarchar](max) NULL,

  [nvarchar](500) NULL,

  [DATE_MODIF] [datetime] NULL,

  [IMAGE] [nvarchar](150) NULL,

  CONSTRAINT [PK_RECHERCHE_CONTENU] PRIMARY KEY CLUSTERED

  (

  [ID] ASC

  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

  ) ON [PRIMARY]

  GO

  ALTER TABLE [dbo].[RECHERCHE_CONTENU] WITH CHECK ADD CONSTRAINT [FK_RECHERCHE_CONTENU_SITE_WEB] FOREIGN KEY([ID_SITE])

  REFERENCES [dbo].[SITE_WEB] ([id_site])

  ON UPDATE CASCADE

  ON DELETE CASCADE

  GO

  ALTER TABLE [dbo].[RECHERCHE_CONTENU] CHECK CONSTRAINT [FK_RECHERCHE_CONTENU_SITE_WEB]

  GO

  Stéphane Demers
  Web/ecommerce Consultant
  CaméléWeb - ecommerce Solution: CaméléShop
  www.cameleweb.com[/url]