[AccessD] Curiosity Question - Function in Query versus SQL "Where"Statement in Record Set

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.




More information about the AccessD mailing list