Subject Re: Record numbering
Author Adam
--- In, Ali Gökçen <ali_gokcen@...>
> Adam,
> >
> > > ROW_COUNT is a function, defined after a succesful DML
> > > operation (including SELECT).
> >
> > Only works with selects in FB2. In FB 1.5 it excludes selects and
> > execute statement DML operations.
> >
> > >
> > > Firebird does not provide any other thing related to row numbers,
> > > row counts or anything.
> >
> > I think the point Ali is trying to make (with some strange
> arguments)
> > is that in order to return ROW_COUNT from a select statement, it
> must
> > be adding 1 to a counting mechanism while each record is returned. A
> > call to ROW_COUNT simply returns this counter value. If a counting
> > mechanism was not maintained during the normal processing, then the
> > only way to implement ROW_COUNT would be to repeat the query.
> only way to implement ROW_COUNT would be to repeat the qury
> may not help here. it doesnt work with readcommited isolation mode.
> ( we need a nested snapshot isolation for a pair command in this case)
> some records may be deleted, some updated and some inserted in
> multiuser environment...
> it will equal to:
> select * from foo;
> select count(*) from foo;
> the only valid way to count after operation is reread all cursor
> buffer and count them. is FB cursors buffered or rereadable? is it a
> cheap or logical way?
> ROW_COUNT may be a function that returns the isolated counter
> variable value. but we are not talking about current coding style.
> what i said was: if you have a valid ROW_COUNT value, that proofs
> you also counting result rows.
> >
> > Providing thie counter is somewhere accessible by the part of the
> > engine that is processing the fetch, it could output the current
> > counter value for each record. After all the sequence number of the
> > final record equals the ROW_COUNT. But just because a count is being
> > maintained somewhere, doesn't necessarily make it easy to retrieve
> at
> > a point in time, and if selects always return 0 for ROW_COUNT in FB
> > 1.5, then there is no evidence that such a counter is even in place
> > until FB2.
> >
> > >
> > > Forget the hippos, the reports, the semi-smart managers etc...
> >
> > Agreed. Tell the semi-smart managers that the way they want to do
> > things will make it 30% slower to retrieve the records, increase
> data
> > transmission costs by 30%, increase the chance of connection dropout
> > during transfer by 30% and deliver no real benefits. If they still
> > insist on doing it a way we know is less than ideal, then they
> > probably don't deserve the title of semi-smart, document the
> > conversation where the manager has over-ruled your expertise and do
> as
> > they ask with one of the mechanisms that has been proposed (stored
> > procedures, context variables or selecting counts).
> >
> > Do not expect developers who have useful features like asynchronous
> > statement cancelling and proper SMP support with SS to drop what
> they
> > are doing to develop such a feature.
> >
> > Adam
> >
> %30 transmissiýn costs for an extra 4 or 8 byte integer in SQL data
> packets? i disagre at this point. FB need more serious optimisations
> about client communications.

Disagree all you like, it is transmission costs are obviously relative
to the size of the record returned. If your query is already returning
5KB per record, than an additional bigint field probably isn't a
worry, but if you are only returning a small amount of data per
record, then it becomes quite significant.

jasajona wrote:

RecNo SomeField
----- --------------
1 sdferfer
2 ertertt dfgdfg
3 df ;dlfklee

Presumably we are looking at an integer or bigint field for RecNo and
a varchar field for SomeField.

In fact, the above example could be varchar(14) for all we know, and
if RecNo is a bigint then you are looking at about 33% of the data.

Also don't forget the NULL flag takes up some space too (but I do not
know how much). The point is that the usage must rise and the benefit
is not there.

Optimisations are hard to implement without breaking backwards
compatibility which is a high priority.

> These semi% persons are not and can not be my coworkers, they are
> customers.
> Their brain washed with ora, their checklist is full about high level
> SQL usage properties.
> I'm not a computer professional at now and i won't be anymore.
> i'm not even interested with IT/IS technologies other than FB.
> I don't have any expectation (commertial or personal) from FB also.
> It was only a cosmetic suggest.

If the customer has requested it, and you have advised them that it is
an overhead and they insist it returned by the database level rather
than implemented at an application server. The suggestion could easily
be implemented in the client connection components and you then have
the best of both worlds. This is a separate issue to the business
requirements of a customer though.