By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,701 Members | 2,006 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,701 IT Pros & Developers. It's quick & easy.

Application Speed

P: n/a
Ever since I successfully applied some techniques for increasing the
speed of my 17 meg. Application; it has only made me hunger for more.
First, let me list what I have done so far:

1. Split the application and database into front and back-ends,
2. Increased the load-time of my application by "pre-loading my
heaviest forms when the database is initially opened,
3. When forms do open, under, "Filter Lookup" I use, "Never",
4. I set my combo boxes under "auto expand" to "no",
5. I have discovered that fonts can make a significant difference in
"large" applications. For example; if you measure the file size of
folders for each of Window's font types for lightening your
application's size/weight, "Microsoft Sans Serif" does not take
up as much memory. Again, the benefit from using Microsoft Sans Serif
is more apparent in larger databases. However, using, Microsoft Sans
Serif in applications, make fonts more visible/clear on LCD screens;
even with "Clear Type" turned on. Another benefit in using this
font-type is that I no longer have a need to use bold, which uses more
memory. Also, using Microsoft Sans Serif, with larger fonts for main
titles gives it a sharper bold look than using bold type for other same
font size.
6. I created "query files" for using only table fields that are
needed in a particular form.

These are just a few, apart from suggestions applied from using the,
FMS Total Access Analyzer, that I used for speeding up my application.

However, let get peoples opinions on a couple of thoughts for
furthering my application's speed :

A. There are three or four groups of linked tables in my database that
are not linked to any other part of the database except for using
lookups i.e., employee's names and locations. In the event that I
upsize my database to SQL at a later time, would it be more beneficial,
for application speed, to take some of these, near self-containing
groups of tables, and place them in multiple groups of back-ends, i.e.,
employee information vs. client private information and their treatment
schedules? Also,

B. If I were to take my form specific query files (over 200), and
convert them to SQL statements instead of using query files; would that
help in, a) reducing the size of my front-end application, speed up my
application, and ease the process of later upsizing my
application/database?

John

Dec 4 '06 #1
Share this Question
Share on Google+
2 Replies


P: n/a
"jphelan" <jp*********@hotmail.comwrote in message
news:11**********************@l12g2000cwl.googlegr oups.com...
1. Split the application and database into front and back-ends,
Do you distribute to your users a mde. A mde runs faster, is smaller, has
source stripped, and also is far more reliable
(it is more reliably since it can't become un-compiled, and even better is
that un-handled errors don't re-set variables like
they do in a mde).

So, as much as the following things you have, a mde would be a welcome step
in your question. And, by split, I thus
assume that this means that EACH user gets a copy of the mde.
2. Increased the load-time of my application by "pre-loading my
heaviest forms when the database is initially opened,
The above is a catch 22. By loading up forms that may not yet be needed, you
are using up considerable amounts of memory. (far more then your following
comments about fonts...which are really insignificant).
3. When forms do open, under, "Filter Lookup" I use, "Never",
Well, the above is results of 2. In fact, form load time is usually VERY
VERY
small, what slows down the forms load is how much data they load. So, I
would
revise the above concept to:

Always open a form using a "where" clause. You always want to
*restrict* the number of records loading into a form. MOST of my
forms do not have navigation buttons. IF you tabke has 10, or 500,000
records, the form load time will be the same if you use a where clause to
restrict the form to the one record.
4. I set my combo boxes under "auto expand" to "no",
Well, a combo box should not have more then 50, to 100 entries anyway. So,
out of convenience for your end users, auto expand is a nice feature.
However,
if you load large lists into combo boxes, then you have a problem anyway.
So, MUCH more important then the auto expand, is to keep the list down to a
small size. Not only is keeping the combo box lists smaller good from
performance, but you can't expect users to scroll though lists longer then a
100 or so without torturing them anyway.....
5. I have discovered that fonts can make a significant difference in
"large" applications.
You better define significant. With a cheap-o machine with a 3300+ processor
rating and 1 gig of ram going for $500, then I don't think there is much to
economy, or EVEN MEASURABLE in terms of form load speed. This is like
cutting grass with a pair of scissors to save some gas in he lawnmower.

I would MOST certainly agree that fonts should be consistent throughout the
application. The windows fonts services will have to render the fonts in
memory, but ONLY has to do this once. This issue of fronts is much more a
good user interface issue then that of even being able to measure
performance.

It your application has 5, or 500 forms, you not going to be able to even
measure this difference in memory by use of fronts.

Changing the fonts used in a form DOES NOT change the memory used in a
significant way - at least for a average form. Unless some very weird font
sizes (huge font sizes) etc. are being used..but, since no one does that,
then again this is NON issue.

The only good point here is to be consistent, and stick to ONE font size for
aesthetic reasons (and, also keeping a lid on the number of fonts used
also does save some memory and eliminates windows having to render the font
on the fly more then once).
For example; if you measure the file size of
folders for each of Window's font types for lightening your
application's size/weight
Well, the fonts are NOT stored in the database. Only the font *NAME* and
FONT *SIZE* are
stored...not the actual font!!!!

If you change the font size in a form, the form does NOT increase in size!!!
The memory requirements at runtime may go up, but that is windows memory,
and not ms-access memory. And, as mentioned above, if you are looking to
save memory..the fonts issues is not going to be significant (this is a wild
goose chase).
6. I created "query files" for using only table fields that are
needed in a particular form.
You will have to expand on the above, as it not clear what the benefits are
in the above?

