Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2010
    Posts
    2

    Unanswered: help to tune this query

    Hello all,

    Could someone please help me to tune this query:

    DELETE FROM SomeTable where datediff(day,dateTime, getutcdate()) >30 and someField = 'xxxx'

    It seems this query requires table scan. It's no good. Maybe the solution is to create indexes.

    What's the ideal query?

    Thank you.

  2. #2
    Join Date
    Jun 2010
    Posts
    51
    How many rows does the table have? If there are not many rows (only ~5000) then sybase may not choose to use index at all.

    Do you have index on the column:
    Code:
    and someField = 'xxxx'
    If not then create composite clustered index on this and datetime field.

    Thanks,
    Meet
    Please always reply to the post if it was helpful. Others may find it helpful.

  3. #3
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Avoid WHERE clauses that are non-sargable
    If you use a function on a column then any index defined on that column can't be used.
    A better way is
    where dateTime < dateadd(day, -30, getutcdate())

    And it also depends on the percentage of qualifying rows
    i.e. if the majority of rows qualify for
    and someField = 'xxxx'
    then an index on someField will not be of much use

  4. #4
    Join Date
    Dec 2010
    Posts
    2
    Thanks for your replies and sorry for the late response!

    Could someone please help me to check why these two queries return different values?

    select count(*) FROM WebUsageStatsTb where datediff(day,dateTime, getutcdate()) >30 and website = 'xxx'

    select count(*) FROM WebUsageStatsTb where dateTime < dateadd(day, -30, getutcdate()) and website = 'xxx'

    Actually I have to retire this query:

    DELETE FROM WebUsageStatsTb where datediff(day,dateTime, getutcdate()) >30 and website = 'xxx'

    and use a query that contains dateadd() instead of datediff(), in order to have a better performance.

    Thank you.

    Hugo.

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    You need to truncate time from getutcdate() i.e.
    Code:
    ...
    where dateTime<dateadd(dd,datediff(dd,'',getutcdate())-30,'')
    ...

Posting Permissions

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