Thread: Simple SQL Question

Simple SQL Question

From
Andras Kutrovics
Date:
Hi All!

In one of my application, I would like to implement incremental
fetching. The problem is that I would like to run it
from a stateless application server, so I have to tell where
to begin fetching data. It is simple for a table which has single column
primary key, but causes difficulties (at least for me) in
a more-column primary key..

Let say I have a table wich has the primary key: itemkey,location

table1
------
itemkey
location
...
--------


select * from table1 LIMIT x

gives me the first x row of the result.
After that, I save the last value, and next time, I adjust
the query as

select * from table1 where itemkey>:lastvalue LIMIT x

that should be enough for an 'Item' table, but I have another
column in the primary key.

let say, I have the following in Table1:

itemkey        location
------------------------
00001        00001
00001        00002
...
00005        00001
00005        00002
00005        00003 <--- lets say this is the last value

next time i want to run a query, which starts from

00005        00004
00006        00005

and so on..

How can I specify that in sql?
I dont want to use cursor:), I would like to do it in plain sql.
(It it is possible).

Thank you in advance

Andras Kutrovics


Re: Simple SQL Question

From
Franco Bruno Borghesi
Date:
wouldn't it be easier using offset & limit?: you always select from the table with an itemkey,location order by
clause.You save the current offset between requests, and for every request you re-execute the query with a different
offset.<br/><br /> If you still want to go with what you already have, you should keep the lastItemKey and
lastLocaltionvalues between requests, and your where clause should be something like:<br /> WHERE (itemKey=:lastItemKey
ANDlocation>:lastLocation) OR (itemKey>:lastItemKey)<br /><br /> hope it helps.<br /><br /> On Fri, 2004-11-05 at
13:54,Andras Kutrovics wrote: <blockquote type="CITE"><pre><font color="#737373"><i>Hi All! 

In one of my application, I would like to implement incremental
fetching. The problem is that I would like to run it
from a stateless application server, so I have to tell where
to begin fetching data. It is simple for a table which has single column
primary key, but causes difficulties (at least for me) in
a more-column primary key..

Let say I have a table wich has the primary key: itemkey,location

table1
------
itemkey
location
...
--------


select * from table1 LIMIT x

gives me the first x row of the result.
After that, I save the last value, and next time, I adjust
the query as

select * from table1 where itemkey>:lastvalue LIMIT x

that should be enough for an 'Item' table, but I have another
column in the primary key.

let say, I have the following in Table1:

itemkey        location
------------------------
00001        00001
00001        00002
...
00005        00001
00005        00002
00005        00003 <--- lets say this is the last value

next time i want to run a query, which starts from

00005        00004
00006        00005

and so on..

How can I specify that in sql?
I dont want to use cursor:), I would like to do it in plain sql.
(It it is possible).

Thank you in advance

Andras Kutrovics

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
</i></font></pre></blockquote>

Re: Simple SQL Question

From
Tomasz Myrta
Date:
> Hi All!
> 
> In one of my application, I would like to implement incremental
> fetching. The problem is that I would like to run it
> from a stateless application server, so I have to tell where
> to begin fetching data. It is simple for a table which has single column
> primary key, but causes difficulties (at least for me) in
> a more-column primary key..
> 
> Let say I have a table wich has the primary key: itemkey,location
> 
> table1
> ------
> itemkey
> location
> ...
> --------
> 
> 
> select * from table1 LIMIT x
> 
> gives me the first x row of the result.
> After that, I save the last value, and next time, I adjust
> the query as
> 
> select * from table1 where itemkey>:lastvalue LIMIT x

Why do you complicate it so much? Everything you need is:

select * from table1 LIMIT x
select * from table1 LIMIT x OFFSET x
select * from table1 LIMIT x OFFSET 2*x

Remember to sort rows before using limit/offset.

Regards,
Tomasz Myrta



Re: Simple SQL Question

From
Greg Stark
Date:
Tomasz Myrta <jasiek@klaster.net> writes:

> > select * from table1 LIMIT x
> > gives me the first x row of the result.
> > After that, I save the last value, and next time, I adjust
> > the query as
> > select * from table1 where itemkey>:lastvalue LIMIT x
> 
> Why do you complicate it so much? Everything you need is:
> 
> select * from table1 LIMIT x
> select * from table1 LIMIT x OFFSET x
> select * from table1 LIMIT x OFFSET 2*x
> 
> Remember to sort rows before using limit/offset.

There are two good reasons to prefer his Andras' solution to yours.

a) If the data is modified between the two queries his will continue from
where the previous page left off. Yours will either skip records or overlap
with the previous page.

