TOP

[Vince Morgan]

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

[Vince Morgan]

Re: TOP

"Vince Morgan" <vinhar@REMOVEo ptusnet.com.auw rote in message
news:46315428$0 $16552$afc38c87 @news.optusnet. com.au...

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'.

[Gordon Burditt]

Re: TOP

>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
>
>

[Vince Morgan]

Re: TOP

"Gordon Burditt" <gordonb.8k34d@ burditt.orgwrot e in message
news:13330ffoq4 m7b74@corp.supe rnews.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

[Geoff Berrow]

Re: TOP

Message-ID: <46318ed6$0$977 2$afc38c87@news .optusnet.com.a ufrom 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/

[Vince Morgan]

Re: TOP

"Geoff Berrow" <blthecat@ckdog .co.ukwrote in message
news:f75333l2ep 35g034mia83q9j5 s7it23j07@4ax.c om...

Message-ID: <46318ed6$0$977 2$afc38c87@news .optusnet.com.a ufrom 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

[Geoff Berrow]

Re: TOP

Message-ID: <463199bd$0$977 6$afc38c87@news .optusnet.com.a ufrom 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/

[Toby A Inkster]

Re: TOP

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!

[Toby A Inkster]

Re: TOP

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!

[Bocah Sableng]

Re: TOP

On Apr 27, 8:39 am, "Vince Morgan" <vin...@REMOVEo ptusnet.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

[Vince Morgan]

Re: TOP

"Bocah Sableng" <cahsableng@gma il.comwrote in message
news:1177678128 .189724.230540@ t39g2000prd.goo glegroups.com.. .

On Apr 27, 8:39 am, "Vince Morgan" <vin...@REMOVEo ptusnet.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

[Vince Morgan]

Re: TOP

"Toby A Inkster" <usenet200703@t obyinkster.co.u kwrote in message
news:bbu8g4-q7c.ln1@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

[Vince Morgan]

Re: TOP

"Vince Morgan" <vinhar@REMOVEo ptusnet.com.auw rote in message
news:463407c9$0 $11385$afc38c87 @news.optusnet. com.au...

"Toby A Inkster" <usenet200703@t obyinkster.co.u kwrote in message
news:bbu8g4-q7c.ln1@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.