Re: slow variable against int??

Please cc the list so others can help.

> From: Witold Strzelczyk [mailto:w.strzelczyk@digitalone.pl]
> On Friday 12 May 2006 00:04, you wrote:
>
> Yes, thanks but method is not a  point.

Actually, it is a point. Databases don't like doing things procedurally. Using a stored procedure to operate on a set
ofdata is very often the wrong way to go about it. In the case of ranking, I'm extremely doubtful that you'll ever get
aprocedure to opperate anywhere near as fast as native SQL. 

> Can You tell me why
>
>         select into inGameRating  count(game_result)+1
> from users
>          where game_result > 2984;
>
> tooks ~100 ms and
>
>          select into inGameRating  count(game_result)+1
> from users
>          where game_result > inRow.game_result;
>
> where inRow.game_result = 2984 tooks ~1100 ms!?

No, I can't. What's EXPLAIN ANALYZE show?

> btw. I must try your temp sequence but if it is not as quick
> as my new (and
> final) function I'll send if to you.
>
> > If you're trying to come up with ranking then you'll be much happier
> > using a sequence and pulling from it using an ordered
> select. See lines
> > 19-27 in http://lnk.nu/cvs.distributed.net/9bu.sql for an example.
> > Depending on what you're doing you might not need the temp table.
> >
> > On Fri, May 05, 2006 at 04:46:43PM +0200, Witold Strzelczyk wrote:
> > > I have a question about my function. I must get user
> rating by game
> > > result. This isn't probably a perfect solution but I have
> one question
> > > about
> > >
> > > select into inGameRating  count(game_result)+1 from users
> > >         where game_result > inRow.game_result;
> > >
> > > This query in function results in about 1100 ms.
> > > inRow.game_result is a integer 2984
> > > And now if I replace inRow.game_result with integer
> > >
> > > select into inGameRating  count(game_result)+1 from users
> > >         where game_result > 2984;
> > >
> > > query results in about 100 ms
> > >
> > > There is probably a reason for this but can you tell me
> about it because
> > > I can't fine one
> > >
> > > My function:
> > >
> > > create or replace function ttt_result(int,int) returns setof
> > > tparent_result language plpgsql volatile as $$
> > > declare
> > >     inOffset alias for $1;
> > >     inLimit alias for $2;
> > >     inRow tparent_result%rowtype;
> > >     inGameResult int := -1;
> > >     inGameRating int := -1;
> > > begin
> > >
> > > for inRow in
> > >     select
> > >         email,wynik_gra
> > >     from
> > >         konkurs_uzytkownik
> > >     order by wynik_gra desc limit inLimit offset inOffset
> > > loop
> > >     if inGameResult  < 0 then -- only for first iteration
> > >         /* this is fast ~100 ms
> > >         select into inGameRating
> > >             count(game_result)+1 from users
> > >             where game_result >     2984;
> > >         */
> > >         /* even if inRow.game_result = 2984 this is
> very slow ~ 1100 ms!
> > >         select into inGameRating  count(game_result)+1
> from users
> > >         where game_result > inRow.game_result;
> > >         */
> > >         inGameResult  := inRow.game_result;
> > >     end if;
> > >
> > >     if inGameResult  > inRow.game_result then
> > >         inGameRating  := inGameRating  + 1;
> > >     end if;
> > >
> > >     inRow.game_rating := inGameRating;
> > >     inGameResult       := inRow.game_result;
> > >     return next inRow;
> > >
> > > end loop;
> > > return;
> > > end;
> > > $$;
> > > --
> > > Witold Strzelczyk
> > > witek.strzelczyk@gmail.com
> > >
> > > ---------------------------(end of
> broadcast)---------------------------
> > > TIP 9: In versions below 8.0, the planner will ignore
> your desire to
> > >        choose an index scan if your joining column's
> datatypes do not
> > >        match
>
> --
> Witold Strzelczyk
>
>   : :   D i g i t a l  O n e  : :  http://www.digitalone.pl
>   : :   Dowborczykow 25  Lodz  90-019  Poland
>   : :   tel. [+48 42] 6771477  fax [+48 42] 6771478
>
>    ...Where Internet works for effective business solutions...
>