Re: Performance Issue with Database Connection
From: Gys de Jongh (Gys_nospam_deJongh_at_planet.nl)
Date: 08/13/04
- Next message: Tushar Mehta: "Re: Speed - or lack of it"
- Previous message: Tom Ogilvy: "Re: Excel macro to extract information from MS project files"
- In reply to: Ctal: "Performance Issue with Database Connection"
- Next in thread: RB Smissaert: "Re: Performance Issue with Database Connection"
- Messages sorted by: [ date ] [ thread ]
Date: Fri, 13 Aug 2004 14:08:47 +0200
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
"Ctal" <notme@nothere.com> wrote in message
news:eq_Sc.192518$tH1.9669985@twister.southeast.rr.com...
> 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.
>
>
- Next message: Tushar Mehta: "Re: Speed - or lack of it"
- Previous message: Tom Ogilvy: "Re: Excel macro to extract information from MS project files"
- In reply to: Ctal: "Performance Issue with Database Connection"
- Next in thread: RB Smissaert: "Re: Performance Issue with Database Connection"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
