SQL DELETE - best practice?

[marjoh]

Hi!

I have a problem deleting records from a database (sql 2005) in a c++ program. The query I've made is simple:
DELETE FROM Tbl1 WHERE ID IN ('1','2','3')
Yet I receive a "timeout expired" error message. The table holds 250 recs, so it's pretty small.
Is "ID IN ('1','2'..." the wrong way of dealing with multiple records when deleting? Any ideas how I could modify my code to avoid the timeout?

Thanks!

[davide++]

Hi all.

The query is right; you can use three ORs instead of IN operator, but IN is better than ORs from the point of view of performance. In my opinion there's another problem; maybe the C++ application puts a lock on the table before executing the DELETE, so the timeout fires.

[marjoh]

Thanks for the reply!

I just found out that when I run the same query in MSSQL Server Management Studio I also get a timeout. I don't understand how it can result in a timeout when I'm simply trying to delete 3 records!?
Any ideas? Anyone?

[davide++]

Hi all

Timeouts fire when the db engine cannot perform the operations by the time of timeout period...
One (common) reason maybe a lock on the table, but I don't think this is your scenario, because you have the same problem when perform the command within MSSQL Server Management Studio. So I suggest to heck the value of timeout, if it's too small.

[hspc]

Your query is OK Marjoh.
Also deleting 3 rows should not timeout as davide++ explained. This makes me ask:
Is your table referenced in a foreign key constraint with (on delete cascade) by a big table that causes the query to timeout when you try to delete ?