[AccessD] Number of 'OR's in queries - what's the limit?

IN() forces a table scan. There's a much hipper way to do this. Given some
arbitrary front end that asks you for values and you supply 1, 307, 590, and
12 (which correspond to PKs or FKs of interest), then write these values to
a temp table and then do a join to the real table of interest. Even assuming
100 user-supplied values, resulting in 100 rows in the temp table, this is
WAY faster than any other known method. Compared to IN(), against a large
number of rows, there  is no comparison. IN() forces a table scan. The
parser is not smart enough to create a temp table of the values supplied,
and therefore cannot use the index to find the rows. Instead it walks the
entire table. Create the temp table as described above, then do a join to
the real table of interest and boink! You've got your rows WAY more quickly
than in the other methods.

hth,
Arthur

On 7/24/07, Dan Waters <dwaters at usinternet.com> wrote:
>
> Perhaps this will bypass the limit altogether!
>
> Thanks Eric!
> Dan
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Eric Barro
> Sent: Tuesday, July 24, 2007 1:45 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Number of 'OR's in queries - what's the limit?
>
> Why not do a WHERE PKID IN (1,2,3,5,15) query instead? Queries with Ors
> tend
> to take longer to execute.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
> Sent: Tuesday, July 24, 2007 10:32 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Number of 'OR's in queries - what's the limit?
>
> That's a good plan!  I was hoping to do a customer update within the next
> half hour though.
>
> I was hoping that someone had seen some documentation on this.  The number
> 40 was documented 'somewhere' in Access help.
>
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby
> Sent: Tuesday, July 24, 2007 12:13 PM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Number of 'OR's in queries - what's the limit?
>
> You could reasonably quickly set up a test to find out by building a
> dynamic
> query, a string such as select PKID from tblX where (PKID=1 or PKID=2
> or...).  Add 10 (or 100) at a time and see how far you get.
>
>
> John W. Colby
> Colby Consulting
> www.ColbyConsulting.com
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Dan Waters
> Sent: Tuesday, July 24, 2007 1:02 PM
> To: 'Access Developers discussion and problem solving'
> Subject: [AccessD] Number of 'OR's in queries - what's the limit?
>
> In Access 97, there was a limit of 40 OR words in a query.  There is a
> limit
> in Access 2003, it's >250, but I don't know what it actually is.
>
> Does anyone know?
>
> Thanks!
> Dan
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.476 / Virus Database: 269.10.16/914 - Release Date: 7/23/2007
> 7:45 PM
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>