Performance Issue - 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 2008 Forums

Transact-SQL (2008)

Performance Issue

Author

Topic

Prabu.Ramaiah
Starting Member

15 Posts

Posted - 2011-09-12 : 03:54:45

Hi

The following procedure getting more time to execute. Some one could optimize the procedure.

ALTER PROCEDURE [dbo].[sp_RQ_UpdateInformation]
-- Add the parameters for the stored procedure here
@ProgrammeID int,
@CompanyID int ,
@IsSubmitted bit ,
@UserId int,
@UserLogIn varchar(100)

AS
BEGIN

BEGIN TRANSACTION
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

--1
UPDATE tblCompany
SET [Name] = (SELECT tblRenewalQuestionnaire.Answer
FROM tblRenewalQuestionnaire
WHERE tblRenewalQuestionnaire.CompanyID = @CompanyID
AND tblRenewalQuestionnaire.ProgrammeID = @ProgrammeID
AND tblRenewalQuestionnaire.AnswerID = 1
AND tblRenewalQuestionnaire.IsSubmitted = @IsSubmitted
)
WHERE CompanyID = @CompanyID

IF (@@ERROR <> 0) GOTO ERR_HANDLER
----2
UPDATE tblCompany
SET [Address] = (SELECT tblRenewalQuestionnaire.Answer
FROM tblRenewalQuestionnaire
WHERE tblRenewalQuestionnaire.CompanyID = @CompanyID
AND tblRenewalQuestionnaire.ProgrammeID = @ProgrammeID
AND tblRenewalQuestionnaire.AnswerID = 2
AND tblRenewalQuestionnaire.IsSubmitted = @IsSubmitted
)
WHERE CompanyID = @CompanyID

IF (@@ERROR <> 0) GOTO ERR_HANDLER
----3TradingCompany
UPDATE tblCompany
SET [Website] = (SELECT tblRenewalQuestionnaire.Answer
FROM tblRenewalQuestionnaire
WHERE tblRenewalQuestionnaire.CompanyID = @CompanyID
AND tblRenewalQuestionnaire.ProgrammeID = @ProgrammeID
AND tblRenewalQuestionnaire.AnswerID = 3
AND tblRenewalQuestionnaire.IsSubmitted = @IsSubmitted
)
WHERE CompanyID = @CompanyID

End

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 04:08:02

why seperate updates? isnt this enough?


UPDATE c
SET c.[Name] = t.[Name],
c.[Address] = t.[Address],
c.[Website] = t.[Website]
FROM tblCompany c
JOIN(SELECT tblRenewalQuestionnaire.CompanyID,MAX(CASE WHEN tblRenewalQuestionnaire.AnswerID = 1 THEN tblRenewalQuestionnaire.Answer ELSE NULL END) AS [Name],
MAX(CASE WHEN tblRenewalQuestionnaire.AnswerID = 2 THEN tblRenewalQuestionnaire.Answer ELSE NULL END) AS [Address] ,
MAX(CASE WHEN tblRenewalQuestionnaire.AnswerID = 3 THEN tblRenewalQuestionnaire.Answer ELSE NULL END) AS [Website] 
FROM tblRenewalQuestionnaire 
WHERE tblRenewalQuestionnaire.CompanyID = @CompanyID 
AND tblRenewalQuestionnaire.ProgrammeID = @ProgrammeID 
AND tblRenewalQuestionnaire.IsSubmitted = @IsSubmitted
GROUP BY tblRenewalQuestionnaire.CompanyID 
) t
ON t.CompanyID = c.CompanyID 
WHERE c.CompanyID = @CompanyID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Kristen
Test

22859 Posts

Posted - 2011-09-12 : 04:11:20

I would do it like this:



UPDATE U
SET	[Name] = CASE WHEN RQ1.CompanyID IS NOT NULL THEN RQ1.Answer ELSE U.[Name],
	[Address] = CASE WHEN RQ2.CompanyID IS NOT NULL THEN RQ2.Answer ELSE U.[Address],
	[Website] = CASE WHEN RQ3.CompanyID IS NOT NULL THEN RQ3.Answer ELSE U.[Website]
