|
ramesh31
Starting Member
11 Posts |
 Posted - 2007-07-18 : 17:11:14
|
| Hi,I have to fine tune a sp which use Cursors and it take 1 min 32 sec to execute.I have attached the code the below , any suggestions would be appreciated. Create Procedure dbo.PostTSPData @bUpdatePriceGroup int, @bUpdateReportGroup int, @bUpdateSPAGroup int, @bUpdateSPAGroup1 int, @bZeroPrices int, @bUpdateDesc int, @VNDNo VARCHAR(6) AS BEGIN SET NOCOUNT ON DECLARE @ProductId AS INT DECLARE @COL1PRICE AS FLOAT DECLARE @OLDCOL1PRICE AS FLOAT DECLARE @COL2PRICE AS FLOAT DECLARE @OLDCOL2PRICE AS FLOAT DECLARE @COL3PRICE AS FLOAT DECLARE @OLDCOL3PRICE AS FLOAT DECLARE @LISTPRICE AS FLOAT DECLARE @OLDLISTPRICE AS FLOAT DECLARE @DISTCOST AS FLOAT DECLARE @OLDDISTCOST AS FLOAT DECLARE @DESC AS CHAR(33) DECLARE @LOCALMFRCODE AS CHAR(5) DECLARE @MFRCODE AS CHAR(5) DECLARE @LOCALCATALOGNO AS CHAR(20) DECLARE @CATALOGNO AS CHAR(20) DECLARE @LOCALDESCRIPTION AS CHAR(60) DECLARE @DESCRIPTION AS CHAR(60) DECLARE @COMMCODE AS CHAR(4) DECLARE @TSPCODE AS CHAR(1) DECLARE @VNDCODE1 AS CHAR(6) DECLARE @ITEMCODE AS CHAR(5) DECLARE @UPC AS CHAR(11) DECLARE @CARTONQTY AS CHAR(20) /*INT*/ DECLARE @PRICEUOM CHAR(1) DECLARE @LOCALPRICEUOM CHAR(1) DECLARE @DISCPERCENT FLOAT DECLARE @OLDDISCPERCENT FLOAT DECLARE @LASTMODIFIED AS DATETIME DECLARE @RPTGRPCODE VARCHAR(8) DECLARE @SPAGRPCODE VARCHAR(20) DECLARE @PRICEGRPCODE VARCHAR(8) DECLARE @MASTERSPAGRPCODE VARCHAR(20) DECLARE @COUNT INT DECLARE @ISRPTADDED BIT DECLARE @ISSPAADDED BIT DECLARE @ISPRICEADDED BIT DECLARE @CARTONUPDATE BIT DECLARE @UPDATENOTALLOWED BIT DECLARE @ORGUNITID AS INT DECLARE @PRODCARTONQTY CHAR(20) /*INT */ SET @ISRPTADDED = 0 SET @ISSPAADDED = 0 SET @ISPRICEADDED = 0 SET @CARTONUPDATE = 0 SET @UPDATENOTALLOWED = 0 SELECT TOP 1 @ORGUNITID = PROFITCENTERID FROM PROFITCENTER SET @COUNT = 0 IF EXISTS (SELECT * FROM sysobjects WHERE name = 'PostTSPDataTables') BEGIN DROP TABLE dbo.PostTSPDataTables END CREATE TABLE dbo.PostTSPDataTables(VNDCODE CHAR(6), ITEMCODE CHAR(5), COMMCODE CHAR(4), LOCALMFR CHAR(5), LOCALCATALOGNO CHAR(20),LOCALDESCRIPTION VARCHAR (60), MFR CHAR(5), CATALOGNO CHAR(20), DESCRIPTION VARCHAR (60), CARTONQTY CHAR(20), DISTCOST FLOAT, LISTPRICE FLOAT,COL1PRICE FLOAT, COL2PRICE FLOAT, COL3PRICE FLOAT, OLDCOL3PRICE FLOAT, PRICEUOM CHAR(1), LOCALPRICEUOM CHAR(1), DISCPERCENT FLOAT, COL3GP FLOAT, TSPCODE CHAR(1), ISRPTADDED BIT, ISSPAADDED BIT, ISPRICEADDED BIT, CARTONUPDATEFAILED BIT, UPDATENOTALLOWED BIT ) IF EXISTS (SELECT * FROM sysobjects WHERE name = 'TMPVENDORS') BEGIN DROP TABLE dbo.TMPVENDORS END CREATE TABLE dbo.TMPVENDORS(VNDNO CHAR(6)) IF (LEN(@VNDNO) > 0) BEGIN INSERT INTO dbo.TMPVENDORS(VNDNO) VALUES(@VNDNO) END ELSE BEGIN INSERT INTO dbo.TMPVENDORS(VNDNO) SELECT DISTINCT LEFT(UPC,6) FROM STDPRODUCT WHERE UPC IS NOT NULL END DECLARE PRODUCTS CURSOR FOR SELECT PRODUCT.PRODUCTID, LEFT(UPC,6), RIGHT(UPC,5),UpdateControl, MFRCODE, CATALOGNO, DESCRIPTION, CARTONQTY, PRICEGROUPCODE, REPORTGROUPCODE, ISNULL(SPAGROUPCODE,'') , COL3PRICE, PRICEUOM, CASHDISC, COL1PRICE , COL2PRICE, LISTPRICE, DISTRIBUTORCOST, PRODUCT.UPC FROM PRODUCT,TMPVENDORS WHERE PRODUCT.Status = 'A' AND LEFT(PRODUCT.UPC,6) = TMPVENDORS.VNDNO OPEN PRODUCTS FETCH NEXT FROM PRODUCTS INTO @ProductId , @VNDCODE1, @ITEMCODE,@TSPCODE, @LOCALMFRCODE, @LOCALCATALOGNO, @LOCALDESCRIPTION, @CARTONQTY, @PRICEGRPCODE , @RPTGRPCODE, @SPAGRPCODE , @OLDCOL3PRICE, @LOCALPRICEUOM, @OLDDISCPERCENT, @OLDCOL1PRICE, @OLDCOL2PRICE, @OLDLISTPRICE, @OLDDISTCOST, @UPC WHILE @@FETCH_STATUS = 0 BEGIN SET @ISRPTADDED = 0 SET @ISSPAADDED = 0 SET @ISPRICEADDED = 0 SET @CARTONUPDATE = 0 SET @UPDATENOTALLOWED = 0 SELECT @COL1PRICE = MP.COL1PRICE, @COL2PRICE = MP.COL2PRICE, @COL3PRICE = MP.COL3PRICE, @LISTPRICE = MP.LISTPRICE, @DISTCOST = MP.DISTRIBUTORCOST, @DISCPERCENT = MP.CASHDISC , @MFRCODE = MP.ADMINMFRCODE, @CATALOGNO = MP.CATALOGNO, @DESCRIPTION = MP.DESCRIPTION , @COMMCODE = MP.PRICEGROUPCODE, @COMMCODE = ISNULL(MP.PRICEGROUPCODE,''), @CARTONQTY = MP.CARTONQTY, @PRICEUOM = MP.PRICEUOM, @MASTERSPAGRPCODE = MP.SPAGROUPCODE, @DESC = LEFT(ISNULL(DESCRIPTION, '') , 33) FROM STDPRODUCT MP WHERE MP.UPC = @UPC IF (@@ROWCOUNT <> 0) BEGIN /* Set the values of cash dicscount back from local product . 2 means update only prices */ IF (@TSPCODE = 2 ) BEGIN SET @DISCPERCENT = @OLDDISCPERCENT END IF (@TSPCODE = 3 ) BEGIN SET @COL1PRICE = @OLDCOL1PRICE SET @COL2PRICE = @OLDCOL2PRICE SET @COL3PRICE = @OLDCOL3PRICE SET @LISTPRICE = @OLDLISTPRICE SET @DISTCOST = @OLDDISTCOST SET @PRICEUOM = @LOCALPRICEUOM END IF (@TSPCODE = 4 ) BEGIN SET @DISCPERCENT = @OLDDISCPERCENT SET @COL1PRICE = @OLDCOL1PRICE SET @COL2PRICE = @OLDCOL2PRICE SET @COL3PRICE = @OLDCOL3PRICE SET @LISTPRICE = @OLDLISTPRICE SET @DISTCOST = @OLDDISTCOST SET @PRICEUOM = @LOCALPRICEUOM END IF (@bUpdateDesc = 1) BEGIN UPDATE PRODUCT SET DESCRIPTION = @DESC WHERE LEN(RTRIM(@DESC)) > 0 AND PRODUCTID = @PRODUCTID END IF (@bUpdatePriceGroup = 1) BEGIN IF (LEN(@COMMCODE) > 0 AND @PRICEGRPCODE <> @COMMCODE) BEGIN SELECT PRICEGROUPCODE FROM PRICEGROUP WHERE PRICEGROUPCODE = @COMMCODE IF (@@ROWCOUNT = 0) BEGIN INSERT INTO PRICEGROUP (PRICEGROUPCODE, ORGUNITID, DESCRIPTION, CREATEDON, LASTUPDATEDON, LOCKNUM) VALUES (@COMMCODE, @ORGUNITID, @COMMCODE, GETDATE(), GETDATE(),0) SET @ISPRICEADDED = 1 END UPDATE PRODUCT SET PRICEGROUPCODE = @COMMCODE WHERE PRODUCTID = @PRODUCTID END END IF (@bUpdateReportGroup = 1) BEGIN IF (LEN(@COMMCODE) > 0 AND @RPTGRPCODE <> @COMMCODE) BEGIN SELECT REPORTGROUPCODE FROM REPORTGROUP WHERE REPORTGROUPCODE = @COMMCODE IF (@@ROWCOUNT = 0) BEGIN INSERT INTO REPORTGROUP (REPORTGROUPCODE, DESCRIPTION, CREATEDON, LASTUPDATEDON, LOCKNUM) VALUES ( @COMMCODE, @COMMCODE, GETDATE(), GETDATE(),0) SET @ISRPTADDED = 1 END UPDATE PRODUCT SET REPORTGROUPCODE = @COMMCODE WHERE PRODUCTID = @PRODUCTID END END /* IF (@bUpdateSPAGroup = 1) BEGIN IF (LEN(@COMMCODE) > 0 AND @SPAGRPCODE <> @COMMCODE) BEGIN SET @COUNT = 0 SELECT @COUNT = COUNT(SPAGROUPCODE) FROM SPAGROUP WHERE SPAGROUPCODE = @COMMCODE AND MFRCODE = @LOCALMFRCODE IF (@COUNT = 0) BEGIN INSERT INTO SPAGROUP (SPAGROUPCODE, ORGUNITID, DESCRIPTION, CREATEDON, LASTUPDATEDON, LOCKNUM, MFRCODE) VALUES (@COMMCODE, @ORGUNITID, @COMMCODE, GETDATE(), GETDATE(),0, @LOCALMFRCODE) SET @ISSPAADDED = 1 END UPDATE PRODUCT SET SPAGROUPCODE = @COMMCODE WHERE PRODUCTID = @PRODUCTID END END */ IF (@bUpdateSPAGroup1 = 1) BEGIN IF (LEN(@MASTERSPAGRPCODE) > 0 AND @SPAGRPCODE <> @MASTERSPAGRPCODE) BEGIN SET @COUNT = 0 SELECT @COUNT = COUNT(SPAGROUPCODE) FROM SPAGROUP WHERE SPAGROUPCODE = @MASTERSPAGRPCODE AND MFRCODE = @LOCALMFRCODE IF (@COUNT = 0) BEGIN INSERT INTO SPAGROUP (SPAGROUPCODE, ORGUNITID, DESCRIPTION, CREATEDON, LASTUPDATEDON, LOCKNUM, MFRCODE) VALUES (@MASTERSPAGRPCODE, @ORGUNITID, @MASTERSPAGRPCODE, GETDATE(), GETDATE(),0, @LOCALMFRCODE) SET @ISSPAADDED = 1 END UPDATE PRODUCT SET SPAGROUPCODE = @MASTERSPAGRPCODE WHERE PRODUCTID = @PRODUCTID END END IF (@TSPCODE = 4 ) BEGIN SET @UPDATENOTALLOWED = 1 SELECT @PRODCARTONQTY = CARTONQTY FROM PRODUCT WHERE PRODUCTID=@PRODUCTID IF (@PRODCARTONQTY <> @CARTONQTY) BEGIN SET @CARTONUPDATE = 1 END END INSERT INTO PostTSPDataTables(VNDCODE , ITEMCODE, COMMCODE, LOCALMFR, LOCALCATALOGNO ,LOCALDESCRIPTION , MFR , CATALOGNO , DESCRIPTION, CARTONQTY, DISTCOST, LISTPRICE, COL1PRICE , COL2PRICE , COL3PRICE , OLDCOL3PRICE , PRICEUOM , LOCALPRICEUOM , DISCPERCENT , COL3GP , TSPCODE , ISRPTADDED , ISSPAADDED , ISPRICEADDED, CARTONUPDATEFAILED, UPDATENOTALLOWED ) VALUES (@VNDCODE1, @ITEMCODE, @COMMCODE, @LOCALMFRCODE, @LOCALCATALOGNO, @LOCALDESCRIPTION, @MFRCODE, @CATALOGNO, @DESCRIPTION, @CARTONQTY, @DISTCOST, @LISTPRICE, @COL1PRICE, @COL2PRICE, @COL3PRICE, @OLDCOL3PRICE, @PRICEUOM, @LOCALPRICEUOM, @DISCPERCENT, 0.00,@TSPCODE, @ISRPTADDED, @ISSPAADDED, @ISPRICEADDED, @CARTONUPDATE, @UPDATENOTALLOWED) IF (@TSPCODE <> 4 ) BEGIN SET @LASTMODIFIED = current_timestamp UPDATE PRODUCT SET CASHDISC = @DISCPERCENT, COL1PRICE = @COL1PRICE , COL2PRICE = @COL2PRICE, COL3PRICE = @COL3PRICE, LISTPRICE = @LISTPRICE, DISTRIBUTORCOST = @DISTCOST, PRICEUOM = @PRICEUOM, LASTMODIFIEDDATE = @LASTMODIFIED WHERE PRODUCTID = @PRODUCTID AND ORGUNITID <> 9636 UPDATE PRODUCT SET CARTONQTY = @CARTONQTY where PRODUCTID=@PRODUCTID END END ELSE BEGIN IF (@bZeroPrices = 1) BEGIN IF (@TSPCODE = 1 OR @TSPCODE = 2 ) BEGIN SET @COL1PRICE = 0.00 SET @COL2PRICE = 0.00 SET @COL3PRICE = 0.00 SET @LISTPRICE = 0.00 SET @DISTCOST = 0.00 END IF (@TSPCODE = 1 OR @TSPCODE = 3) BEGIN SET @DISCPERCENT = 0.00 END IF (@TSPCODE = 4 ) BEGIN SET @UPDATENOTALLOWED = 1 SELECT @PRODCARTONQTY = CARTONQTY FROM PRODUCT WHERE PRODUCTID=@PRODUCTID IF (@PRODCARTONQTY <> @CARTONQTY) BEGIN SET @CARTONUPDATE = 1 END END INSERT INTO PostTSPDataTables(VNDCODE , ITEMCODE, COMMCODE, LOCALMFR, LOCALCATALOGNO , LOCALDESCRIPTION, MFR , CATALOGNO , DESCRIPTION, CARTONQTY, DISTCOST, LISTPRICE, COL1PRICE , COL2PRICE , COL3PRICE , OLDCOL3PRICE , PRICEUOM , LOCALPRICEUOM , DISCPERCENT , COL3GP , TSPCODE , ISRPTADDED , ISSPAADDED , ISPRICEADDED, CARTONUPDATEFAILED, UPDATENOTALLOWED ) VALUES (@VNDCODE1, @ITEMCODE, @COMMCODE, @LOCALMFRCODE, @LOCALCATALOGNO, @LOCALDESCRIPTION, @MFRCODE, @CATALOGNO, @DESCRIPTION, @CARTONQTY, @DISTCOST, @LISTPRICE,@COL1PRICE, @COL2PRICE, @COL3PRICE, @OLDCOL3PRICE, @PRICEUOM, @LOCALPRICEUOM, @DISCPERCENT, 0.00,@TSPCODE, 0,0,0, @CARTONUPDATE, @UPDATENOTALLOWED) IF (@TSPCODE <> 4 ) BEGIN SET @LASTMODIFIED = current_timestamp UPDATE PRODUCT SET COL1PRICE = @COL1PRICE , COL2PRICE = @COL2PRICE , COL3PRICE = @COL3PRICE, LISTPRICE = @LISTPRICE, DISTRIBUTORCOST = @DISTCOST, LASTMODIFIEDDATE = @LASTMODIFIED WHERE PRODUCTID = @PRODUCTID UPDATE PRODUCT SET CARTONQTY = @CARTONQTY where PRODUCTID=@PRODUCTID END END END FETCH NEXT FROM PRODUCTS INTO @ProductId , @VNDCODE1, @ITEMCODE,@TSPCODE, @LOCALMFRCODE, @LOCALCATALOGNO, @LOCALDESCRIPTION, @CARTONQTY, @PRICEGRPCODE , @RPTGRPCODE, @SPAGRPCODE , @OLDCOL3PRICE, @LOCALPRICEUOM, @OLDDISCPERCENT, @OLDCOL1PRICE, @OLDCOL2PRICE, @OLDLISTPRICE, @OLDDISTCOST, @UPC END CLOSE PRODUCTS DEALLOCATE PRODUCTS RETURN 0 END SET NOCOUNT OFF |
|
Performance tuning the SP