slow odbc response from backend mysql dB

[Harry_38]

Administration program for a non profit organisation, that organizes 125 courses and has 1000 members. Access2003 with backend dB; works fine on a PC. Next year more people in different locations need access to read and write.
I converted the Access dB to mysql and put it on a server; and I coupled my frontend Access dB with mysql through odbc driver. It works but reports and input forms are very slow; I clocked up to 4 minutes for a report thru mysql that takes less than a second if run with the backend on my PC.
How can I speed up the process and are there alternatives?
Particulars:
Seize of the backend MS Access dB:
· 17 tables with 3 tables between 1500 en 2500 records and one table, the centre of the database with 300 records
· The remaining tables less than 100 records
· Total size in: 4.6 Mb

Size of frontend MS Access Application program.
· 5 small tables
· 150 queries; I could bring this down to 50
· 90 forms and 90 rapporten; could be reduced
· 24 macros and 4 modules
· Seize: 23 Mb

 

[Banana]

The most probable cause is SQL written toward Access & VBA functions which can hurt performance and may need to be rewritten so it can be better optimized. In case of reports, you can just use passthrough and therefore write in native MySQL dialect to ensure all processing happens at MySQL. If you hand a query that as an example call a VBA or Access' built-in function as a part of criteria in the WHERE, Access is forced to pull down the whole table (or at least a large subset of it) then process the VBA function locally.

For more concrete examples, look at the this section in Beginner's Guide to ODBC. Note that there are also several more links on the bottom of page referencing various articles and whitepapers discussing optimization with a ODBC backend.

I hope that helps.

 

[Harry_38]

Banana, thanks a lot for yr reply. I understand that actually Access is not fully compatible with odbc backend and that adjustments have to be made at the front end to improve performance. I will try to work my way through the beginner's course, certainly not easy stuff!
May come back to you with questions, hope that's ok with you.

 

[Harry_38]

Banana, I understand that the slow response comes from MS Access queries that are not compatible with mysql. If I rewrite the slow queries in mysql, within my frontend Access, could that solve my response problems?
Another problem seems to be the combo boxes in forms. Appreciate your answer, before I am going to invest another xx hours.

 

[Banana]

Well, it depends.

As I said, for your reports, you can go ahead and use passthrough queries - there's no reason to not to. With forms, it's more complicated because typically we want to be able to bind the form and update stuff in it -- passthrough queries aren't updatable. Therefore for forms, we probably would want to write queries that conforms to standard SQL (to be more precise, ODBC SQL) so we can be sure Access is able to pass the query back to MySQL in its entirety and provide analogous performance as we see with passthrough queries.

As for comboboxes, I find that it helps to keep a local copy of lookup tables so Access doesn't have to go back to MySQL everytime we use combobox. This works well for table that doesn't change often in values (e.g. a table of states or status flag or something like that). For comboboxes where you need to look up data such as clients, events and therefore must have most up to date, then it's back to same principle behind writing queries for form - write it in most ODBC-compatible format so MySQL can handle it entirely.

 

[Harry_38]

Banana, I found several causes of the slow response and I am solving them one by one. e.g.
Moved several dB's to front end; redesign of Input forms; alternatieves to calculations of totals in Bankbooks; etc..
Thanks again for the help; I am hopeful that I'll solve the problems
Regards, harry

 

[Banana]

Excellent. Best of luck with your project - if you do have any specific question, do feel to ask.