Existing SQL Call - Needs refining

I am working with an existing SQL Stored procedure the way that things are being done are not totally efficient which is mainly down to the database design with no ability (permission) to affect change in how things are done (politics). So... I have been refining a number of SQL calls to make the system more efficient.

To date I have refined a number of processes that take one button click event that calls up to 2000 separate SQL calls over the LAN down to one single call with the use of various Table Value Parameters and UDT's and letting SQL Server do all the work in one go.

However......  I have got to this call and I am looking at the most efficient way to complete the updates.

DECLARE CURTEMP1 CURSOR FORWARD_ONLY READ_ONLY FOR
        
		SELECT
			ITEM_SEQ_NR,
			CONFRM_ASN_QTY,
			CONFRM_REDUCE_QTY
        FROM 
			CSMDC
        WHERE 
			PO_NR					= @PONO
        AND PO_ITEM_NR				= @LINE
        AND REF_NR					= CONVERT(VARCHAR, @REF_NR)
        AND CONFRM_CATGY_CD			= 'AB'
        ORDER BY 
			CONFRM_ASN_DUE_DT ASC
        
		OPEN CURTEMP1
        FETCH NEXT FROM CURTEMP1 INTO @SEQ, @CONFIRMED_QTY, @REDUCED_QTY
        WHILE @@FETCH_STATUS = 0
        BEGIN
          IF @CONFIRMED_QTY >= @TOTALSHIPPEDQTY
          BEGIN
            SET @REDUCED_QTY = @TOTALSHIPPEDQTY
            SET @TOTALSHIPPEDQTY = 0
          END
          ELSE-- IF @CONFIRMED_QTY < @TOTALSHIPPEDQTY 
          BEGIN
            SET @REDUCED_QTY		= @CONFIRMED_QTY
            SET @TOTALSHIPPEDQTY	= (@TOTALSHIPPEDQTY - @REDUCED_QTY)
          END
          UPDATE CSMDC
          SET CONFRM_REDUCE_QTY = @REDUCED_QTY,
              LAST_MOD_DTTM = GETDATE(),
              LAST_UPD_USERID = 'I318'
          WHERE PO_NR = @PONO
          AND PO_ITEM_NR = @LINE
          AND ITEM_SEQ_NR = @SEQ
          AND CONFRM_CATGY_CD = 'AB'
          FETCH NEXT FROM CURTEMP1 INTO @SEQ, @CONFIRMED_QTY, @REDUCED_QTY
        END
        CLOSE CURTEMP1
        DEALLOCATE CURTEMP1

Open in new window


My initial thought is to create a CTE or Temp table and run my if conditions \ checks at the point of inserting the data into the chosen container and then run a bulk update.  

I would appreciate thoughts and comments on this plan and if anyone has any other comments or considerations that would be great.

Thanks

Simon
LVL 1
SimonPrice3376Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Pawan KumarDatabase ExpertCommented:
Please provide full/complete code. You just need a simple update statement. thats all. Shall write a statement for you.

