Stored Procedure Tuning

Question

Post reply

Stored Procedure Tuning

farooque_s

SSC Enthusiast

Points: 144
More actions
June 27, 2013 at 4:17 pm

#295203

Hi There,
I have this below mentioned stored procedure which I am executing through VB.NET what is the best way to Fine tune it, I appreciate all the ideas and suggestion, if possible with example.
USE [PRD]
GO
/****** Object: StoredProcedure [dbo].[SD_InsertInventoryItem1] Script Date: 06/27/2013 14:51:29 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[SD_InsertInventoryItem1]
-- Add the parameters for the stored procedure here
@ITEMNMBR Varchar(30),
@VENDOR Varchar(30),
@ITEMDESC Varchar(100),
@ITMSHNAM Varchar(15),
@ITMGEDSC Varchar(10),
@ITMCLSCD Varchar(10),
@ITEMTYPE smallint,
@VCTNMTHD smallint,
@TAXOPTNS smallint,
@ITMTSHID Varchar(15),
@UOMSCHDL Varchar(10),
@ITEMSHWT numeric(8,2),
@TCC Varchar(30),
@CNTRYORGN VarChar(6),
@CURRCOST Numeric(20,1),
--Parameters For Taxonomy.
@TDepartment Varchar(255),
@TSubDept Varchar(255),
@TClass Varchar(255),
@TSubClass Varchar(255),
--Parameters HTS window.
@TCustomDesc VarChar(255),
@TCountry Varchar(55),
@THSCode Numeric(6),
@TExportCode Numeric(4),
--OutPut Parameters.
@iErrorState int output,
@ErrString varchar(255) output
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Executing the Stored Procedure to Insert the Inventory Items.
If @ITEMNMBR <> (SELECT ITEMNMBR FROM IV00101 WHERE ITEMNMBR = @ITEMNMBR)
BEGIN
EXECUTE dbo.taUpdateCreateItemRcd
@I_vITEMNMBR = @ITEMNMBR,
@I_vITEMDESC = @ITEMDESC,
@I_vITMSHNAM = @ITMSHNAM,
@I_vITMGEDSC = @ITMGEDSC,
@I_vITMCLSCD = @ITMCLSCD,
@I_vITEMTYPE = @ITEMTYPE,
@I_vVCTNMTHD = @VCTNMTHD,
@I_vTAXOPTNS = @TAXOPTNS,
@I_vITMTSHID = @ITMTSHID,
@I_vUOMSCHDL = @UOMSCHDL,
@I_vITEMSHWT = @ITEMSHWT,
@I_vTCC = @TCC,
@I_vCNTRYORGN = @CNTRYORGN,
@I_vCURRCOST = @CURRCOST,
@I_vUseItemClass = 1,
@O_iErrorState = @iErrorState OutPut,
@oErrString = @ErrString Output;
EXECUTE dbo.taCreateItemVendors
@I_vITEMNMBR = @ITEMNMBR,
@I_vVNDITDSC = @ITEMDESC,
@I_vVENDORID = @VENDOR,
@I_vVNDITNUM = @ITEMNMBR,
@I_vFREEONBOARD = 1,
@O_iErrorState = @iErrorState OutPut,
@oErrString = @ErrString Output;
EXECUTE dbo.SD_InsertTaxonomyValues
@ITEMNMBR = @ITEMNMBR,
@Department = @TDepartment,
@SubDept = @TSubDept,
@Class = @TClass,
@SubClass = @TSubClass;
EXECUTE dbo.SD_InsertHTSValues
@ITEMNMBR = @ITEMNMBR,
@TCustomDesc = @TCustomDesc,
@TCountry = @TCountry,
@THSCode = @THSCode,
@TExportCode = @TExportCode
ELSE
-- Return unused error state
SET @iErrorState = 2627;
SET @ErrString = 'Duplicate Item Number, Sku Already Exists in the Database.';
END
END

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

The easy answer: You can't tune this SP.

This procedure is calling other procedures and we would need to know the definition of those stored procedures as well.

To get better help on this kind of issues, read the following article

http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 2

I agree. This proc probably isn't the problem.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)