Access Queries vs Stored Procedures vs Pass Through Queries (1 Viewer)

Dear MS Access Expert,

Suppose I have a MDB that has ODBC links to SQL Server. All major tables are on SQL Server.

Suppose my backend is between 0.5 - 1 GB and the number of concurrent users is 50. Can I get good performance (assume good client / server design) if all queries are built using the Access query grid and stored in the MDB?

How good is Access at translating my Access Queries for SQL Server? Can I make all types of queries in Access (Select, Update, Totals, Union) for SQL server backend? (I know I can't use TRANSFORM.)

How much more effecient is executing Stored Procedures using ADO and bounding the resulting recordset to a form vs. bounding my forms to Access Queries.

What about bounding reports to pass-through queries? Is there a difference in performance between a pass-through query vs. a stored procedure in a read - only situation?


There seem to be alot of options when building Client / Server Access FE applications and I am trying to figure things out. Note: I have also looked at ADP and many people are suggesting to opt for MDB (ACCDB) using ODBC links to SQL Server.

Thanks

Peter.

Thank you John.

John in your experience what is the upper limit of MDB FE with ODBC links to SQL Server? (Is it ... 2 GB / 50 concurent users?)

Assume:

1) I use the client/ server techniques outlined in your "Building MS Access Applications" book.

2) Store all Select and Action queries in the MDB. (Call stored procedures only on rare occasions)

3) Use only DAO.

Thank you for your time John.

Peter.

The trouble is that even with your assumptions, it still depends on several factors such as:

1) What is the business model being modeled?
2) What are the querying requirement?
3) What kind of network/environment is this going to run in?
4) What is the hardware for the server?

But one thing for sure: The top limit is governed by the backend of your choice. If you used Access, then it's 2 GB and 255 concurrent users (though we know that practical limit are lower than that). Use SQL Server, then it's whenever they have put down as the limit. Ditto for MySQL or any other engines.



However, I would argue that all other things being equal, Access (as a front-end client) has the potential to be far more scalable than other choices such as web client where server simply must do all work or rich client created in Visual Studio where programmers must write the code to improve concurrency. With Access, it's fairly free because it always has a local database engine and thus can be utilized to perform local calculations, querying, reporting where appropriate and thus offload the burden from server to client.

However, Access is double-edge sword because a poorly written query would force unnecessary client-side evaluation and thus hurt the concurrency even more. But that's not really Access's shortcoming; the blame lies squarely at the feet of the developer.

HTH.

<< John in your experience what is the upper limit of MDB FE with ODBC links to SQL Server? (Is it ... 2 GB / 50 concurent users?) >>

I can show you how to do 2GB and 50 concurrent users with an mdb back end! The upper limit is much higher with SQL Server, unless you're talking about the "Express" edition. If you're talking about a transaction processing system, you can do several hundred concurrent users against many gigs of data. The trick is each user is grabbing / updating / inserting only one or a few rows at a time. If you're talking about a Data Warehouse, you can do terabytes on the back end, but the number of concurrent users drops as the system needs to spend type solving complex drill-down queries. Of course, it also depends on the hardware and network available. You'll be lucky to do 10 concurrent users if you have an old Pentium II server over at 10meg network. <s>

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

Banana-

Excellent reply. You laid out the limits more succinctly than I.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

John,

Thank you very much for the kind words. It's always great to have more MVP on the board and hear their perspectives so thank you also for pitching in.

Thanks John and Banana.

I am making this final concluding statement. Please correct me if I am wrong.

Using …..

0) Proper client server techniques (fetching and passing small recordsets and using indexes on joins, where, and sort fields)

1) MS Access as a Front End using ODBC links to SQL Server 2008 with the latest hardware architecture for clients, server, and network.

2) Building all Query Definitions using the MS Access Query Grid, storing them on the MDB Front end, and making sure they are compatible with SQL Server 2008 (no built in VBA functions such as IIF or user defined functions allowed)

3) Using bound form design.

4) Using DAO

5) Understanding that the key to success lies in a normalized data schema and keeping the “wire cool.”



.... will efficiently support a medium size business ERP system with 50 concurrent user and with 1 GB of data on SQL Server 2008 and allow for scalability to 100 users and 2 GB of data.

Thank you

John & Banana,

John, I read about your 50 concurrent users system, using unbound forms, in the “Pushing the Envelope” section (chapter 3) of your book. It appears quite code intensive but it’s good to know that the option of taking MS Access to its limits does exist.

John and Banana would you recommend developing the system using a MDB front end and backend and then upsizing to SQL server links near the completion of the project?

I would find this the most attractive option but perhaps it’s not the most efficient.

As you can tell this is my first exposure to MS Access client / server technology but I feel very comfortable making MDB file server applications so I would like to use as much of my current knowledge as possible for the transition.

Thank you.

Peter.

John and Banana may have other thoughts but I would say if you are going to be using SQL Server - then develop with SQL Server from the start. It doesn't make sense to develop using an Access backend if you are going to be in SQL Server as you can start optimizing for SQL Server from the start instead of having to do a bunch of changes later.

I have to agree with Bob. It's best to design for SQL Server. Though there are many similar optimizations, almost always the devil is in the details and you don't want that to trip you over and bringing on unnecessary work in transitioning when you can just start out right there.

With regards to your concluding statements:

#2) Keep in mind there is nothing wrong with doing a query like this:


You don't want any VBA functions in the ON, WHERE, HAVING, GROUP BY, ORDER BY clauses, definitively but there's no performance penalty in doing VBA evaluation upon SELECT'd columns.

#3) It's important to understand that bound form per se doesn't mean effective interaction with server. The advantage of bound form lies in its short and rapid development cycle as opposed to efficiency. It's possible that you can write unbound forms that's more efficient than a equivalent bound form, but the case where this actually happens is rare, so you're just needlessly wasting developmental time for little/no gain. I do use bound forms for well over 95% of time, but there are times where unbound form may make more sense and is worth the extra investment in development time for extra efficiency. But in that 95% of the cases, the efficiency difference between a bound form and unbound form would be roughly equivalent, assuming optimized queries is driving the forms.

#4) Recall that you can use ADO to bind recordset to forms. This is useful in cases where you may want to use a result of stored procedure or table-valued function for SQL Server and still have full updatability upon the form. I'd even go and argue this may be still cheaper than doing it unbound, though ADO by necessity requires some kind of VBA coding to set up the bindings. As for efficiency, I suspect there is little difference in efficiency. In case of SQL Server, ODBC and OLEDB are implemented natively so we're already dealing with the Server closely as possible without any artificial wrapper between us and the Server.

HTH.

Thanks Banana and Bob.

In regards to number 4) I plan to read more about ADO and will most likely make the switch to ADO for the client server application as it seems SQL Server and ADO interact better? Is this a true statement?

Does anyone want to put a stamp of approval on the concluding statement above?

Peter

I would not say that. I'd probably go with this: "ADO is more flexible than DAO in terms of connecting to, manipulating and managing data. Use DAO for native interface."

However, the cases where you actually need ADO because DAO can't do it is fairly few and far in between, so you can still develop a great application using DAO only and use ADO for a corner case (e.g. when we need a unique interface not easily expressed in DAO for just one form out of the whole project).

Thanks for your input Banana.

Just for the record, I agree with everything Banana has said in this thread.

John Viescas, author
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Paris, France)

Thanks for everyone's input in this Thread.

John what is the next book you are working on or are you enjoying Paris at the moment?