Help on SQL query

Question

Hi,
Can you let me know, which of the following query execution gives better performance.

Also, the table T1 is not indexed and the table can have 100,000 records.

Case 1:
DELETE FROM T1 where NAME = 'Ayyappan'
DELETE FROM T1 where NAME = 'Manivel'

Case2:
DELETE FROM T1 where NAME in ('Ayyappan', 'Manivel')

Sam Hobbs · Answer

You could use a higher-level interface and let it take care of the details. Such as TableAdapters and the Entity Framework.

Vulpes · Answer

The best way to answer questions like this is to try it on a test database and see :) On the face of it, you might expect that 'case 2' would be faster as it should only be necessary to traverse the records in the table once, looking for those to delete. However, I suspect that there will actually be little or no difference because of the way the query optimizer treats the 'IN' clause. In addition to the links which Anuja posted, I'd check out this one: http://stackoverflow.com/questions/714590/most-efficient-fast-t-sql-delete-for-many-rows

Anuja Pawar · Answer

Just refer this too http://www.bennadel.com/blog/940-SQL-Optimization-Case-Study-JOIN-Clause-vs-IN-Clause.htm H ope it makes clear to you :)

Anuja Pawar · Answer

In my opinion both are same. You can either apply a join or use IN statement. Refer this thread to clear your doubts http://stackoverflow.com/questions/7486856/disadvantages-of-in-operator-in-sql-server-and-alternate-way-not-to-use-in-opera

Help on SQL query

Answers (4)