Re: How big is my result set?

Scott Burns wrote: > Hi, > I've a google search showed me this question has been mentioned, but > never seems to be asked or anwered directly: > > I am running SE, and have made a select cursor I traverse using "Next" > and "Prev" buttons in my C application. I want my buttons to becomes > insesnsitive when I hit either the start or the end of the set ( cant go > further, and want the user to know that ). > > I have a function that figures out if I'm at the start or end, even > taking into account any records I've deleted from my set ( but which > still have a blank entry ). My application skips over the blank > entries. Unfortunately, I need to know what the theoretically last > entry in the set is, or, in other words, how big is the set I just selected? > > At the moment I traverse the list just after selection but before > display, counting as I go. For a set of 6500 odd records this takes > well over a minute on my hardware. I could use a count(*) but it would > have to be a seperate call. I'm worried that if an entry is added or > deleted my application may either never think it is at the end, or will > miss a record. Is there a way to embed the count(*) into my select > statement I use for my cursor, returning the total count of all rows on > each row? The last time I tested the performance of scrolling through the entire set versus COUNT(*), the scrolling was faster. That could be because most of the records were read in the first buffer, but then I wouldn't allow a user to select 6500 odd records. Do they REALLY scroll through all 6500? There are aesthetic tricks you can use to hide the time of the count. Things like displaying the first record as soon as you get it, so that by the time the user reads it and presses Next, the count is done, or the delay is seen as a delay in the Next function and not the initial load. ;-) Also, you don't need to know how many records are in the set to stop at the end. You can just check the error code to see when you scroll off the end, in either direction. If you have coded First and Last options, then that's a different story. > My select statements are always in the form "SELECT <key value> FROM > <table> WHERE ..." with no order by, groups etc. Good. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+ sending to informix-list

Mark D. Stock wrote: > >The last time I tested the performance of scrolling through the entire set >versus COUNT(*), the scrolling was faster. That could be because most of >the records were read in the first buffer, but then I wouldn't allow a user >to select 6500 odd records. Do they REALLY scroll through all 6500? > > They might not scroll, but then again, they may just have a reason to. And even if they don't scroll, I want the same functionality independant of wether they choose 5 records or 500,000. Having the user scroll through to the 67,889th record and the Next button not becoming insensitive is the sort of thing that would eat at me... Also, I'm trying to reuse functionality so my scrolling could be faster if I wrote special functions just to scroll forwards, then skip back to the first record, with no feild processing. I was simply hoping for a DB supported easy fix. >There are aesthetic tricks you can use to hide the time of the count. >Things like displaying the first record as soon as you get it, so that by >the time the user reads it and presses Next, the count is done, or the >delay is seen as a delay in the Next function and not the initial load. ;-) > This would help, but it still leaves the delay there. > >Also, you don't need to know how many records are in the set to stop at the >end. You can just check the error code to see when you scroll off the end, >in either direction. If you have coded First and Last options, then that's >a different story. > This has the problem that when they hit the last record, they need to hit Next again before I'd know to make the button insensitive. Thanks for your response. -- Scott Burns Mirrabooka Systems Tel +61 7 3857 7899 Fax +61 7 3857 1368