On Tue, Mar 26, 2013 at 10:23 AM, Tim Chase <django.us...@tim.thechases.com>wrote:
> > 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. My "Historically" comment is based upon memory of observed behaviors from many years ago and could even be an ingrained memory dating back to SQL Server 7. I'll concede that your testing is probably more accurate than my memory of observed SQL optimizer behaviors from upwards of a decade ago. The execution plan is a result of the specific query and schema, so it's possible that we're both correct and both wrong at the same time. > 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. > Agreed, EXISTS is more flexible and at least to me, often easier to read, format, and maintain. Assuming this is implemented with a database feature, I'll most likely enable the EXISTS change for django-mssql. My main objections to a blanket change is to not have the specific behaviors of one database dictate how every other database must behave. Some one needs to be the voice of 3rd party database backends and it appears for the time being, I'm wearing that hat. > > > 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. The non "baroque hack" way of doing limit offset is with nested selects. Maybe someday the non-standard LIMIT/OFFSET keywords will get added to the standard (I truly hope this happens) so Oracle, MSSQL, DB2, and Informix could share SQL with postgres and mysql without needing to mangle it. Regards, Michael Manfre -- 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.
