Huge Database

[telexpress]

I have a database that has over 340,000 entries and I have interfaced it with an asp page. It takes about 30-45 seconds to load and I was wondering if there was any way to speed this process up?

 

[DanJR]

Hi there,

Compact and repair your db then re-assess the tables indices. Index any fields that you join on, use as lookup, or use a criteria in sql.

How many records does your asp page return? What does the sql statement look like?

Cheers,
Dan

 

[jimmythegeek]

Besides the good points Dan makes, you need to evaluate the backend you are using. When you start getting into the hundreds of thousands of records, no matter what you do, Access' performance will suffer, espically if you are doing joins on that table. Access is a great small to intermediate tool, but it certainly has it's limits. I have actually seen databases with over a million records, but it takes forever to do anything in them.

You may want to look at some other options, ESPECIALLY if you database is going to grow any further. SQL Server is great solution in that is not overly costly, and can handle millions of records with no problems at all.

If this is not an option, then try what Dan suggests, there is not alot you can do when you get that many records in Access aside from indexing and possibly tweaking your SQL statements.

You may have to change what or how you display your data on the .asp page. Maybe show x number of records at a time or change the filter Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[telexpress]

I have Pervasive SQL Server, but I am not exactly sure how to interface that with the web.

 

[jimmythegeek]

I have never used Pervasive, so I can't speak to that, here is a link on installing, don't know if it will help.

http://www.dataaccess.com/whitepapers/PervasiveWebApp.htm

Jim Lunde
compugeeks@hotmail.com
We all agree your theory is crazy, but is it crazy enough?

 

[cmmrfrds]

Since you are using ASP and ADO, you must have set up a connection string to the Access MDB. Provided your tables and fields have the same names in Pervasive changing the connection string should be all you need to do on the connection side. If Pervasive is installed on the IIS Server PC then there should be an OLE-DB Provider for Pervasive on the PC.

Go on the server pc and use the udl wizard to set up the connection string.

Here is how you invoke the udl wizard. Do this on your desktop.

1. create a blank notepad file.
2. save the file.
3. rename the file by adding a new extention of .udl
make sure you save as all files not txt or some other
file type.
4. the icon for the file should change from notepad to a
little computer - it has now become the wizard.
5. double click on the file and it will bring up a dialog
box with multipule tabs.
6. use the Pervasive provider.
7. look under the ALL tab and there will be parameter settings which you can change if necessary. There is a test
button, press to test the connection.
8. close the file.
9. open the file from notepad instead of double clicking the icon. You will see the connection string which you can copy and paste into your program.