sql-ledger-users

[SQL-Ledger-users] LARGE INVENTORY

[Thomas S. <ps...@on...>]

Hi again... 

The bigger thing is that I just loaded up the database with 25,000+ inventory
records! Postgresql handled it nicely, loading them from an .sql file
of insert staements in about 5 minutes.

But when I went to my windows client machine and did an inventory search
on all the items it took nearly 30 minutes to load the page! It worked
though so that's good! The slow down apparently comes from building such
a large page --not so much the transfer via the network. CGI Perl isn't
all that fast. Is there any way to speed this up?

One thing that happened during the course of this load is that Windows
2000 reported that it needed to increase Virtual memory to complete the
operation I bet it did! That's one big .html file!

I have to get this working with this a large inventory. So I'm looking
at adding some selection criteria limitations to the ic.cgi script. I'm
thinking of adding limiting criteria where one has to select the first
and second letter of the search. (i.e. Aa, Ab, Ac...Zx, Zy, Zz) How does
this sound to you all? Any other ideas?




-- 
Thomas Sawyer
ps...@on... - email


__________________________________________________
FREE voicemail, email, and fax...all in one place.
Sign Up Now! http://www.onebox.com

Re: [SQL-Ledger-users] LARGE INVENTORY

[Martin L. <ma...@li...>]

Thomas Sawyer wrote:

> I have to get this working with this a large inventory. So I'm looking
> at adding some selection criteria limitations to the ic.cgi script. I'm
> thinking of adding limiting criteria where one has to select the first
> and second letter of the search. (i.e. Aa, Ab, Ac...Zx, Zy, Zz) How does
> this sound to you all? Any other ideas?

maybe you should create a new parameter (eq. min_search_str_length) in
configuration page, so people with small inventories (like me) can set
it to 0 and in your case, it can be set to 2, 3 or whatever. then add
code to ic.cgi that displays error message if search string is shorter
than value specified in config. does this make any sense or am I
over-complicating things again? :)

another idea to consider is grouping inventory items, so that you first
select group and then only products belonging to specified group are
displayed.

idea #3 - limit displaying inventory items on one page (does PostgreSQL
have equivalent to MySQL LIMIT?). Maybe implement this throughout the
program + another option to config page?

--
Martin Lillepuu | E-mail: ma...@li... | GSM: 051 56 450

Re: [SQL-Ledger-users] LARGE INVENTORY

[<tra...@ya...>]

   I think it would be better to slice this huge page in pages of n
elements and then display just n elements and give to the user a next
page link or button so we will just be selecting from the DB all the
elements but the heavy operation that is generating the page will be
done with a subset of all the elements. But in this case we should
keep the state of the module, I mean the "next page" will mean
different places every time we show it. So we could put that
information in the "next page" link, we could include the query
parameters and the next starting ID for the next page, and the same
for "prev page" . Do I explain my self?

Claudio Santana.

--- Martin Lillepuu <ma...@li...> escribió: > Thomas Sawyer
wrote:
> 
> > I have to get this working with this a large inventory. So I'm
> looking
> > at adding some selection criteria limitations to the ic.cgi
> script. I'm
> > thinking of adding limiting criteria where one has to select the
> first
> > and second letter of the search. (i.e. Aa, Ab, Ac...Zx, Zy, Zz)
> How does
> > this sound to you all? Any other ideas?
> 
> maybe you should create a new parameter (eq. min_search_str_length)
> in
> configuration page, so people with small inventories (like me) can
> set
> it to 0 and in your case, it can be set to 2, 3 or whatever. then
> add
> code to ic.cgi that displays error message if search string is
> shorter
> than value specified in config. does this make any sense or am I
> over-complicating things again? :)
> 
> another idea to consider is grouping inventory items, so that you
> first
> select group and then only products belonging to specified group
> are
> displayed.
> 
> idea #3 - limit displaying inventory items on one page (does
> PostgreSQL
> have equivalent to MySQL LIMIT?). Maybe implement this throughout
> the
> program + another option to config page?
> 
> --
> Martin Lillepuu | E-mail: ma...@li... | GSM: 051 56 450
> 


