Performance Issue with Database Connection

C

Ctal

I have an app that runs several queries depending on user input. The data
source is an Access database that's right at the 2GB limit. I stored
numerous queries in the database to cover all the possible user selections
from the Excel app. I'd read that stored queries are a bit faster than just
sending the SQL script. Database resides on a LAN

Average time on these varies from 2 1/2 mins to 5 mins. From listening to
users (especially my boss) we need to improve this time.

I've tried every way I could think of to run this and nothing shortens the
time. 99.5% of the wait is while opening the recordsets.

My question:

Due to the size of this db (and its only one table but with 1.5M records)
would moving this to a SQL table and querying off of that speed this up? I
do some other stuff against our main SQL tables and they are painfully slow,
but that's with lots of joins and far more records. This would just be the
one table.
 
G

Gys de Jongh

Hi,
I think you mix up with stored procedures. They are a lot faster but can only be
handeled by MSSQL. You can upsize MSAccess to MSDE which is a "light" MSSQl with
the upsize wizzard in MSAccess. Than people can connect to the server that runs
(the there stored procedures) to get the data they want to their own PC's.The
size limit for MSAccess and MSDE is 2GB. But that is for each table.So you can
have more tables as long as each is less than 2GB. If you use linked tables than
there is no size limit , because Access just stores the pointers. If you have
more than a few users than going from Access (JET) to MSSQL will again speedup
things considerably. If you want an experts view than post at :
microsoft.public.vb.database.ado

hth
Gys
 
R

RB Smissaert

Your queries maybe inefficient or there
maybe something wrong with the VBA code.
If you post the code and queries you might get
a suggestion how to improve the speed.

RBS
 
J

Jamie Collins

...
Your queries maybe inefficient or there
maybe something wrong with the VBA code.
If you post the code and queries you might get
a suggestion how to improve the speed.
Click to expand...

And post the table schema i.e. it may be flaw in the design.

With a Jet db on a network, you want to reduce the number of data
pages that get sent, so you will have to pay some regard to physical
storage. If you have an integer primary key (e.g. an IDENTITY
autnumber), read on...

The clustered index determines the physical order of the table i.e.
how it is stored on disk (the rows' physical order is the same as
their logical order). Therefore, a clustered index is particularly
efficient on columns that are often searched for ranges of values e.g.
an application that frequently executes a query to retrieve records
between a range of dates. You only get one clustered index per table
so choosing it wisely is one of the biggest performance boosts you can
give to your application.

The obvious analogy is a telephone directory, which is physically
ordered by surname and first names respectively. This is its clustered
index, it couldn't possibly have another clustered index (physical
order).

With Jet, the primary key is always the clustered index, you have no
choice in the matter. One of the strongest arguments against an
IDENTITY/autonumber PK (or a manually-increasing integer, for that
matter) in Jet is that a unique monotonic integer makes a poor choice
for a clustered index e.g. how many times are you going to want to
search a range of telephone numbers?

You can get round Jet's limitation by making the PK a composite of a
more meaningful column and the integer column *in that order*. Note
that with Jet the a table is only physically reordered on the
clustered index when the database file is compacted; while a compact
is pending, any new rows will physically appended to the table i.e. be
in logical but not physical order.

So to rectify, DROP the existing PK, define a new composite primary
key with the first column being your most often used index column and,
if you truly really need it for uniqueness, your integer column last.
Then ensure the clustered index is being regularly rebuilt.

Jamie.

--
 
Top