Access World Forums

Access World Forums (https://www.access-programmers.co.uk/forums/index.php)
-   Theory and practice of database design (https://www.access-programmers.co.uk/forums/forumdisplay.php?f=74)
-   -   optimizing DB w/ access front end & MySQL backend (https://www.access-programmers.co.uk/forums/showthread.php?t=301830)

JJSHEP89 10-05-2018 08:12 AM

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!

Minty 10-05-2018 08:29 AM

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.

JJSHEP89 10-05-2018 11:01 AM

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.

Pat Hartman 10-05-2018 09:41 PM

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.

CJ_London 10-06-2018 01:44 AM

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.

JJSHEP89 10-08-2018 04:01 AM

Re: optimizing DB w/ access front end & MySQL backend
 
Quote:

Originally Posted by Pat Hartman (Post 1594442)
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 (Post 1594442)
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)

CJ_London 10-08-2018 04:50 AM

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

JJSHEP89 10-08-2018 04:55 AM

Re: optimizing DB w/ access front end & MySQL backend
 
Quote:

Originally Posted by CJ_London (Post 1594662)
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 10-08-2018 05:05 AM

Re: optimizing DB w/ access front end & MySQL backend
 
Quote:

Originally Posted by CJ_London (Post 1594662)
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

Pat Hartman 10-08-2018 08:14 AM

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.

JJSHEP89 10-08-2018 09:37 AM

Re: optimizing DB w/ access front end & MySQL backend
 
Quote:

Originally Posted by Pat Hartman (Post 1594706)
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 10-08-2018 01:08 PM

Re: optimizing DB w/ access front end & MySQL backend
 
Quote:

Originally Posted by CJ_London (Post 1594446)
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;


CJ_London 10-08-2018 02:33 PM

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


JJSHEP89 10-09-2018 10:29 AM

Re: optimizing DB w/ access front end & MySQL backend
 
Quote:

Originally Posted by CJ_London (Post 1594761)
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.... :confused:

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.

Pat Hartman 10-18-2018 08:43 PM

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.


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

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