Re: [SQL-Ledger-users] LARGE INVENTORY

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

Postgresql does have a limit clause. It is different from MySQL in that
it uses reversed order. Ex. Lets say you have a large inventory, like
I do, then query:

SELECT * FROM parts LIMIT 1000, 0;

This will return records 0 (the first) through the 1,000th.

For the next set of records we would query:

SELECT * FROM parts LIMIT 2000, 1000;

and get the 1000th though the 2000th records. There would only ever be
a descrepency in the returned records if someone added or deleted records
between these queries.

I have already placed this limiting code in to sql-ledger's ic.cgi script
without any errors being reported, though I have yet to fully test it.
I'll let you know.

Given that this limit clause works okay then this is probably a bit better
than script proccessing the top n returned records, and the next n records,
because each time you'd still have to query all the records. Not that
much more overhead for such a capable database as postgresql but still
more overhead.

-- 
Thomas Sawyer
ps...@on... - email
(303) 285-3487 x1594 - voicemail/fax



---- "Roderick A. Anderson" <raa...@ti...> wrote:
> 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!
> 
> 
> 
> 
> 

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