Welcome to WebmasterWorld Guest from 34.236.171.181

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

Loading a 150k line <SELECT>

     
11:09 am on Dec 16, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:June 26, 2008
posts:178
votes: 0


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.
11:23 am on Dec 16, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member penders is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:July 3, 2006
posts: 3153
votes: 7


I assume the slow part is downloading the resulting HTML to the client? (So caching server-side probably makes little difference?)
12:37 pm on Dec 16, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:June 26, 2008
posts:178
votes: 0


I guess so, yeah. Wrong forum, you think? I was hoping this could be reduced with some improved SS programing.
1:27 pm on Dec 16, 2011 (gmt 0)

Senior Member

WebmasterWorld Senior Member penders is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:July 3, 2006
posts: 3153
votes: 7


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.
1:53 pm on Dec 16, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:June 26, 2008
posts:178
votes: 0


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!
3:17 pm on Dec 16, 2011 (gmt 0)

Senior Member

joined:Nov 12, 2005
posts:5967
votes: 0


>> 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.
3:49 pm on Dec 16, 2011 (gmt 0)

Senior Member from GB 

WebmasterWorld Senior Member brotherhood_of_lan is a WebmasterWorld Top Contributor of All Time 10+ Year Member Top Contributors Of The Month

joined:Jan 30, 2002
posts:5046
votes: 60


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.
3:52 pm on Dec 16, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:June 26, 2008
posts:178
votes: 0


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.
3:53 pm on Dec 16, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:June 26, 2008
posts:178
votes: 0


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.
6:10 pm on Dec 16, 2011 (gmt 0)

Full Member

10+ Year Member

joined:July 12, 2002
posts:207
votes: 1


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.
3:10 am on Dec 20, 2011 (gmt 0)

Senior Member from CA 

WebmasterWorld Senior Member httpwebwitch is a WebmasterWorld Top Contributor of All Time 10+ Year Member

joined:Aug 29, 2003
posts:4061
votes: 0


better to use an autocomplete box with AJAX back-end search
6:23 am on Dec 20, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:May 7, 2005
posts:195
votes: 0


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.
8:58 am on Dec 20, 2011 (gmt 0)

Junior Member

10+ Year Member

joined:June 26, 2008
posts:178
votes: 0


Cheers for the input guys.

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