_________________________________________________________
Do You Yahoo!?
Obtenga su dirección de correo-e gratis @yahoo.com
en http://correo.espanol.yahoo.com

Re: [SQL-Ledger-users] LARGE INVENTORY

[Matt B. <ma...@li...>]

I like this suggestion.  It would allow you to browse all available data
in user-configurable increments.

It is also a very familiar interface on the web, which would make it easy
and comfortable for end-users to learn.  (Reading "Phillip and Alex's
Guide to Web Publishing" has opened my eyes to the importance of
easy-to-learn interfaces, I think.)

Matt


Matt Benjamin President/CTO

The Linux Box
206 South Fifth Ave. Suite 150
Ann Arbor, MI  48104

tel. 734-761-4689
fax. 734-769-8938
pgr. 734-431-0118

On Tue, 6 Feb 2001, [iso-8859-1] Claudio Santana wrote:

>    I think it would be better to slice this huge page in pages of n
> elements and then display just n elements and give to the user a next
> page link or button so we will just be selecting from the DB all the
> elements but the heavy operation that is generating the page will be
> done with a subset of all the elements. But in this case we should
> keep the state of the module, I mean the "next page" will mean
> different places every time we show it. So we could put that
> information in the "next page" link, we could include the query
> parameters and the next starting ID for the next page, and the same
> for "prev page" . Do I explain my self?
>
> Claudio Santana.
>
> --- Martin Lillepuu <ma...@li...> escribi=F3: > Thomas Sawyer
> wrote:
> >
> > > I have to get this working with this a large inventory. So I'm
> > looking
> > > at adding some selection criteria limitations to the ic.cgi
> > script. I'm
> > > thinking of adding limiting criteria where one has to select the
> > first
> > > and second letter of the search. (i.e. Aa, Ab, Ac...Zx, Zy, Zz)
> > How does
> > > this sound to you all? Any other ideas?
> >
> > maybe you should create a new parameter (eq. min_search_str_length)
> > in
> > configuration page, so people with small inventories (like me) can
> > set
> > it to 0 and in your case, it can be set to 2, 3 or whatever. then
> > add
> > code to ic.cgi that displays error message if search string is
> > shorter
> > than value specified in config. does this make any sense or am I
> > over-complicating things again? :)
> >
> > another idea to consider is grouping inventory items, so that you
> > first
> > select group and then only products belonging to specified group
> > are
> > displayed.
> >
> > idea #3 - limit displaying inventory items on one page (does
> > PostgreSQL
> > have equivalent to MySQL LIMIT?). Maybe implement this throughout
> > the
> > program + another option to config page?
> >
> > --
> > Martin Lillepuu | E-mail: ma...@li... | GSM: 051 56 450
> >
>
>
> _________________________________________________________
> Do You Yahoo!?
> Obtenga su direcci=F3n de correo-e gratis @yahoo.com
> en http://correo.espanol.yahoo.com
>

Re: [SQL-Ledger-users] LARGE INVENTORY

[Dieter S. <dsi...@sq...>]

Slicing output into pages does not work very well.

I'll add an option to limit searches by forcing users to enter a minimum
number of characters for those who can't teach their users to enter
something. This leaves the program the way it is for people who like to
produce a complete inventory listing and limits output for those who need
a limit.

It used to be in version 0.1, I'll add it again for version 1.4


Dieter Simader    http://www.sql-ledger.org   (780) 472-8161
DWS Systems Inc.     Accounting Software       Fax: 478-5281
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D On a clear disk you can seek forever =3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

On Tue, 6 Feb 2001, Matt Benjamin wrote:

>=20
> I like this suggestion.  It would allow you to browse all available dat=
a
> in user-configurable increments.
>=20
> It is also a very familiar interface on the web, which would make it ea=
sy
> and comfortable for end-users to learn.  (Reading "Phillip and Alex's
> Guide to Web Publishing" has opened my eyes to the importance of
> easy-to-learn interfaces, I think.)
>=20
> Matt
>=20
>=20
> Matt Benjamin President/CTO
>=20
> The Linux Box
> 206 South Fifth Ave. Suite 150
> Ann Arbor, MI  48104
>=20
> tel. 734-761-4689
> fax. 734-769-8938
> pgr. 734-431-0118
>=20
> On Tue, 6 Feb 2001, [iso-8859-1] Claudio Santana wrote:
>=20
> >    I think it would be better to slice this huge page in pages of n
> > elements and then display just n elements and give to the user a next
> > page link or button so we will just be selecting from the DB all the
> > elements but the heavy operation that is generating the page will be
> > done with a subset of all the elements. But in this case we should
> > keep the state of the module, I mean the "next page" will mean
> > different places every time we show it. So we could put that
> > information in the "next page" link, we could include the query
> > parameters and the next starting ID for the next page, and the same
> > for "prev page" . Do I explain my self?
> >
> > Claudio Santana.
> >
> > --- Martin Lillepuu <ma...@li...> escribi=F3: > Thomas Sawyer
> > wrote:
> > >
> > > > I have to get this working with this a large inventory. So I'm
> > > looking
> > > > at adding some selection criteria limitations to the ic.cgi
> > > script. I'm
> > > > thinking of adding limiting criteria where one has to select the
> > > first
> > > > and second letter of the search. (i.e. Aa, Ab, Ac...Zx, Zy, Zz)
> > > How does
> > > > this sound to you all? Any other ideas?
> > >
> > > maybe you should create a new parameter (eq. min_search_str_length)
> > > in
> > > configuration page, so people with small inventories (like me) can
> > > set
> > > it to 0 and in your case, it can be set to 2, 3 or whatever. then
> > > add
> > > code to ic.cgi that displays error message if search string is
> > > shorter
> > > than value specified in config. does this make any sense or am I
> > > over-complicating things again? :)
> > >
> > > another idea to consider is grouping inventory items, so that you
> > > first
> > > select group and then only products belonging to specified group
> > > are
> > > displayed.
> > >
> > > idea #3 - limit displaying inventory items on one page (does
> > > PostgreSQL
> > > have equivalent to MySQL LIMIT?). Maybe implement this throughout
> > > the
> > > program + another option to config page?
> > >
> > > --
> > > Martin Lillepuu | E-mail: ma...@li... | GSM: 051 56 450
> > >
> >
> >
> > _________________________________________________________
> > Do You Yahoo!?
> > Obtenga su direcci=F3n de correo-e gratis @yahoo.com
> > en http://correo.espanol.yahoo.com
> >
>=20
>=20
>=20

Re: [SQL-Ledger-users] LARGE INVENTORY

[Matt B. <ma...@li...>]

Why not, exactly?  It seems like a sensible approach when you really do
want to browse many pages of data.  That must be why even the most recent
search engines provide this feature.

I certainly wouldn't substitute paging for search criteria, but again,
sometimes you want to browse.

Matt

> Slicing output into pages does not work very well.
>

Re: [SQL-Ledger-users] LARGE INVENTORY

[Andrew S. <an...@me...>]

