Do you have a question? Post it now! No Registration Necessary. Now with pictures!
- Posted on
- Vince Morgan
April 27, 2007, 1:39 am
rate this thread
I'm working on redesigning an ecommerce site written by someone else in the
Currently a user can choose a product group and select items from a returned
list. I noticed that on many of these groups the number of items to choose
from topped out at 200, when in fact the actual number of those items could
be as many as 5000.
Perfectly adequate if you only ever needed items in the lower range of those
beginning with "a".
When I asked the owner of the site why this was so, he said the original
develloper did this to speed things up because it was too slow.
Fortunately, a customer can access the products they may actualy want via
Finding this was easy, but fixing it has left me scratching my head
Acutaly, I _hope_ that's why I'm scratching my head ; @@
Currently the query selects the top 200 records sorted on the item names.
Before I dive headlong into this I would be very gratefull to see the
thoughts of others who know of workable solutions.
At some point user interface considerations can't be papered over with
hardware improvements. Even if you have a large monitor, you shouldn't
try to put 5000 items in a list box. Break them up into subcategories.
I am reminded of an argument I had once with someone who wanted to
use a mouse for everything and saw no use for a keyboard. It's
fine to select a name and phone number from a personal address book.
It is NOT fine to use the same technique for the World Phone Book,
particularly if you think you can fit it all on the screen at once.
I asked this guy to describe how he'd actually scale his design,
with the proviso that he was limited in the size of the monitor he
could use to THAT (I point outside to a nearby 25-story building
occupying most of a city block, completely covered in glass, which
I presumed could have monitors behind it) and make the monitor
resolution as much as he wanted (e.g. a trillion pixels per square
inch) but that he was not allowed to alter the resolution of human
Perhaps the "other means" are adequate. Or just preferable.
I'd like to suggest that if you try to put 5000 listbox elements on one
page, the results may be too slow due to browser paging regardless of
what else you do, other than putting fewer of them on each page.
My question was badly formed.
I think 200 in the list is certainly adequate, it's that i _only_ 200 are
being selected from the entire recordset;
It would be preferable to be able to click say [Next 200], and alow viewing
25 at a time. And that's where my head starts to spin.
I can't imagine putting 5000 records into the $_SESSION as an array and then
selecting 200 at a time, it seems like a huge amount of data.
But then again, it may not be?
I would have wondered at his sanity ;)
Morgan contained the following:
Well no, the answer is to write the query to give you what you want.
You don't say what database you are using but as you mention TOP I guess
you are not using MySQL MySQL has a handy LIMIT feature which makes
The first set of results is easy, top 200. the next set is the top 200
where the result is not in the first top 200, something like
$query = odbc_exec($odbc,"SELECT TOP 10 * FROM table where id not in
(SELECT TOP $offset id FROM table ORDER BY date_entered DESC) ORDER BY
Geoff Berrow (put thecat out to email)
It's only Usenet, no one dies.
My opinions, not the committee's, mine.
Simple RFDs http://www.ckdog.co.uk/rfdmaker/
You have made my day Geoff! That is perfect. The db is foxpro I beleive,
but it could be dbase. No one seems to actualy know.
The data is pulled from the companies stock and accounting db, and they have
no real idea. The db develloper is being hedgey about it too.
Vince Morgan wrote:
As far as I'm aware (and it is not unknown for me to be wrong on
occasion!) the only databases that use the "SELECT TOP n" syntax to limit
the number of results are Microsoft SQL Server and Microsoft Access.
For those who are interested, the Perl module SQL::Abstract::Limit comes
with a very good manual which explains the differences in syntax for
limiting SELECT queries between common RDBMSs.
Also, http://troels.arvin.dk/db/rdbms/#select-limit has a good write up on
the standards-compliant way of doing things, which databases support the
standards-compliant method, and how to use the proprietary syntaxes for
limiting SELECT queries where standards-compliant mechanisms won't work.
Toby A Inkster BSc (Hons) ARCS
Geek of ~ HTML/SQL/Perl/PHP/Python*/Apache/Linux
* = I'm getting there!
Why don't you provide a inputbox for searchable select list?
When the user choose a product group, enter a string in the input, and
click a submit button, the page refresh and your select item only
contain the product name match with user's product group and input
string. For optimization, the initial select item is empty and only
filled if user's string length minimal 3 characters. You can put the
inputbox and the select list in different forms. If its performance
still poor, consider using AJAX.
Sorry for the messy english.
Actualy if it were not for the JS dependency, I would consider AJAX very
usefull in this scenario. I would like the site to eventualy [it isn't
currently] be totaly non dependant on JS and use it only as an enhancement
here and there. However, maybe if I use it conditionaly (JS being available
on the client) I could use AJAX as an enhancement.
Thank you Bocah,