Re: Using EXISTS instead of IN for subqueries

How does this query look like? Not sure if this is gonna help, but you can take a look at Exists() subqueries On Wednesday, September 6, 2017 at 4:29:21 PM UTC+3, Stephan Seyboth wrote: > > Sorry for resurrecting

Re: Using EXISTS instead of IN for subqueries

Sorry for resurrecting this ancient thread, but it was the last direct mention I could find about this topic. Curious to hear what happened to Anssi's proposal to change the ORM to use EXISTS subqueries instead of IN subqueries. Looks like the ORM still uses IN as of Django 1.11.4. One of our

Re: Using EXISTS instead of IN for subqueries

On Tue, Mar 26, 2013 at 5:47 PM, Petite Abeille wrote: > > On Mar 26, 2013, at 10:03 PM, Alex Gaynor wrote: > > > For what it's worth, SQL2011 does define OFFSET, finally. > Just checked and it appears this has been implemented with SQL Server 2012. A quick check of the other database with Djang

Re: Using EXISTS instead of IN for subqueries

On Mar 26, 2013, at 10:03 PM, Alex Gaynor wrote: > For what it's worth, SQL2011 does define OFFSET, finally. Perhaps worthwhile mentioning as well : "Do not try to implement a scrolling window using LIMIT and OFFSET. Doing so will become sluggish as the user scrolls down toward the bottom of

Re: Using EXISTS instead of IN for subqueries

For what it's worth, SQL2011 does define OFFSET, finally. Alex On Tue, Mar 26, 2013 at 5:00 PM, Petite Abeille wrote: > > On Mar 26, 2013, at 4:19 PM, Michael Manfre wrote: > > > Maybe someday the non-standard LIMIT/OFFSET keywords will get added to > the > > standard (I truly hope this happen

Re: Using EXISTS instead of IN for subqueries

On Mar 26, 2013, at 4:19 PM, Michael Manfre wrote: > 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. FWIW, Oracle 12c is

Re: Using EXISTS instead of IN for subqueries

On 2013-03-26 15:54, Michael Manfre wrote: > On Tue, Mar 26, 2013 at 12:40 PM, Anssi Kääriäinen > deal with limit/offset. A generic approach would be nice to have, > but I can't imagine a generic way that would let me generate the > "SELECT ... FROM (SELECT ROW_NUMBER() OVER (...)) WHERE ..." > mon

Re: Using EXISTS instead of IN for subqueries

On Tue, Mar 26, 2013 at 12:40 PM, Anssi Kääriäinen wrote: > Hmmh, this means Oracle, MSSQL, DB2 and Informix are doing more or less > the same thing for limit/offset support? If so, then having a more generic > approach to this problem than having a custom compiler per backend might be > worth it.

Re: Using EXISTS instead of IN for subqueries

On Tuesday, March 26, 2013 5:19:52 PM UTC+2, Michael Manfre wrote: > > > > On Tue, Mar 26, 2013 at 10:23 AM, Tim Chase > > > wrote: > >> 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

Re: Using EXISTS instead of IN for subqueries

On Tue, Mar 26, 2013 at 10:23 AM, Tim Chase 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 it

Re: Using EXISTS instead of IN for subqueries

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 optimi

Re: Using EXISTS instead of IN for subqueries

On 26 maalis, 07: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 optimi

Re: Using EXISTS instead of IN for subqueries

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 s

Re: Using EXISTS instead of IN for subqueries

On 25 March 2013 12:37, Anssi Kääriäinen wrote: > I feel pretty strongly that NOT EXISTS semantics are wanted. The NOT > IN semantics are likely there just because that is how the > implementation was originally done, not because there was any decision > to choose those semantics. Most likely, y

Re: Using EXISTS instead of IN for subqueries

I have no idea how EXISTS performs on MySQL, however I can say that IN + subqueries on MySQL are so atrocious that we outright banned that where I work, so I don't see how it could be worse :) Alex On Mon, Mar 25, 2013 at 8:37 AM, Anssi Kääriäinen wrote: > On 25 maalis, 13:23, Simon Riggs wrot

Re: Using EXISTS instead of IN for subqueries

On 25 maalis, 13:23, Simon Riggs wrote: > On 25 March 2013 10:58, Tim Chase wrote: > > > On 2013-03-25 03:40, Anssi Kääriäinen wrote: > >> I am very likely going to change the ORM to use EXISTS subqueries > >> instead of IN subqueries. I know this is a good idea on PostgreSQL > >> but I don't hav

Re: Using EXISTS instead of IN for subqueries

On 25 March 2013 10:58, Tim Chase wrote: > On 2013-03-25 03:40, Anssi Kääriäinen wrote: >> I am very likely going to change the ORM to use EXISTS subqueries >> instead of IN subqueries. I know this is a good idea on PostgreSQL >> but I don't have enough experience of other databases to know if >>

Re: Using EXISTS instead of IN for subqueries

On 2013-03-25 03:40, Anssi Kääriäinen wrote: > I am very likely going to change the ORM to use EXISTS subqueries > instead of IN subqueries. I know this is a good idea on PostgreSQL > but I don't have enough experience of other databases to know if > this is a good idea or not. I can only speak fo

Using EXISTS instead of IN for subqueries

I am very likely going to change the ORM to use EXISTS subqueries instead of IN subqueries. I know this is a good idea on PostgreSQL but I don't have enough experience of other databases to know if this is a good idea or not. There are two main reasons for doing this. First, exists should perform