By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,967 Members | 1,684 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,967 IT Pros & Developers. It's quick & easy.

TOP

P: n/a
Please forgive the off topic content.
I'm working on redesigning an ecommerce site written by someone else in the
near future.
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
other means.
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.
TIA
Vince
Apr 27 '07 #1
Share this Question
Share on Google+
12 Replies


P: n/a
"Vince Morgan" <vi****@REMOVEoptusnet.com.auwrote in message
news:46***********************@news.optusnet.com.a u...
Please forgive the off topic content.
I'm working on redesigning an ecommerce site written by someone else in
the
near future.
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

I should have mentioned the data is retrieved via an sql statement that uses
'TOP 200'.
Apr 27 '07 #2

P: n/a
>I'm working on redesigning an ecommerce site written by someone else in the
>near future.
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.
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
vision.
>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
other means.
Finding this was easy, but fixing it has left me scratching my head
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.
>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.
TIA
Vince


Apr 27 '07 #3

P: n/a
"Gordon Burditt" <go***********@burditt.orgwrote in message
news:13*************@corp.supernews.com...
I'm working on redesigning an ecommerce site written by someone else in
the
near future.
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.

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.
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 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
vision.
I would have wondered at his sanity ;)
Thank's
Vince
Apr 27 '07 #4

P: n/a
Message-ID: <46**********************@news.optusnet.com.aufr om Vince
Morgan contained the following:
>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.
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
pagination easy.

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
date_entered DESC");
--
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/
Apr 27 '07 #5

P: n/a
"Geoff Berrow" <bl******@ckdog.co.ukwrote in message
news:f7********************************@4ax.com...
Message-ID: <46**********************@news.optusnet.com.aufr om Vince
Morgan contained the following:
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.

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
pagination easy.

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
date_entered DESC");
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.
Highest regards,
Vince
Apr 27 '07 #6

P: n/a
Message-ID: <46**********************@news.optusnet.com.aufr om Vince
Morgan contained the following:
>You have made my day Geoff!
It's always a pleasure to do that. :-)
--
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/
Apr 27 '07 #7

P: n/a
Vince Morgan wrote:
The db is foxpro I beleive, but it could be dbase. No one seems to
actualy know.
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
http://tobyinkster.co.uk/
Geek of ~ HTML/SQL/Perl/PHP/Python*/Apache/Linux

* = I'm getting there!
Apr 27 '07 #8

P: n/a
Vince Morgan wrote:
Before I dive headlong into this I would be very gratefull to see the
thoughts of others who know of workable solutions.
Divide the products into more groups. If possible, use groups with
subgroups, and perhaps subsubgroups, etc.

--
Toby A Inkster BSc (Hons) ARCS
http://tobyinkster.co.uk/
Geek of ~ HTML/SQL/Perl/PHP/Python*/Apache/Linux

* = I'm getting there!
Apr 27 '07 #9

P: n/a
On Apr 27, 8:39 am, "Vince Morgan" <vin...@REMOVEoptusnet.com.au>
wrote:
Please forgive the off topic content.
I'm working on redesigning an ecommerce site written by someone else in the
near future.
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
other means.
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.
TIA
Vince
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.
HTH

Apr 27 '07 #10

P: n/a
"Bocah Sableng" <ca********@gmail.comwrote in message
news:11**********************@t39g2000prd.googlegr oups.com...
On Apr 27, 8:39 am, "Vince Morgan" <vin...@REMOVEoptusnet.com.au>
If its performance
still poor, consider using AJAX.
Sorry for the messy english.
HTH
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,
Vince
Apr 29 '07 #11

P: n/a
"Toby A Inkster" <us**********@tobyinkster.co.ukwrote in message
news:bb************@ophelia.g5n.co.uk...
Vince Morgan wrote:
Before I dive headlong into this I would be very gratefull to see the
thoughts of others who know of workable solutions.

Divide the products into more groups. If possible, use groups with
subgroups, and perhaps subsubgroups, etc.
That's an excellent suggestion. In conjunction with Geoff's solution it's a
nice package.
I hope you get better soon! Errr,, I mean, I'm sure your very good at what
you do,, I mean, I hope you achieve full and complete bipedialiality
forthwith!
Thank you,
Vince
Apr 29 '07 #12

P: n/a
"Vince Morgan" <vi****@REMOVEoptusnet.com.auwrote in message
news:46***********************@news.optusnet.com.a u...
"Toby A Inkster" <us**********@tobyinkster.co.ukwrote in message
news:bb************@ophelia.g5n.co.uk...
Vince Morgan wrote:
I hope you get better soon! Errr,, I mean, I'm sure your very good at
what
you do,, I mean, I hope you achieve full and complete bipedialiality
forthwith!
Oops, pass that on to Rik for me please.
Apr 29 '07 #13

This discussion thread is closed

Replies have been disabled for this discussion.