web page too slow loading using multiple tables

[samauto]

I am creating a web page that is pulling data from multiple tables through CFQUERY to be used in a drop down list for a form. The size of the tables is close to 10000 entries and is causing the web page to load really slowly. Is there any way to speed the load time.

Thanks

 

[jalbao]

Can you post an example of your code - the way that you have phrased your question is a little vague. Also, try to narrow down which part of the code is slowing down the process: you can do this by using the getTickCount() function.

 

[Sarky78]

I hope that your not trying to put 10,000 records into the drop down list!

There are a few things that you could do. You could cache the query (if its not going to change that much) and save having to run off to the database all the time for the information. You do this by adding the CacheWithin Attribute to the end of the CFQUERY tag, like this:

<CFquery.... cacheWithin=&quot;#CreateTimeSpan(1,0,0,0)#&quot;>

The above will cache the query for one day, CreateTimeSpan(Days,Hours, Minutes,Seconds)

What database are you using?

If you are using something like Access then think about moving to something more powerful, and capable of such data volume like SQL Server, of if thats not an option a Open Source database such as MySQL.

If you want to post your query up on here, I am sure someone might be able to offer some advice on how it might be optimised

Hope this helps!

Tony

 

[SidianConsulting]

There are a couple of solutions:

1. Use a next/previous interface to sort through the data. This would give you a set number of records displayed per page with next/previous links and possible numbers or letters that are links to shoot right to a certain page of data.

2. Use CFFLUSH to output the data (doesn't work with tables unless they are fully rendered, so you could output a table for every 10 records or so, and then use cfflush).

Making use of caching in either case is imperative, unless you are caching different queries based on user input. That might take a lot of memory depending on the load of your site.

One thing to note is, rendering a lot of data in a browser, in tables no less, is going to slow down the browser no matter how fast your queries execute, even if they are cached.

-Tek