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?

My select statements are always in the form "SELECT <key value> FROM
<table> WHERE ..." with no order by, groups etc.


--
Scott Burns
Mirrabooka Systems

Tel +61 7 3857 7899
Fax +61 7 3857 1368

You can use SQLCA structure - more specific sqlca.sqlerrd[0], but this is
only an estimate(!). Documentation says, that actual number of rows in a
result set is not guaranteed to be accurate unless you've scrolled through
the whole result set. You can get more information on SQLCA record on:
http://www.dbcenter.cise.ufl.edu/tri...lc/11.fm3.html

Two more good sites are:
http://www.dbcenter.cise.ufl.edu/tri...ex/global.html and
http://www.docs.rinet.ru:8080/InforSmes/index.htm

Gorazd

"Scott Burns" <scott (AT) mirrabooka (DOT) com> wrote

If you're using IDS 7.3x there is some very useful documentation available
at my site:
http://www.klimaexpert.com/gorazd/informix/index.html

Gorazd

"Scott Burns" <scott (AT) mirrabooka (DOT) com> wrote

Scott Burns 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?

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.

Good.

Cheers,
--
Mark.

+----------------------------------------------------------+-----------+

sending to informix-list

You can do a read ahead in your program, whenever user presses next ,
that is read not only the next, but next and next. This way you will
know when to disable the buttons. For start, it is easy, maintain a
count variable starting with zero and if count will become zero when
previous is pressed , disable previous button.

Just ur program needs to take care of this, this might decrease the
performance a bit, but since the cursor is already open, it should be
affordable. Also you can make up a bit in subsequent next button presses
as then you will already have the requested record in program.

Rgds
Preetinder

Scott Burns wrote:

sending to informix-list

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.

Success
Robert A. Reissaus
RISDI
Ibm/Informix consultants
Amsterdam, the Netherlands
ra.reissaus (AT) risdi (DOT) com
www.risdi.com

Robert A. Reissaus wrote:

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

[clipped]

What kind of isolation level are you using?

Gorazd

Gorazd Hribar Rajteric wrote:

here. The database has always been up to the job with default setings.
Of course, even with our clients in full swing I doubt they do more
than 10 update/insert/delete operations each minute. In the middle of
the night, it might be as low as 1 per minute...

--
Scott Burns
Mirrabooka Systems

Tel +61 7 3857 7899
Fax +61 7 3857 1368

Does my result set look big in this?

--
"C'est pas parce qu'on n'a rien à dire qu'il faut fermer sa gueule"
- Coluche