If you are pulling data from sql server, then the fields used in the query
can reduce the network traffic by a significant amount. In the case of a JET
based back end, since each record is in a disk frame, then savings are far
far less, and most of the time insignificant. Since the jet engine is
running on EACH machine, it can't load ONLY one field from the disk drive,
it HAS TO LOAD THE WHOLE ms-access disk frame. so, there is no "field"
resolution ability in JET for reading data from the disk. The exception to
this rule is of course memo fields, and ole/binary object fields. In some
cases, *some* optimizing can be made by JET to eliminate some frame loads
when you use less fields, but for 99% of things, again this is not
significant (exception is those memo/object fields, and of course sql
server). Of course, it makes no sense from a design point of view to load up
forms reocrdsets will large numbers of fields when you don't need them. So,
sure, using ONLY the fields you need for a form/report is good, but not very
much of a performance or memory issue in most cases.

A. There are three or four groups of linked tables in my database that
are not linked to any other part of the database except for using
lookups i.e., employee's names and locations. In the event that I
upsize my database to SQL at a later time, would it be more beneficial,
for application speed, to take some of these, near self-containing
groups of tables, and place them in multiple groups of back-ends, i.e.,
employee information vs. client private information and their treatment
schedules? Also,
Actually, it would make more sense to include these small lookup tables in
the FRONT END. Since these front ends are INSTALLED ON EACH pc, then NO
network (or sql server) traffic need occur for these combo boxes to load.
>
B. If I were to take my form specific query files (over 200), and
convert them to SQL statements instead of using query files; would that
help in, a) reducing the size of my front-end application, speed up my
application, and ease the process of later upsizing my
application/database?
Well, as mentioned, changing the font size DOES NOT increase the size of the
front end (since only text + font name + font size is stored...not the
actually font data).

The same much applies to sql quires. They are not a large storage issue.
Storing the sql as in-line code (I assume that is the alternative you are
suggesting here in place of stored queries) is really not a huge gain. The
key concept here is ease of maintains. If you can move a query from code
into the query builder..and have ONE copy of that query used in many places
in code, then use the query builder and saved quires. The space used by
these quires is not a large issue for the most part.

I have a application with 160 forms, 30,000 lines of code...and as a mde it
zips onto a single floppy disk. So, front end size should be quite small in
your case anyway.

And, for lots of those queries, you can often eliminate then. For example,
if you load up a form using a "where" clause, then you don't usually need
that many quires in the first place. So, using code to supply parameters to
reports or forms can eliminate large numbers of queries.

The normal approach is to build a un-bound form (a un-bound form is a form
that is NOT attached to a table - these forms are typically designed for
user interface face stuff like prompts, print buttons etc).

The following screen shots are all un-bound forms, and they simply prompt
the user for information.

http://www.members.shaw.ca/AlbertKal.../ridesrpt.html

The above should give you some ideas

So, the solution use now is simply to take the values from the form, and
build your own where clause in code. That way, you simply design the reports
(or forms), and attached them to the query. And, NO FORMS conditions are
placed in the query. And, you don't need a zillion queries.

To "send" the conditions to the report (or form), you simply use the "where"
clause. This is exactly why ms-access has this feature...and it solves a
zillion problems...and will reduce your development costs by a substantial
amount.

The code to make those above screens work and launch the report with the
selected restrictions when you hit the "print" button is easy:
dim strWhere as string

' select sales rep combo

if isnull(cboSalesRep) = false then

strWhere = "SalesRep = '" & cboSalesRep & "'"

end if

' select what City for the report

if isnull(cboCity) = false then
if strWhere <"" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "City = '" & cobCity & "'"
end if

Note how the 2nd combo test is setup. You can add as "many" more conditions
you want. Lets say we have a check box to only include Special Customers. We
can add to our very nice prompt screen a check box to

[x] Show Only Special customers

The code we add would be:

if chkSpeicalOnly = True then
if strWhere <"" then
strWhere = strWhere " and "
endif
strWhere = strWhere & "SpecialCust = true"
endif

For sure, each combo and control we add to the nice report screen takes a
bit of code, but no more messy then the query builder..and this way, each
query is nice and clean, and free of a bunch of HIGHLY un-maintainable
forms! expressions.

Further, it means you can re-use the same query for different reports, and
have no worries about some form that is supposed to be open. So, a tiny bit
more code eliminates the messy query problem.. For me, this is very worth
while trade.

For the most part, computing power is cheap today, and human cost is very
high. So, reducing human cost, and things like maintains tend to take a
greater precedence then performance tuning.

And, for performance, the
#1 issues still is keep the data transferred to the form down to a min.

--
Albert D. Kallal (Access MVP)
Edmonton, Alberta Canada
pl*****************@msn.com
http://www.members.shaw.ca/AlbertKallal


Dec 4 '06 #2

P: n/a
Albert D. Kallal wrote:

<snipped>

LORD POLONIUS

This business is well ended.
My liege, and madam, to expostulate
What majesty should be, what duty is,
Why day is day, night night, and time is time,
Were nothing but to waste night, day and time.
Therefore, since brevity is the soul of wit,
And tediousness the limbs and outward flourishes,
I will be brief: your noble son is mad:
Mad call I it; for, to define true madness,
What is't but to be nothing else but mad?
But let that go.

QUEEN GERTRUDE

More matter, with less art.

Dec 4 '06 #3

This discussion thread is closed

Replies have been disabled for this discussion.