[AccessD] Fastest Way

Using the queries is probably the quickest.  Creating a five field index requires a lot of work to maintain.  Joining all five fields to an intermediate query of the master table, perhaps matching the date range only, and using the result as the basis for a bulk delete against the import data and then appending the balance would be very fast.  An 'unmatched records' query would definitely be slower.

Ciao Jürgen Welz Edmonton, Alberta jwelz at hotmail.com


 
> From: Lambert.Heenan at chartisinsurance.com
> To: accessd at databaseadvisors.com
> Date: Thu, 14 Jan 2010 12:05:11 -0500
> Subject: Re: [AccessD] Fastest Way
> 
> 
> As they say, suck it and see. :-)
> 
> I suspect that Query #3 will return the complete result set a lot faster than FindNext will take to locate them one at a time. So just throw the query together and see how long it take to run.
> 
> Lambert
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Rocky Smolin
> Sent: Thursday, January 14, 2010 11:28 AM
> To: 'Access Developers discussion and problem solving'
> Subject: Re: [AccessD] Fastest Way
> 
> Lambert:
> 
> That looks like a good approach. Query on 2) would be interesting but I'm not sure I would need that information if I use the query in 3) to process just the incoming records that are not already present. I'm a little concerned about the time the Unmatched Query will take when there are 3-4,000,000 records in the table especially since the table in the back end is on a server. But it wouldn't take long to implement.
> 
> Thanks
> 
> Rocky

 		 	   		  
_________________________________________________________________