Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
filf
Yak Posting Veteran
67 Posts |
 Posted - 2001-07-18 : 06:00:07
|
| I was prompted by the current discussion on row locking to investigate an alternative to using cursors in my code. Now I have implemented a row based solution which tested fine on my local setup, but when run live takes forever, I even on one occaion had sql server buffer manager issue a stop request. The thing is the new solution uses half as much code as the cursor based solution, which to me seems much more elegant but these unknown problems are becoming a pain.Now I did have some locking specified but I have removed that, what I want to ensure is that there are not further problems with my sql, which there could be.Here is the sql statement I belive to be causing problems.UPDATE team SET daily_total = (SELECT SUM(daily_total) FROM scores_temp INNER JOIN team_players ON scores_temp.celebrity_id = team_players.celebrity_id WHERE team_players.team_no = team.team_no AND player_state<>'Substitute' GROUP BY team_players.team_no)WHERE team_state = 'Active' AND score_delay = 0 UPDATE team SET weeks_total = weeks_total+daily_total, team_total = team_total+daily_total WHERE daily_total > 0Would it be better to use a second inner join on the team and team_players table rather than the statement WHERE team_players.team_no = team.team_no because team is not referenced in the select statement, but is in the actual update. Any help/advice on this would be greatly appreciated because this morning a stored procedure calling this statement as well as a number of others took over 28 minutes for only 2178 records. I really don't want to have to revert to my cursor based solution because of what I have read but seemingly my new solution is grossly inefficient compared to the original solution. I am sure it is my SQL. |
|
|
|
|
|
Table update takes an age for only 2178 recs