SQL Server - Is it faster? Speeding ACCESS Up! (1 Viewer)

[Oreynolds]

Hi,

I have a split DB with the BE (0.5GB) residing on a network drive. All users have an FE file on their local machine. All machines are at least i5's, mostly i7's with plenty of RAM running on a gigabit network. There are usually around 12 users using the DB simultaneously.

I have been doing a lot of research about how to speed the DB up and have done most of the tricks and tips I have found with only very marginal gains. I have read a number of SQL threads on here but the advice as to whether it gives significant performance benefit seems inconclusive.

Has anyone any experience of a similar setup to mine and whether they have migrated to SQL or other and achieved a significant speed increase?]

Thanks

 

[June7]

SQL is not a database, it is a language used by many database platforms - SQLServer, MySQL, Oracle, Access, etc.

I managed Access system with about a dozen users and never had speed issues.

I have read threads describing slower performance after migrating to SQLServer.

 

[The_Doc_Man]

I had a 40-50 user DB on a 1 Gbit LAN where each user system was a low-to-mid laptop and never had that much of a problem. I was careful to assure that I had indexes where they were needed and that my queries were designed to maximize use of JOINs and other tricks of the query optimization trade.

 

[Isaac]

Here are a few excellent pieces of reading from people I've come to trust on the subject of Speed, moving a back-end to SQL Server (etc), Expectations, and Design:

Move split db

Greetings, We are using office 365/ ms access db split into BE/FE. The BE is located on the shared folder on the server and each user has a copy of the FE on his/her desktop. Currently, everyone is working from home using their laptops and accessing the database via remote desktop. Which seems...

www.access-programmers.co.uk

Move Access backend database tables to SQL

I have an Access application that is linked to some tables in an existing SQL database plus another 148 local tables in a separate backend Access databasae. I would like to move the backend Access tables to the SQL database and then link them back to my application in the hope that the SQL...

www.access-programmers.co.uk

Access Querys to SQL

Could somebody please point me in the right direction? I am converting my Access database to SQL and trying to replicate my querys. In one instance I have eight querys that rely on one another. Query within a Query Two questions: 1. Am I to assume that I would have to create nested SQL to...

www.access-programmers.co.uk

Out of all the platitudes or hyperbole (which isn't true), that over-generalize and that people sometimes expect moving to SQL Server will auto-magically achieve, there are only a couple things I've found to be true, in my experience:

1. It does seem like SQL Server power can often result in a bit more speed, as in forgiving poorly-written queries, if the size of the table is not too large to begin with, compared to Access. Of course, your goal shouldn't be to solve bad designed queries with different hardware or systems, though!
2. I do think SQL Server is "just better" at handling larger amounts of data, but always remember, your goal should be to write efficient queries and stage data in an efficient way for querying - not just move to something more forgiving and keep doing things wrong.

To me, the reasons for moving a back end to SQL Server aren't even mostly related to speed.

There are like ... a hundred things ... or more ... that are nicer and better about coding in sql server than Access.

Just today I was enjoying the fact that a large query was running in the same window that I was still writing code.

Imagine developing in an IDE where you can keep writing new code, save the file, open a new window and start running a new query, save/create/close/test a new query file, ALL DURING the time a query is running! Just that fact alone is life-changing, and that's just one of what feels like a million.

Still, strive to use each tool appropriately rather than either become so passionate about one you incorrectly miss the value/niche of the other, Or, becoming so frustrated with bad results of poor design in one, that you blindly switch to another for the wrong reasons.

The right mindset needs to come first. Then (maybe) changes to systems

 

[Pat Hartman]

Simply upsizing a Jet/ACE BE to SQL Server will almost certainly result in SLOWER performance rather than faster! HUH?

Typically, Access apps developed by someone who has no experience with database applications will use "Access" techniques such as binding a form to a table and using form filters to find the data a user wants to work with. You need to understand how a relational database works and why it needs different techniques.

When you open a form bound to a table, Access retrieves enough records to show the form, then behind the scenes, sucks down the rest of the rows in the table until they are all in memory. But, a user doesn't actually work with more than one main form record at a time. Therefore, you really don't want to bring thousands of records across the LAN when all you actually need is one at a time. So, for starters, bind your forms/reports to queries with selection criteria that severely limits the number of rows and columns retrieved. It is far more efficient to retrieve ONE row, work on it, then go back for another row, work on it, then go back for another row, etc rather than bringing all data local at once.

When I build an Access app, I don't always know up front whether the BE will ever be upsized or not. Some always start out with the intention of being SQL Server. Others grow into it. But, using client server techniques works well regardless of whether the BE is ACE or SQL Server but not vice versa.

Then you need to understand that the server has NO knowledge of VBA so if you use internal VBA functions or UDF's, Access cannot pass these to the server. Only functions with SQL equivalents can be passed to the server. Access has to work with all others locally. So, depending on where they are in the query, Access might have to bring down all rows and process them locally. You want to avoid at all costs Access requesting entire tables from the server.

Access makes every attempt to "pass through" all queries but you can defeat that if you don't know what you are doing.

Start by identifying what parts of the app are slow and we can work out from there.

I have several applications that are sold to the public. They're not shrink wrapped so you won't see them on Amazon. Because they are semi-custom and come with training and support, they can be installed with ACE BE's or SQL Server. You can even change your mind after the install. So, you might start with an ACE BE but later decide, your data is growing or your concurrent user count is growing and so it would be better to have SQL Server as the BE. Someone (possibly me, possibly in house IT) has to upsize the BE. But no changes need to be made to the FE. You just go to the form that relinks the BE and switch from ACE to SQL Server. The code deletes all the ACE links and then links to SQL Server tables. NOTHING Else. The app just works

 

[Oreynolds]

Simply upsizing a Jet/ACE BE to SQL Server will almost certainly result in SLOWER performance rather than faster! HUH?

Typically, Access apps developed by someone who has no experience with database applications will use "Access" techniques such as binding a form to a table and using form filters to find the data a user wants to work with. You need to understand how a relational database works and why it needs different techniques.

When you open a form bound to a table, Access retrieves enough records to show the form, then behind the scenes, sucks down the rest of the rows in the table until they are all in memory. But, a user doesn't actually work with more than one main form record at a time. Therefore, you really don't want to bring thousands of records across the LAN when all you actually need is one at a time. So, for starters, bind your forms/reports to queries with selection criteria that severely limits the number of rows and columns retrieved. It is far more efficient to retrieve ONE row, work on it, then go back for another row, work on it, then go back for another row, etc rather than bringing all data local at once.

When I build an Access app, I don't always know up front whether the BE will ever be upsized or not. Some always start out with the intention of being SQL Server. Others grow into it. But, using client server techniques works well regardless of whether the BE is ACE or SQL Server but not vice versa.

Then you need to understand that the server has NO knowledge of VBA so if you use internal VBA functions or UDF's, Access cannot pass these to the server. Only functions with SQL equivalents can be passed to the server. Access has to work with all others locally. So, depending on where they are in the query, Access might have to bring down all rows and process them locally. You want to avoid at all costs Access requesting entire tables from the server.

Access makes every attempt to "pass through" all queries but you can defeat that if you don't know what you are doing.

Start by identifying what parts of the app are slow and we can work out from there.

I have several applications that are sold to the public. They're not shrink wrapped so you won't see them on Amazon. Because they are semi-custom and come with training and support, they can be installed with ACE BE's or SQL Server. You can even change your mind after the install. So, you might start with an ACE BE but later decide, your data is growing or your concurrent user count is growing and so it would be better to have SQL Server as the BE. Someone (possibly me, possibly in house IT) has to upsize the BE. But no changes need to be made to the FE. You just go to the form that relinks the BE and switch from ACE to SQL Server. The code deletes all the ACE links and then links to SQL Server tables. NOTHING Else. The app just works

Thanks for this Pat, I have direct messaged you

 

[Pat Hartman]

Hi Ollie,
As I suggested in your PM, please post your additional questions in this thread where everyone can follow along

 

[Oreynolds]

Hi Pat,

Thanks very much for your comprehensive reply to my post. So to give you a little more background whilst I am far from an expert I've been building by DB within our business now for 20 years + and obviously learnt an enormous amount in the time. Historically I had forms that opened with thousands of records behind them etc etc so totally understand your key pointers in your reply. As it stands with all I've learnt users are now taken initially to very simple list box forms that are super quick. Here they filter down and select the actual record they want to work on after-which they open the main form which then is filtered to only a single record. All data is retrieved and filtered using SQL queries so essentially it's all about as efficient I can get it data transfer wise.

I have recognised that a couple of my forms are over complicated and I am currently working on removing a couple of the subforms on them onto pop of forms to help speed the form up.

The above all said when we have all 12 users logged in simultaneously it does slow the response times of everything right down. The BE file exists on a pretty powerful server. I have experimented with having the users individual FE files on a shared server drive as well as on their local machines with little benefit to either options as far as I can measure. When there is just a single user working in the DB the whole thing is considerably more responsive.

Recently we have installed an SSD drive into the server hoping improved disk access would help but it had no effect whatsoever! So I have been looking for other ways to improve things hence my post on this forum regarding SQL and any other ideas people have that may help.

I am more than happy to buy products/software and or pay for the services of someone if they believe they can make a significant improvement to our setup. So if you have any further feedback on anything you or others are able to offer then I would be extremely grateful.

Thanks

Ollie

 

[cheekybuddha]

Upsizing to SQLServer (or similar) only becomes a question once you have diagnosed what is causing the bottlenecks/speed issues in your current database as you have it.

There are plenty of things that can be done to an Access database to speed it up (eg judicious use of indexes, analysis of table structure, persistent connections to the back end, turning off NameAutoCorrupt, etc, etc ...) - we don't know whether you have implemented any of these.

You would be better served first explaining the specific situations where you database is slow, with detailed descriptions of the workflow, tables involved, amount of code that runs etc.

It may be that upsizing is the answer, but you should go through the preliminary steps here first.

 

[amorosik]

I had a 40-50 user DB on a 1 Gbit LAN where each user system was a low-to-mid laptop and never had that much of a problem. I was careful to assure that I had indexes where they were needed and that my queries were designed to maximize use of JOINs and other tricks of the query optimization trade.

50 users on Sql Server backend or on a mdb/accdb data file?

 

[Pat Hartman]

I've had as many as 100 users with an ACE BE. Eventually, the BE got large enough to justify upsizing to SQL Server. But the FE had been built using good client/server techniques to begin with so it was already as optimized as it could be. The upsizing took less than a week and that was only because of the extensive user testing we had to do. The conversion took a couple of tries because there was some bad date data. Access and SQL server support different date ranges. I ended up having to add additional date range checking to insure that the date controls that didn't have it already were rational.

 

[amorosik]

I've had as many as 100 users with an ACE BE. Eventually, the BE got large enough to justify upsizing to SQL Server. But the FE had been built using good client/server techniques to begin with so it was already as optimized as it could be. The upsizing took less than a week and that was only because of the extensive user testing we had to do. The conversion took a couple of tries because there was some bad date data. Access and SQL server support different date ranges. I ended up having to add additional date range checking to insure that the date controls that didn't have it already were rational.

When you write ACE BE you mean a single file .mdb o .accdb ?
What exactly do you mean by 'large enough'? 10 Megabyte? 100 Megabyte? 1000 Megabyte?
When you did this project, what operating system were used for the clients?
If the system was fine before, why did you switch to Sql Server?

 

[Pat Hartman]

When you write ACE BE you mean a single file .mdb o .accdb ?

ACE = .accdb; Jet = .mdb The primary data was in one .accdb where the largest table exceeded 500,000 rows and the file was over 1g. There was a secondary database that was used for batch processing. It created and processed EDI (Electronic Data Interchange - common method for companies to exchange data between themselves. We were using some of the medical billing transactions). I generated millions of row tables twice a week during our "exchange" with the state. EDI is very complex and transactions are always multi-row. Return files were smaller since they were mostly acknowledgements or error messages. But it was this BE that caused the concern. Which is why we upgraded.

We were all using Win 10 at the time.

 

[Isaac]

EDI is very complex and transactions are always multi-row. Return files were smaller since they were mostly acknowledgements or error messages.

Ain't that the truth. Used to work at a company they called some fancy sequence of terms like "healthcare revenue cycle management company"
SOOOOO many different "files" with soooo many different hair-brained formats.

I remember meeting with the PM's trying to write scripts, conversations like: "well if you see a dollar sign, that's the beginning of the secondary header's subpayor title suffix", and "if you see a smiley face that winks at you, that's the end of the first modifier for the primary diagnosis that wasn't covered due to one of the reasons in subsection 5"

Those files gave me a great many reasons to curse, that's for sure. Really puts a face-to-the-name of the garbage that government comes up with. Mostly we dealt with Medicare and Medicaid, but not always. Then each state implements their own versions of one of them too

 

[Pat Hartman]

I don't think the government invented EDI. It seems to be a private company and the transaction definition manuals cost $800+ each. I think it was a consortium that wanted to standardize certain order entry transactions and it grew from there. But, you're right. It was designed by a monkey typing. And of course, they can't really change existing formats so they tend to add more parts to the individual parts.

 

[Minty]

I wrote an EDI export to talk to one of the large German Manufacturing Companies we did a lot of work for back in the early 90's.
They assumed because we were a small company we wouldn't be able to do it. The process file was supposed to be transferred via a dedicated dial-up phone terminal at their end....

When I had it all working locally, I quizzed them on why the dial-up system seemed to be broken when I was trying to test it.
"Oh we can't get that working at the moment. <pause> Wait a minute, you mean you have a file ready to send to us??"
They were genuinely astonished. That was my first real Access database work. It did make us laugh that they weren't anywhere near ready for us. It was months later and I seem to remember we had to send them a diskette in the post!

 

[Isaac]

Yeah. Well certainly dealing with the medicare and medicaid gov files there was some crazy stuff too.
I seem to remember a b-something "response" file ... just weirdness all the way around.

Ahh a diskette. I have some saved in my garage. I want to look at what the heck I saved on there, some day, but
1) I'm not sure I won't be embarrassed (I was 18, ok?)
2) Where can I put a floppy disk into something?

 

