[AccessD] ACCESS Left Behind

Jim Dettman jimdettman at verizon.net
Sat Jan 24 08:22:14 CST 2015


 There's a lot of wiggle room in that question<g>

 When using linked tables, JET will always try to push a query server side
if it can even though it's not a pass-through specifically.  It can't do
that when:

1. You join to tables from different data sources.
2. You use Access or VBA Expressions in the SQL.
3. You use JET Specific SQL.
4. You use some SQL expressions such as DISTINCT or GROUP BY

  All those will force JET into processing the query locally.  So depending
on the app (how it was written), number of concurrent users, and the size of
the record sets involved, you may or may not notice a tremendous difference
between the two.

  I can say that in general when moving to SQL it is usually faster, but
there have been cases where you actually end up slower.  That's why when you
consider a move to SQL Server, pushing the data should be considered just
the start of the start of the process and not the whole process.

Jim. 

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of John R Bartow
Sent: Friday, January 23, 2015 06:49 PM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] ACCESS Left Behind
Importance: High

Hi Jim,
On the issue of performance would the stated example, when compared to using
an a native access db,  make a noticeable difference in a small office with
very few users?

John B

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Jim Dettman
Sent: Friday, January 23, 2015 8:14 AM
To: 'Access Developers discussion and problem solving'
Subject: Re: [AccessD] ACCESS Left Behind


 While that's true to an extent, you just need to understand what's going on
under the hood.

 You can get very acceptable performance just with linked tables, but some
of the features that make Access easy to use will cause you a problem.

 Something as innocent as an IIF() in a query, which many use, and boom,
lousy performance (any Access, VBA, or JET specific expression in a query
forces JET to process the query rather than passing it off to SQL).

Jim. 

-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list