Forum Moderators: open
The back end is currently Access through ODBC. I hope to migrate to mySQL soon.
Here is the difficulty. The plain pages (html, asp w/o extensive db) load very quickly. Very pleased with the speed. Certain pages, especially those involving student lists are brutally slow to load. Tonight I helped a client on their site in a school with a T1 line try to use my app. I was totally embarassed and appalled at how slow some of the pages were to load. It seemed to me that the pages that involved sifting through the student table were the slowest ones. The queries are basically very simple select statements.
Currently the basic student information is divided into three tables: names, detail, parents the student ID and the schoolcode are the unique keys to each table. There are about 5000 unique records in each table. (That could grow to 50,000 in a short time!)
Sometimes, when I notice the app running slowly from the server itself, I refresh the IIS service which seems to help, but I run the risk of knocking off anyone online at the moment.
Here are my questions:
1) Do my queries need optimizing?
2) Would a switch to mySQL help?
3) Should I try a DSN-less connection to Access?
4) Is there something I need to tweak in IIS or the server?
5) Is someone with more expertise willing to take a look?
6) Right now the server is running with 320 MB RAM. My first guess would be to up the RAM. Would that help significantly?
Thanks all.
Simple questions...
Is it taking too long to retrieve the result-set? If so the problem lies in actually retrieving the records rather than handling them.
Is it taking too long to cycle through the records / write them to screen? If so you might want to consider writing a batch, sending it to screen, sending next batch etc.
Is it taking too long between finishing writing your data and finishing writing the page? If so the problem is that something else is causing the delay.
Other ideas...
1) Get the source of a slow loading db-heavy page (when it eventually loads) and save it under another name - Try loading this page. You might find that the delay is a rendering issue in the browser rather than a bottleneck.
2) Have you checked your tables are linked and indexed according to the database design? If not then that is certainly a likely candidate for improving response time.
Access will easily scale to 50,000 (50k) records per table IMO, at a push it will still work up to 500k records - a good working size of table is normally ~50k records with basic data in a well designed db).
IMO you have the right idea in looking at moving to something other than Access - using that as your primary database is a little like getting a *really* cheap first car - it's great to have the features it offers but when you find yourself becoming too reliant you need to think about upgrading to something more sensible.
- Tony
Some more tips/things to try.
- use getRows() or better still getString()
- Experiment with Response.Flush e.g every 100 records
- dont ever use select *
- Use index on the fields in the where clause
- Yes - use dsn less connection - never use odbc!
- If you upsize to a real rdbms use a stored proc for this query
- dont use & (concatenate) in the response.writes - better performance to use separate lines
I am guessing here but when I was doing ASP stuff rather than doing the whole SQL thing I made some ready made queries in Access. Then I call the query name and then filter using an ID. I found this to be quick.
It may be relevent, maybe wrong. :) interested to find out why though if you do figure it out.
Cheers
This is the fastest way to retrive ReadOnly data.
I think the major problem was RAM, and the division of resources between serving and other programs. I found that after a session of Fireworks, Quickbooks, Quicken, DW, Access and the browser, that the program ran rather slowly.
So another 512 MB of RAM is going in tomorrow.
I'll also be playing with getting the data converted over to mySQL and testing that on another box until it's ready to roll.
some good suggestions here, some I had not thought of. I assume you have checked the ususal culprits like closing connections and cleaning up objects after the information has been retrieved ... <ducks> ... yes, I thought you were.
I like to use the database to filter the information internally as it is much quicker, as ukgimp also suggests, so I would have a good look at this if I were you. I have a database with 20,000+ records at the moment which seems to run smoothly and quickly so it can be done.
One other thing I am playing with at the moment is preformatting an extra field(s) for each record with the results as you want them displayed, including all the html stuff. The preformatting would be done as you add/edit a record, and/or you could write a routine to manually re-format all records periodically.
This would increase database size somewhat but if you are running your own system, or have a generous HD allottment with your host, this is less of a problem. The trade off however is you are then only calling one field when retrieving data rather than formatting on the fly with the resultant speed increase.
I might add that this would only work if you have a preset way of displaying the data which doesn't change. But even then you could add/preformat a few fields for basic queries and then format on the fly for more detailed queries. Maybe something worth trying.
Onya
Woz
