Optimized sp - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Optimized sp

Author

Topic

sardinka
Posting Yak Master

142 Posts

Posted - 2005-07-21 : 10:57:03

Is it some how The following sp can be optimized?
IF @groupID='812846'
BEGIN
IF (SELECT count(*) from Employee where SSN= @SSN and groupID=@groupID) > 0
BEGIN
UPDATE Employee
SET NameLast=@LastName,
NameFirst=@FirstName,
NameMiddle=@MI,

WHERE SSN= @SSN and GroupId=@GroupId
select @EmpId=EmpId from Employee where SSN= @SSN and groupID=@groupID
END
ElSE
BEGIN
insert into Employee (GroupId, NameLast, NameFirst, NameMiddle,SSN)
values (@GroupId, @LastName, @FirstName, @MI, @SSN)
select @EmpId = @@IDENTITY
END

END

else
BEGIN
insert into Employee (GroupId, NameLast, NameFirst, NameMiddle, SSN)
values
(@GroupId, @LastName, @FirstName, @MI, @SSN)
select @EmpId = @@IDENTITY
END

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2005-07-21 : 16:00:03

Replace the "SELECT Count(*)..." with an "EXISTS(SELECT *...)". The COUNT will cause the entire table to be visited when all you really care about is if at least one exists. The EXISTS will terminate once it finds the first occurrence.

I don't know what your indexing is on the Employee table but I'd want to be performing the UPDATE based on a unique key, hopefully clustered. This might be (SSN, GroupID) or it might be the EmpID, or both. If it is the EmpID column, this would involve moving the SELECT @EmpID before the UPDATE and using it in place of the SELECT count(*). e.g., Try to get the EmpID and if there is none, perform the update, else INSERT.

I'd replace the @@IDENTITY function with Scope_Identity(). It's not faster, that I know of, but it's a bit more bulletproof.

HTH

=================================================================
The most tyrannical of governments are those which make crimes of opinions, for everyone has an inalienable right to his thoughts. -Baruch Spinoza, philosopher (1632-1677)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources