Go Back   Access World Forums > Microsoft Access Discussion > Theory and practice of database design

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-05-2018, 08:12 AM   #1
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 94
Thanks: 22
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
optimizing DB w/ access front end & MySQL backend

So the day was upon us where our database system(s) needed to upgrade our back end to something that could handle all our data. The decision was made to consolidate our largest and most used databases into one front end and one SQL server for the data. Were early on in the consolidation but my big question is with regards to the most efficient way of pulling the data from the MySQL back end to the access front end.

I currently have one main form with a large number of queries so optimizing this process is crucial.

first off, which index(es) are used, the ones with MySQL or with Access? Does access require its own indexes?

secondly, would a Pass through query be best or would it be better if i created custom views within MySQL and then link to those views in my front end?

Can i create a pass-through query in VBA, currently most of the SQL is VBA generated based on the user selections and actions, which is then queried to a recordset or assigned to a recordsource property, can this same thing be done and then passed through?

running a MySQL back end and connecting via ODBC is new territory for me so I want to make sure i get this right. were early on in this transition and currently many of our forms with built in queries are pretty slow, enough to be frustrating.

iv'e googled for days now and haven't come across a good source for this kind of situation, any suggested reading would be appreciated.

TIA!

JJSHEP89 is offline   Reply With Quote
Old 10-05-2018, 08:29 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,676
Thanks: 137
Thanked 1,531 Times in 1,503 Posts
Minty has a spectacular aura about Minty has a spectacular aura about Minty has a spectacular aura about
Re: optimizing DB w/ access front end & MySQL backend

Once the data is in another database Access no longer has any Indexes it manages at all, it's all handled by the DBMS you have switched to.

Provided you have a good network, and your previous database was also a split FE and shared BE and worked okay, you should find the move to SQL server pretty painless.

Pass through queries can make a difference if there is a lot of processing required on the server.

You will need to examine all your queries for any VBA functions you may have added to Access. These will not work very efficiently in your new environment. This is because all the data has to be queried through to access then the local function run on the data, then presented to you as the query result - very slow.

They would be better passed back to the server and incorporated in Views (Stored queries) on the server, that can be accessed just like a table in Access.

Also try and learn to restrict recordsets on forms to only retrieve the minimum data you need.

For instance don't load a form with SELECT * FROM your Table, if you then search for a single record. Load it with SELECT * FROM Yourtable WHERE PrimaryKeyID = 0 instead. Then reload with your search string after user input.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
Old 10-05-2018, 11:01 AM   #3
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 94
Thanks: 22
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: optimizing DB w/ access front end & MySQL backend

so it seems to me that building the views and adding the indexes all within the SQL server is best. Now i have several instances where the VBA gnerates the SQL statement (ex. SELECT fields1, 2 etc. FROM Table WHERE ID =" & varID & ") i guess these would all need to become pass through queries right?

How would I specify a pass through query in VBA? when i change a query to pass-through from within access i don't see any difference in the SQL.

JJSHEP89 is offline   Reply With Quote
Old 10-05-2018, 09:41 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: optimizing DB w/ access front end & MySQL backend

The vast majority of Access applications work quite well using linked tables and Access querydefs. So start with just a straight replace of Jet/ACE with MySQL.

As you go through and test everything, you will see what needs tweaking and what doesn't. Don't make any change that is not necessary.

The biggest problem with converting from Jet/ACE to ODBC is that if you haven't used good client/server techniques to build the Access app, you'll have a lot more stuff to change. One of the biggest killers is binding forms directly to tables or to queries with no selection criteria. This is fairly common with Access and the user simply filters the recordset locally. This is poor practice once you move to ODBC because one of the points of the move is to minimize network traffic. So, you never want to bring entire tables or recordsets down to the user computer. You always want to use queries with criteria so the database engine on the server can do the heavy lifting and return only the requested data. Access makes every effort to make every query a "pass-through" query. You can defeat Access though so you really need to understand how Access interacts using ODBC. For example as someone already mentioned, you can use VBA and UDF functions in your Access queries. That works fine because your Access application can use VBA to run those functions. However, the RDBMS knows nothing about VBA and so cannot possibly run the function at the server. To handle this, Access is very helpful and so it breaks up the query and sends what it can to the server and then performs the rest of the query locally. So, if the function is in the Select clause, there is rarely a problem. Access sends the query sans the function and then gets back the small recordset and applies the query before passing the recordset back to the app. However, if the function is used in the Where clause, that changes everything and now Access is forced to request entire tables and then run the functions locally to select the requested records.

