jwcolby
jwcolby at colbyconsulting.com
Mon Oct 29 11:21:13 CDT 2007
Sorry, this is not using an IN() clause in any manner. See my response to Arthur. 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 Drawbridge, Jack: SBMS Sent: Monday, October 29, 2007 11:21 AM To: Access Developers discussion and problem solving Subject: Re: [AccessD] "Not In" query speed John, If you are timing queries using IN and Not IN, you may wish to try EXISTS and NOT Exists. We have had many queries that were just "too slow" with IN operator that were speeded up by using Exists. eg SELECT claimId FROM tblClaim where NOT EXISTS (select "x" from tblClaimLogged WHERE tblClaim.claimId = tblClaimLogged.claimId) Jack -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of jwcolby Sent: Monday, October 29, 2007 10:16 AM To: 'Access Developers discussion and problem solving' Subject: [AccessD] "Not In" query speed Does anyone have any information on the speed of a "not in" query? That is where an outer join to a table is used and then a filter set on the PK (or any field really) of the outer joined table to discover all records "not in" the outer joined table? Is the speed the same as an inner join would have been? Is there more overhead because of the where clause? In other words, assume a table where there are 50K claim records in tblClaim. Assume that there are 25K records in TblClaimLogged (exactly 1/2 the number of records in tblClaim) and that each ClaimID is only in tblClaimLogged one time. Now do an inner join to discover which records are IN tblClaimLogged. Now do an outer join to discover which records are NOT IN tblClaimLogged. Both queries should return exactly 25K records since exactly 1/2 of the records in tblClaim are in tblClaimLogged, and each record can only be in there once. Do the two queries return the result sets in the same amount of time? John W. Colby Colby Consulting www.ColbyConsulting.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