Results 1 to 5 of 5
  1. #1
    Join Date
    Nov 2004
    Location
    Dundee, Scotland
    Posts
    45

    Exclamation Unanswered: Performance Issues with SET DATEFORMAT command

    Hi,

    Are there any know performance problems with using the command SET DATEFORMAT dmy in SQL Server 2000 stored procedures? The reason I ask is because a particular stored procedure is taking 750ms to execute, yet when I remove 'SET DATEFORMAT dmy' line the execution times only takes 20ms (as in twenty - no typo). I mean that's a massive difference, why does SET DATEFORMAT command do this, is there a faster alternative?

    As for the sProc itself it does a DateTime comparison on a VarChar field that stores a date time value (yes, I know it's not very well normalised/typed), eg.

    Code:
    AND b.value >= GetDate()
    ...where value is a VarChar field.

    Thanks for any help!
    Sunday Ironfoot

    SQL Server 2000 n00b

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    functions on columns can negate index usage.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Quote Originally Posted by Thrasymachus
    functions on columns can negate index usage.
    http://www.sqlteam.com/article/avoid...e-where-clause
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  4. #4
    Join Date
    Nov 2004
    Location
    Dundee, Scotland
    Posts
    45
    Come to think of it, I think that column has been indexed. So this could be the cause of it.

    BTW I've removed the SET DATEFORMAT command and replaced the WHERE clause part with...

    Code:
    AND CONVERT(DATETIME, b.value, 103) >= GetDate()
    ...and this executes soo much quicker. Thing is, surely this is technically a function on an indexed column, so how is it faster than the SET DATEFORMAT command?
    Sunday Ironfoot

    SQL Server 2000 n00b

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Have a look at the estimated query plans. Maybe the index on the date field is not good for the query, and an alternate index is much better. you can also update statistics on the table, to make sure that the optimizer has the latest and greatest statistics to resolve query plans with.

Posting Permissions

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