Post reply

Performance Issue!!Please help me

  • October 25, 2007 at 12:12 am

    #126415

    Hi ,

    Could anyone help me out on this...

    It is not possible to create index on text data type.

    So once we run a query it will definitely go for table scan.Therefore it wil decrease the performance.

    In order to increase the performance of the query(Change Table Scan to Seek) what should I do?

    Thanks in advance

  • October 25, 2007 at 12:27 am

    #745227

    Hi,

    If your text data is a maximum of 8000, then you may use varchar(8000) instead of text data type.

    Osama

  • October 25, 2007 at 12:32 am

    #745228

    Have you considered inplementing full-text indexing?

    Could you possibly post the schema and the query so we can take a look?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 25, 2007 at 12:32 am

    #745229

    I want text data type only.My text is more than 8000 char

    What should I do in this scenario?

  • October 25, 2007 at 12:34 am

    #745231

    try out these samples..

    technet.microsoft.com/library/ms345118.aspx

    technet.microsoft.com/en-us/library/ms345121.aspx

  • October 25, 2007 at 12:37 am

    #745233

    Create Table ItemDetail(Itemid int, ItemCodeDescription text)

    The table contains 20 million records.

    Now I want to increase the performance ie.Change the table scan to seek

    for the following query

    SELECT * FROM ItemDetail WHERE ItemcodeDescription like 'Chocolate%'

  • October 25, 2007 at 12:55 am

    #745237

    Hi

    Your best bet is to created a varchar (8000 or less) column if that is possible. then create a index on that.

    "Keep Trying"

  • October 25, 2007 at 12:56 am

    #745238

    Nisha,

    I think you should think of implementing full-text indexing capabilities.

    --Ramesh

  • October 25, 2007 at 12:56 am

    #745239

    Hi,

    So, as mentioned above, you may indeed find the best performance is to use the Ful Text Searching.

    If you don't want to do that and want to explore things, at the risk of not being Best Practice, provided the number of rows is also not too large, then you may do the following:

    1. estimate the maximum size. If it is more than 8000, what is it?? 80000 perhaps??

    2. Define 10 columns each 8000 .. and in addition to the original Text column, which you still have, you will also store the same info again in those 10 varchar extra columns but must make sure that the long text is portioned across those 10 extra columns. You can find the last occurrence of a blank and portion based on that.

    3. In all your T-SQL queries, you must use:

    if Varcharcol1 like '%chocolate' or Varchacol2 like '%chocolate' or .. Varchacol10 like '%chocolate' then ...

    Perhaps this lazy solution can help a bit .. but I personally would create a full text searching service as others described above.

    Osama

  • October 25, 2007 at 1:05 am

    #745243

    Full text does sound like the best option.

    If you don't want to go full text, and your queries are always of the form

    SELECT FROM ItemDetail WHERE ItemcodeDescription like 'abc%' then what you can try is create a varchar colum, round about 20 characters wide and put the first 20 characters of the text field in there. Then index that varchar column and change your queries so that they are of the form

    DECLARE @value VARCHAR(40), @shortValue = varchar(21)

    SET @value = 'Vanilla mocca coffee with hazelnut syrup'

    SET @ShortValue = LEFT(@Value,20) + '%'

    SET @value = @value + '%'

    SELECT FROM ItemDetail WHERE ItemcodeDescription like @value and ItemcodeDescrShort like @ShortValue -- ItemcodeDescrShort is the indxes varchar(20)

    That way, you should, if the data is selective enough, get a seek on that index with lookups and an additional filter based on the text field like.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 25, 2007 at 1:17 am

    #745248

    Osama Kandil (10/25/2007)

    3. In all your T-SQL queries, you must use:

    if Varcharcol1 like '%chocolate' or Varchacol2 like '%chocolate' or .. Varchacol10 like '%chocolate' then ...

    With wildcards at the begnning of the string, that's also not seekable on any index. The only way that construction can be queries is with a full table scan.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • October 25, 2007 at 1:39 am

    #745255

    Thank you. Of course. I meant it at the end of the string. But I think with 200 million records, Full Text index is the way to go.

    🙂

    Osama

  • October 25, 2007 at 10:14 pm

    #745564

    Is there a good reason for using TEXT? It is marked for deprecation.

  • October 25, 2007 at 10:46 pm

    #745569

    Since this is a 2k5 forum... my recommendation would be to convert the date in the TEXT column to a VARCHAR(MAX) column and then implement Full-text indexing as Gail originally suggested.

    --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)
    Intro to Tally Tables and Functions

Viewing 14 posts - 1 through 13 (of 13 total)

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