Speed Improvements With SQL Server?

[maxhugen]

I have an Access app (split into FE and BE) running for some years, that is now also being used in a second office, connected by a WAN. This office has network problems, as it's over-utilized (97% according to one IT guy!!).

I'm looking into converting the MDB app into an ADP with a SQL Server backend. To justify the costs involved, I'm trying to get a feel for what spped improvements might reasonably be expected.

After a lot of searching, I've found a great many sites that say it will (or should) be faster, but I have yet to find any mention of anyone doing it, and whether they were actually able to get speed improvements.

Has any had some actual personal experience in 'upsizing' from Access to ADP + SQL Server that they could share pls? Or point me to any URLs?

MTIA

Max Hugen
Australia

 

[jsteph]

Max,
It's easy to say flat out you'd get a huge increase, but a lot depends on what the app actually does as to how big the increase.

In general though, if you're updating a lot of data, entering data, with multiple users, you'll see a good increase in response.

When querying large tables, such as for a report or for a Search/Browse screen, you'll see a huge increase.

A low-cost alternative if the front-end is relatively simple, is to put the front end stuff on a Web system. This way, the backend sits on the same Web server, and the web pages themselves see the backend as 'local', so there's no network bandwidth hit--the web page just returns the data needed--just like sql-server would for an Access front-end.
--Jim

 

[jsteph]

Max,
I should clarify, in my point about the low-cost alternative I'm referring to keeping Access as the backend--when it's on the IIS Web server (or a machine on the same local LAN as the IIS Server), it is, for all practical purposes, the same as having the Access backend on the local machine.
--Jim

 

[maxhugen]

Thanks Jim

Have you upgraded any Access apps to SQL Server yourself?

Max Hugen
Australia

 

[JoeAtWork]

I've done several upgrades. Change in response time is not universal, some screens will seem faster, some won't. And complicated queries (such as deeply nested ones, i.e. a query based on a query based on a query, etc.) can actually become slower, sometimes significantly so.

I've found that to best take advantage of SQL Server with Access is to give up binding on your most query intesive forms (such as ones that do searching) and make direct stored procedure calls. As I mentioned, complicated nested queries can actually become very slow with linked tables, I usually replace these with stored procedure calls. For example, if a listbox loads much slower after an upgrade, I will unbind the listbox, open a recordset with a stored procedure, and then bind the recordset to the listbox. This approach is almost always much faster.

I think the real benefit with switching to an SQL Server backend is now you have a stable database - you do not have to worry so much any more about losing your data through random corruption.

Joe Schwarz
Custom Software Developer

http://www.plandev.net

 

[JBinQLD]

Dont forget that you wont need to pay for full blown SQL Server - We use the free SQL Server Express 5 edition to great effect. I has a few quirks but once you're aware of them its a good little package. We often have 50 users connecting access front end to the backend happily whereas the access backend was crippled with more than 10 or so users.

 

[maxhugen]

Thanks Joe!

It's been surprisingly difficult finding anyone who's actually done the 'upsizing' (or case examples), so your comments are most appreciated!

I had determined that if the client wanted to make the move, I would have to move the majority of queries to SQL Server as stored procedures, functions or view, as appropriate. I'd probably also have to look at taking out any data processing in vba code wherever possible and putting it into stored procedures.

It will be quite a big job to upsize the app, as it's relatively complex with lots of business rules (which frequently change). But with ~30 concurrent network users, for whom this app is crucial, I need 'facts' to convince the client that it's a worthwhile investment to upsize. Especially since it's been running very successfully as a FE/BE Access app!

The main catalyst for re-looking at the upsizing option is the second office that now uses the app as well, which has network issues that 'won't be addressed for probably a couple of years', according to their IT people.

Max Hugen
Australia

 

[JBinQLD]

OMG. Dont even consider Access over WAN connection! Go SQL now! One of our regional offices in NT use an SQL server here in Brissy with no drama where as with an Access app it would time out again and again making it unusable.

It was so bad we actually set up a couple of redundant PCs here and gave them VNC access to those while we were rapidly upsizing to the SQL Server.

To truly benefit converting to stored procedures will help but for now just converting the tables and then using the same front end will help. At least then there is minimal interuption and a rapid deployment.

The resultant happiness will make you look a hero and make the business case for further optimisation too easy

 

[maxhugen]

Hi JBinQLD

Thanks, I totally accept your comments re the WAN!

We've considered a VNC too... but the client is a big US healthcare company, with a big IT division - and IT seems to be off in la-la land! They won't even increase their LAN/WAN bandwidth capacity 'for another couple of years' despite being at 90%-plus capacity!!! We're unlikely to be able to organise even a VNC. The cost of the clogged lines to the organisation's productivity must be horrendous.

I know there's a very strong case to move to SQL Server. My major concern is that we do the upsize - but despite cutting our 'data traffic' down through good use of stored procs etc, the network is so bad that there seems little if any speed improvement to the users. That would severely damage our credibility.

I feel like I'm between a rock and a hard place...

Max Hugen
Australia

 

[JBinQLD]

max, I know only too well how hard it is to get large IT departments to work. In fact, even within our own company we are fighting to get one of our SQL databases moved from one department server to another

Anyhoo.

Try this - Demonstrate a test case.

Install SQL Server Express on one of your PCs and create table with a few thousand records and a million or so related records. (Just rip up a one off routine to make these for you)

Create a simple Access front end that allows you to browse the records or find a particular one.

Connect to an access backed and an SQL server backend.

SQL Server does some funky stuff when it comes to indexeing so first query may not be too much quicker. Wait until you've done a couple. Woot! Your case will be proven, trust me

 

[jrbarnett]

Max - see thread1121-1469016 for my experiences on this.

One more thing I would say - this can't be a partial upgrade. One of the systems I worked on had some tables locally and others remote, and queries doing joins between the two tables were painful. Moving this local table to SQL server then migrating the manipulation to write to this remote table via a stored proc took query execution time from 3 mins approx down to under 5 seconds.

John

 

[JBinQLD]

I'd def say you've got your answer fella!

To convert it to manager speak take average manager hourly wage and multiply it by users. tell them that the daily saving.

Yeah, i know it dont make much sense, but they need a number before they react. That number is as good as any other

 

[jsteph]

Max,
Yes, I've upgraded many. Some that I've done a were as simple as importing the table from Access to sql-server, and re-linking and basically walking away. The ODBC drivers will generally take Access/Jet queries and perform joins and Where clauses on the server, as if it were a pass-thru query. So in some cases you can get away with something that simple.

However, most apps you want to use either pass-thru queries or stored procedures for reports and browse-forms, and use a query keyed on a single record for your update forms.
--Jim

 

[maxhugen]

Many thanks guys!

@JBinQLD, yep, as part of the proposal, I'll try some simple benchmarking as you suggested! I'll also add one of the more complex queries, and try it against both, ie mdb->mdb vs adp->sql server. I think I should make the FE that connects to SQL Server an ADP, to ensure I'm in true 'client/server' mode, and don't have JET (in an mdb) doing something locally without me actually being aware of it.

@jrbarnett, thanks for the link to your post, it was very informative... and I spotted another couple of 'gotchas' that I hadn't yet thought of. My 'Upsizing - Watch Out For' list is getting longer by the day! Gonna have to rethink any quote for this very seriously...

@jsteph, I can already see that I will need to change most of my existing queries to procs etc, especially since I need to squeeze every bit of speed from such a conversion as I can.

Max Hugen
Australia