[AccessD] SQL Server back end conversion

Jim Dettman jimdettman at verizon.net
Mon Apr 9 16:30:53 CDT 2018


<< It's the 5-20X time multiplier that's the problem; which probably results
from the fact that all heavily-used forms are fed by complex queries, having
many tables with outer joins>>

One thing to be aware of, if the query has:

1. VBA expressions.
2. JET specific SQL.
3. Joins to local tables.

 Then JET will do the processing rather than handing it off to SQL.  That
means a lot of data over the wire and depending on the query, JET may
actually issue one SQL statement per row in your result set.   I forget what
the rules are for that off-hand, but it typically happens on deletes.

 In general though, most apps do OK with a conversion to SQL with just
linked tables, but there are a few that end up slower.  

 In order to harness the power of SQL, you need to send processing server
side.  That's done by using:

1. Views
2. Pass-through queries
3. Stored Procedures

The other thing is that if you have not already done so, you should add a
Time Stamp field to every table that JET will work with.  It's really not a
time stamp field as one would think.  What it does is turn on row versioning
for SQL, and JET will latch onto that field for building recordsets.  

 Add the time stamp fields if you don't have them, relink, and see if you
get a performance boost on your tests.

Jim.



-----Original Message-----
From: AccessD [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of
Fred Hooper
Sent: Monday, April 9, 2018 5:10 PM
To: Access Developers discussion and problem solving
Subject: [AccessD] SQL Server back end conversion

Hi All,

I've written a couple of times in the past about converting my
brother-in-law's software program from an Access back end to a SQL Server
back end. I'd like to summarize the situation and my immediate plans in hope
that someone will see something I've missed.

The speed issues are apparent from two test cases (in seconds):
Time to open two forms:                     #1                #2
Access back end:                                 0.55             2.13
SQL Server table links only:               0.96           13.88
SQL Server views linked too:             9.50             0.54
SQL Server w/ADO recordset:           0.55             0.19

These results are on a fast computer with everything on that computer. 
The times are 5-20 times longer on their network -- which has about 20
people using the program. We've updated and replaced network components and
the server, but found only small improvements. The back end mdb is just
140Mb.

It's the 5-20X time multiplier that's the problem; which probably results
from the fact that all heavily-used forms are fed by complex queries, having
many tables with outer joins. I've tried a SQL Server table-links-only
version on their network and it was pathetic, far slower than Access. The
recordset-fed-forms version is my only real hope, absent a "Why didn't you
try X?" response to this email. My theory is that the reduction of network
load by server query execution and results-only recordsets will make their
performance almost as fast as on my PC.

Here's what I've done so far:
(1) Improved the network
(2) JIT subforms, particularly on the large, heavily-used forms
(3) Replaced complex embedded SQL with queries
(4) Tweaked queries so they're not grouping entire tables
(5) Tried to find a commercial substitute for their program; there isn't one
even close

The program was written by someone as their first big project, but was
generally well done. My only two real complaints are zero defaults for
foreign keys and no referential integrity (RI). I can't move forward with
recordsets without RI, as they won't be editable. So that's where I'm going
next.

TIA, Fred
--
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