Welcome to WebmasterWorld Guest from 220.127.116.11
Forum Moderators: ocean10000
I have 12 recordsets that returning information from approx 4000 records.
When I checked approx 3 weeks ago everything was fine (infromation retreived 'quickly') I estimate that there must have been 3200 records. However now the page takes 2 mintues to load - Can that be right? am I going mad?
Could someone please tell me if there is something wrong or I should expect that sort of timelag
Not my sites btw.., but I was wondering if that is being caused by an unclosed database connection or perhaps something else?
I use access databases on my site. They also fly on my local test bed system and slow dramatically when uploaded to the live site.
The principal reason for this is that I am using shared hosting - more than one website is hosted on the same pc. This slows down the response times of the sites, particularly in database access.
If you use a dedicated server that you might try compacting the database.
You can use the connection object to execute the query - its faster than explicit recordsets. Also use the getRows() or getString() methods for faster display of large data sets.
Also look at buffering the HTML output better, with response.flush() and using the Response.isClientConnected() method to ensure that if the user stops the page loading, the script stops executing.
As aspdad says, using getRows or getString can greatly speed up your code. I'm not sure how good DW-generated code is, but you could also check that it is only opening the connection once, rather than for each recordset.
When Access starts having to deal with >1 user you start heading for problems. SQL Server is a better option for scaleability. HTH.
Here is a typical query:
SELECT COUNT(site1.`CAT Ratio`) AS CAT1Fail
WHERE (site1.monthentry = MMColParam) AND (site1.CAT = '1') AND ((site1.reason LIKE 'Insufficient Time To Complete') OR (site1.reason = 'Fail'))
Not that taxing the first thing I do is to filter only the month I need which cuts out 75% of the records to look at only 800 records, how else could I optimse this query?
aspdaddy said to use 'connection object to execute the query' how does that work?