[AccessD] SQL Server back end conversion

[Stuart McLachlan]

I second Jim's comments.

First thing I'd do is convert those form record source "complex queries" to views.

On 9 Apr 2018 at 17:30, Jim Dettman wrote:

> << 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
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>