在SQL中优化插入查询 [英] Optimization of insert query in sql

问题描述

大家好，

下面的Storedprocedure需要15秒才能执行，请提出如何优化此SP的建议?

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

推荐答案

删除set @message可以通过使用@IDENTITY检查插入的返回来实现相同的结果:
http://social.msdn.microsoft.com /Forums/zh-CN/transactsql/thread/efbe2d59-9129-455e-b723-43ceb6752d56/ [

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/[^]

这篇关于在SQL中优化插入查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！