Once you have organized your forms so they return only the absolute minimum number of records, then you can look to optimizing if necessary. My first attempt is to build views so that certain common joins can be optimized. I also occasionally use pass-through queries for bulk deletes or updates. In 25 years of building Access apps linked to SQL Server, Oracle, DB2, Sybase, and other RDBMS', I can count on one hand the number of reports I've needed to create stored procedures to satisfy. Most Access apps are primarily interactive. They rarely have any significant batch processing requirements. I've had a couple of apps that needed significant batch processing to interface with other mainframe systems. In one case, I did the batch process using stored procedures but in all the others, I just used VBA. One app had multiple external interfaces, the biggest being exchanging huge EDI recordsets.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-06-2018, 01:44 AM   #5
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,432
Thanks: 40
Thanked 3,366 Times in 3,261 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: optimizing DB w/ access front end & MySQL backend

Ensure all queries use the sql server language, try to avoid using vba functions such as iif, dlookup, UDF's etc.

They can be a killer over ODBC because although the driver will make a good job of converting access sql to the host equivalent, anything that uses vba functions will need all relevant data 'passed back' for vba to evaluate.

If you have to use them, ensure the query is constructed in a way that the volume of data that vba needs to work on is minimised.
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-08-2018, 04:01 AM   #6
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 94
Thanks: 22
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: optimizing DB w/ access front end & MySQL backend

Quote:
Originally Posted by Pat Hartman View Post
The vast majority of Access applications work quite well using linked tables and Access querydefs. So start with just a straight replace of Jet/ACE with MySQL.

As you go through and test everything, you will see what needs tweaking and what doesn't. Don't make any change that is not necessary.
This is essentially where I am at right now......

Quote:
Originally Posted by Pat Hartman View Post
The biggest problem with converting from Jet/ACE to ODBC is that if you haven't used good client/server techniques to build the Access app, you'll have a lot more stuff to change.
Being the novice that I am, i'm sure i haven't been using the best techniques.


One of the biggest issues I have right now is with one of the primary forms in my database, its fairly large with a tab control and 8 tabs, 3 subforms, and roughly a hundred controls (all read only, except for one search box, edits occur in other pop-up forms). The form took a few seconds to open before the switch to SQL now it takes almost a minute. Almost all the information is pulled from queries generated in SQL, which all the SQL statements are generated in VBA because the search terms, login info, etc. etc. are stored in global variables that are then passed through to the SQL statement in the WHERE clause.

I've even gone through and broken down the SQL statements into a Select Case based on which tab is open, hoping it would run a little faster if I waited to query the data when it was needed rather than all at once when the form opened (I didn't see much of a difference)
JJSHEP89 is offline   Reply With Quote
Old 10-08-2018, 04:50 AM   #7
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,432
Thanks: 40
Thanked 3,366 Times in 3,261 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: optimizing DB w/ access front end & MySQL backend

there are many reasons that sql server/MySQL (which one by the way?) can be slower

Quote:
The decision was made to consolidate our largest and most used databases into one front end and one SQL server for the data.
this is one of them - if many people are using the server - different apps perhaps - then the server can be slower. It needs to be sized and tuned. Indexes in particular need to be rebuilt on a regular basis on tables subject to many changes - similar to compact and repair.

A client of mine migrated the backend to sql server thinking it would be faster - in fact it was significantly slower, despite optimisation. Reason as above. One app (not access) was such a heavy user that all the other apps suffered. IT did not have the budget to increase resources and in the end, the backend was returned to the ACE environment.

with regards your form and optimisation, consider whether you have addressed the issues raised in this link https://www.access-programmers.co.uk...hlight=indexes

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
The Following User Says Thank You to CJ_London For This Useful Post:
JJSHEP89 (10-08-2018)
Old 10-08-2018, 04:55 AM   #8
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 94
Thanks: 22
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: optimizing DB w/ access front end & MySQL backend

Quote:
Originally Posted by CJ_London View Post
there are many reasons that sql server/MySQL (which one by the way?) can be slower
https://www.access-programmers.co.uk...hlight=indexes
My apologies, I have to remember im not talking lay-man's terms here. Were running MySQL piggy backed on an existing server (it will eventually move to a dedicated server once we feel the need)
JJSHEP89 is offline   Reply With Quote
Old 10-08-2018, 05:05 AM   #9
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 94
Thanks: 22
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: optimizing DB w/ access front end & MySQL backend

