[AccessD] SQL Server back end conversion

[Arthur Fuller]

I second Stuart's advice. The thing about complex queries is that, with
linked tables, all the data is sent to the Access client, which then
applies filters and sorts to deliver the desired data. With Views, on the
other hand, the database itself does all the heavy lifting, then sends the
results to the client.

On Mon, Apr 9, 2018 at 8:59 PM, Stuart McLachlan <stuart at lexacorp.com.pg>
wrote:

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



-- 
Arthur