Pls show some sample data and the expected output.
0
SimonPrice3376Author Commented:
This is the complete code with sample data too

  BEGIN TRANSACTION

    SET NOCOUNT ON;

    DECLARE 
			--THESE VALUES WILL TYPICALLY BE PASSED IN TO THE STORED PROCEDURE
			@PONo char(10) = '0342438',
			@LINE INT = 00010,
			@VENDOR CHAR(10) = 'P0636',
			@RECDATE VARCHAR(10) = '20161117',
			@REF VARCHAR(35) = 'SP_TEST',
			@QTY INT = 0,
			@REF_NR INT = 0,
			-------------------------------------
            @SEQ INT,
            @PRICE DECIMAL(11, 2),
            @MATERIAL VARCHAR(20),
            @TOTALSHIPPEDQTY INT,
			@DIFF INT,
            @CONFIRMED_QTY INT,
            @REDUCED_QTY INT,
            @TOTALGRQTY INT,
            @TOTALLAQTY INT --, @TOTALGRWITHLAQTY INT

    IF (SELECT COUNT(PO_RFQ_NR) FROM CSMDA WHERE PO_RFQ_NR = @PONO AND SAP_VENDOR_NR = @VENDOR) > 0
    BEGIN
      
	  SELECT 
        @PRICE						= ORDR_PRICE,
        @MATERIAL					= SAP_MTRL_NR
      FROM 
		CSMDB
      WHERE 
		  PO_RFQ_NR					= @PONO
      AND PO_RFQ_ITEM_NR			= @LINE
     
      SELECT @SEQ					= COALESCE(MAX(ITEM_SEQ_NR), 0) + 1 FROM CSMDC WHERE PO_NR = @PONO AND PO_ITEM_NR = @LINE
	  SELECT @REF					= COALESCE(MAX(CONVERT(INT, REF_NR)), 0) FROM CSMDC WHERE PO_NR = @PONO AND PO_ITEM_NR = @LINE AND DBO.CSMDC.CONFRM_CATGY_CD = 'AB'
	  

      INSERT INTO 
		CSMDC 
		(PO_NR						,
		 PO_ITEM_NR					,
		 ITEM_SEQ_NR				,
		 CONFRM_CATGY_CD			,
		 REF_NR						,
		 REC_CRT_DTTM				,
		 REC_CRT_USERID 			,
		 MTRL_NR 					,
		 ALTN_PART_FG 				,
		 CONFRM_PRICE 				,
		 CONFRM_ASN_DUE_DT			,
		 CONFRM_ASN_QTY				,
		 LINE_STS_CD)
      VALUES 
		(@PONO						, 
		 @LINE						, 
		 @SEQ						, 
		 'GR'						, 
		 @REF						, 																											
		 GETDATE()					, 
		 'I318'						, 
		 @MATERIAL					, 
		 ''							, 
		 @PRICE						, 
		 @RECDATE					, 
		 @QTY						, 
		 'X')

      --*************************************************************************
      --DISTRIBUTE TOTAL ASN QTY SO FAR, IN CONFRM_REDUCE_QTY COLUMN, ACROSS CONFIRMATIONS IF THEY EXIST
      SET @TOTALLAQTY				= (SELECT 
										SUM(CONFRM_ASN_QTY)
									   FROM CSMDC
									   WHERE PO_NR					= @PONO
										AND PO_ITEM_NR				= @LINE
										AND CONFRM_CATGY_CD			= 'LA')

      SET @TOTALGRQTY				= (SELECT 
										SUM(CONFRM_ASN_QTY)
									   FROM CSMDC
									   WHERE PO_NR					= @PONO
									   AND PO_ITEM_NR				= @LINE
									   AND CONFRM_CATGY_CD			= 'GR')

      IF @TOTALLAQTY > @TOTALGRQTY
		BEGIN
		    PRINT 'SETING FROM @TOTALLAQTY'
			SET @TOTALSHIPPEDQTY	= @TOTALLAQTY
		END
	  ELSE
		BEGIN
		    PRINT 'SETING FROM @TOTALGRQTY'
			SET @TOTALSHIPPEDQTY	= @TOTALGRQTY
		END

      IF @REF_NR > 0
      BEGIN

	   DECLARE CURTEMP1 CURSOR FORWARD_ONLY READ_ONLY FOR
        
		SELECT
			ITEM_SEQ_NR,
			CONFRM_ASN_QTY,
			CONFRM_REDUCE_QTY
        FROM 
			CSMDC
        WHERE 
			PO_NR					= @PONO
        AND PO_ITEM_NR				= @LINE
        AND REF_NR					= CONVERT(VARCHAR, @REF_NR)
        AND CONFRM_CATGY_CD			= 'AB'
        ORDER BY 
			CONFRM_ASN_DUE_DT ASC
        
		OPEN CURTEMP1
        FETCH NEXT FROM CURTEMP1 INTO @SEQ, @CONFIRMED_QTY, @REDUCED_QTY
        WHILE @@FETCH_STATUS = 0
        BEGIN
          IF @CONFIRMED_QTY >= @TOTALSHIPPEDQTY
          BEGIN
            SET @REDUCED_QTY = @TOTALSHIPPEDQTY
            SET @TOTALSHIPPEDQTY = 0
          END
          ELSE-- IF @CONFIRMED_QTY < @TOTALSHIPPEDQTY 
          BEGIN
            SET @REDUCED_QTY		= @CONFIRMED_QTY
            SET @TOTALSHIPPEDQTY	= (@TOTALSHIPPEDQTY - @REDUCED_QTY)
          END
          UPDATE CSMDC
          SET CONFRM_REDUCE_QTY = @REDUCED_QTY,
              LAST_MOD_DTTM = GETDATE(),
              LAST_UPD_USERID = 'I318'
          WHERE PO_NR = @PONO
          AND PO_ITEM_NR = @LINE
          AND ITEM_SEQ_NR = @SEQ
          AND CONFRM_CATGY_CD = 'AB'
          FETCH NEXT FROM CURTEMP1 INTO @SEQ, @CONFIRMED_QTY, @REDUCED_QTY
        END
        CLOSE CURTEMP1
        DEALLOCATE CURTEMP1
      END
    END

  COMMIT

