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)
-   -   Saved Queries vs SQL build on the fly (https://www.access-programmers.co.uk/forums/showthread.php?t=303234)

DiogoCuba 01-11-2019 01:41 PM

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: :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 01-11-2019 02:14 PM

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.

MajP 01-11-2019 02:15 PM

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.

isladogs 01-11-2019 02:48 PM

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

Galaxiom 01-11-2019 04:08 PM

Re: Saved Queries vs SQL build on the fly
 
Quote:

Originally Posted by isladogs (Post 1606085)
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 01-11-2019 04:15 PM

Re: Saved Queries vs SQL build on the fly
 
Quote:

Originally Posted by Galaxiom (Post 1606088)
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 01-11-2019 06:37 PM

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.

Jeffr.Lipton 03-12-2019 02:01 PM

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.

The_Doc_Man 03-12-2019 07:17 PM

Re: Saved Queries vs SQL build on the fly
 
Sounds like a rational choice to me, Jeffr.Lipton.

Pat Hartman 04-13-2019 07:37 PM

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.

CJ_London 04-14-2019 02:21 AM

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.

Pat Hartman 04-15-2019 10:44 AM

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.


All times are GMT -8. The time now is 09:27 AM.

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