[AccessD] SQL Server back end conversion

[John Colby]

Fred,

I did a year contract for IBM in research triangle park.  During that 
time I was tasked with completely rewriting an access database that no 
one understood and which only kinda sorta worked.  I had to analyze a 
ton of queries which were more complex than most anything I had ever 
encountered.  Statistical stuff. The queries were based on a dozen 
tables.  The queries were layered, i.e. a set of data pulled into 
Access, manipulated, stored in temp tables, then those results used to 
pull more data, manipulated, stored, used for more data etc.

It was a mess but it was based on a process by a skilled person who 
understood the data but not databases.

I ended up pulling all of that crap out of access.  I built stored 
procedures which did pretty much the exact same thing but back in the 
SQL Server.  The result was a LONG and COMPLEX stored procedure that 
returned a data set from that stored procedure. The times dropped from 
dozens of minutes to a dozen seconds.  The ENTIRE thing was SQL Server 
based TSQL sitting out in SQL Server and executed by a query in Access 
that just told SQL Server to return some results.

Users won't wait for dozens of minutes for results from Access, and 
worse, because Access is single threaded, the user interface freezes.  
They will wonder if the computer is hung.  They will reset their 
computer.  Things get ugly.

On 4/9/2018 5:10 PM, Fred Hooper wrote:
> 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

-- 
John W. Colby