Re: How big is my result set?

Robert A. Reissaus wrote: >Keep in mind that whenever you use a count(*) to determine how many >records comply to the selection criteria you performace degrades >tremendously because the count(*) needs to go thru the entire table to >do it's job, just as the 'select' would. And there is no need for it. > >*Assuming* you are using a limited array to fill every time 'next >(page)' is hit you can simply do a ' fetch next' to determin whether >you are at the end of your 'fille array' selection. If so set a flag >(i.e. ' end_of_table' to true) and use that to disable your keys. >Don't forget to do a ' fetch previous' if you DID NOT reach the end of >the table. > > > Mostly. It's made a little bit more tricky because if one of the records in my reult set is deleted, there is still a place holder for it. I don't recall exactly what sits there as I worked around it several months ago. My problem is that just because fetch next returns a record doesn't mean that it's a real record or that it's valid for my program to leave the next button sensitive. I worked around this by storing a linked list of the indexes I delete and checking against this, possibly moving on another record in the result set (which in turn may have been deleted, and so on). What we end up with is a series of checks, which while it won't be as bad as running through all possible records and then back again, it still has to be done every time the user moves forward. Admissibly, I still have to check against my deleted list when I know the max size, though. The program is quite intensive when changing records anyway. All I have in the result set is the key of the table, and I then go through and run a second SQL to get the current data and populate the screen. This SQL is dynamically generated depending on what fields are being displayed in the application. I've redone my procedure to run through all records in the set, counting as it goes, and it's down to 3 seconds for 6500 records, and I can probably halve that if I go straight to the first record rather than scanning backwards. This is still ( I think ) slower than a count(*) but it's liable to be more accurate if people are adding or deleteing regularly. Whichever way I do it, though, I'm duplicating work that Informix did when it went through and made the result set for me. While tears are a while off, the time for Mr Keyboard to meet Mr Monitor is drawing near. -- Scott Burns Mirrabooka Systems Tel +61 7 3857 7899 Fax +61 7 3857 1368