Please cc the list so others can help. > From: Witold Strzelczyk [mailto:w.strzelczyk@xxxxxxxxxxxxx] > 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.strzelczyk@xxxxxxxxx > > > > > > ---------------------------(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... >