FROM tblCompany AS U
	LEFT OUTER JOIN tblRenewalQuestionnaire AS RQ1
		 ON RQ1.CompanyID = @CompanyID 
		AND RQ1.ProgrammeID = @ProgrammeID 
		AND RQ1.AnswerID = 1 
		AND RQ1.IsSubmitted = @IsSubmitted 
	LEFT OUTER JOIN tblRenewalQuestionnaire AS RQ2
		 ON RQ2.CompanyID = @CompanyID 
		AND RQ2.ProgrammeID = @ProgrammeID 
		AND RQ2.AnswerID = 2
		AND RQ2.IsSubmitted = @IsSubmitted 
	LEFT OUTER JOIN tblRenewalQuestionnaire AS RQ3
		 ON RQ3.CompanyID = @CompanyID 
		AND RQ3.ProgrammeID = @ProgrammeID 
		AND RQ3.AnswerID = 1 
		AND RQ3.IsSubmitted = @IsSubmitted 
WHERE CompanyID = @CompanyID

You need an index on tblRenewalQuestionnaire with key fields CompanyID, ProgrammeID, AnswerID, IsSubmitted and an INCLUDE column of Answer

Run these tests
1) SELECT COUNT(DISTINCT CompanyID) FROM tblRenewalQuestionnaire
2) SELECT COUNT(DISTINCT ProgrammeID) FROM tblRenewalQuestionnaire

If (1) > (2) use the key field sequence above, otherwise use ProgrammeID, CompanyID, AnswerID, IsSubmitted and an INCLUDE column of Answer

Kristen
Test

22859 Posts

Posted - 2011-09-12 : 04:17:54

"isnt this enough?"

Dumno if relevant but I was assuming that one/many of the Answers may not exist/match the criteria for @IsSubmitted, and tblCompany columns may already contain a value (needing to be preserved)

But if there are always answers available your way would be more efficient.

In which case same applies to the Index, except move AnswerID from the key fields to the Include columns

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-12 : 04:21:34

quote:
Originally posted by Kristen

"isnt this enough?"

Dumno if relevant but I was assuming that one/many of the Answers may not exist/match the criteria for @IsSubmitted, and tblCompany columns may already contain a value (needing to be preserved)

But if there are always answers available your way would be more efficient.

In which case same applies to the Index, except move AnswerID from the key fields to the Include columns

yep thats a point
in which we need a small tweak


UPDATE c
SET c.[Name] = COALESCE(t.[Name],c.[Name]),
c.[Address] = COALESCE(t.[Address],c.[Address]),
c.[Website] = COALESCE(t.[Website],c.[Website])
FROM tblCompany c
JOIN(SELECT tblRenewalQuestionnaire.CompanyID,MAX(CASE WHEN tblRenewalQuestionnaire.AnswerID = 1 THEN tblRenewalQuestionnaire.Answer ELSE NULL END) AS [Name],
MAX(CASE WHEN tblRenewalQuestionnaire.AnswerID = 2 THEN tblRenewalQuestionnaire.Answer ELSE NULL END) AS [Address] ,
MAX(CASE WHEN tblRenewalQuestionnaire.AnswerID = 3 THEN tblRenewalQuestionnaire.Answer ELSE NULL END) AS [Website] 
FROM tblRenewalQuestionnaire 
WHERE tblRenewalQuestionnaire.CompanyID = @CompanyID 
AND tblRenewalQuestionnaire.ProgrammeID = @ProgrammeID 
AND tblRenewalQuestionnaire.IsSubmitted = @IsSubmitted
GROUP BY tblRenewalQuestionnaire.CompanyID 
) t
ON t.CompanyID = c.CompanyID 
WHERE c.CompanyID = @CompanyID

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources