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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-19-2018, 03:48 AM   #16
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Location: TEXAS!
Posts: 115
Thanks: 26
Thanked 0 Times in 0 Posts
JJSHEP89 is on a distinguished road
Re: optimizing DB w/ access front end & MySQL backend

would the VBA code run to compile a complete SQL statement then send it to the server or does it send a portion of the sql, stop to execute the vba function, then send the rest of the sql? so essentially the server is tied up the whole time waiting for the vba to execute?

JJSHEP89 is offline   Reply With Quote
Old 10-19-2018, 03:54 AM   #17
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,976
Thanks: 141
Thanked 1,611 Times in 1,583 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

What Pat is saying is that SQL Server will return the entire unfiltered dataset to allow Access (via VBA) to perform the function locally.

Think of it like you holding a coin you need to compare to a big bunch of coins in a bank. Normally you'd send the description to the bank they'd use their clever coin sorting machine to find a match. However the coin you have also has an infrared ink mark on it that you also need to match , and the bank don't have the infra red glasses.

So they send you all the coins to look though yourself.
__________________
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 online now   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
JJSHEP89 (10-19-2018)
Old 10-19-2018, 03:59 AM   #18
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,613
Thanks: 40
Thanked 3,434 Times in 3,325 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

like that analogy

__________________
CJ_London
_______________________
A little thanks goes a long way. If you have found this post useful, please tick the thanks button
CJ_London is online now   Reply With Quote
Old 10-19-2018, 04:00 AM   #19
JJSHEP89
Newly Registered User
 
Join Date: Aug 2016
Location: TEXAS!
Posts: 115
Thanks: 26
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 Minty View Post
What Pat is saying is that SQL Server will return the entire unfiltered dataset to allow Access (via VBA) to perform the function locally.

Think of it like you holding a coin you need to compare to a big bunch of coins in a bank. Normally you'd send the description to the bank they'd use their clever coin sorting machine to find a match. However the coin you have also has an infrared ink mark on it that you also need to match , and the bank don't have the infra red glasses.

So they send you all the coins to look though yourself.
awesome example, i can only laugh at the thought of what this would look like... definitely helps me to understand what is actually happening.

So in order to avoid getting truckloads of coins dumped on me, the best course of action would be to execute my VBA and assign the resulting SQL statement to a string variable, then send the full SQL string to the server, correct? or is there a better way of doing it?
JJSHEP89 is offline   Reply With Quote
Old 10-19-2018, 04:45 AM   #20
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 5,976
Thanks: 141
Thanked 1,611 Times in 1,583 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

The ODBC drivers involved generally do a very good job of all of this.
So any already saved query will be "translated" into optimised T-SQL without you needing to do a lot.

The only time you really need to think clever is if you do Access side VBA functions, or have local tables joining to linked tables. There are some easyish fixes though.

VBA functions, I have one that gets exchange rates for instance from our Accounts package, and displays the current Sterling equivalent value, I simply rewrote the function into the SQL server, and I apply it there in a saved SQL view that I use as source for my form.

All the heavy lifting is done remotely on the server, I simply display the results in access.

This does mean learning T-SQL (for SQL server) but trust me it's not that hard if you understand Access SQL.
__________________
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 online now   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
JJSHEP89 (10-22-2018)
Old 10-21-2018, 07:56 AM   #21
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,496
Thanks: 13
Thanked 1,440 Times in 1,372 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: optimizing DB w/ access front end & MySQL backend

Clever analogy Minty.

Saved querydefs (even those that reference form variables) are compiled the first time they are executed and the execution plan is saved. Then every time the query runs, that same execution plan is used. When you send queries to the server, whether they originate as querydefs or SQL strings, there is no ability to pre-compile them. The server creates the execution plan on the fly each time. The only way to get around this is to use views. Views are updateable as long as a plain query would be updateable. So make sure you include all the PK's in the selection list because if you are not careful, you can make the view not updateable.

I always used querydefs for all queries unless I actually need to build the SQL String on the fly as I might for a complex search form. So, even though the query is going to be sent to the server where an execution plan will be built on the fly, there is some processing already decided on so Access has to do slightly less work for each execution.

This used to be more problematic in the early days of Access. Using embedded SQL used to cause extreme bloat because Access couldn't clean up after itself without a compact. Access has gotten more efficient in this area and so although there is a very slight time difference, it is no longer imperative to always use querydefs for efficiency. I use querydefs because they are easier to change, plus they are reusable. embedded SQL is what it is and if you have to modify something, you have to modify it in every separate SQL string.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
JJSHEP89 (10-22-2018)
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 05:59 AM.


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

Featured Forum post


Sponsored Links


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