Post reply

SQL request performance problem

  • April 9, 2012 at 12:18 pm

    #1470825

    Missing the full_text index script.

  • 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]

  • 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

  • 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]

  • 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

  • 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]

  • 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

  • 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)

  • 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]

  • April 12, 2012 at 7:12 am

    #1472553

    Any news?

  • 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]

  • 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.

  • 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

  • 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]

  • 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

Viewing 15 posts - 16 through 30 (of 53 total)

You must be logged in to reply to this topic. Login to reply