simple query timesout

[jayjay66]

Hi

I get the following message when I try to run the simple query.

Error message:
[Microsoft][ODBC SQL Server Driver] Timeout expired.

SLQ Query:
DELETE FROM Accounting
WHERE (LogDate <= '2005/12/31 23:59:59')

Please help me with this.

Thanks,
JJ

 

[jbenson001]

Try increasing the timeout duration on the SQLServer

 

[jayjay66]

Hi,

I increased it to the max & it still timesout.

Any other suggestions please.

Thanks,
JJ

 

[gmmastros]

Do you have an index on the LogDate field in the Accounting table? Try adding an index and then see if the performance improves.

It could be that a table scan is causing your problem (probably because there are a ton of records in this table).

You may also benefit by creating a variable with the DateTime data type and using that instead.

[tt][blue]
Declare @TheDate DateTime

Set @TheDate = '2005/12/31 23:59:59'

DELETE FROM Accounting
WHERE (LogDate <= @TheDate)
[/blue][/tt]

The theory is that SQL Server may be converting the string to a DateTime data type for each record in the table (which would be slower than converting it once and comparing it to a DateTime variable).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLSister]

You might try doing the delete in batches.
How to Use Batch Size to Speed Mass Updates, Inserts and Deletes faq183-3141

You might also consider if you have cascading deletes or triggers that they may be what is causing the delete to take so long. If you have related tables with a cascading dete, try deleting the related records from those tables first, then the main table. If you have triggers make sure they are not running cursors but are acting on allthe records as a whole.




Questions about posting. See faq183-874

 

[RobertT687]

Another thing to look at is a clustered index on the table.
If there is one, SQL Server will be physically rearranging the data to match the index. Consider dropping the clustered index and recreating it after the delete finishes. Also, you may gain some speed by wrapping the DELETE in a TRANsaction.
Also, if the delete WHERE clause would effect the majority of the rows in a table, it MIGHT be more efficient to INSERT the remaining rows into a new table, then drop the original and rename the new table. Remember to recreate any Indexes, triggers, constraints etc to match the original.