Subject Re: how do i speed this up
Author martinknappe
Hi Sven,
in case you're still interested, your procedure works now; I've
changed it a bit:

asterm_in varchar(240),
id_in bigint)
returns (
id_out bigint,
pos integer)
declare variable id_temp bigint;
declare variable asterm_temp varchar(240) character set unicode_fss;
pos = 0;
id_temp = :id_in;
asterm_temp = :asterm_in;
while (1 = 1) do
select min(asterm) from dicentries where ((asterm = :asterm_temp
and id >= :id_temp)) or (asterm > :asterm_temp) into :asterm_temp;
if (asterm_temp is null) then
for select id from dicentries where asterm = :asterm_temp and id
>= :id_temp order by id ascending into :id_out do
pos = pos + 1;
if (:pos = 20) then
id_temp = :id_out + 1;

Also, see my other post from an hour ago for something very strange
with firebird 1.5:



--- In, "martinknappe" <martin@...>
> --- In, Svein Erling Tysvaer
> <svein.erling.tysvaer@> wrote:
> >
> > Hi again!
> >
> > Your procedure is similar to what I suggested, but probably a bit
> > slower. At first glance, I do not see how it could return NULL, but I
> > haven't looked closely since this may be gone when you clean up your
> > procedure and if not, then it could possibly be easily
circumvented by
> > adding WHERE IDOUT IS NOT NULL to your calling query.
> well, that would prevent entries w/o an idout value from appearing in
> the result set, but it's not the solution to the problem; it's just a
> cover-up which effectively hides data away from the user when he is
> really requesting a matter of fact, for a simple test, i
> changed my procedure in such a way that it also returned the proper
> value for asterm of the result set entries..what i did then was copy
> asterm of those entries that had null in their id field and queried
> the following:
> select id from dicentries where asterm = copiedValue
> and it did give me an id so this shows that the id really exists..i
> don't know what's going on nearly so far as to claim this is
> a bug..
> > You seem to prefix variables with a colon everywhere.
> afaik that's absolutely necessary
> > Moreover, SET TERM is part of the
> > stored procedure language, so I would try to avoid having TERM as
> > the name of a parameter.
> ok, i tried with a different identifier; didn't help either
> > You combine SELECT FIRST with counting manually,
> > and that is just wasting time and resources.
> i know, i tried "select min(id)..." before but then tried with "first"
> just to see if that was the really kept me busy for a good
> while yesterday :-(
> > And finally, is ASTERM at
> > all indexed
> yes it is; in the first post of this thread theres a link to the sql
> script for creating the database where you can look everything up
> i've nearly given up..what ive done now is some kind of a
> workaround...whereas before the select procedure my client user would
> invoke did something like:
> select first 20 id from dicentries
> where (asterm = :asterm and id >= :id) or (asterm > :asterm)
> order by asterm ascending, id ascending
> i now changed the procedure so that it be more selective:
> select first 20 id from dicentries
> where ((asterm = :asterm and id >= :id) or (asterm > :asterm)) and
> (asterm <= :upperbound)
> order by asterm ascending, id ascending
> so now it's in the user's hand to make response time shorter by simply
> "cutting off" the end of the table, which he wouldn't have seen in his
> result set anyway...not the nices of solutions but better than keep
> the user waiting for 7 seconds on every page scroll...
> thanx anyway,
> martin