Professional Web Applications Themes

Optimize this query... - Microsoft SQL / MS SQL Server

"Jignesh Doshi" <com> wrote in message news:phx.gbl...  top  Fix your data so the query can be written as: Select top 100 * from Table1 where col1 like '320%' That can use an index on col1. David...

  1. #1

    Default Re: Optimize this query...


    "Jignesh Doshi" <com> wrote in message
    news:phx.gbl... 
    top 

    Fix your data so the query can be written as:

    Select top 100 * from Table1
    where col1 like '320%'

    That can use an index on col1.

    David



    David Guest

  2. #2

    Default Re: Optimize this query...

    changing the column type is not possible..

    jignesh

    "David Browne" <davidbaxterbrowne no potted com> wrote in
    message news:#phx.gbl... [/ref]
    is 
    > top 
    >
    > Fix your data so the query can be written as:
    >
    > Select top 100 * from Table1
    > where col1 like '320%'
    >
    > That can use an index on col1.
    >
    > David
    >
    >
    >[/ref]


    Jignesh Guest

  3. #3

    Default Re: Optimize this query...

    since it is numeric, you don't really need the trims.
    try this
    Select top100* from Table1
    where Cast(col1 as char(3)) = '320'

    if that is not fast enough, then you have to create an indexed materialized
    view
    on table1 that has cast(col1 as char(3)) as a separate column and then index
    the column



    "David Browne" <davidbaxterbrowne no potted com> wrote in
    message news:#phx.gbl... [/ref]
    is 
    > top 
    >
    > Fix your data so the query can be written as:
    >
    > Select top 100 * from Table1
    > where col1 like '320%'
    >
    > That can use an index on col1.
    >
    > David
    >
    >
    >[/ref]


    HSalim Guest

  4. #4

    Default Re: Optimize this query...

    Both CAST(col1 AS CHAR(3)) and LEFT(col1, 3) will do a convert on the col1
    column from int to char(3) (the first one explicit, the second one implicit)
    which means that an index on col1 won't be used, as is the case with the
    original SQL from Jignesh.

    If the datatype of col1 one can't be changed than the solution is either, as
    you pointed out earlier, to create an indexed view, or to create a computed,
    indexed column. The indexed view is probably the best solution.

    hth

    Jacco

    "HSalim" <com> wrote in message
    news:O%phx.gbl... [/ref][/ref]
    It [/ref][/ref]
    include 
    > >
    > > Fix your data so the query can be written as:
    > >
    > > Select top 100 * from Table1
    > > where col1 like '320%'
    > >
    > > That can use an index on col1.
    > >
    > > David
    > >
    > >
    > >[/ref]
    >
    >[/ref]


    Jacco Guest

  5. #5

    Default Re: Optimize this query...

    set rowcount 100

    select * from table1 where col1 between 320 and 329
    union all
    select * from table1 where col1 between 3200 and 3299
    union all
    select * from table1 where col1 between 32000 and 32999
    union all
    select * from table1 where col1 between 320000 and 329999
    union all
    select * from table1 where col1 between 3200000 and 3299999

    set rowcount 0

    Gert-Jan


    Jignesh Doshi wrote: 
    Gert-Jan Guest

Similar Threads

  1. Optimize Query
    By umuayo in forum Coldfusion Database Access
    Replies: 7
    Last Post: April 14th, 03:58 PM
  2. PDF won't optimize
    By Beth_R_Phillips@adobeforums.com in forum Adobe Acrobat Macintosh
    Replies: 3
    Last Post: February 8th, 11:25 PM
  3. Help optimize query
    By anilcool@gmail.com in forum MySQL
    Replies: 4
    Last Post: June 28th, 06:48 PM
  4. How to optimize?
    By Krillo webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 1
    Last Post: August 4th, 06:38 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139