Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Can i make this stored procedure more efficent?

Posted on 2007-11-25
8
Medium Priority
?
149 Views
Last Modified: 2012-06-22
I've got a sp i.e. below - which i'm looking to run anything from 0 to 1million times per hour, as such i'm wondering if theres any way i can make this more efficent?

E.g. Merge Call 2 and 3 maybe?
Create PROCEDURE [dbo].StoredProcedureA
 
	@CompanyId int,
	@UsersId int,
	@SentCount int out
 
AS
	 SET NOCOUNT ON
begin
 
------------------
-- CALL 1
------------------
Update Users 
set STATUSID=3 
WHERE UsersId = @UsersId
 
------------------
-- CALL 2
------------------
UPDATE [Company]
	SET 
		countA = countA+1
		,countB = countB+1
	WHERE CompanyId =@CompanyId
 
------------------
-- CALL 3
------------------
select top 1 @SentCount=countA 
from [Company] 
WHERE CompanyId =@CompanyId
 
end

Open in new window

0
Comment
Question by:paulCardiff
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20346157
i am not sure what exactly u mean by call 1 ,2 and 3
In case you need to perform all the 3 operations with each call, then you need to encapsulate this inside a transaction. Now, in case only one expression need to be executed with a call, then better u modify this as 3 separate sps.
also make sire that you have indexes on the columns which is there in the where clause
0
 

Author Comment

by:paulCardiff
ID: 20346183
Thanks for that
What would be the best way to put this in a single transaction ?
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 20346196

BEGIN TRAN 
 
Update Users 
set STATUSID=3 
WHERE UsersId = @UsersId
 
IF @@ERROR<>0 
BEGIN 
	ROLLBACK TRAN
	SELECT -201 AS RetVal
	RETURN
END 
------------------
-- CALL 2
------------------
UPDATE [Company]
        SET 
                countA = countA+1
                ,countB = countB+1
        WHERE CompanyId =@CompanyId
 
IF @@ERROR<>0 
BEGIN 
	ROLLBACK TRAN
	SELECT -202 AS RetVal
	RETURN
END  
------------------
-- CALL 3
------------------
select top 1 @SentCount=countA 
from [Company] 
WHERE CompanyId =@CompanyId
IF @@ERROR<>0 
BEGIN 
	ROLLBACK TRAN
	SELECT -203 AS RetVal
	RETURN
END 
 

Open in new window

0
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

 

Author Comment

by:paulCardiff
ID: 20346218
do i need to say comit at the end?
0
 

Author Comment

by:paulCardiff
ID: 20346223
sorry also what does " SELECT -202 AS RetVal" do?
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 20346257
Yup , you need a COMMIT TRANS statement at the end ...
In case of an error , the sp throws an error value of -201/-202/-203 ; and based on this return value you can set an appropriate error message

BEGIN TRAN

Update Users
set STATUSID=3
WHERE UsersId = @UsersId

IF @@ERROR<>0
BEGIN
      ROLLBACK TRAN
      SELECT -201 AS RetVal
      RETURN
END
------------------
-- CALL 2
------------------
UPDATE [Company]
        SET
                countA = countA+1
                ,countB = countB+1
        WHERE CompanyId =@CompanyId

IF @@ERROR<>0
BEGIN
      ROLLBACK TRAN
      SELECT -202 AS RetVal
      RETURN
END  
------------------
-- CALL 3
------------------
select top 1 @SentCount=countA
from [Company]
WHERE CompanyId =@CompanyId
IF @@ERROR<>0
BEGIN
      ROLLBACK TRAN
      SELECT -203 AS RetVal
      RETURN
END
 
CoMMIT TRAN
SELECT 1 AS RetVal --retval 1 means the operations were successfull

END
0
 
LVL 6

Expert Comment

by:PaultheBroker
ID: 20347268
Check out this cool OUTPUT feature of MSSQL2005: it would prevent you having to rescan the whole of the company statement in CALL 3, so might be marginally more efficient if you are really worried about the processing time.....
http://msdn2.microsoft.com/en-us/library/ms177564.aspx

I think it would work something like this in your case
DECLARE @company table (countA int)
....(update statement here)
OUTPUT  inserted.countA INTO @company
SELECT @SentCount=FIRST(countA) FROM @company

The inserted table contains the value of whatever you've just inserted, so in this case it will be 'countA + 1'....
0
 
LVL 12

Expert Comment

by:kselvia
ID: 20348225
This is probably even faster;

Update Users
set STATUSID=3
WHERE UsersId = @UsersId
AND STATUSID <> 3   -- <-- Prevent updating if the value is already set

-- Get the value of @SentCount when we update the Company table
UPDATE [Company]
      SET
      @SentCount = countA = countA+1   -- <-- Here
            ,countB = countB+1
      WHERE CompanyId =@CompanyId
 

And TOP 1 really had no meaning here;

select top 1 @SentCount=countA
from [Company]
WHERE CompanyId =@CompanyId


I know what you are going to say;  "It means the FIRST matching row", but if there is more than one row, what is the FIRST without an ORDER BY?  I suspect there is only 1 matching row since CompanyID looks like the primary key of [Company].

Also for what it's worth in the UPDATE with OUTPUT, OUTPUT goes before the WHERE clause.  Since the accepted solution returns RetVal, he is now prepared to read the resultset of the proc.  In that case, eliminate the OUTPUT to a table variabale and just output CountA as the resultset (which is the default for OUTPUT anyway)  Still, the method I posted above is even better.

Experts - here is the challenge; How can he write both update statements in one batch to eliminate the need for BEGIN/END TRANSACTION?  I'm not sure it's possible.  Now that the SELECT from company is  elimianted, I'm not sure he needs the transaction anyway.
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.
Viewers will learn how the fundamental information of how to create a table.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question