SQL Back-end

[Learn2010]

I have approximately 20 users on a database with a FE and BE setup. The BE is on a large network that is shared (1000's). Two users run the FE program 24/7. Of the remaining 18 users there are probably two or three at the most that will use it at any one time.

The two users running constantly update records as the steps in the process are accomplished. They are linked to a table in the BE with 50 records at the most at any one time. When the process is completed for a record it is marked as complete, appended to another table on the BE and removed from their tables. The rest of the users pull data primarily from that one table in the backend and run reports on it. Some of these reports take 30 - 45 seconds to run.

My question is this. If I replace the BE with a SQL database, will I get a noticeable increase in speed?

Thank you

 

[Banana]

The answer is going to be possibly no, not at first.

See, so much depends on how well-designed your applications are. To give an example - if you have a form where you allow your users to filter using say, Access' built-in Filter-By-Form functionality and it performs decently with a Access backend. It may be unacceptable with a linked BE because of excessively roundtrips required to complete the request. You'd have to substitute this with a search form that generate a good query so the linked backend can then process it entirely in back.

Another common case is that we may have some queries where we use VBA functions. In Access, that's a great way to write powerful queries and it may work very well since it's local. But with a linked database server, having a VBA function in your WHERE clause effectively forces Access to download the whole table from linked database server and apply VBA function locally. To avoid this, you'd have to rewrite the query to either take advantage of the server's querying capabilities or stick to standard SQL so that your queries are sargable.

When you've addressed considerations like those, then yes, you can expect performance gains with a linked SQL Server. It's just that you can't just upsize an Access database and declare it "mission accomplished".

For more details & links...

Beginning SQL Server Development
Beginner's Guide to ODBC

 

[Learn2010]

Thanks for your quick reply. I need to think about that for a while.

 

[Learn2010]

I have been writing simple queries using SQL. I create a new query and choose the SQL view. That is where I put the SQL.

Let's suppose I use three queries. 1) I find records between two time parameters. 2) I calculate the amount of time. 3) I then count the number of records for each person, or group for these results.

Are you saying that I should write that as a procedure, using subqueries, instead of using VBA to tell it to run the three queries?

 

[Banana]

Afraid the answer is going to be "it depends." Don't you hate that?

But the relevant factor you need to think about is this:

1) Do you need it to be updatable? This is especially important for binding a form. Reports can be based on a non-updatable data source, and therefore you certainly can use pass-through query to guarantee that processing occurs only at the back-end. Mind, the Access engine is pretty smart and will try to push as much it can to back-end when you don't use passthrough query but it's up to you to not give it anything that it has no choice but process it locally.

2) Do you need Access-specific functionalities? A good example would be crosstab query which as far as I know, exists only in Access. SQL Server has PIVOT/UNPIVOT but it's not really that flexible as Access' crosstab.

3) If you don't require updatability (or at least don't mind the extra complexity with ADO), you would want to use stored procedure to encapsulate and perform steps of complex queries.

More importantly, you need to ensure your queries are sargable. That is true irrespective of whether your query is created & executed in VBA, a parameter query or something else. If it's not sargable, then your application will suffer for it.

 

[Learn2010]

Thanks again.