Archiving old data to improve performance

[julia77a]

Helo everyone,
I have an Access front end application with back end on MS SQL 2000
server. Some of the tables are local, but most of them are linked
tables using ODBC connection (File DSN). The database works decent for
us, but recently our European team started using the database (they are
on the same network and have access to it) and its performance was
really slow, so slow that it was painful to use it. So far I have made
some changes to the database (list at the end of this message) which
did improve the performance, but still it is far from being what we
hope for. So, the next thing I'm trying to do is to archive some of
the old records hoping that it will help with the searches and the
performance in general. The tables are not that big (100K records tops)
but I think this is worth trying to see if it helps with the speed (or
lock of it). I'm hopping to either create an archive database on the
SQL server and connect to it as needed, or even move the data to local
tables and store it zipped along with the front end (forms and queries)
on a share (that's what my boss wants, but I'm not certain if
that's a good idea). What are your thoughts on this? And more
importantly how do I do it? I've never done that before, so I don't
even know where to start. I think it would be best if I had some script
to do it, but my programming is very basic and I would need some help
with that. Any help is appreciated.
Thank you,
Aneta

List of the things that I did to improve the performance:
Forms
- Redesigned Search page by dividing it up into Basic Search and
Advanced Search. Basic Search returns basic information but runs quick,
and Advances Search returns more detailed information but takes longer
to complete.
- Redesigned complex forms removing unnecessary and unused controls and
simplifying the design
- Used page tabs in company details form, which only load when tab gets
clicked.
- Loading most used forms in invisible mode on startup and hiding and
showing them as opposed to closing and opening them. This significantly
improves the opening time of forms.

Indexes
Checked table indexes and discovered that there were duplicate indexes
created on tables and that some indexes were unnecessary. Dropped all
indexes and re-indexed the tables, making sure that each table has one
clustered index, no indexes are duplicated, and only necessary indexes
have been created.

Views
Created some views on the SQL server to speed up some Access queries

 

[Albert D. Kallal]

If you have a table on sql server, and there is 10 reocrds.

If you load up a form with a 'where' clause to open up that one reocrd, then
lets now assume that the table has 10 million roecrds.

If you load up that form to one reocrd...the performance will BE THE SAVE AS
IF YOU HAD ONLY 10 reocrds.

So, the #1 critial concpet here is to reduce the number of reocfds a form
laods to.

You also mentoend that your tables aer small...only in the 100,000 rocfrd
range.

Even without sql server, a talbe of 150,000 reocord is VERY small.

You mentioned a seach form. that seach form should be near instanclry in
terms of perahpse.
As along as you avoicd using findfirst in yoru reocrdsets, then 100, or 1
millon recrds should
perform IDETNICAL...adn be instanct.

Here is some ideas for a serach screen:

A few things:

having a table with 100k records is quite small. Lets assume you have 12
users. With a just a 100% file base system (jet), then the performance of
that system should really have screamed.

I have some applications out there with 50, or 60 HIGHLY related tables.
With 5 to 10 users on a network, response time is instant. I don't think any
form load takes more then one second. Many of those 60+ tables are highly
relational..and in the 50 to 175k records range.

I mean, when using a JET file share, you grab a invoice from the 75k record
table..only the one record is transferred down the network with a file share
(and, sql server will also only transfer one record).

Here is a few tips:

** What kind of network are you using. If you are trying to use a WAN in
place of a LAN..then you REALLY need to read the following:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

Here is a few more tips:

** Ask the user what they need before you load a form!

The above is so simple, but so often I see the above concept ignored.
For example, when you walk up to a instant teller machine, does it
download every account number and THEN ASK YOU what you want to do? In
access, it is downright silly to open up form attached to a table WITHOUT
FIRST asking the user what they want! So, if it is a customer invoice, get
the invoice number, and then load up the form with the ONE record (how can
one record be slow!). When done editing the record...the form is closed, and
you are back to the prompt ready to do battle with the next customer. You
can read up on how this "flow" of a good user interface works here (and this
applies to both JET, or sql server appcltions):

http://www.attcanada.net/~kallal.msn/Search/index.html

I actually asked a 90 year old grandmother if it was dumb to download
everyone's account into a teller machine, and THEN start searching. If
a 90 old grandmother can figure out this concept, then as developer
this should be crystal clear.

the same goes for reocrdsets. Don't load up a huge reocrdset, and
then seach, or filter. Simply load up the reocrdset with ONLY the
reocrds you need.

My only point here is restrict the form to only the ONE record the user
needs. Of course, sub-forms, and details records don't apply to this rule,
but I am always dismayed how often a developer builds a nice form, attaches
it to a large table, and then opens it..and the throws this form attached to
some huge table..and then tells the users to go have at and have fun. Don't
we have any kind of concern for those poor users? Often, the user will not
even know how to search for something ! (so, prompt, and asking the user
also makes a HUGE leap forward in usability. And, the big bonus is reduced
network traffic too!...Gosh...better and faster, and less network
traffic....what more do we want!).

** You can continue to use bound forms..but as mentioned..restrict the form
to the one record you need. You can safely open up to a single invoice, and
even continue to use the "where" clause of the openform. Bound forms are way
less work then un-bound forms...and performance is generally just is good
anyway when done right.

 

[Aneta]

Thank you so much for your valuable input.
Yes, we do use LAN.
As far as the forms, all of my forms display one record at a time,
except for the search form which may display multiple results. It's
not so much that the performance is bad for us (by us I mean a group
of roughly 20 users in our US office). The database works fine for us
and we can live with it (except for some complex quires which may take
a several minutes to run), but it is mostly a problem to our
colleagues in Europe who are trying to use the same database and are
experiencing real delays. We timed the performance of the db on their
side. We found that some forms which open in less than one SECOND
(virtually instantaneous) for us would take about a MINUTE for them to
open. The search for instance, may take us 3 seconds to complete, and
40 seconds for them. That is really bad, and I don't blame them that
they don't want to use the database if they have too put up with such
sluggish performance. And my boss demands that the database works
better, but I don't know what else to do to make it run faster for
them. They are using the same network as we do (not VPN, which I do
sometimes from home), so I assume there should not be so much delay.
Any other ideas would be greatly appreciated.

 

[Albert D. Kallal]

Yes, we do use LAN.

Or, do you use a wan????

Actually, you are on a WAN. I would have to think that the term Wide area
netowrk apples to
those overseas people.

So, the commets about the WAN applly to your netowrk.

So, once again...how fast, or slow is that network?

Obivlisty their network is slow. Read that article again:

http://www.members.shaw.ca/AlbertKallal//Wan/Wans.html

The best solution I outline in the above is to use a thin client, and that
works well over a limited network...even one that is 100 times slower.

So, you are on a WAN...wide area network..and it is slow.

So, you have to either optimize your sql stuff even further, to
reduce data transferred, or consider
using thin client technology (terminal services).

And get an answer has to how much slower that network is then your cheap-o
office network
(as I said...it likely 50 to 100 times slower).

 

[Aneta]

Thank you for your help... Per your advice I requested some
information about our network and following is the answer that I got
from our network guy:
"Yes, we connect to Amsterdam via the WAN (Wide Area Network). The
link speed is not the issue (the link is not saturated) the issue is
latency which is a function of distance. It takes at a minimum 115
milliseconds for a round trip between Detroit and Amsterdam, whereas
between computers within headquarters facility it takes only one to
two milliseconds.

One must then look at the conversations that occur between the client
and the server to complete a transaction. The sum of those
conversations plus the latency for each make the perceived slow
response." So I guess you were right, comparing the two WAN seems to
be about 100 times slower! No wonder the delay.
I read the article that you recomended and it did clear up some things
for me. I'm not very technical when it comes to things like that, so
please be patient with me. I have a question though about the thin
client: Do you think it would work for my situation? And if so how do
I go about setting one up? Maybe I should just set it up for the
remote office to use it and we should continue using the database as
we're used to? We all work from a local (not shared) copy of the .mdb
file (front end) and use the SQL server located in our local office as
a back end. I have a simple website where I post new versions of the
database and when there is one, everyone goes to that web page, clicks
on a button which runs an update batch file replacing the old .mdb
file with the new one.
We have looked into upgrading to a web application, but I don't know
much about .net and I don't think my boss even wants me to do it. But
with limited recourses we can't really afford getting someone to do
this for us. So, we're stuck. But at t he same time they want to see
some improvement in speed and they want to see it NOW. If what you
said is right, I don't think archiving some 20 thousand records will
make a big difference, now will it.So, what am I to do? Please help if
you can some more.
Thank you for all your great help and valueble input.

Aneta

 

[Albert D. Kallal]

"Yes, we connect to Amsterdam via the WAN (Wide Area Network). The

link speed is not the issue

Really? Why not get a simple answer as to the speed, and then decide for
your self?

(the link is not saturated)

It don't matter right now. I want a speed number. A simple number...

Anyway, you can see my response to your other post. It is true
that latency is a important issue..but, the data transfer speed is
still a issue here.....

latency which is a function of distance. It takes at a minimum 115
milliseconds for a round trip between Detroit

Right...so, we are to complain about a 1/10 of a second delay...I think
not!!!! (there is more to this issue..but, the 115 milliseconds = 0.115
seconds. Hardly a intolerable delay).....

Get that speed number (but, really....the number don't matter a whole lot,
since we likely to use TS..then it not a big deal).

....anyway..read my other response...