Simple SQL Question

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

Re: Simple SQL Question

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

--=-5DW3MliWpvthJ5p5Omxl
Content-Type: multipart/alternative; boundary="=-hoPsvHiTKF01gM1rzpfk"


--=-hoPsvHiTKF01gM1rzpfk
Content-Type: text/plain
Content-Transfer-Encoding: quoted-printable

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.

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=3D:lastItemKey AND location>:lastLocation) OR
(itemKey>:lastItemKey)

hope it helps.

On Fri, 2004-11-05 at 13:54, Andras Kutrovics wrote:

> Hi All!
>=20
> 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..
>=20
> Let say I have a table wich has the primary key: itemkey,location
>=20
> table1
> ------
> itemkey
> location
> ...
> --------
>=20
>=20
> select * from table1 LIMIT x
>=20
> gives me the first x row of the result.
> After that, I save the last value, and next time, I adjust
> the query as
>=20
> select * from table1 where itemkey>:lastvalue LIMIT x
>=20
> that should be enough for an 'Item' table, but I have another
> column in the primary key.
>=20
> let say, I have the following in Table1:
>=20
> itemkey location
> ------------------------
> 00001 00001
> 00001 00002
> ...
> 00005 00001
> 00005 00002
> 00005 00003 <--- lets say this is the last value
>=20
> next time i want to run a query, which starts from
>=20
> 00005 00004
> 00006 00005
>=20
> and so on..
>=20
> 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).
>=20
> Thank you in advance
>=20
> Andras Kutrovics
>=20
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>=20

--=-hoPsvHiTKF01gM1rzpfk
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: quoted-printable








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 offse=
t between requests, and for every request you re-execute the query with a d=
ifferent offset.



If you still want to go with what you already have, you should keep the las=
tItemKey and lastLocaltion values between requests, and your where clause s=
hould be something like:

WHERE (itemKey=3D:lastItemKey AND location>:lastLocation) OR (itemKey>=
;:lastItemKey)



hope it helps.



On Fri, 2004-11-05 at 13:54, 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



---------------------------(end of broadcast)---------------------------

TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org

Re: Simple SQL Question

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


---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

Re: Simple SQL Question

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

Re: Simple SQL Question

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
>


---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org