We have a multi-user desktop application (<10 users) which for various reasons stores the data on a server in Microsot Access 2007 type files. Up to this point we have been using OBDC drivers to connect to the data quite sucessfully and without any major problems. However a recent increase in the amount of data has caused a slowdown in the speed of our software (only talking Mb’s not Gb’s).
We have been using:
[quote]rs1=db1.SQLSelect("SELECT * FROM Detail WHERE DocNo = "+DocN)[/quote] to get data, and
[quote]db1.SQLExecute("UPDATE Detail Set Status = “+str(I)+” WHERE DocNo = "+DocN)[/quote] to write data
It has been suggested to us that this is a ‘feature’ of using ODBC and that we should rewrite the software to use ADO drivers or move the data to a propriator database like OpenBase or SQLite.
Do other poeple agree that ODBC is cumbersome and slow? Is there a better way we should be doing this?
ODBC is dog slow. At least in my experience under Windows. How often do you do this? When you are dealing with MBs ODBC should be enough.
A couple of years ago I wanted to use Access as front end for Microsoft SQL Server because I had a reporting tool in Access. With my data going over 2 GB I had to find an alternative. Using Microsoft SQL Server via ODBC was very slow. But for reporting I had to access way more data than single record selects.
Compared with ADO, MSSQL Express or MSSQL Server ODBC is kinda slow and with less functionality. I would’nt use it for day to day applications. The main advantage of ODBC is its wide availability and as gateway for importing/exporting data, that’s it.
On the other hand Access or the level of JET Engine it uses, is imho not designed for multiple users over network. Neither Sqlite is! You will run into problems when switcing between 32Bit and 64Bit and it has some more drawbacks when thinking about locking and using NAS devices with hosted database files. I would change to mySQL or MSSQL instead of keep on riding a dead horse (combination Access/ODBC)
Usually, I would say to use a prepared statement and host variables with ODBC, instead of a dynamic statement you build each time, but - I am not sure Xojo implements host variables that way… Perhaps someone from Xojo can answer?
ODBC usually imposes a penalty on execution, but it depends strongly on the driver and database. For example, Progress Datadirect ODBC drivers are very fast. In this case, using an Access data file is, in my opinion, probably the biggest culprit in your slowdown. That file might actually be transferred over your network for each query, instead of just the data you select.
Can you possibly switch to a more centralized database server, such as CUBase, MySQL, or better yet, MSSQL Server Express? Of course, how much data you are storing is a factor there. Are you talking about terabytes or megabytes of data in the database?
Another thing to consider, since this was only a recent problem, it could be that the data just needs indexing. What I can’t remember is if there’s a way to do that in Access.
FWIW, years ago (before Xojo) I had a client who was using Access as a back end and they were on the edge between 10 and needing 11 simultaneous users. As I recall, switching to MSSQLServer Express was light years ahead of Access in terms of speed and reliability (no more strange db errors when user 11 was online) but the sacrifice was in IT. it meant that they needed a more sophisticated backup system than a Zip drive (remember those?)
That said, I liked the upgrade path from Access to Express to a full version of SQLServer. It gave customers a taste of what they were in for IT-wise for a relatively small cost. When their app needed more CPU cores or more memory, it was usually time to get a real server anyway because they’d grown to that point anyway.
I have found there are a few ways to increase the speed of Access databases, although sometimes the performance changes are large and sometimes small.
Access has two utilities that assist with performance: Performance Analyzer and Table Analyzer. Run these and Access will offer some helpful suggestions.
Compact Regularly - this frees up old objects and records. In Access 2010 click the File tab and click options (under help). Choose the current database in the left pan. Check Compact On in Applications Options section, and then click ok.
If its possible, change the connection type from DSN-less to DSN. This would require changing the connection to the database.
Thanks for that Eugene, helpful but doesn’t really move things forward as I’d already gone through the Access Analyzer, Compacting the databases etc. DNS or DNS-less seems to make little difference on a test set-up.
I guess the choices I have are:
1. Stick with ODBC and put up with slowness.
2. Try ADODB and see if that’s any better speed wise.
3. Change to SQLite/Express or similar
Each option has it’s advantages and disadvantages:
My customer likes the data being stored in .accdb files and it’s easy for me to correct mistakes in the ‘raw’ data when he screws things up or needs some amendment made. He can access parts of the data from Word/Excel etc if he wants to with ODBC. It’s easy to locate the data files for back-up, etc. If we’re to keep these files it looks like it’s got to be either ODBC or ADODB routes.
SQLite/Express looks a good option to me from a programming point of view. Technically better in a networked environment. It’s going to be faster and the coding appears to be quite similar to what I’ve currently used for ODBC. However: the customer loses knowing where the data is and being able to see it, making changes to data could be less straightforward, backup routines would have to be changed, so I would have to put forward a convincing argument for this.
At the moment I’m playing with writing some routines as a test to do some comparisons between ODBC and ADODB, but I’m struggling to get it to work with the examples Eugene gave in his post http://forums.realsoftware.com/viewtopic.php?f=3&t=28743
As always any thoughts, opinions and comments gratefully received.
[quote=192330:@John Dear]I guess the choices I have are:
1. Stick with ODBC and put up with slowness.
2. Try ADODB and see if that’s any better speed wise.
3. Change to SQLite/Express or similar
[/quote]
Create a JSON-RPC server that runs on the same computer as the database and that performs the reads and writes then adapt your client program to communicate with that server instead of accessing the database directly.
As Paul alluded to before one of the major problems you’ll have with your current set up is all filtering will happen client side. If you had a table with a million records in but you were only interested in one of them still all million records would be transferred across the network to the client and only then would the one interesting record would be sought out for you. You suggested you have around 10 users so that inefficiency could be happening ten times over.
Steve, that sounds an interesting idea as, if I understand you correctly, it gives the set-up the customer wants retains his .accdb files and may give the speed and responce I want.
I may be showing my ignorance here, I’ve not heard of JSON-RPC before let alone worked with it. I don’t suppose you have any brief code examples of opening a connection to the RPC server and how you read and write data so I can access how much recoding might be involed? I’ve had a quick look at their web site but got a bit confused.
[quote=192350:@John Dear]Steve, that sounds an interesting idea as, if I understand you correctly, it gives the set-up the customer wants retains his .accdb files and may give the speed and responce I want.[/quote]Yes.
[quote=192350:@John Dear]I may be showing my ignorance here, I’ve not heard of JSON-RPC before let alone worked with it. I don’t suppose you have any brief code examples of opening a connection to the RPC server and how you read and write data so I can access how much recoding might be involed? I’ve had a quick look at their web site but got a bit confused.[/quote]Sending a request to a JSON-RPC server is not complicated at all, you just use a HTTPSocket to send a POST request with your JSON object (usually as a UTF8 string) as the data and ‘application/json’ as the Content-Type.
The server itself is really just a web server but instead of sending HTML it sends back JSON. It could be implemented with Apache/PHP, NodeJS, Python or even Xojo (although the latter would take more work).
I’ll try to rustle up a simple example for you when I get the chance (but the server side will be in Python).
Also, it sounds like the customer wants to go in and manually manipulate his data. With MySQL you can use something like RazorSQL (http://razorsql.com) to give him a nice user friendly GUI based front end to access, manipulate, manage, edit, and even change his data, from any workstation, safely, and with some controls. Much faster and far more safely than using single user Access tables.
By the way, XoJo does have a prepared statement for ODBC (http://documentation.xojo.com/index.php/ODBCPreparedStatement) and it might help your speed a bit. It isn’t going to improve your efficiency if indeed, you are transferring large chunks of or even the entire data file over the network each time you do a query. For that you need to centralize the data.
With the help of Eugene’s example code I’ve created two almost identical programs, one uses ODBC, the second ADODB. ADODB takes 1.93 seconds to do two simple queries on the database across the network where ODBC takes 3.41 seconds to do the same two queries.
Going to try to recreate the same test using SQLExpress. From your comments I’m expecting it to be quicker, but the question is how much quicker
Have you tried to use a profiler to see if your slow queries might come from some other root cause? 2 seconds for the selection of one single record is waaaaaayyyyy too slow.
It would appear that you are querying without the table key. A sequencial read is always really slow. Even so, your timings are way slower than I would expect. Perhaps you have a problem with the database, or with the PC it is on. An adequate table key is essential. If a table is to be queried without its key fields, then additional indices are necessary. Too many indices will at some point decrease performance.
Now, Access is a desktop database, not really suitable for multi-user roles. I am convinced that SQL Express with ADODB would be light years ahead. In an other thread, I suggested the use of MS SQL Server data migration assistant, a free download that moves your tables and data to a SQL Server DB. A nice tool that I use from time to time. I converted Access 200 and Access 2013 databases with the tool. (the 2000 DB was just to test the versatility of the tool.)
The customer for various reasons rejected moving to SQLExpress type database choosing to stay with Access type databases. However, ADODB proved to be much faster than ODBC, both reading from the database and particularly when updating multiple (30+) fields in a single record.
So the answer to my original question Is ODBC slow or there another cause? is, Yes, ODBC is slow.
