optimizing DB w/ access front end & MySQL backend

[Pat Hartman]

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.

 

[aaronkempf]

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

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;

I'd start by getting rid of ALL of those awful parenthesis. Access randomly adds those in to help, and I think that they are hogwash.

 

[Minty]