| Subject | Re: Record numbering |
|---|---|
| Author | Adam |
| Post date | 2006-07-15T00:55:44Z |
--- In firebird-support@yahoogroups.com, Svein Erling Tysvaer
<svein.erling.tysvaer@...> wrote:
If you are new to Firebird and don't get the joke, please don't use
this suggestion if the table has more than 50 records, or at the very
least somefield is indexed and your where constraint limits it to no
more than 50 records. There will be from my maths n^2 reads, where n
is the number of records in Mytable.
I have been following this thread and two things concern me.
Firstly, why are we asking the database server to do this work? It is
simply a client side cursor of some sort. It is just adding additional
unnecessary data to what must be sent to the client. You are going to
be transferring an additional 8 bytes per row + whatever size the null
flag is. You may slow down such a queries fetch time by 30% for no
benefit.
Secondly, most query components I have seen actually have such a RecNo
property. It wouldn't even surprise me if most grid components had an
autonumber option.
Thirdly, if yours does not, it is less than a trivial operation to
generate it on the fly at the client end.
Fourthly, it concerns me what you will do with this information. If it
is simply an indicator to the user that they are looking at a
particular 'record number', that is ok, but if you intended on using
the fact they are on record 74 in some future database interaction,
the bad design alarm bells should be ringing. Firebird does not store
records by 'record number'.
If you absolutely must get the server to generate it (and I really
doubt that is the case), I liked Ivan's FB2 based solution, it was
quite cute, but the cheapest would be a stored procedure.
Adam
<svein.erling.tysvaer@...> wrote:
>client
> With a set ordered by a unique field, this can be done as
>
> select (select count(*) + 1 from MyTable M2 where M2.Somefield <
> M1.Somefield) as RecNo, M1.Somefield
> from MyTable M1
> Order by M1.Somefield
>
> Though it doesn't work on any unordered dataset and doing it on a
> or through a stored procedure is a much better solution (though theLOL, ok you gave me a chuckle, very creative.
> above solution is quite funny and should work).
If you are new to Firebird and don't get the joke, please don't use
this suggestion if the table has more than 50 records, or at the very
least somefield is indexed and your where constraint limits it to no
more than 50 records. There will be from my maths n^2 reads, where n
is the number of records in Mytable.
I have been following this thread and two things concern me.
Firstly, why are we asking the database server to do this work? It is
simply a client side cursor of some sort. It is just adding additional
unnecessary data to what must be sent to the client. You are going to
be transferring an additional 8 bytes per row + whatever size the null
flag is. You may slow down such a queries fetch time by 30% for no
benefit.
Secondly, most query components I have seen actually have such a RecNo
property. It wouldn't even surprise me if most grid components had an
autonumber option.
Thirdly, if yours does not, it is less than a trivial operation to
generate it on the fly at the client end.
Fourthly, it concerns me what you will do with this information. If it
is simply an indicator to the user that they are looking at a
particular 'record number', that is ok, but if you intended on using
the fact they are on record 74 in some future database interaction,
the bad design alarm bells should be ringing. Firebird does not store
records by 'record number'.
If you absolutely must get the server to generate it (and I really
doubt that is the case), I liked Ivan's FB2 based solution, it was
quite cute, but the cheapest would be a stored procedure.
Adam