help with Database Drop Down list

sparky21

I have a database generated drop down list that is killing the performance on my web page. The slowness is due to that fact that the list contains over 17,000 companies. So, that is way to big for a drop down. Are there other ways to provide the user with a select list that won't eat so much time up. Is there a way to have a field ( in this case company, auto fill as they type based on what is in the database?) Any ideas would be greatly appeciated. Below is the code. It takes 90 seconds to load the page with it from dial up and 3 seconds without the dropdown. Drop me a note at tom@omg.org

$sql = "select COMP_NAME from COMPANY";

$result = sybase_query($sql);
$num_results = sybase_num_rows($result);
if($num_results < 0){
echo "<option>No Companies in the list</option>";
exit;
}
for($count=0; $row = sybase_fetch_array($result); $count++){
$rows = trim($row["COMP_NAME"]);
echo "<option>$rows</option>";
}
sybase_free_result($result);
sybase_close($connect);

toma42

I had the same problem. I fixed it by breaking the list into groups alphabeticly.

http://db.etree.org/shows_search.php

brandonschnell

17,000 companies * 10 characters for the average company name (est) = 170,000 bytes

170,000 bytes / 1024 = 166KB

166KB / 3KB modem connection = 55 second minimum load time

so by just including the complete list on one page the download will take a minimum of about 60 seconds to download for a modem users. this doesn't count the time it takes for the browser to parse and format the data. the more code you add to simplify the selection process (javascript & images take space) means a longer download for the user.

so you can either split this among several different pages (user waits for refresh), or make it easy to choose a company from one screen (users will have to live with a potentially several minute load time). ie select the first letter of the company then choose from all companies beginning with the letter "F". or users download a javascript/java "wizard" that offers auto-complete features.

compression might help to a limited degree. look at enabling gzip on the web server.

pinkbike

I would assume that the database access time to get the data is negligable and that this is slow simply due to the amount of data you are trying to load.

Once php is done generating the page it's simply a file sil ALL that HTML test one it. THis is a large amount of data that just takes a long time to get over a slow connection.

Here are some ideas..
1. Make the user first select an alphabet letter and then take them to a page with just a drop box of companies starting with that letter. This will minimize the load time by 26. (Assuming same amount of companies starting with each letter)
2. Place a search box where the user types in the beginning or sub text of the company and the result page shows a drop box only matching those companies.

sparky21

Great Idea. Can you point me to any resources that show how to do this kind of thing.. Documents, Articles etc.. If you have a code sample that you don't mind sharing that would be helpfull. If not, I understand.

-Tom

sparky21

This is a very helpful board. Thanks for all the ideas. It seams the alphabetical listing is the way to go. Does anyone have an example of how to impliment this or where I can find out more about it. Also, I would like to find more information on auto-fillin, if anyone has resourses please let me know.

Thanks again all.

toma42

Here's some code. There's plenty of other code in that /source/ directory too.

http://db.etree.org/source/include/artist_select.phps