Professional Web Applications Themes

Query execution time? - Microsoft SQL / MS SQL Server

Hi all, 1. Select * from table where ADEDATE = '23 June 2003' in SQLServer2000 This one works fine immediately. 2.Select * from table where convert(char(10),ADEDATE,121) = convert(char(10),getdate(),121) This takes a lot of time and also the consumes the complete resources of the system I understand that first query is working fast as it based on index.When we use CONVERT in 2nd query the execution time is more than 45 mins. How to make the second query work fast?The table has 51 million records. If we have to create indexes or whatever please mention in detail. Thanks Clive...

  1. #1

    Default Query execution time?

    Hi all,

    1.
    Select * from table where ADEDATE = '23 June 2003' in SQLServer2000
    This one works fine immediately.

    2.Select * from table where convert(char(10),ADEDATE,121) =
    convert(char(10),getdate(),121)
    This takes a lot of time and also the consumes the complete resources of the
    system

    I understand that first query is working fast as it based on index.When we
    use CONVERT in 2nd query the execution time is more than 45 mins.

    How to make the second query work fast?The table has 51 million records.

    If we have to create indexes or whatever please mention in detail.

    Thanks
    Clive


    Clive Guest

  2. #2

    Default Re: Query execution time?

    SELECT * FROM [table] WHERE ADEDATE >= convert(char(8),getdate(),112)
    AND ADEDATE < convert(char(8),getdate() +1,112)

    Always use 112 for as the datetime style when you do date comparisons. It is
    always interpreted the same independent of any regional/language/dateformat
    settings.

    --
    Jacco Schalkwijk MCDBA, MCSD, MCSE
    Database Administrator
    Eurostop Ltd.


    "Clive" <cutercheeersinternational.com> wrote in message
    news:OZw0dIiRDHA.560TK2MSFTNGP10.phx.gbl...
    > Hi all,
    >
    > 1.
    > Select * from table where ADEDATE = '23 June 2003' in SQLServer2000
    > This one works fine immediately.
    >
    > 2.Select * from table where convert(char(10),ADEDATE,121) =
    > convert(char(10),getdate(),121)
    > This takes a lot of time and also the consumes the complete resources of
    the
    > system
    >
    > I understand that first query is working fast as it based on index.When we
    > use CONVERT in 2nd query the execution time is more than 45 mins.
    >
    > How to make the second query work fast?The table has 51 million records.
    >
    > If we have to create indexes or whatever please mention in detail.
    >
    > Thanks
    > Clive
    >
    >

    Jacco Schalkwijk Guest

  3. #3

    Default Re: Query execution time?

    Thanks a lot Jacco,

    Sometimes its very funny how a small thing can work....i tried the same kind
    of condition but not with 112 format.
    This again proves EXPERIENCE is not replaceable.

    Thanks again
    Clive

    "Jacco Schalkwijk" <NOSPAMjaccoseurostop.co.uk> wrote in message
    news:uEaYGiiRDHA.2636TK2MSFTNGP10.phx.gbl...
    > SELECT * FROM [table] WHERE ADEDATE >= convert(char(8),getdate(),112)
    > AND ADEDATE < convert(char(8),getdate() +1,112)
    >
    > Always use 112 for as the datetime style when you do date comparisons. It
    is
    > always interpreted the same independent of any
    regional/language/dateformat
    > settings.
    >
    > --
    > Jacco Schalkwijk MCDBA, MCSD, MCSE
    > Database Administrator
    > Eurostop Ltd.
    >
    >
    > "Clive" <cutercheeersinternational.com> wrote in message
    > news:OZw0dIiRDHA.560TK2MSFTNGP10.phx.gbl...
    > > Hi all,
    > >
    > > 1.
    > > Select * from table where ADEDATE = '23 June 2003' in
    SQLServer2000
    > > This one works fine immediately.
    > >
    > > 2.Select * from table where convert(char(10),ADEDATE,121) =
    > > convert(char(10),getdate(),121)
    > > This takes a lot of time and also the consumes the complete resources of
    > the
    > > system
    > >
    > > I understand that first query is working fast as it based on index.When
    we
    > > use CONVERT in 2nd query the execution time is more than 45 mins.
    > >
    > > How to make the second query work fast?The table has 51 million
    records.
    > >
    > > If we have to create indexes or whatever please mention in detail.
    > >
    > > Thanks
    > > Clive
    > >
    > >
    >
    >

    Clive Guest

  4. #4

    Default Re: Query execution time?

    Hi Clive,

    to add to the two responses above, if in some cases you must use an
    expression in the WHERE clause, you can generate a view that returns exactly
    that expression as a column and then create an index on this view. This way,
    you can still use an index and avoid a table scan.

    Martin

    "Clive" <cutercheeersinternational.com> wrote in message
    news:OZw0dIiRDHA.560TK2MSFTNGP10.phx.gbl...
    > Hi all,
    >
    > 1.
    > Select * from table where ADEDATE = '23 June 2003' in SQLServer2000
    > This one works fine immediately.
    >
    > 2.Select * from table where convert(char(10),ADEDATE,121) =
    > convert(char(10),getdate(),121)
    > This takes a lot of time and also the consumes the complete resources of
    the
    > system
    >
    > I understand that first query is working fast as it based on index.When we
    > use CONVERT in 2nd query the execution time is more than 45 mins.
    >
    > How to make the second query work fast?The table has 51 million records.
    >
    > If we have to create indexes or whatever please mention in detail.
    >
    > Thanks
    > Clive
    >
    >

    Martin Lingl Guest

Similar Threads

  1. Get execution plan of dynamic query
    By Алексей Ш. in forum PostgreSQL / PGSQL
    Replies: 1
    Last Post: January 9th, 05:05 PM
  2. Execution time consuming the same Webservice Vb6 vs VB.Net
    By Ghislain Tanguay in forum ASP.NET Web Services
    Replies: 0
    Last Post: July 29th, 02:36 PM
  3. page execution time
    By Joel Barsotti in forum ASP
    Replies: 2
    Last Post: August 6th, 08:47 PM
  4. [PHP] Script Execution Time
    By Chris W. Parker in forum PHP Development
    Replies: 2
    Last Post: July 29th, 10:35 AM
  5. Script Execution Time
    By Radek Zajkowski in forum PHP Development
    Replies: 3
    Last Post: July 28th, 09:18 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