Quote:
Originally Posted by CJ_London View Post
this is one of them - if many people are using the server - different apps perhaps - then the server can be slower. It needs to be sized and tuned. Indexes in particular need to be rebuilt on a regular basis on tables subject to many changes - similar to compact and repair.

A client of mine migrated the backend to sql server thinking it would be faster - in fact it was significantly slower, despite optimisation. Reason as above. One app (not access) was such a heavy user that all the other apps suffered. IT did not have the budget to increase resources and in the end, the backend was returned to the ACE environment.

with regards your form and optimisation, consider whether you have addressed the issues raised in this link https://www.access-programmers.co.uk...hlight=indexes
currently i don't think server performance is the issue since whats been moved over is still relatively small, less than a 5 gigs (it will grow to 100+ gigs as we consolidate our other systems, hence the move) I think its more the way i have things structured. I've been going through deleting sections of my main form just to narrow down the operations that are bogging things down and i'm seeing drastic improvements. Currently my form takes 13-14 seconds to load, removing a few things I've cut that down to 3 seconds (which is still too long)

Think's for the Link, I'll read through and see what i can gleam from it
JJSHEP89 is offline   Reply With Quote
Old 10-08-2018, 08:14 AM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: optimizing DB w/ access front end & MySQL backend

Consolidating database FE's into one isn't necessarily a good idea. If the same user group uses the individual FE's, it might be more convenient for them to open only a single application but it could also complicate security and maintenance.

Consolidating the BE's is probably a bad idea unless there are relationships between tables where RI would be beneficial.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-08-2018, 09:37 AM   #11
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 94
Thanks: 22
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: optimizing DB w/ access front end & MySQL backend

Quote:
Originally Posted by Pat Hartman View Post
Consolidating database FE's into one isn't necessarily a good idea. If the same user group uses the individual FE's, it might be more convenient for them to open only a single application but it could also complicate security and maintenance.

Consolidating the BE's is probably a bad idea unless there are relationships between tables where RI would be beneficial.
some of the front ends will be consolidated, the ones commonly used by the same people/department. the back-ends all use the same data but that data is currently stored and maintained in 30+ Back end databases all storing similar information, its a huge mess and not normalized by any means.
JJSHEP89 is offline   Reply With Quote
Old 10-08-2018, 01:08 PM   #12
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 94
Thanks: 22
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: optimizing DB w/ access front end & MySQL backend

Quote:
Originally Posted by CJ_London View Post
Ensure all queries use the sql server language, try to avoid using vba functions such as iif, dlookup, UDF's etc.

They can be a killer over ODBC because although the driver will make a good job of converting access sql to the host equivalent, anything that uses vba functions will need all relevant data 'passed back' for vba to evaluate.

If you have to use them, ensure the query is constructed in a way that the volume of data that vba needs to work on is minimised.
so this is a good point, i've started going through some of my queries and i have a few subforms based on queries with several IIF's and DLookup fields in them.

for example, here is one query that a subform is tied to, there are a series of checkboxes on the main form that are used to filter out the data based on the checkbox selection(s). I'd like to move this query to vba and have it generated then passed to the back end but im not sure the best method for doing that.

here is the SQL currently

