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.

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:
If not then create composite clustered index on this and datetime field.

Thanks,
Meet

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

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.

You need to truncate time from getutcdate() i.e.