On 2013-03-25 22:16, Michael Manfre wrote: > On Monday, March 25, 2013 6:58:12 AM UTC-4, Tim Chase wrote: > > I can only speak for testing IN-vs-EXISTS speed on MSSQLServer at > > $OLD_JOB, but there it's usually about the same, occasionally > > with IN winning out. > > In SQL 2008r2, the optimizer is usually smart enough to end up with > the same execution plan for IN and EXISTS queries. Historically, > EXISTS was usually the faster operation for SQL Server and if > memory serves it had to deal with its ability to bail out of the > EXISTS query sooner compared to the IN query.
I'd have to go back and re-test 2008r2, as my testing was on 2005 (and earlier). But my testing directly contradicts your "Historically..." bit, as I DISTINCTly (bad SQL pun intended) remember being surprised precisely because of what you say: EXISTS should be able to optimize and bail early. EXISTS also has some nice features like the ability to do testing against multiple columns, i.e., you can't do something like select * from tbl1 where (col1, col2) in ( select a, b from tbl2 ) but that's a simple EXISTS query. > > MSSQL is a 2nd-class citizen in the Django world, so I'm +1 > > Reasoning like that helps to keep it in its place. MSSQL's lack of certain core features is what does that. OFFSET/LIMIT provided by pretty much every other DB vendor? Use baroque hacks to get the same functionality. I seem to recall other issues, though v2008 seems to have addressed many of them (min date = 1900-01-01 which was an issue when dealing with historical/genealogical data; seems to be better in 2008) Either way, if EXISTS in MSSQL is now faster than IN, it just is one more tally in the "plus" column for why this might be a good idea (modulo implementation complexities). -tkc -- You received this message because you are subscribed to the Google Groups "Django developers" group. To unsubscribe from this group and stop receiving emails from it, send an email to django-developers+unsubscr...@googlegroups.com. To post to this group, send email to django-developers@googlegroups.com. Visit this group at http://groups.google.com/group/django-developers?hl=en. For more options, visit https://groups.google.com/groups/opt_out.