Code:
SELECT presscalllog.PCL_ToolNumber, presscalllog.PCL_Date, presscalllog.PCL_Description, presscalllog.PCL_Employee, presscalllog.PCL_Operator, presscalllog.PCL_Category, presscalllog.PCL_Yank, presscalllog.[PCL_At Setup], presscalllog.PCL_Missfeed, presscalllog.PCL_DetailsReplaced, presscalllog.PCL_DetailsSharpened, presscalllog.PCL_MaterialThickness, presscalllog.[PCL_JOB#]
FROM presscalllog
WHERE (((presscalllog.PCL_ToolNumber)=[Forms]![Toolbook]![txtTM_ToolNumber]) AND ((presscalllog.PCL_Category)=IIf([Forms]![Toolbook]![ckSlugPulling],"Slug Pulling"))) OR (((presscalllog.PCL_Category)=IIf([Forms]![Toolbook]![ckWear],"Wear"))) OR (((presscalllog.PCL_Category)=IIf([Forms]![Toolbook]![ckMaterialBad],"Material Bad"))) OR (((presscalllog.PCL_Category)=IIf([Forms]![Toolbook]![ckMaterialVariation],"Material Variation"))) OR (((presscalllog.PCL_Category)=IIf([Forms]![Toolbook]![ckWorkInstructions],"Work Instructions"))) OR (((presscalllog.PCL_Category)=IIf([Forms]![Toolbook]![ckPressEquipment],"Press Equipment"))) OR (((presscalllog.PCL_Category)=IIf([Forms]![Toolbook]![ckOperator],"Operator"))) OR (((presscalllog.PCL_Category)=IIf([Forms]![Toolbook]![ckToolAndDie],"Tool & Die"))) OR (((presscalllog.PCL_Category)=IIf([Forms]![Toolbook]![ckChangeOver],"Change Over"))) OR (((presscalllog.PCL_Category)=IIf([Forms]![Toolbook]![ckSensorRelated],"Sensor Related"))) OR (((presscalllog.PCL_Category)=IIf([Forms]![Toolbook]![ckUnknownOther],"Other"))) OR (((presscalllog.PCL_Category)=IIf([Forms]![Toolbook]![ckDieTryOut],"Die Try Out")))
ORDER BY presscalllog.PCL_Date DESC;
JJSHEP89 is offline   Reply With Quote
Old 10-08-2018, 02:33 PM   #13
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,432
Thanks: 40
Thanked 3,366 Times in 3,261 Posts
CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice CJ_London is just really nice
Re: optimizing DB w/ access front end & MySQL backend

Quote:
WHERE (((presscalllog.PCL_ToolNumber)=[Forms]![Toolbook]![txtTM_ToolNumber])
because you are referencing a form variable, the ODBC driver will return everything to then compare against the form variable

you also have # in a field name - do not use non alpha numeric characters (except underscore) it can cause issues

with regards the iif - bad bit of coding. I'm not going to try to understand why you are doing it that way - would suggest wear etc should be in a lookup table and PCL_Category should be numeric linked to the lookup PK

with regards the vba, the build would be something like

Code:
 
dim sqlStr as string
sqlStr="SELECT......FROM.....WHERE PCL_ToolNumber=" & me.txtTM_ToolNumber & " AND PCL_Category IN (" & 
me.recordsource=sqlStr
__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is offline   Reply With Quote
Old 10-09-2018, 10:29 AM   #14
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Posts: 94
Thanks: 22
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: optimizing DB w/ access front end & MySQL backend

Quote:
Originally Posted by CJ_London View Post
because you are referencing a form variable, the ODBC driver will return everything to then compare against the form variable

you also have # in a field name - do not use non alpha numeric characters (except underscore) it can cause issues

with regards the iif - bad bit of coding. I'm not going to try to understand why you are doing it that way - would suggest wear etc should be in a lookup table and PCL_Category should be numeric linked to the lookup PK

with regards the vba, the build would be something like

Code:
 
dim sqlStr as string
sqlStr="SELECT......FROM.....WHERE PCL_ToolNumber=" & me.txtTM_ToolNumber & " AND PCL_Category IN (" & 
me.recordsource=sqlStr
a lot of this code is pieced from older & other databases, and some from back when i knew a lot less about what i was doing....

I've significantly sped this form up just by eliminating a few IIF's, and Dlookups from some of my queries (i also deleted an entire tab that was running a pretty intense query) now i'm down to less than 1.5 sec to open which i can live with for now.
JJSHEP89 is offline   Reply With Quote
Old 10-18-2018, 08:43 PM   #15
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,431
Thanks: 13
Thanked 1,419 Times in 1,353 Posts
Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light Pat Hartman is a glorious beacon of light
Re: optimizing DB w/ access front end & MySQL backend

Code:
because you are referencing a form variable, the ODBC driver will return everything to then compare against the form variable
Actually, the value of the form field is resolved BEFORE the query is sent to the server. The only time there is an issue is if you use a function.

Where Somefield = myfunction(someotherfield)

Since the server cannot interpret VBA, Access has to send the query sans the WHERE clause and then apply the function locally.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Windows 7 users unable to access backend MySQL jetatu General 5 04-01-2014 01:44 PM
Convert Exisitng Access Database to Access Frontend MySQL backend DBnub11 SQL Server 12 04-01-2011 08:15 AM
Access Front End and MySQL Database coyote General 26 05-01-2009 03:57 AM
MS Access distribution with MySQL backend. The_Vincester General 1 02-03-2007 12:34 PM
Using Access as a front end to MySQL davesmith202 General 1 09-16-2005 05:08 PM




All times are GMT -8. The time now is 06:10 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World