Click here to Skip to main content
12,943,299 members (76,470 online)
Rate this:
 
Please Sign up or sign in to vote.
Hello everyone,

Below Storedprocedure takes 15 secs to execute , please suggest how to optimize this SP?

 
USE [Helpdesk_Server]
GO
/****** Object:  StoredProcedure [dbo].[sp_InsertSchoolComplaint]    Script Date: 09/26/2012 10:08:44 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		<Author,,Name>
-- Create date: <Create Date,,>
-- Description:	<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_InsertSchoolComplaint]
	-- Add the parameters for the stored procedure here
	@complaintnumber		nvarchar(50)	,
	@assettypeid			int				,
	@assetid				int				,
	@schoolid				int				,
	@stateid				int				,		
	@complainantname		nvarchar(50)	,
	@complainantcontact		nvarchar(50)	,
	@complaintdescription	nvarchar(max)	,
	@remarks				nvarchar(max)	,
	@createdby				int				,
	@emailid				nvarchar(100)	,	
	@employeecomplaintname nvarchar(max),
	@employeecomplaintcity	nvarchar(max)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	Declare @message nvarchar(20)
	begin try
	
	Insert Into [MH.Complaints]
				(
					ComplaintNumber				,
					AssetTypeId					,
					AssetId						,
					SchoolId					,					
					StateId						,					
					ReportedDate				,
					Reporteddatetime			,
					ComplainantName				,
					ComplainantContactNumber	,
					ComplaintDescription		,
					ComplaintStatusId			,
					Remarks						,
					CreatedBy					,
					isDeleted					,
					isAdminApproved				,
					EmailId									,
					EmployeeComplaint,
					EmployeeComplaintCity,
					isMailed
					
				)	
				values
				(
					@complaintnumber			,
					@assettypeid				,
					@assetid					,
					@schoolid					,					
					@stateid					,					
					GETDATE()					,
					GETDATE()					,
					@complainantname			,
					@complainantcontact			,
					@complaintdescription		,
					'5'							,
					@remarks					,
					@createdby					,
					'false'						,
					'false'						,
					@emailid 					,@employeecomplaintname ,@employeecomplaintcity	,'false'
				)
				set @message ='Yes'
			end try		
			begin catch
			set @message ='No'
			end catch
						
	Select @message as 'Message'
 
	
END
Posted 25-Sep-12 19:12pm

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

Remove the set @message you can achieve the same result by checking the return of the insert with @IDENTITY :
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/efbe2d59-9129-455e-b723-43ceb6752d56/[^]
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

    Print Answers RSS
Top Experts
Last 24hrsThis month
OriginalGriff 4,643
CHill60 2,930
Maciej Los 2,328
Jochen Arndt 1,900
ppolymorphe 1,765


Advertise | Privacy | Mobile
Web02 | 2.8.170518.1 | Last Updated 26 Sep 2012
Copyright © CodeProject, 1999-2017
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100