You mean code sorta like this:



    wilbur_init::header("Entries", $entID);
    print "<img src=3Dentrylist.png alt=3D\"List\"><br><hr>\n";

    $FORM{lim}|=3D 0;=09
    my $qq=3D"select blah,blah,blah=20
                   from entries
                   where blah=3Dblah';
    if($CONFIG{sqllimit} =3D=3D 1) {
        # this presuposes a limit statement in postgresql, but
        # I think it has them.  If it doesn't it can be coded
        # around anyway.
	$qq .=3D "limit $FORM{lim}, $PREF{pListNum}";
    } else {
	my $tlim =3D $FORM{lim} + $PREF{pListNum};
	$qq .=3D "limit $tlim";=20
    }

    my $q =3D $dbh->prepare($qq);
    $q->execute() or print STDERR "$0 $qq\n";

    if($CONFIG{sqllimit} =3D=3D 0) {
	my $tc=3D0;
	while($tc < $FORM{lim}) {
	      my ($blah, $blah) =3D $q->fetchrow_array();
	      $tc++;
	  }
    }

    while(my ($blah, $blah) =3D $q->fetchrow_array()) {
        wilbur::showentry($entID, $blah, $blah);
    }
    $q->finish();

    $qq =3D "select count(entID) from entries where entUID=3D$uID";
    $q =3D $dbh->prepare($qq);
    $q->execute() or print STDERR "$0 $qq\n";
    my ($ct) =3D $q->fetchrow_array();
    $q->finish();   =20

    if(($FORM{lim} - $PREF{pListNum}) >=3D 0 ) { #need prev=20
	my $newlim =3D $FORM{lim} - $PREF{pListNum};
	print "&lt;&lt; <a href=3D\"list.cgi?mode=3Dlist&lim=3D$newlim\">prev</a=
> ]";
    }

    my ($l, $lct) =3D (0, 1);=20
    if($ct > $PREF{pListNum}) {
	print "[";
	do {
	    print "<a href=3D\"list.cgi?mode=3Dlist&lim=3D$l\"> $lct </a>";
	    $l +=3D $PREF{pListNum};
	    $lct++;
	} while($l < $ct);
	print "]\n";
    }
    if(($FORM{lim} + $PREF{pListNum}) < $ct) { # on last already
	my $newlim =3D $FORM{lim} + $PREF{pListNum};
	print "[ <a href=3D\"list.cgi?mode=3Dlist&lim=3D$newlim\">next</a> &gt;&=
gt;\n";
    }


This kind of code is extremely common in programs like this that may have=
 a large number of items to display, and it is desireable to break them u=
p into "pages."  Also, some have mentioned that a user might not want thi=
ngs broken up into pages.  The preference to not have any limit is also t=
here in the above code.

a

Claudio Santana wrote:
>=20
>    I think it would be better to slice this huge page in pages of n
> elements and then display just n elements and give to the user a next
> page link or button so we will just be selecting from the DB all the
> elements but the heavy operation that is generating the page will be
> done with a subset of all the elements. But in this case we should
> keep the state of the module, I mean the "next page" will mean
> different places every time we show it. So we could put that
> information in the "next page" link, we could include the query
> parameters and the next starting ID for the next page, and the same
> for "prev page" . Do I explain my self?
>=20
> Claudio Santana.
>=20
> --- Martin Lillepuu <ma...@li...> escribi=F3: > Thomas Sawyer
> wrote:
> >
> > > I have to get this working with this a large inventory. So I'm
> > looking
> > > at adding some selection criteria limitations to the ic.cgi
> > script. I'm
> > > thinking of adding limiting criteria where one has to select the
> > first
> > > and second letter of the search. (i.e. Aa, Ab, Ac...Zx, Zy, Zz)
> > How does
> > > this sound to you all? Any other ideas?
> >
> > maybe you should create a new parameter (eq. min_search_str_length)
> > in
> > configuration page, so people with small inventories (like me) can
> > set
> > it to 0 and in your case, it can be set to 2, 3 or whatever. then
> > add
> > code to ic.cgi that displays error message if search string is
> > shorter
> > than value specified in config. does this make any sense or am I
> > over-complicating things again? :)
> >
> > another idea to consider is grouping inventory items, so that you
> > first
> > select group and then only products belonging to specified group
> > are
> > displayed.
> >
> > idea #3 - limit displaying inventory items on one page (does
> > PostgreSQL
> > have equivalent to MySQL LIMIT?). Maybe implement this throughout
> > the
> > program + another option to config page?
> >
> > --
> > Martin Lillepuu | E-mail: ma...@li... | GSM: 051 56 450
> >
>=20
> _________________________________________________________
> Do You Yahoo!?
> Obtenga su direcci=F3n de correo-e gratis @yahoo.com
> en http://correo.espanol.yahoo.com

Re: [SQL-Ledger-users] LARGE INVENTORY

[Roderick A. A. <raa...@ti...>]