Open in new window

0
SimonPrice3376Author Commented:
Sorry for the formatting, when I move it from SSMS if distorts it a little
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Pawan KumarDatabase ExpertCommented:
sample data and output missing.
0
SimonPrice3376Author Commented:
sample data is in the call (example below) or do you mean table data?, and there is no required output from this call

                        @PONo char(10) = '0342438',
                  @LINE INT = 00010,
                  @VENDOR CHAR(10) = 'P0636',
                  @RECDATE VARCHAR(10) = '20161117',
                  @REF VARCHAR(35) = 'SP_TEST',
                  @QTY INT = 0,
                  @REF_NR INT = 0,
0
Pawan KumarDatabase ExpertCommented:
do you mean table data?
Yes..
0
SimonPrice3376Author Commented:
ok, give me a moment I will get you a few lines of data
0
SimonPrice3376Author Commented:
Try this. I have changed some of the important \ identifying data for obvious reasons, but this should be enough for you. These are the top 4 records in the database and the columns I have changed is PO_NR and MTRL_NR

File should be attached
sampleData.xlsx
0
Pawan KumarDatabase ExpertCommented:
Got the input. Thanks , what about the output needed?
0
SimonPrice3376Author Commented:
There isnt any output required by the look of what I am with...
0
Pawan KumarDatabase ExpertCommented:
You are updating the data with this cursor so what you data will look like after the update.
0
SimonPrice3376Author Commented:
I think we are missing each others point.

I know the data is being updated with a cursor, and there is no output. The data will look as is, other than the rows\columns that are being affected by the where clause.

I dont know why the original developer years ago decided to do it this way, but there are currently many procedures like this, but because of the way it is determining its own sequence and references because there are no incremented \ incrementing \ computed columns this causes some degree of difficulty.

The reason there is no output is because this is being called from a Service that has no and needs no gui so no one needs to see an output.
0
SimonPrice3376Author Commented:
Pawan,

Have you been able to make any progress on this at all please?
0
Pawan KumarDatabase ExpertCommented:
checking sir...
0
SimonPrice3376Author Commented:
I think I have my answer one what I want to do

SELECT 
			PO_NR																								,
			PO_ITEM_NR																							,
			ITEM_SEQ_NR																							,
			CASE WHEN CONFRM_ASN_QTY >= @TOTALSHIPPEDQTY THEN @TOTALSHIPPEDQTY ELSE CONFRM_ASN_QTY END AS QTY	,
		INTO #CSMDC_TMP
        FROM 
			CSMDC
        WHERE 
			PO_NR					= @PONO
        AND PO_ITEM_NR				= @LINE
        AND REF_NR					= CONVERT(VARCHAR, @REF_NR)
        AND CONFRM_CATGY_CD			= 'AB'
        ORDER BY 
			CONFRM_ASN_DUE_DT ASC

		MERGE CSMDC T 
		USING #CSMDC_TMP S ON T.PO_NR          	= S.PO_NR          
							  T.PO_ITEM_NR     	= S.PO_ITEM_NR     
							  T.ITEM_SEQ_NR    	= S.ITEM_SEQ_NR
							  T.CONFRM_CATGY_CD = 'AB'    
		WHEN MATCHED THEN UPDATE
			T.CONFRM_REDUCE_QTY = S.QTY,

Open in new window

1

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Pawan KumarDatabase ExpertCommented:
great..cheers.
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
>Existing SQL Call - Needs refining
>I am looking at the most efficient way to complete the updates.
Recommend either accepting Pawan's #a42392205 and #a42392372, or a delete.  This question is the equivalent of 'optimize my code' without well defined success criteria, so it is always difficult to know when the question is completed, and Pawan was the only expert that engaged and pushed the code forward.
0
SimonPrice3376Author Commented:
This is the sort of thing I was looking for, but couldnt see the wood through the trees. Dyslexia sometimes really kicks me in the ass when looking at a problem
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.