asked
Alter this procedure to be more efficient
FIRST OF ALL : This is transact-sql language and SQL Anywhere 12! I think there is a better way to express the same thing, because this procedure seems like it could be written more efficiently, but i can't think how. Maybe i could use a trigger of some sort ? (I tried to do one but i couldn't think a way to update games_played with just 1. ALTER PROCEDURE "DBA"."sp_end_sologame" (IN in_player1 VARCHAR(30), IN in_player2 VARCHAR(30), IN in_endtime DATETIME, IN in_score1 INTEGER, IN in_score2 INTEGER, IN in_winner VARCHAR(30)) BEGIN UPDATE Sologames SET Endtime=in_endtime, WinnerId=(SELECT UserId FROM Users WHERE username=in_winner), pl1_rating_change= f_ratingchange(in_player1,in_player2,in_winner), pl2_rating_change= f_ratingchange(in_player2,in_player2,in_winner), pl1_score=in_score1, pl2_score= in_score2, modified= CURRENT TIMESTAMP, modifier=in_player1 WHERE First_player=(SELECT UserId FROM Users WHERE Username=in_player1) AND Second_player=(SELECT UserId FROM Users WHERE Username=in_player2) AND WinnerId IS NULL; IF in_player1 = in_winner THEN UPDATE Users SET Games_played= ((SELECT Games_played FROM Users WHERE Username=in_player1)+1), Rating = ((SELECT Rating FROM Users WHERE Username = in_player1)+f_ratingchange(in_player1,in_player2,in_winner)), Games_won = ((SELECT Games_won FROM Users WHERE Username= in_player1)+1) ELSE UPDATE Users SET Games_played= ((SELECT Games_played FROM Users WHERE Username=in_player1)+1), Rating = ((SELECT Rating FROM Users WHERE Username = in_player1)+f_ratingchange(in_player1,in_player2,in_winner)) ENDIF; IF in_player2 = in_winner THEN UPDATE Users SET Games_played= ((SELECT Games_played FROM Users WHERE Username=in_player2)+1), Rating = ((SELECT Rating FROM Users WHERE Username = in_player2)+f_ratingchange(in_player1,in_player2,in_winner)), Games_won = ((SELECT Games_won FROM Users WHERE Username= in_player2)+1) ELSE UPDATE Users SET Games_played= ((SELECT Games_played FROM Users WHERE Username=in_player2)+1), Rating = ((SELECT Rating FROM Users WHERE Username = in_player2)+f_ratingchange(in_player1,in_player2,in_winner)) ENDIF; END This does not give me any errors, but it seems very very clumsy.
You're likely to get much better Sybase answers on the Sybase SQL Anywhere forum -
http://sqlanywhere-forum.sybase.com/
I was curious about the syntax. Sybase has changed some since I used to use it (15 years ago).
I don't think you need all of those subqueries in your IF THEN UPDATE statements. For example, UPDATE Users SET Games_played= ((SELECT Games_played FROM Users WHERE Username=in_player1)+1), Rating = ((SELECT Rating FROM Users WHERE Username = in_player1)+f_ratingchange(in_player1,in_player2,in_winner)), Games_won = ((SELECT Games_won FROM Users WHERE Username= in_player1)+1) could probably be rewritten as UPDATE Users SET Games_played= Games_played+1, Rating = Rating + f_ratingchange(in_player1,in_player2,in_winner), Games_won = Games_won + 1 WHERE Username=in_player1 Another thought, if you can modify the application, is to pass in UserID instead of UserName. That way you could use the ID in the queries instead of looking it up multiple times in the `WHERE` clause. If in_player1 and in_player2 were UserIDs, this: WHERE First_player=(SELECT UserId FROM Users WHERE Username=in_player1) AND Second_player=(SELECT UserId FROM Users WHERE Username=in_player2) could become this: WHERE First_player=in_player1 AND Second_player=in_player2
16 People are following this question.
