Re: slow variable against int??

Please cc the list so others can help.

> From: Witold Strzelczyk [mailto:w(dot)strzelczyk(at)digitalone(dot)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 of data is very often the wrong way to go about it. In the case of ranking, I'm extremely doubtful that you'll ever get a procedure 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(dot)strzelczyk(at)gmail(dot)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...
>