Access2000-ADO 2.5 - Jet4 Slow Retrieval - need urgent help - Demo

[bzac]

I am using Access 2000-ADO 205-Jet4 combination for my data retrieval from a table of nearly 240800 records and the table size will be nearly 30341300 total. I am using a database with only one table due to the size limit and other transactions are with other database. Running this under a Win2000 platform.

This is a table which using to store data for each customer for each Hour for the whole Year. Primary key is a combination 5 fields.

Here the problem is when I try to plot the daily values in an MSHFlex Grid, by taking average value of 24 hrs., for a period of 8 days for a set of customers, the process is taking nearly 15 minutes.

Here I can't use "adCmdTableDirect" in the ADO connection string as I need to specify some condition through SQL. Hence using "adCmdText".

Here rst.index is not working(showing error like provider not supported).

How can I make the process faster?
How can i use CacheSize property? Any body please help me with some Code sample.

Any body please help me, very urgent, about to show a Demo next day.

 

[grampalew]

When you use an SQL statement, VB or Access must examine EVERY record in the database to check if it meets the criteria. On a large database, this is time consuming. Try copying records, using your indexes, to a "temporary" database and then performing your SQL query on that database.

Hope this helps.

lew@sstar.com

 

[bzac]

Thank you grampalew, I will try it let you know.

 

[bzac]

Can anybody suggest me to the CacheSize property of ADO to display records of a large database.

 

[wekkew]

Hello

I'm so glad I was sitting down when I read your message.

ONE TABLE - eek..

You desperately need to normalise your database - it will never perform efficiently as it is because the underlying structure is inefficient. I'm amazed it's only taking 15 mins - what's your HW?

An excellent book which may help you is Accessible Access 2000 by Mark Whitehorn & Bill Marklyn (about 24 pounds) which will explain the *theory* behind databases - and that involves having more than one table and a composite key of FIVE fields is quite frightening (especially to a DB engine!)

best of luck
Kate

 

[JerryDennison]

I must agree with Kate. A compound pk of 5 fields makes me believe the db is not normalized properly either. Performance is vastly improved when you have many tables with few fields over few tables with many fields.

 

[bzac]

Thank you very much for your suggestions. Is it like Acces cannot handle 8 fields and nearly 300000 Records?. If I am migrating from Access to SQL Server with the same tables, is there any advantage?

Why in Access thru ADO doesn't recognize records with a date field in GeneralDate format?.