SQL request performance problem

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 9, 2012 at 12:18 pm

  #1470825

  Missing the full_text index script.

• CameleWeb

  Old Hand

  Points: 375

  More actions

  April 9, 2012 at 12:27 pm

  #1470841

  How can I get the full_text index script?

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

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  April 9, 2012 at 12:49 pm

  #1470869

  IN SSMS you need to go to Tools -> Options -> Scripting and set Script FUllText Indexes to True. Then when you generate the table script it will include the FullText index

  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

• CameleWeb

  Old Hand

  Points: 375

  More actions

  April 9, 2012 at 1:17 pm

  #1470886

  Thanks!

  Here's the script again :

  USE [cameleshop]

  GO

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

  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

  /****** Object: FullTextIndex Script Date: 04/09/2012 15:15:09 ******/

  CREATE FULLTEXT INDEX ON [dbo].[RECHERCHE_CONTENU](

  [CONTENU] LANGUAGE [English],

  [DESCRIPTION] LANGUAGE [English],

  [IMAGE] LANGUAGE [English],

  [TITRE] LANGUAGE [English],

  LANGUAGE [English])

  KEY INDEX [PK_RECHERCHE_CONTENU]ON ([RECHERCHE], FILEGROUP [PRIMARY])

  WITH (CHANGE_TRACKING = AUTO, STOPLIST = SYSTEM)

  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]

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  April 9, 2012 at 1:39 pm

  #1470903

  I don't know how FT reacts to the blob types like nvarchar(max) which you have in the table, but I do know that the inclusion of these columns in any select statement that returns more than 1 row does slow down the query significantly. I can't say that it the issue here, but I'd be willing to bet that is the issue.

  What happens if you changed it to use SOUNDEX() instead of the FT index? I'm not saying the results will be the same, but I'd be interested in what happens and how close you come.

  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

• CameleWeb

  Old Hand

  Points: 375

  More actions

  April 9, 2012 at 2:53 pm

  #1470935

  Sorry, I dont know all about SOUNDEX. Sounds complicated...

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

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  April 9, 2012 at 3:00 pm

  #1470940

  CameleWeb (4/9/2012)

  ---

  Sorry, I dont know all about SOUNDEX. Sounds complicated...

  Sorry, that's my bad, as SOUNDEX wouldn't work here anyway.

  Basically I'm trying to find a way to get away from the FT Index to see if that is the issue and LIKE wouldn't even be close because you are using FORMSOF(INFLECTIONAL, n) which is kind of like SOUNDEX.

  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

• Jeff Moden

  SSC Guru

  Points: 1004329

  More actions

  April 9, 2012 at 8:24 pm

  #1471034

  My first step would be to add the recommended missing index. FTS is quite fast when properly indexed. It's really not much more than the output of a split fuction under the covers.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• CameleWeb

  Old Hand

  Points: 375

  More actions

  April 10, 2012 at 3:45 pm

  #1471566

  I added the missing index... I'll see if this fixes the performance!

  I'll keep you informed!

  Thanks all for your help!

  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 12, 2012 at 7:12 am

  #1472553

  Any news?

• CameleWeb

  Old Hand

  Points: 375

  More actions

  April 12, 2012 at 7:50 am

  #1472579

  The problem is still there... But here the new 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.1" Build="10.50.2500.0" xmlns="http://schemas.microsoft.com/sqlserver/2004/07/showplan">

  <BatchSequence>

  <Batch>

  <Statements>

  <StmtSimple StatementCompId="1" StatementEstRows="93" StatementId="1" StatementOptmLevel="FULL" StatementOptmEarlyAbortReason="GoodEnoughPlanFound" StatementSubTreeCost="0.258602" 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="33718" CompileCPU="2312" CompileMemory="288">

  <RelOp AvgRowSize="1279" EstimateCPU="0.00104998" EstimateIO="0.0112613" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="93" LogicalOp="Sort" NodeId="0" Parallel="false" PhysicalOp="Sort" EstimatedTotalSubtreeCost="0.258602">

  <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="82" ActualEndOfScans="1" ActualExecutions="1" />

  </RunTimeInformation>

  <Sort Distinct="false">

  <OrderBy>

  <OrderByColumn Ascending="false">

  <ColumnReference Column="Expr1003" />

  </OrderByColumn>

  </OrderBy>

  <RelOp AvgRowSize="1279" EstimateCPU="0.00648835" EstimateIO="0" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="93" LogicalOp="Inner Join" NodeId="1" Parallel="false" PhysicalOp="Merge Join" EstimatedTotalSubtreeCost="0.246291">

  <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="82" 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="452" 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="452" 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="452" 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.0012526" EstimateIO="0.230532" EstimateRebinds="0" EstimateRewinds="0" EstimateRows="93" LogicalOp="Clustered Index Scan" NodeId="21" Parallel="false" PhysicalOp="Clustered Index Scan" EstimatedTotalSubtreeCost="0.231785" TableCardinality="996">

  <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="93" ActualEndOfScans="0" 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 12, 2012 at 8:00 am

  #1472589

  P.S. You can upload the file rather than paste it, it's easier for us.

  What happens if you run the query without the join? Just run the query with the fts part.

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  April 12, 2012 at 8:01 am

  #1472590

  Interesting that the added index was not used as it still did an CI Scan.

  I have a couple more questions:

  How many rows are in the table?

  Does the CONTENU column have to be nvarchar(max)? I ask this because the blob datatypes are treated differently and when they are included in a result set they do tend to slow down the query. Could you determine the max length of the data in the table and set the column to NVARCHAR(the max length of the data)?

  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

• CameleWeb

  Old Hand

  Points: 375

  More actions

  April 12, 2012 at 8:12 am

  #1472604

  Jack Corbett (4/12/2012)

  ---

  Interesting that the added index was not used as it still did an CI Scan.

  I have a couple more questions:

  How many rows are in the table?

  1000 rows

  Does the CONTENU column have to be nvarchar(max)? I ask this because the blob datatypes are treated differently and when they are included in a result set they do tend to slow down the query. Could you determine the max length of the data in the table and set the column to NVARCHAR(the max length of the data)?

  Well, I guess yes, since I need to put in there a text I never know how long it could be. IE, I have a page with more than 15000 characters.

  You said that the blob data types do tend to slow down the query. But should this slow down the query at all time?

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

• Jack Corbett

  SSC Guru

  Points: 184394

  More actions

  April 12, 2012 at 8:28 am

  #1472614

  Okay, it sounds like you do need the MAX datatype. I asked because there are often people who throw MAX out there and could work without it.

  What makes it slower is that once you overflow a page with a BLOB SQL Server stores a pointer to another page or pages which store the BLOB. This can seriously affect performance.

  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