b) If itemkey is indexed his will be an efficient index scan that performs
similarly regardless of what page is being fetched. Yours will perform more
and more slowly as the user gets deeper into the results.


Note that both queries are wrong however. You need an "ORDER BY itemkey" or
else nothing guarantees the second page has any relation at all to the first
page.

-- 
greg



Re: Simple SQL Question

From
Bricklen
Date:
Andras Kutrovics wrote:
> Hi All!
> 
> In one of my application, I would like to implement incremental
> fetching. The problem is that I would like to run it
> from a stateless application server, so I have to tell where
> to begin fetching data. It is simple for a table which has single column
> primary key, but causes difficulties (at least for me) in
> a more-column primary key..
> 
> Let say I have a table wich has the primary key: itemkey,location
> 
> table1
> ------
> itemkey
> location
> ...
> --------
> 
> 
> select * from table1 LIMIT x
> 
> gives me the first x row of the result.
> After that, I save the last value, and next time, I adjust
> the query as
> 
> select * from table1 where itemkey>:lastvalue LIMIT x
> 
> that should be enough for an 'Item' table, but I have another
> column in the primary key.
> 
> let say, I have the following in Table1:
> 
> itemkey        location
> ------------------------
> 00001        00001
> 00001        00002
> ...
> 00005        00001
> 00005        00002
> 00005        00003 <--- lets say this is the last value
> 
> next time i want to run a query, which starts from
> 
> 00005        00004
> 00006        00005
> 
> and so on..
> 
> How can I specify that in sql?
> I dont want to use cursor:), I would like to do it in plain sql.
> (It it is possible).
> 
> Thank you in advance
> 
> Andras Kutrovics

maybe the OFFSET keyword will help here?


Re: Simple SQL Question

From
Andras Kutrovics
Date:
Franco Bruno Borghesi wrote:


Hi!

Sorry for being late with the answer, I was busy at one of our customer;)

> wouldn't it be easier using offset & limit?: you always select from the 
> table with an itemkey,location order by clause. You save the current 
> offset between requests, and for every request you re-execute the query 
> with a different offset.  Sure, but sometimes I have to query by name, and dont want to create  another query
component.

> If you still want to go with what you already have, you should keep the 
> lastItemKey and lastLocaltion values between requests, and your where 
> clause should be something like:
> WHERE (itemKey=:lastItemKey AND location>:lastLocation) OR 
> (itemKey>:lastItemKey)
  It works perfectly, but I have to consider the performance issue,  because if i use 'or' statement, postgres doesn't
useindex scan,  and I also have tables with 3 or more keys and 500.000 records , 
 
where  the performance of this method is poor.  Maybe I will end up using limit and offset in case of incremental
fetching,butif the table is modified between requests, it can behave  weird.  Is there a perfect solution to this?
 

Sorry for the english

Thank you again,

Andras Kutrovics



Re: Simple SQL Question

From
Franco Bruno Borghesi
Date:
Didn't know about the seqscan problem when using ORs. But you still can 
split the query in two, and then use Union to join the results:

SELECT .... WHERE itemKey=:lastItemKey AND location>:lastLocation
UNION
SELECT ... WHERE itemKey>:lastItemKey


You could solve the OFFSET/LIMIT modification problem if you could keep 
the transaction open, but I don't know if it's applicable in your case.


Andras Kutrovics wrote:

> Franco Bruno Borghesi wrote:
>
>
> Hi!
>
> Sorry for being late with the answer, I was busy at one of our customer;)
>
>> wouldn't it be easier using offset & limit?: you always select from 
>> the table with an itemkey,location order by clause. You save the 
>> current offset between requests, and for every request you re-execute 
>> the query with a different offset.
>
>   Sure, but sometimes I have to query by name, and dont want to create
>   another query component.
>
>> If you still want to go with what you already have, you should keep 
>> the lastItemKey and lastLocaltion values between requests, and your 
>> where clause should be something like:
>> WHERE (itemKey=:lastItemKey AND location>:lastLocation) OR 
>> (itemKey>:lastItemKey)
>
>
>   It works perfectly, but I have to consider the performance issue,
>   because if i use 'or' statement, postgres doesn't use index scan,
>   and I also have tables with 3 or more keys and 500.000 records , where
>   the performance of this method is poor.
>   Maybe I will end up using limit and offset in case of incremental
>   fetching,but if the table is modified between requests, it can behave
>   weird.
>   Is there a perfect solution to this?
>
> Sorry for the english
>
> Thank you again,
>
> Andras Kutrovics
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
>               http://archives.postgresql.org
>