Menu
In T-SQL what's faster?
DELETE * FROM ... WHERE A IN (x,y,z)
Or
DELETE * FROM ... WHERE A = x OR A = y OR A = z
In my case x, y and z are input parameters for the stored procedure. And I'm trying to get the performance of my DELETE and INSERT statements to the best of my abilities.
842
The EXISTS clause is faster than IN when the subquery results are very large. The IN clause is faster than EXISTS when the subquery results are very small.
"Having" is slower if we compare with large amount of data because it works on group of records and "WHERE" works on number of rows.. Save this answer.
As MongoDB stores a large volume of unstructured data and follows a document-based storage approach, it's relatively faster than MySQL. It means MongoDB stores data in a single document for an entity and helps in faster data read or write. Features like replication can be a big reason for this.
NOT IN vs NOT EXISTS performance in SQL Server Regarding performance aspects, SQL NOT EXISTS would be a better choice over SQL NOT IN. NOT EXISTS is significantly faster than NOT IN especially when the subquery result is very large.
Don't think; profile.
I urge you not to rely on intuition, yours or anyone else's, when considering questions of speed. Instead, try both options, with some kind of profiling/run time measurement, and find out which is faster in your circumstances.
140
"IN" will be translated to a series of "OR"s...if you look at the execution plan for a query with "IN", you'll see it has expanded it out.
Much cleaner to use "IN" in my opinion, especially in larger queries it makes it much more readable.
12
Write two stored procedures, one using IN, the other using OR, on a test server. Run each procedure 10,000 (or 1,000,000, or whatever) times, and compare the timings.
In general, this is pretty much the "only" way to have a good answer to the question of which approach is faster: write simple timing test cases, and run them many, many times.
4
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
