Tablesize and speed

[Filips Benoit]

Dear All,

I would like your oppinion on this

I have build a access-ADP that is running well for 3 years.
As tables grow in size some actions are slow.
In the code i used ADO, some command.executes but no stored procedures (
never done!)

a. tablesize (1/9/2003)
1. ORDER 37.214
2. ORDER_PHASE 353.198
3. ORDER_PROPERTY 162.667
4. PRODUCT 5.978
5. PRODUCT_PHASE 24.297
6. PRODUCT_PROPERTY 64.540
b. All actions using this tables became slow. Copying an order uses
table 1,2 en 3, so it's very slow.


Solutions.

a. Change the code > stored procedures.
Problem1: I don't know it yet. So I 'll have to give it to another
person !!
Problem2: Even this solution 'll have limits on size, I suppose!

b. Copy the DB as an archive and delete old data (orders and products)
in the running DB.
Orderdelting can by coded. Productdeleting will always be manual.

thanks,

Filip

 

[Mary Chipman]

Using ADO is always going to be the slowest way of doing anything
because you're adding another API, and thus another layer. Re-write
your actions as stored procedures or direct SQL statements.
Alternately, archive old data into another database. You can always
join together tables from the live and the archive database for
reports, etc.

-- Mary
Microsoft Access Developer's Guide to SQL Server
http://www.amazon.com/exec/obidos/ASIN/0672319446

 

[Sal]

Thnis may not be an issue of how many records you have in
the database. It looks like a performance issue in the
database server. Let me ask you this;
1) are you backing up regularly (no backups make SQL
server perform slow due to large log file)
2) are you running something else on the server that is
slowing you down?
3) have you indexed your database correctly?

You have a performance issue in your server, it may be on
the database, but unless you have a lot of users all
hitting the server at the same time, I do not see why you
would need to archive old data.

Stored procedures are not hard to learn by the way.


Sal