[Pat Hartman]

There are companies that will handle EDI for you which we looked at but only briefly. It didn't make any sense to me to write code to format a file to send to the middle-man when I could write code to create the EDI file. Granted, the EDI formats are from hunger but they are documented if you can read gobbledygook. Frequently, trading partners produce dumbed down descriptions of the files they are exchanging.

 

[Oreynolds]

Upsizing to SQLServer (or similar) only becomes a question once you have diagnosed what is causing the bottlenecks/speed issues in your current database as you have it.

There are plenty of things that can be done to an Access database to speed it up (eg judicious use of indexes, analysis of table structure, persistent connections to the back end, turning off NameAutoCorrupt, etc, etc ...) - we don't know whether you have implemented any of these.

You would be better served first explaining the specific situations where you database is slow, with detailed descriptions of the workflow, tables involved, amount of code that runs etc.

It may be that upsizing is the answer, but you should go through the preliminary steps here first.

Hi, thanks for your reply. So, since the post I have been concentrating on the following:

1) Removed all lookups from fields within tables
2) Removed all indexes not necessary
3) Removed a number of historic now unused table fields and adjusted queries
4) Adjusted queries to only query exact required fields
5) Removed non-critical sub-forms from the 4no. main forms used and re-located them to pop up forms
6) The DB already has a persistent connection

Subsequent to the above changes I have achieved some performance increases which has been good. Is there anything else application wise that I ought to implement before I further consider moving to SQL?

Thanks

 

[Minty]

Using a Snapshot instead of Dynasets on form record-sources that are only used as lookup lists, e.g. don't need to be edited.