Professional Web Applications Themes

How to SPEEDUP QUERY EXECUTION? - Microsoft SQL / MS SQL Server

Hi all, Iam executing the following query and this works absolutely fine. -------------- Select ADETDATE from CC_CDR where ADETDATE >=convert(char(8),getdate(),112) and ADETDATE <convert(char(8),getdate()+1,112) -------------------- Iam executing the same query but with EXTRA COLUMN and this takes lots of time. -------------------- Select ADETDATE , ADA from CC_CDR where ADETDATE >=convert(char(8),getdate(),112) and ADETDATE <convert(char(8),getdate()+1,112) -------------------- Note:When i execute the second query with fixed dates rather than getdate() then its executes fine. If i use CONVERT with getdate() then its slows down(only with extra column). The CC_CDR table has 51 million records. Could you help me with a standard solution? Thanks...

  1. #1

    Default How to SPEEDUP QUERY EXECUTION?

    Hi all,

    Iam executing the following query and this works absolutely fine.
    --------------
    Select ADETDATE from CC_CDR
    where ADETDATE >=convert(char(8),getdate(),112)
    and ADETDATE <convert(char(8),getdate()+1,112)
    --------------------

    Iam executing the same query but with EXTRA COLUMN and this takes lots of
    time.
    --------------------
    Select ADETDATE , ADA from CC_CDR
    where ADETDATE >=convert(char(8),getdate(),112)
    and ADETDATE <convert(char(8),getdate()+1,112)
    --------------------
    Note:When i execute the second query with fixed dates rather than getdate()
    then its executes fine.
    If i use CONVERT with getdate() then its slows down(only with extra column).
    The CC_CDR table has 51 million records.

    Could you help me with a standard solution?

    Thanks


    Clive Guest

  2. #2

    Default Re: How to SPEEDUP QUERY EXECUTION?

    The fact that the first query is very fast indicates that you have an index
    on ADETDATE.

    The reason for the long execution time in the second case is most likely
    that the database optimizer selects a table scan instead of an index seek
    followed by a number of bookmark lookups.

    The reason for the good performance if you use hardcoded dates is that with
    hardcoded dates, the query optimizer can use the statistics for the ADETDATE
    column to estimate how many rows will match the condition. If only a few
    rows match, it will use the index because it is faster.

    If you have expressions in the query, the optimizer selects a plan that
    avoids worst-case behavior, so to avoid the potentiual cost of a large
    number of bookmark lookups it selects a table scan instead.

    There are two ways to handle this situation:

    1) Modify the indexes so you can avoid bookmark lookups.

    Either make the index on ADETDATE clustered, or if that is not possible, add
    the column ADA to the ADETDATE index (thus creating a covering index)

    2) Give the query optimizer more information so it can select a better plan

    If you know (because you know your data) that the query will never return
    more than say 1000 rows, you can write the query like this:

    Select TOP 1000 ADETDATE , ADA from CC_CDR
    where ADETDATE >=convert(char(8),getdate(),112)
    and ADETDATE <convert(char(8),getdate()+1,112)

    This will tell the optimizer that it will never have to perform more than
    1000 bookmark lookups, so it will probably use the index instead of
    performaing a table scan.

    /SG


    "Clive" <com> wrote in message
    news:phx.gbl... 
    getdate() 
    column). 


    Stefan Guest

  3. #3

    Default Re: How to SPEEDUP QUERY EXECUTION?

    If you have an index on (ADEDATE,ADA) you will not have to specify an index
    hint, it will be used automatically.

    Specifying an index hint is actually a third way to solve your problem -
    instead of letting the optimizer choose a plan you are telling it to use the
    index. I try to stay away from index hints as much as possible, because they
    will cause the code to stop working if you later on decide to rename or drop
    the index. In my opinion, indexes should only affect the speed of an
    application, not its correctness.

    /SG

    "Clive" <com> wrote in message
    news:phx.gbl... 
    > index [/ref]
    seek 
    > with 
    > ADETDATE 
    > add 
    > plan [/ref]
    return [/ref]
    than [/ref]
    > of 
    > > getdate() 
    > > column). 
    > >
    > >[/ref]
    >
    >[/ref]


    Stefan Guest

Similar Threads

  1. Query of Queries on query New type query
    By david_h in forum Coldfusion Database Access
    Replies: 2
    Last Post: May 6th, 08:55 PM
  2. Get execution plan of dynamic query
    By Алексей Ш. in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 9th, 05:05 PM
  3. altivec, no apparant speedup
    By Keith in forum Mac Programming
    Replies: 3
    Last Post: October 7th, 02:13 AM
  4. how to speedup tweening?
    By Charliedhq webforumsuser@macromedia.com in forum Macromedia Flash Sitedesign
    Replies: 1
    Last Post: August 21st, 02:07 PM
  5. Query execution time?
    By Clive in forum Microsoft SQL / MS SQL Server
    Replies: 3
    Last Post: July 10th, 11:33 AM

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