Loading a 150k line <SELECT>

[Tom_Cash]

Hi all,

I am writing some software that requires a select list of over 150,000 products.

It needs to be in a list format to prevent user error.

However, it's loading really slowly.

I've even tried caching it and recalling it as a .txt but it still takes time.

Can anyone offer some advice on speeding this up, or suggest an alternative method?

Here's my code - for reference:

$cachefile = "products-cache.txt";
$cachetime = 30240 * 60; // NEW LOAD EVERY 3 WEEKS
if (file_exists($cachefile) && (time() - $cachetime < filemtime($cachefile))) 
 {
 include($cachefile);
 }
else
 {
 ob_start();

 echo "<select name=\"part\">\n";
 $result = mysql_query("SELECT PartID, PartName FROM parts ORDER BY PartName ASC");
 while($row = mysql_fetch_array($result))
  {
  echo "<option value=\"".$row['PartID']."\">".$row['PartName']."</option>\n";
  }
 echo "</select>\n";

 $fp = fopen($cachefile, 'w');
 fwrite($fp, ob_get_contents());
 fclose($fp);
 ob_end_flush();
 }

Thanks!

Tom.

[penders]

I assume the slow part is downloading the resulting HTML to the client? (So caching server-side probably makes little difference?)

[Tom_Cash]

I guess so, yeah. Wrong forum, you think? I was hoping this could be reduced with some improved SS programing.

[penders]

Well... 150,000 <option> elements. Each <option> taking at least 50 bytes in the source (possibly more depending on encoding) is going to result in an HTML document of at least 7MB (and that's being optimistic).

So I think you need to look at not sending the whole lot to the client in one go? My first thought is an "AJAX search" approach. JavaScript / Server-Side.

[Tom_Cash]

I thought that, but I was hoping there was a simple HTML solution. Ahhh dear...

I guess my common sense knew all along, but I was just hoping that there was something I wasn't aware of yet.

Well - worth asking!

Thanks for your help!

[eelixduppy]

>> 150,000 products

Can these be categorized at all? The user would first select a category (or categories) which would shrink the domain of products you'd have to load into the product drop down.

[brotherhood of LAN]

150K items may be large in bandwidth but you'll probably find it really slows the page down as well, possibly causing people on lower spec systems to have their browser hang/crash.

Also, is it user friendly to have such a large list?

For saving bandwidth, I'd go with the ajax suggestion.

From the user perspective, categories & ajax would help break down the huge list.

Personally I'd use ajax to do real time searching, so the user types in a couple of letters and the matching/suggested products are returned.

At the very least there isn't a need to send <option> and </option>... you can use javascript to build up the elements.

[Tom_Cash]

Thanks for the reply, eelixduppy. I did exactly that. I broke them down into manufacturers which reduced inital load.

The load upon selecting certain manufacturers is still taxing, but only a bit and only sometimes... Even so, it's much better than every page load being stupidly long.

[Tom_Cash]

Sorry for the DP!

Brotherhood, thanks for the reply and informative input!

You're right, it isn't ideal/too user friendly. However, it is an internal system and it works well within the companies tech-spec.

[Dinkar]

Why don't you try paging technique? List 100 options per page. IMO, that will be better. Or try A, B, C, D format to find product name starts with A, B, C, D and so on.

[httpwebwitch]

better to use an autocomplete box with AJAX back-end search

[azn romeo 4u]

cant u create a databse instead of a flat file? calling and sorting a 150k line file is going to destroy any system. mine struggles with 20k lines until I added it to a db instead. mysql can do this faster for u.

[Tom_Cash]

Cheers for the input guys.

Romeo, my code shows that I used an SQL DB. :P