Saved Queries vs SQL build on the fly (1 Viewer)

DBApprentice

On Error GoTo AWF *****:
Local time
Today, 09:53
Joined
Dec 24, 2018
Messages
150
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: :confused:
  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?
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:53
Joined
Aug 30, 2003
Messages
36,118
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.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 08:53
Joined
May 21, 2018
Messages
8,463
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.
 

isladogs

MVP / VIP
Local time
Today, 12:53
Joined
Jan 14, 2017
Messages
18,186
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
 

Galaxiom

Super Moderator
Staff member
Local time
Today, 23:53
Joined
Jan 20, 2009
Messages
12,849
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.
 

isladogs

MVP / VIP
Local time
Today, 12:53
Joined
Jan 14, 2017
Messages
18,186
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!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:53
Joined
Feb 28, 2001
Messages
26,996
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.
 

Jeffr.Lipton

Registered User.
Local time
Today, 05:53
Joined
Sep 14, 2018
Messages
31
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.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:53
Joined
Feb 28, 2001
Messages
26,996
Sounds like a rational choice to me, Jeffr.Lipton.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:53
Joined
Feb 19, 2002
Messages
42,970
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.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 12:53
Joined
Feb 19, 2013
Messages
16,553
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.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 08:53
Joined
Feb 19, 2002
Messages
42,970
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.
 

aaronkempf

New member
Local time
Today, 05:53
Joined
Jul 7, 2020
Messages
14
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.
what is the extension ACCDR for ? Runtime? I used to play around with swapping the extensions of MDB and MDE and tricking people into thinking it's one thing, when it's really another? I wish I knew more about how it worked on the INSIDE. But I've never heard of ACCDR!
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:53
Joined
Feb 28, 2001
Messages
26,996
@aaronkempf - You were correct in your guess. .ACCDR is a run-time DB for use in the free-to-distribute Access Runtime version that allows no development. If you created an .ACCDR and tried to launch it with a full version of Access, it would temporarily mimic the behavior of the Runtime executable.
 

Users who are viewing this thread

Top Bottom