As one of the more experienced DBAs at RBC (Really Big Corporation, Inc), Marle was frequently called upon to review changes being proposed by her younger, less worldly-wise colleagues. This includes not just "pure" DBAs but also DBA wannabes (otherwise known as developers). Marle had no problem with this part of her job. People had mentored her when she was just getting started and paying back was just good karma. It was also, occasionally, the source for a really good WTF.

On a Tuesday morning, the following stored procedure to review came across her desk.

--declare @cnt int
--declare @searchuserid int
declare @tempid int
set @tempid = 931200
--set @cnt = 1
--while @cnt > 0
--set @searchuserid = @tempid
--set @cnt = (select count(*) from user_table where UserLoginName = @searchuserid)
--if @cnt=1
--set @tempid = @tempid + 1
set @tempid = (select max(UserLoginName) from user_table where UserLoginName between @tempid and @tempid + 100000)
set @tempid = @tempid + 1
return @tempid

A note was included with the review request.

"Production was timing out on inserts. The more rows that went into user_table, the longer it took to run, until it finally the connection timeout was exceeded. This stored procedure was identified as the root of the problem. I ended up refactoring it to use a select MAX with a 'between' clause that had zero performance impact."


First off, the analysis of the original code is accurate. The performance curve of the original procedure resembles the aerodynamic characteristics of a 2-ton boulder. However, the 'fix' is not nearly the improvement the author hopes. While it's true that, since the UserLoginName column is indexed, using MAX and BETWEEN will be performant, it has the stink of a skunk-scented dog.

Marle added a note to the review.

"You might want to look into making UserLoginName an IDENTITY column and use SCOPE_IDENTITY to get the id for the inserted record"

With a quiet sigh, she and clicked Send.

"Just a developer", she thought. "But there's still time to fix him up so he'd make a passable DBA."

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!