[AccessD] SQL Server back end conversion

[Fred Hooper]

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