Thread: 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
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>
> 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
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
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?
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
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 >