SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Improving Query Performance
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Looktroniks
Starting Member

South Africa
2 Posts

Posted - 04/11/2013 :  09:46:00  Show Profile  Reply with Quote
Hi Guys I have a job which runs the following SQL stored procedure, but sometimes this takes a lot of time to execute, I suspect its the way I wrote the SQL code because I'm not very good at it. Please look at the following stored procedure and try and find what I can change for this to execute a bit quicker. The stored procedure runs well its just that sometimes it takes too long and this affects other applications to follow.


ALTER PROCEDURE [dbo].[New_Extract_Translate_export_PMA] 
	
AS
BEGIN
	
	SET NOCOUNT ON;
	
	declare 
		@tmpexportid		VARCHAR(50),
		@TMPSTREAMTAG		VARCHAR(50),		
		@tmpcount			int,
		@v_counter			int,
		@SampleTotal		int,
		@TMPPRO_JOB			VARCHAR(50),
		@TMPCUID			VARCHAR(50),
		@TMPSCH_CODE		VARCHAR(50),
		@TMPSAMPLEIDENT		VARCHAR(50),
		@TMPANALYTECODE		VARCHAR(50),
		@TMPANALYTESTATUS	VARCHAR(50),
		@TMPRESULT			VARCHAR(50),
		@TMPCLI_CODE		VARCHAR(50),
		@TMPPROJ_CODE		VARCHAR(50),
		@TMPEXPORTED		DATETIME,
		@TMPREGISTERED		DATETIME,
		@TMPLBATCHJOB		VARCHAR(50),
		@TMPVALIDATEDBY		VARCHAR(50)
		
	declare @tab table (i int identity , EXPORT_ID  VARCHAR(50), STREAM_TAG  VARCHAR(50), PRO_JOB  VARCHAR(50), 
				    CUID  VARCHAR(50), SCH_CODE  VARCHAR(50), SAMPLEIDENT  VARCHAR(50), ANALYTECODE  VARCHAR(50), 
					ANALYTESTATUS  VARCHAR(50), RESULT  VARCHAR(50), CLI_CODE  VARCHAR(50), PROJ_CODE  VARCHAR(50), 
					EXPORTED  VARCHAR(50), REGISTERED  VARCHAR(50), LBATCHJOB  VARCHAR(50),	VALIDATEDBY  VARCHAR(50)) 

	declare @Rows int 

	INSERT INTO @Tab 
	SELECT  EXPORT_ID, STREAM_TAG, PRO_JOB, CUID, SCH_CODE, SAMPLEIDENT, ANALYTECODE, 
		ANALYTESTATUS, RESULT, CLI_CODE, PROJ_CODE, EXPORTED, REGISTERED, LBATCHJOB, 
		VALIDATEDBY
	  FROM CCLS_EXPORT 
	  WHERE PMA_translate is null 
	  AND SCH_CODE NOT LIKE '%_A' 
	  AND SCH_CODE NOT LIKE '%_B'
	  AND ANALYTESTATUS = 'CPL'
	  AND EXPORTED >= getdate()- 45 
	  AND SCH_CODE NOT IN (SELECT NAME FROM PMAIGNORE 
							WHERE SCHEME='Y')	
	  AND ANALYTECODE NOT IN (SELECT NAME FROM PMAIGNORE 
							WHERE ANALYTE='Y')
	  AND STREAM_TAG IN (SELECT STREAMTAG FROM TRANSLATE)
	  AND CLI_CODE <> 'LE_PRP' AND SUBSTRING(SAMPLEIDENT,32,1) <> 'C'

	  ORDER BY STREAM_TAG

	SELECT @Rows = @@ROWCOUNT

	WHILE @Rows > 0 
	BEGIN
		SELECT @TMPEXPORTID=EXPORT_ID, @TMPSTREAMTAG=STREAM_TAG, @TMPPRO_JOB=PRO_JOB, @TMPCUID=CUID, @TMPSCH_CODE=SCH_CODE, 
			   @TMPSAMPLEIDENT=SAMPLEIDENT, @TMPANALYTECODE=ANALYTECODE, @TMPANALYTESTATUS=ANALYTESTATUS, @TMPRESULT=RESULT, 
			   @TMPCLI_CODE=CLI_CODE, @TMPPROJ_CODE=PROJ_CODE, @TMPEXPORTED=EXPORTED, @TMPREGISTERED=REGISTERED, 
			   @TMPLBATCHJOB=LBATCHJOB, @TMPVALIDATEDBY=VALIDATEDBY  
		FROM @Tab 
		WHERE i = @Rows
	
		IF @TMPANALYTESTATUS = 'CPL'
		BEGIN
					print @TMPSAMPLEIDENT
					
					EXEC NEW_PMATranslate @tmpexportid, @TMPPRO_JOB, @TMPCUID, @TMPSCH_CODE , @TMPSAMPLEIDENT, @TMPANALYTECODE, @TMPRESULT, @TMPCLI_CODE, @TMPPROJ_CODE, @TMPEXPORTED, @TMPREGISTERED, @TMPLBATCHJOB, @TMPVALIDATEDBY, @TMPSTREAMTAG
		END	  	 
		SET @Rows = @Rows -1
	END
END



TG
Flowing Fount of Yak Knowledge

USA
6062 Posts

Posted - 04/11/2013 :  12:00:13  Show Profile  Reply with Quote
So the real work is being performed by the called sp [NEW_PMATranslate].
If you want to improve performance the best way would be to rewrite that procedure to work on the entire result set (in @tab) at once in a set-based methodology.

If you want help with that please post the code for [NEW_PMATranslate]

Be One with the Optimizer
TG
Go to Top of Page

tomrippity
Starting Member

USA
37 Posts

Posted - 04/11/2013 :  16:09:08  Show Profile  Reply with Quote
TG is correct, you would need to post the NEW_PMATranslate procedure.

An easy way to see if NEW_PMATranslate is in fact the culprit if your long run time, might be to just comment that piece out and see how long it takes to run. Chances are it won't take long at all, which leaves you with optimizing NEW_PMATranslate.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000