Re: retrieving data from the table in chunks for paging

On Mar 27, 7:47 pm, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > On Mar 27, 4:59 pm, Art Kagel <art.ka...@gmail.com> wrote:> You don't mention what you are using for a front-end, but in most of them > > including ESQL/C, 4GL, ODBC, JDBC, and others, you can take advantage of > > IDS's SCROLL CURSOR feature: > > Since she didn't say what application or front end tools, I don't know > that it would be a good idea to use a scroll cursor. > If they're using a web interface, you're kind of screwed. You have to > maintain state and you can't pool your connection threads if you use a > scroll cursor. > (The cursor has to remain active until the session times out so when > you dynamically create the cursor, you'd have append the session_id to > the cursor name. It can be done, but its ugly and kind of violates > some of the design patterns.) > > This would mean that you'd have to design a pk for the table and then > track the last row retrieved and get the next set of rows. Even this > has some issues in a web app... > > But I do agree, if you're doing straight client/server with 4GL and or > Java, yeah you can use a scroll cursor. > > HTH > > -G Ian sent me the below very good email and it started me thinking. Begin Ian's email: I agree. That's why I said for web apps, scroll cursors aren't a good idea. Your solution works, but its not really cost effective because each time you flag to the next group, you have to rerun the query. I'm a bit interested in that they're not retrieving more data and just caching it per session. Even this solution has trade offs and doesn't work when you're potentially grabbing lots of data. But 1000 rows of character data? Assuming that each row was 200 bytes long, that's 200K which could be cached either on the sever side or sent down to the client via ajax. When you leave the page, you can clean the page cache. ... End Ian's email I don't entirely agree. I don't think that the query is that expensive in Informix because you are using an index to sort and scan data with. This is very quick to bring 100 rows back from an index. Also, I think most people don't page through a 1000 rows of data. I know I abandon my web searches after about 5 pages and try a different search if I haven't found what I need in that many pages. Actually I think I abandon even quicker than that. I have my google set to return about 25 hits per page. So if you cache 1000 rows of data and you only use 100 rows then you are wasting transmitting and processing 900 rows each time someone does one of these queries. I would bet that rerunning the query would be less expensive than that. This is kind of like setting read ahead to high. I'll let Art talk to that sin or you can just google for one of his excellent posts on the subject. Also, the 100 rows was an example number and doesn't really reflect what I would use in the real world. In the real world you could calculate a very good estimate for about how much data to bring back without wasting too much data or rerunning the query too many times. The 100 rows doesn't actually need to reflect the number you put on the screen it could be 5 times the number you put on the screen. It is actually your estimate of about how much data you will use typically.

