Improve performance by basing sub forms & combo boxes on queries

[Guest]

Improve an application's performance by basing sub forms and combo boxes on
queries:

Why does making sub forms based on queries rather than tables improve
performance, of an application when, in either case, you place only fields
that you need on the form anyway?

and

Why do query files, used for combo boxes instead of SQL statements, improve
the performance of an application?

John

 

[Guest]

Saved queries are pre-compiled. They run faster than an uncompiled SQL
string. If you base your control on a table you are bringing back all the
fields whether they are in the form or not.

Also, your return all the records in a table. You can limit the number of
records in a query which will also improve the performance.

 

[Albert D.Kallal]

Improve an application's performance by basing sub forms and combo boxes
on
queries:

Why does making sub forms based on queries rather than tables improve
performance, of an application when, in either case, you place only fields
that you need on the form anyway?

Actually, for the most part, it does not improve performance at all....

and

Why do query files, used for combo boxes instead of SQL statements,
improve
the performance of an application?

Are you taking about a jet based back end, or a sql server based back end?
Without question, when you use sql server, removing the sql from the combo
box, and placing that sql as a view on sql server, and then linking to that
sql view, and then specify ONLY the view name all by its self in the combo
box makes a REAL big improvement. The reason for this is jet has to do less
work, and *most* of the time, that combo box is raw sql, which jet has to
translate to the linked table, and that then that table translates/links to
the sql server. By using the raw view name for the combo box get MUCH better
response time because of less translation going on (no sql in the combo box
means no jet translate...or, least less when you have raw sql in the combo
box, or even a query in the combo box --- I *STRESS* that is advice only
applies when using linked tables to sql server).

However, if you are not talking about sql server, then basing a form on a
query, or a table will not make any difference in performance.

What will make a difference is not willy nilly loading a form based on a
table (or query) without using the "where" clause. So, for all forms, the
trick is not using a table, or query to improve performance, but MAKING SURE
YOU use the where clause to restrict the forms records loaded to the one
record you need...

I give some thoughts, and some ideas as how you would build such a form
here:

http://www.members.shaw.ca/AlbertKallal/Search/index.html