Thread: slow variable against int??
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
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 > -- Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com Pervasive Software http://pervasive.com work: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
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... >