Brad Marks
BradM at blackforestltd.com
Fri Aug 19 17:57:18 CDT 2011
Jim, Thanks for the info, I appreciate it. Brad -----Original Message----- From: accessd-bounces at databaseadvisors.com on behalf of Jim Dettman Sent: Fri 8/19/2011 11:37 AM To: 'Access Developers discussion and problem solving' Subject: Re: [AccessD] Curiosity Question - Function in Query versus SQL "Where"Statement in Record Set Brad, Any JET specific expression (like a VBA function call) or linking to a local table, forces Jet to handle the query execution on the client side. This often ends up with repeated calls to the back end rather then a single SQL statement being sent to the BE. For example, if your deleting 15 records in a BE based on a link to a local table, JET will send 15 separate SQL statements to the backend, one at a time. However if you were to code that all into a WHERE clause and skip the local table link, you'd execute one statement and it'd be over in an instant. You can see this at work by turning on ODBC tracing. Jim. -----Original Message----- From: accessd-bounces at databaseadvisors.com [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Brad Marks Sent: Friday, August 19, 2011 11:54 AM To: Access Developers discussion and problem solving Subject: [AccessD] Curiosity Question - Function in Query versus SQL "Where"Statement in Record Set We have an Access 2007 application that pulls invoice data (for 1 invoice at a time) from a Pervasive database with ODBC. Originally the key (Invoice Number) to obtain the data for a single invoice was contained in a Function which was used in the Query's Criteria. This worked Okay, but it took about 15 seconds for the query to run. Recently, in order to speed things up, I experimented with removing the Function from the Query and setting up a Record Set with a Where statement. This runs in less than 1 second, and the people in our Accounting Department are very happy with the reduced runtime. I am curious as to why there is such a large difference in speed. Thanks, Brad -- 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 -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean.