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

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 01-11-2019, 01:41 PM   #1
DiogoCuba
On Error GoTo AWF *****:
 
DiogoCuba's Avatar
 
Join Date: Dec 2018
Location: Rio de Janeiro - Brazil
Posts: 122
Thanks: 73
Thanked 3 Times in 1 Post
DiogoCuba is on a distinguished road
Saved Queries vs SQL build on the fly

L&G,

I have been thinking about the use of Queries saved on my database vs building them via SQL on the runtime.

Because of this I am thinking about these:
  1. Is it faster to have thee SQL built on the runtime or it would be slower?
  2. Does having a lot of saved queries will bloat the file?
Can you provide me any thoughts on this matter?

__________________
Best regards,
Diogo Cuba

"Stay Hungry, Stay Foolish: you only live once, so make it count and go after what you want no matter what."
DiogoCuba is offline   Reply With Quote
Old 01-11-2019, 02:14 PM   #2
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 32,770
Thanks: 11
Thanked 4,015 Times in 3,952 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Saved Queries vs SQL build on the fly

I doubt you'd see much difference, but:

1. Saved queries are compiled and thus theoretically faster.
2. Saved queries are objects which would make the FE a little bigger, but not enough in my view to make it a factor.

I generally have a mix of both. Mainly saved queries when the SQL doesn't vary (typically with parameters though), dynamic SQL when the SQL will vary.
__________________
Paul
Microsoft Access MVP

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
DiogoCuba (01-12-2019)
Old 01-11-2019, 02:15 PM   #3
MajP
Newly Registered User
 
Join Date: May 2018
Location: USA baby
Posts: 1,546
Thanks: 25
Thanked 466 Times in 443 Posts
MajP will become famous soon enough MajP will become famous soon enough
Re: Saved Queries vs SQL build on the fly

Stored queries are faster. How significant? Isladog can probably tell you based on speed tests.
But there are a lot more things to ensure speed. Two very good articles
http://www.fmsinc.com/tpapers/faster/
http://allenbrowne.com/QueryPerfIssue.html

A query does not take up a lot of space. It is only instructions and not the data like a table.

MajP is offline   Reply With Quote
The Following 2 Users Say Thank You to MajP For This Useful Post:
DiogoCuba (01-12-2019), Tera (03-12-2019)
Old 01-11-2019, 02:48 PM   #4
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,081
Thanks: 105
Thanked 2,445 Times in 2,247 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Saved Queries vs SQL build on the fly

Agree in principle with both the previous answers.
Stored queries SHOULD be faster as they are optimised.
However my speed tests indicated that SQL statements are often faster which I didn't expect.

See Speed Tests - Query vs SQL vs Query Def

The difference in times is usually fairly small and other factors are usually more important.
Which is fastest seems to spend on the complexity of the query/SQL
Using query defs is always slower than either of the other two methods

When I started using Access years ago I only used queries
These days I mainly use SQL statements because I prefer to have all the code visible in one place.
However, if I need to reuse the same SQL in several places, I will save as a query

Whichever approach you use, Access will always try to do things as efficiently as possible. That's one reason why it often rearranges queries or bracketing from the way we've setup the query design.

If you really want to research this topic in detail, you can use the JET ShowPlan to view how Access runs each saved query / SQL statement / query definition. For more details, see: Show Plan - Go Faster
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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.
,
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.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.
isladogs is offline   Reply With Quote
The Following 2 Users Say Thank You to isladogs For This Useful Post:
DiogoCuba (01-12-2019), Tera (03-12-2019)
Old 01-11-2019, 04:08 PM   #5
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,459
Thanks: 85
Thanked 1,448 Times in 1,367 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Saved Queries vs SQL build on the fly

Quote:
Originally Posted by isladogs View Post
Stored queries SHOULD be faster as they are optimised.
However my speed tests indicated that SQL statements are often faster which I didn't expect.
There is the factor that a stored query plan is based on the statistics at the time it was saved. With changes to the data, that plan might no longer be optimum.

Consequenly it can be a good idea to compact occasionally to force query plans to rebuild with the current statistics.
Galaxiom is offline   Reply With Quote
The Following User Says Thank You to Galaxiom For This Useful Post:
DiogoCuba (01-12-2019)
Old 01-11-2019, 04:15 PM   #6
isladogs
Part time moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 9,081
Thanks: 105
Thanked 2,445 Times in 2,247 Posts
isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all isladogs is a name known to all
Re: Saved Queries vs SQL build on the fly

Quote:
Originally Posted by Galaxiom View Post
There is the factor that a stored query plan is based on the statistics at the time it was saved. With changes to the data, that plan might no longer be optimum.

Consequenly it can be a good idea to compact occasionally to force query plans to rebuild with the current statistics.
Good point!
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Website links:
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.
,
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.


Colin
Access 2010 32-bit, Access 2016 32-bit & 64-bit, SQL Server Express 2014, Windows 10,
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.
isladogs is offline   Reply With Quote
Old 01-11-2019, 06:37 PM   #7
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,567
Thanks: 73
Thanked 1,483 Times in 1,372 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Saved Queries vs SQL build on the fly

Diogo - if you want to know how big a stored query is (not counting the query plan), open the query in SQL view. You are looking at the query. That's it. The query plan is actually very simple. We have some articles by Pat Hartman about query plans and I don't recall that they are big, bulky things either. I'd be surprised if a query and its plan together exceeded 1K bytes.

Queries, unless they are open, have NOTHING but text and settings stored with them (and if you prefer "properties" to "settings" you would not be wrong.) When a query is open, there is potentially some type of memory structure, a list of the records condoned by the WHERE clause. But as to dynamically created SQL? That works just fine, and in fact unless you can build a parameter query for what you wanted to do as a dynamic query, sometimes the only way to get the desired effect at run time is to build the query on the fly.

The speed issue of stored vs. dynamic is open to discussion, but the truth is that Colin has done a lot of speed testing. If he says the difference is quite small for many queries that he has tested, then I would have to say that it probably isn't much to worry about. I think the biggest difference comes about if the query is run via DoCmd.RunSQL with a query containing substitution, when compared to a pre-constructed SQL string run via some equivalent of CurrentDB.Execute to run the query.

If you think about it, the topic we call "cascading combo boxes" is all about dynamic queries that are used to reload the .RowSource properties of the second and later combo boxes in a cascading sequence, because that .RowSource is just a query.

You asked about file bloat. That won't be caused by having or not having a bunch of stored queries. It will be caused by not merely having but RUNNING a lot of action queries that include some DELETE or UPDATE queries. Bloat occurs when you have dead space in your DB. Dead space occurs when you delete records. Big dead space occurs when you delete records in bulk.

Besides the obvious DELETE queries, you can have another source of deleted records. I believe that an UPDATE involving one or more string fields will not update the record in-place. Instead, it creates a new updated copy of the original record, then threads the new record into the table, then deletes the old record. So a bulk update would result in having deleted as many records as it updated. And therein originates the bloat.

Access cannot get rid of the bloat without a Compact & Repair because garbage collection algorithms (that would reclaim the space) have theoretical and practical limitations. I've seen it on IBM mainframes, DEC mainframes, HP mainframes, and PCs. I'm told that it affects Windows, UNIX, MAC/OS, OpenVMS, and all of the myriad of small UNIX variants. Some of those I know well enough to agree; for the others, I believe the problem exists as well.

__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
The Following 2 Users Say Thank You to The_Doc_Man For This Useful Post:
DiogoCuba (01-12-2019), Tera (03-12-2019)
Old 03-12-2019, 02:01 PM   #8
Jeffr.Lipton
Newly Registered User
 
Join Date: Sep 2018
Posts: 29
Thanks: 24
Thanked 1 Time in 1 Post
Jeffr.Lipton is on a distinguished road
Re: Saved Queries vs SQL build on the fly

I use queries as the data source for Forms, or if I need a query in multiple places. Inside my code, I almost always use SQL.
Jeffr.Lipton is offline   Reply With Quote
Old 03-12-2019, 07:17 PM   #9
The_Doc_Man
Happy Retired Curmudgeon
 
Join Date: Feb 2001
Location: Suburban New Orleans, LA, USA
Posts: 13,567
Thanks: 73
Thanked 1,483 Times in 1,372 Posts
The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold The_Doc_Man is a splendid one to behold
Re: Saved Queries vs SQL build on the fly

Sounds like a rational choice to me, Jeffr.Lipton.
__________________
I'm a certified grandpa (3 times now) and proud of it.
Retired over one year and survived being home all day with the wife. She must really love me.
If I have helped you, please either click the thanks or click the scales.
The_Doc_Man is online now   Reply With Quote
Old 04-13-2019, 07:37 PM   #10
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,555
Thanks: 13
Thanked 1,453 Times in 1,385 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: Saved Queries vs SQL build on the fly

I use querydefs almost exclusively. In older versions of Access, embedded SQL caused excessive bloat because each time they execute, the execution plan needs to be rebuilt and that took workspace and Access couldn't reuse it so queries that ran many times caused huge bloating problems. Current versions of Access don't have this problem so the timing difference is all that is left and you need to execute the embedded SQL many times before you will see a noticeable delay.

The thing that sways me toward stored querydefs is their reusability. I frequently create "base" queries that include calculated values and then use these base queries every time I need data from a particular table. That means that if a calculation changes, I have only one place to change it rather than dozens if I built the query with embedded SQL.

I use embedded SQL when either the select clause or the where clause needs to change dynamically. As long as everything is static and I can use an argument to pass in criteria, I go with the querydef. So, my search forms always use embedded SQL. There are a couple other situations where I use embedded SQL and those are mostly because of the stupid way that the QBE rewrites the SQL string under some conditions.
__________________
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:
NauticalGent (04-16-2019)
Old 04-14-2019, 02:21 AM   #11
CJ_London
Super Moderator
 
Join Date: Feb 2013
Location: UK
Posts: 10,727
Thanks: 40
Thanked 3,481 Times in 3,368 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: Saved Queries vs SQL build on the fly

I tend to build my queries in VBA (my apps are all .accde) for the simple reason it is one less object a curious user could tinker with. I do have some queries in the back end (password protected) which I use as read only views and to on occasion as a stored procedure.
__________________
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 04-15-2019, 10:44 AM   #12
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 27,555
Thanks: 13
Thanked 1,453 Times in 1,385 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: Saved Queries vs SQL build on the fly

Try delivering the .accde renamed to .accdr. If the users are not savvy Access developers, they may not figure out what changed but they will NOT be able to get to design view at all unless they figure out that they can simply rename the app back to .accde.

If you have tinkers, you should also disable the shift-bypass and hide objects the .accde allows them to muck around with. None of this actually prevents hacking. It just adds more hurdles that are likely to defeat someone who is not a trained Access developer.

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

Tags
queries , runtime , sql , sql at run time

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Saved 10mb with reviewing a few queries, forms etc. PNGBill General 14 07-20-2010 10:47 PM
To build a queries polachan Queries 4 08-19-2008 10:59 PM
Trying to build a report with 2 queries smichaels1234 Reports 0 06-11-2008 04:46 AM
delete saved queries amargupta Queries 1 06-08-2005 08:28 PM
build form to run queries krispetrie Forms 1 06-06-2001 05:33 PM




All times are GMT -8. The time now is 09:46 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