On Tue, 6 Feb 2001, Andrew Sharp wrote:

>     if($CONFIG{sqllimit} == 1) {
>         # this presuposes a limit statement in postgresql, but
>         # I think it has them.  If it doesn't it can be coded
>         # around anyway.

PostgreSQL does have the limit clause which I believe is in the SQL92 and
SQL99 standards.  It's easy to get into the MySQL (choose your own
database) has this feature or that thingy but for the most part PostgreSQL
follows the standands better than the other databases.

   The problem with the limit statement is it is 'stateless'.  It picks
the first 'n' records that meet the rest of the selection criteria.  
There is no easy way to remember the current 'n' items at the database
level so the the next SELECT statement can pick the next 'n' items.

   The better choice is to select all the records that meet the criteria -
using the DBI - and break them up into groups/bunches to display using a
display limit.

Since I've only been half following the discussion (subscribed to too many
other mailing lists - including two of the PostgreSQL lists) I'm not sure
where the 'convince Dieter' discussion is at currently.  I was mostly
concerned over the repeated question about PostgreSQL having the LIMIT
clause but then realized the LIMIT discussion could be going down the
wrong road.

I hope that we convince Dieter to add some method of limiting the
selection and displayed list so please don't take this as a rebuke.


Good Computing,
Rod
-- 
I really need a signature block!

Re: [SQL-Ledger-users] LARGE INVENTORY

[<li...@li...>]

It's been rumoured that Roderick A. Anderson said:
> PostgreSQL does have the limit clause which I believe is in the SQL92 and
> SQL99 standards.  It's easy to get into the MySQL (choose your own
> database) has this feature or that thingy but for the most part PostgreSQL
> follows the standands better than the other databases.
> 
>    The problem with the limit statement is it is 'stateless'.  It picks
> the first 'n' records that meet the rest of the selection criteria.  
> There is no easy way to remember the current 'n' items at the database
> level so the the next SELECT statement can pick the next 'n' items.

Not true: use cursors.

DECLARE cursorname CURSOR FOR query;

FETCH count FROM cursorname;

allows you to pick the next n items forward, back, relative, absolute.
etc.

its in postgres.

Note cursors suck for update, because if someone else inserted a record
into the set of N you are looking at with a cursor, things get confusing
as you scroll around.

--linas

Re: [SQL-Ledger-users] LARGE INVENTORY

[John C. <jcu...@ho...>]

Thomas,

I just fired up the system and so I have not retrieved an
inventory of items yet, but if it puts them into a large 
html table
then you could get a count first and the set the size of the
table first. The will speed up the loading of the page if 
the browser
knows the size of the table first (it fills at it goes 
instead of waiting
for the complete download and then putting the table on the 
screen)

Or you could set up a cursor to fetch 20 at a time. I don't 
have these
solutions directly in front of me and am doing from memory 
on some
inventory issues from a previous job.  There are examples in 
PHP postgres
on how to do this and there is some CPAN stuff for postgres 
on how to do this.

I'll look around and see what I can come up with.

John C.

Thomas Sawyer wrote:

> Hi again... 
> 
> The bigger thing is that I just loaded up the database with 25,000+ inventory
> records! Postgresql handled it nicely, loading them from an .sql file
> of insert staements in about 5 minutes.
> 
> But when I went to my windows client machine and did an inventory search
> on all the items it took nearly 30 minutes to load the page! It worked
> though so that's good! The slow down apparently comes from building such
> a large page --not so much the transfer via the network. CGI Perl isn't
> all that fast. Is there any way to speed this up?
> 
> One thing that happened during the course of this load is that Windows
> 2000 reported that it needed to increase Virtual memory to complete the
> operation I bet it did! That's one big .html file!
> 
> I have to get this working with this a large inventory. So I'm looking
> at adding some selection criteria limitations to the ic.cgi script. I'm
> thinking of adding limiting criteria where one has to select the first
> and second letter of the search. (i.e. Aa, Ab, Ac...Zx, Zy, Zz) How does
> this sound to you all? Any other ideas?