The SQL BE is located on the WAN but so was the Ac BE. We have more than
sufficient bandwidth.
Usually WANS don't have enough performance for a file share back end. If you
WAN did perform well, then moving to sql server for the back end will
perform even BETTER **if**you have good designs.
I am VERY Surprise that you're able to get adequate performance over a WAN
with
a file share back end.
direct posts) and:
1. changed all Control Sources to queries (rather than tables)
The above a general rule doesn't make any difference. In fact, often I find
changing the source of a combo box to a query from a table actually makes it
runs slower (by quite a bit in fact!!). So if you have a linked table or
view
on the SQL server side, then for the data source of the combo box don't use
any SQL at all, but just put in linked table name or link view
name. This runs BETTER then using a query in the front end built with
query builder. The reason why this works better as you reduce one layer of
complexitry here. With a query you load sql, process sql, link to sql server
and then data comes down. If you just place the name of the linked table or
(or linked view) in the combo box, you'll find it runs a lot more snappy.
This is not a huge deal one way or the other, but just simply out of the
blue to changing control sources to queries will not necessarily speed up
your application at all. And, often in this case, you find it runs slower
then just using the table or view name as the source without any sql at
all.
2. removed DLookup from several of the queries, replacing it with a right
join table criteria (without this change many of the queries took 5
minutes
Excellent! We been complaining about people using Dlookups here for years.
In fact you can use dlookup() to get values from sql server, and you can
even
place/use a dlookup() as the source for a contorl on a form.
However, using dlookups() in a query is as slow as a turtle. It will kill
your performance. In fact dlookup() is slow EVEN when you do this in
ms-access without sql server. So, it just a bad choice. However, when you
not running a multi user system, often the performance is adequate, so
people
use it (so at the end of the day, it's not like this is something evil thing
here, it's just a performance hit that you can often take when you're
building
a desktop application that is not networked).
What people don't often realize is that MS access is very forgiving for poor
designs. Often we do a lot of things that experienced developers would NEVER
attempt to do it all. For this reason MS access often gets a bad reputation
as being a poor performing product. In fact it's not the product, it the
way we often do things. To be fair to MS access makes it very easy to do
things the wrong way. In other development platforms often developers don't
get in trouble because they don't have that kind of flexibility that we have
an access to do things the wrong way. Advanced software developers call this
concept social engineering thus they desing their software in such a way
that
users can not do stupid things. So, we might take away the clutch in a car
and give everyone automatic transmissions - presto no more burnt out
clutches anymore!
3. replaced several queries with stored procedures
Again replacing a query with a store procedure won't necessarily help.
However using views with aggruate functions, groping by, or
actually doing summary type totals in which many records are involved in
the totals, but the amount of data (records) returned back is very small,
then
significant savings can be had by using a view or stored procedure. I
useally prefer views, because write t-sql code on sql in a procedure lanuge
in in what amounts to a simple sql select makes no sense at all. use views
fot his. We don't write vba + sql code for a simple select, we biult a
query in the quqery biulder. The same goes for sql server, most cases
views are a better choice.
If you have some processing routines that take advantage of parameters and
need to update data, then by all means do use procedural code on SQL server
side as this can result in big gains in performance. (data is updated on
the server...and never even travles down the wire to your local machine).
When I run the database analyzer, two types of issues are found:
1. It "Recommends" Indexes for Foreign Keys of some of the tables.
However, I
have verified through SQL Manager Studio that the indexes do exist on all
the
foreigns keys.
I don't think the database analyzers has ever given anybody anywhere on the
planet earth a useful piece of advice in the last fifteen years. If you been
using the database analyzer for years and years and it's been giving you
great advice, then all the power to you. Maybe you can tell me some stores
and experiences you've had with using the analyzer over the lastmany years.
I can't think of when it's helped me, and furthermore it really does not
apply to sql server anyway.
The main form has 7 subforms but performance did not improve by deleting
all
subforms and leaving just 4 bound controls on the form.
Right, but I bet removing all the sub forms will dramatically increase the
performance. Again, loading firstname, and then loading first name +
lastname into two contorls might talk half the time, but that time is 1/2 of
a 10,000 of a second. So, you save 1/5000th of a second. That double the
speed, but that not yoru bottle nect anyway. So, again the advice and theory
sounds really good to remove a few controls, but in practice that's not the
bottleneck is it? So, sure you save time by remvign contorls. So, 1 contorl
is 10 times faster then 10 conrorls, but the whole process in total is only
1/100th of a second, so you not going to really save anything here.
I appreciate any suggestions to speed up the performance of this app.
The solution here is simply to reduce your bandwidth requirements.` I once
asked to 80 year old grandmother if it makes sense for an instant teller
machine to download everyone's account into the instant teller machine, and
THEN ask the person what account they want to work on. Why download huge
amounts of records into a form, and then ask the person what record to
work on? If a 80 year old grandmother can tell me this common sense, then
perhaps we should get rid of more developers and start hiring old
grandmothers to do our designs!! ;-)
It makes far more sense to ask the person what customer account number, or
whatever to work on, and then load up the form to the one record. Simply
throwing up a form bound to a large table is a formula for disaster and poor
performance.
As you've seen, even without SQL server, MS access can perform quite well,
with SQL server you can get even more performance. However the trick here
again is not some technology, but simply having patience and taking the time
to build designs in which you limit the amount of information that's
transferred over the wire into your forms.
In the case of having 3-5 sub forms I would place some of those
sub forms behind tabs. Then, you can have those sub forms load
ONLY when you actually click on that tab. Furthrmoe, those sub
forms should then not take long to load since they are loading
ONLY reocrds related to the main form.
I have a series of screen shots and I talk about reducing as bandwidth by
building screens that prompt the user for what they want in the following
little article of mine:
http://www.members.shaw.ca/AlbertKallal/Search/index.html
Here is few more tips:
As mentioned watch your combo boxes, a combo box is good for maybe about
100 records, after that you're simply being rude to your users and forcing
them to scroll through hundreds of records is just torture. So you are not
only torturing your users, but also dragging hundreds of records across the
network. If the combo boxes is for some kind of customer name selection,
then place a button on the form, launch another form that allows them to
search for the customer. They then select the customer, and then return back
to the form. A typical form can maybe handle one or two combo boxes
linked to sql server. After that, your form load times will start to go
increase too much.
another combo box solution is if the list of options is
static, then place the table list of options into the front end of the
application, not on the sql server side. This approach is not
always possible for information that changes and is updated all the time,
but it is possible for quite a few number of lists that are static in
nature.
Also as I mentioned don't use a query for the source of a combo box, use
the direct name of the view or linked SQL server table. So, no sql at
all in the combo source, JUST the name of the table view. You will find
they load far more snappy when you do this.
As mentioned, as a general rule, you don't want to allow any type of
navigation in your main forms. That means you can remove the navigation
options on the bottom of the screen and reduce further clutter. By the
way this advice applies even when you're not doing SQL server. I mean if you
ask a person to load one record into a form, even without SQL server, only
the one record gets loaded into the form (access will only pull the one
record down the network write -- how can that be slow?) This approach
means your form will load as fast when the table has ten records, or 500,000
records.
As mentioned, for sub forms, place them behind tabs on your form, and don't
load the sub form at all until the person clicks on the tab.