You can do something that a colleague and I named the electric slide some 15 years ago. In each fetch session retrieve the number of rows that you think the user is likely to page through, say 10 pages. As long as the users is only interested in those first 10 pages you've eliminated back and forth communications. If the user pages past the nth page SLIDE the last n/2 pages to the beginning of the cache and fetch the next n/2 pages to fill out the cache. That way your app can scroll back and forth in memory for the user most often and only fetch more data when the users scrolls outside of that window. You can electric slide the N row window across the data back and forth as needed. Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Sun, Mar 29, 2009 at 12:46 PM, Curtis Crowson <curtis@crowson1.com>wrote: > On Mar 27, 7:47 pm, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > > On Mar 27, 4:59 pm, Art Kagel <art.ka...@gmail.com> wrote:> You don't > mention what you are using for a front-end, but in most of them > > > including ESQL/C, 4GL, ODBC, JDBC, and others, you can take advantage > of > > > IDS's SCROLL CURSOR feature: > > > > Since she didn't say what application or front end tools, I don't know > > that it would be a good idea to use a scroll cursor. > > If they're using a web interface, you're kind of screwed. You have to > > maintain state and you can't pool your connection threads if you use a > > scroll cursor. > > (The cursor has to remain active until the session times out so when > > you dynamically create the cursor, you'd have append the session_id to > > the cursor name. It can be done, but its ugly and kind of violates > > some of the design patterns.) > > > > This would mean that you'd have to design a pk for the table and then > > track the last row retrieved and get the next set of rows. Even this > > has some issues in a web app... > > > > But I do agree, if you're doing straight client/server with 4GL and or > > Java, yeah you can use a scroll cursor. > > > > HTH > > > > -G > > Ian sent me the below very good email and it started me thinking. > > Begin Ian's email: > > I agree. That's why I said for web apps, scroll cursors aren't a good > idea. > > Your solution works, but its not really cost effective because each > time you flag to the next group, you have to rerun the query. > > I'm a bit interested in that they're not retrieving more data and just > caching it per session. Even this solution has trade offs and doesn't > work when you're potentially grabbing lots of data. But 1000 rows of > character data? Assuming that each row was 200 bytes long, that's 200K > which could be cached either on the sever side or sent down to the > client via ajax. When you leave the page, you can clean the page > cache. > > ... > > End Ian's email > > I don't entirely agree. I don't think that the query is that expensive > in Informix because you are using an index to sort and scan data with. > This is very quick to bring 100 rows back from an index. Also, I think > most people don't page through a 1000 rows of data. I know I abandon > my web searches after about 5 pages and try a different search if I > haven't found what I need in that many pages. Actually I think I > abandon even quicker than that. I have my google set to return about > 25 hits per page. So if you cache 1000 rows of data and you only use > 100 rows then you are wasting transmitting and processing 900 rows > each time someone does one of these queries. I would bet that > rerunning the query would be less expensive than that. This is kind of > like setting read ahead to high. I'll let Art talk to that sin or you > can just google for one of his excellent posts on the subject. Also, > the 100 rows was an example number and doesn't really reflect what I > would use in the real world. In the real world you could calculate a > very good estimate for about how much data to bring back without > wasting too much data or rerunning the query too many times. The 100 > rows doesn't actually need to reflect the number you put on the screen > it could be 5 times the number you put on the screen. It is actually > your estimate of about how much data you will use typically. > > _______________________________________________ > Informix-list mailing list > Informix-list@iiug.org > http://www.iiug.org/mailman/listinfo/informix-list >

On Mar 29, 2:28 pm, Art Kagel <art.ka...@gmail.com> wrote: > You can do something that a colleague and I named the electric slide some 15 > years ago. In each fetch session retrieve the number of rows that you think > the user is likely to page through, say 10 pages. As long as the users is > only interested in those first 10 pages you've eliminated back and forth > communications. If the user pages past the nth page SLIDE the last n/2 > pages to the beginning of the cache and fetch the next n/2 pages to fill out > the cache. That way your app can scroll back and forth in memory for the > user most often and only fetch more data when the users scrolls outside of > that window. You can electric slide the N row window across the data back > and forth as needed. > > Art S. Kagel > Oninit (www.oninit.com) > IIUG Board of Directors (a...@iiug.org) > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > do not reflect on my employer, Oninit, the IIUG, nor any other organization > with which I am associated either explicitly or implicitly. Neither do > those opinions reflect those of other individuals affiliated with any entity > with which I am affiliated nor those of the entities themselves. > > On Sun, Mar 29, 2009 at 12:46 PM, Curtis Crowson <cur...@crowson1.com>wrote: > > > On Mar 27, 7:47 pm, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > > > On Mar 27, 4:59 pm, Art Kagel <art.ka...@gmail.com> wrote:> You don't > > mention what you are using for a front-end, but in most of them > > > > including ESQL/C, 4GL, ODBC, JDBC, and others, you can take advantage > > of > > > > IDS's SCROLL CURSOR feature: > > > > Since she didn't say what application or front end tools, I don't know > > > that it would be a good idea to use a scroll cursor. > > > If they're using a web interface, you're kind of screwed. You have to > > > maintain state and you can't pool your connection threads if you use a > > > scroll cursor. > > > (The cursor has to remain active until the session times out so when > > > you dynamically create the cursor, you'd have append the session_id to > > > the cursor name. It can be done, but its ugly and kind of violates > > > some of the design patterns.) > > > > This would mean that you'd have to design a pk for the table and then > > > track the last row retrieved and get the next set of rows. Even this > > > has some issues in a web app... > > > > But I do agree, if you're doing straight client/server with 4GL and or > > > Java, yeah you can use a scroll cursor. > > > > HTH > > > > -G > > > Ian sent me the below very good email and it started me thinking. > > > Begin Ian's email: > > > I agree. That's why I said for web apps, scroll cursors aren't a good > > idea. > > > Your solution works, but its not really cost effective because each > > time you flag to the next group, you have to rerun the query. > > > I'm a bit interested in that they're not retrieving more data and just > > caching it per session. Even this solution has trade offs and doesn't > > work when you're potentially grabbing lots of data. But 1000 rows of > > character data? Assuming that each row was 200 bytes long, that's 200K > > which could be cached either on the sever side or sent down to the > > client via ajax. When you leave the page, you can clean the page > > cache. > > > ... > > > End Ian's email > > > I don't entirely agree. I don't think that the query is that expensive > > in Informix because you are using an index to sort and scan data with. > > This is very quick to bring 100 rows back from an index. Also, I think > > most people don't page through a 1000 rows of data. I know I abandon > > my web searches after about 5 pages and try a different search if I > > haven't found what I need in that many pages. Actually I think I > > abandon even quicker than that. I have my google set to return about > > 25 hits per page. So if you cache 1000 rows of data and you only use > > 100 rows then you are wasting transmitting and processing 900 rows > > each time someone does one of these queries. I would bet that > > rerunning the query would be less expensive than that. This is kind of > > like setting read ahead to high. I'll let Art talk to that sin or you > > can just google for one of his excellent posts on the subject. Also, > > the 100 rows was an example number and doesn't really reflect what I > > would use in the real world. In the real world you could calculate a > > very good estimate for about how much data to bring back without > > wasting too much data or rerunning the query too many times. The 100 > > rows doesn't actually need to reflect the number you put on the screen > > it could be 5 times the number you put on the screen. It is actually > > your estimate of about how much data you will use typically. > > > _______________________________________________ > > Informix-list mailing list > > Informix-l...@iiug.org > >http://www.iiug.org/mailman/listinfo/informix-list I think you could even have the application tune itself based on how much data is being actually used versus retrieved. Increase the buffer is you are frequently using more data than you cache or decrease it if you are using much less data than you need.

On Mar 29, 3:22 pm, Curtis Crowson <cur...@crowson1.com> wrote: > On Mar 29, 2:28 pm, Art Kagel <art.ka...@gmail.com> wrote: > > > > > You can do something that a colleague and I named the electric slide some 15 > > years ago. In each fetch session retrieve the number of rows that you think > > the user is likely to page through, say 10 pages. As long as the users is > > only interested in those first 10 pages you've eliminated back and forth > > communications. If the user pages past the nth page SLIDE the last n/2 > > pages to the beginning of the cache and fetch the next n/2 pages to fill out > > the cache. That way your app can scroll back and forth in memory for the > > user most often and only fetch more data when the users scrolls outside of > > that window. You can electric slide the N row window across the data back > > and forth as needed. > > > Art S. Kagel > > Oninit (www.oninit.com) > > IIUG Board of Directors (a...@iiug.org) > > > Disclaimer: Please keep in mind that my own opinions are my own opinions and > > do not reflect on my employer, Oninit, the IIUG, nor any other organization > > with which I am associated either explicitly or implicitly. Neither do > > those opinions reflect those of other individuals affiliated with any entity > > with which I am affiliated nor those of the entities themselves. > > > On Sun, Mar 29, 2009 at 12:46 PM, Curtis Crowson <cur...@crowson1.com>wrote: > > > > On Mar 27, 7:47 pm, Ian Michael Gumby <im_gu...@hotmail.com> wrote: > > > > On Mar 27, 4:59 pm, Art Kagel <art.ka...@gmail.com> wrote:> You don't > > > mention what you are using for a front-end, but in most of them > > > > > including ESQL/C, 4GL, ODBC, JDBC, and others, you can take advantage > > > of > > > > > IDS's SCROLL CURSOR feature: > > > > > Since she didn't say what application or front end tools, I don't know > > > > that it would be a good idea to use a scroll cursor. > > > > If they're using a web interface, you're kind of screwed. You have to > > > > maintain state and you can't pool your connection threads if you use a > > > > scroll cursor. > > > > (The cursor has to remain active until the session times out so when > > > > you dynamically create the cursor, you'd have append the session_id to > > > > the cursor name. It can be done, but its ugly and kind of violates > > > > some of the design patterns.) > > > > > This would mean that you'd have to design a pk for the table and then > > > > track the last row retrieved and get the next set of rows. Even this > > > > has some issues in a web app... > > > > > But I do agree, if you're doing straight client/server with 4GL and or > > > > Java, yeah you can use a scroll cursor. > > > > > HTH > > > > > -G > > > > Ian sent me the below very good email and it started me thinking. > > > > Begin Ian's email: > > > > I agree. That's why I said for web apps, scroll cursors aren't a good > > > idea. > > > > Your solution works, but its not really cost effective because each > > > time you flag to the next group, you have to rerun the query. > > > > I'm a bit interested in that they're not retrieving more data and just > > > caching it per session. Even this solution has trade offs and doesn't > > > work when you're potentially grabbing lots of data. But 1000 rows of > > > character data? Assuming that each row was 200 bytes long, that's 200K > > > which could be cached either on the sever side or sent down to the > > > client via ajax. When you leave the page, you can clean the page > > > cache. > > > > ... > > > > End Ian's email > > > > I don't entirely agree. I don't think that the query is that expensive > > > in Informix because you are using an index to sort and scan data with. > > > This is very quick to bring 100 rows back from an index. Also, I think > > > most people don't page through a 1000 rows of data. I know I abandon > > > my web searches after about 5 pages and try a different search if I > > > haven't found what I need in that many pages. Actually I think I > > > abandon even quicker than that. I have my google set to return about > > > 25 hits per page. So if you cache 1000 rows of data and you only use > > > 100 rows then you are wasting transmitting and processing 900 rows > > > each time someone does one of these queries. I would bet that > > > rerunning the query would be less expensive than that. This is kind of > > > like setting read ahead to high. I'll let Art talk to that sin or you > > > can just google for one of his excellent posts on the subject. Also, > > > the 100 rows was an example number and doesn't really reflect what I > > > would use in the real world. In the real world you could calculate a > > > very good estimate for about how much data to bring back without > > > wasting too much data or rerunning the query too many times. The 100 > > > rows doesn't actually need to reflect the number you put on the screen > > > it could be 5 times the number you put on the screen. It is actually > > > your estimate of about how much data you will use typically. > > > > _______________________________________________ > > > Informix-list mailing list > > > Informix-l...@iiug.org > > >http://www.iiug.org/mailman/listinfo/informix-list > > I think you could even have the application tune itself based on how > much data is being actually used versus retrieved. Increase the buffer > is you are frequently using more data than you cache or decrease it if > you are using much less data than you need. "decrease it if you are using much less data than you need." Clearly, I don't think before I type. What I meant to say "decrease it if you are using much less data than you are caching."