Stored Procedure runs slow at first execution

[E11esar]

Hi there.

I have a stored procedure which I need to make more efficient. When first run the stored procedure can take as long as 34 seconds to return results / complete processing, but then any subsequent runs take around 8 seconds; still a bit long as this is for a web service.

Here is how the SP is coded:

USE [thisTest_DEV]
GO
/****** Object:  StoredProcedure [dbo].[spGetParticipantsByAgreementItem]    Script Date: 08/05/2010 08:13:31 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[spGetParticipantsByAgreementItem]
@Client				VARCHAR ( 25 ),
@MaxRecords			INT,
@CustomerID			VARCHAR	( 25 ),			
@AccountID			VARCHAR	( 25 )	= ' ',	
@SubAccountID			VARCHAR	( 25 )	= ' ',	
@IsActive			CHAR	( 1  )	= ' ',	
@LegacyPartNumber		VARCHAR	( 25 )	= ' ',	
@IDNumber			VARCHAR ( 25 )	= ' ',
@IDType				VARCHAR ( 25 )	= ' ', 
@Description			VARCHAR ( 255)	= ' ',	
@DOB				DATETIME		= ' ',	
@Gender				VARCHAR	( 25 )	= ' ',
@BadgeType			VARCHAR	( 25 )	= ' ',
@BadgeLocation			VARCHAR ( 25 )	= ' ',
@AgreementFrequency		VARCHAR ( 25 )	= ' ',	
@CustomerRef			VARCHAR ( 12 )  = ' ',
@MsgId				VARCHAR (25) OUTPUT,
@IsError			BIT OUTPUT	
AS
BEGIN	
	SET NOCOUNT ON;   
	
	DECLARE @SelectSql			 NVARCHAR(MAX)
	DECLARE @SYSTEM_PARAM_GENDER VARCHAR(25)
	DECLARE @ErrorMsg			 VARCHAR(25)
	DECLARE @ParticipantId		 VARCHAR(25)
	-- Fixed Registers
	DECLARE @FR_IDTYPE			VARCHAR(25)
	DECLARE @FR_DOSITYPE		VARCHAR(25)
	DECLARE @FR_FREQUENCY		VARCHAR(25)
	DECLARE @FR_DOSIMETER		VARCHAR(25)
	DECLARE @OrigPartNbr  		VARCHAR(25)
	DECLARE @dateOfBirth		VARCHAR(10)
	DECLARE @Status				VARCHAR(1)
	DECLARE @Count				INT	

	SET @SelectSql			 = ''
	SET @SYSTEM_PARAM_GENDER = 'Q1_GENDER'	
	SET @IsError			 = 0	
	SET @dateOfBirth		 = CONVERT(VARCHAR(10),@DOB,111)
	SET @FR_IDTYPE			 = 'JL31'	
	SET @FR_DOSITYPE		 = 'JL65'
	SET @FR_FREQUENCY		 = 'DI'
	SET @FR_DOSIMETER		 = 'D0'
	SET @OrigPartNbr		 = 'ORIGPARTNBR'
	SET @Status				 = 'N' --Default Active 
	SET @Count				 = 0

	-- Validations
	SELECT @MsgId = 
	CASE	
		-- Check that the Gender exists in the System Setup Parameter-Q1_GENDER,if provided.
		WHEN (@Gender!= '' AND NOT EXISTS
				(SELECT description1 FROM asysvaluelist WHERE name = @SYSTEM_PARAM_GENDER and description1 = @Gender))
				THEN '29007'
		-- Check for Invalid ID Type.
		WHEN (@IDType!= '' AND NOT EXISTS
				(SELECT agrtid FROM agldimvalue WHERE attribute_id = @FR_IDTYPE	AND dim_value = @IDType
					AND client = @Client AND status = @Status) ) THEN '29002'
		-- Cannot search for ID Number without a valid ID Type
		WHEN (@IDNumber!= '' AND @IDType ='')
				THEN '29008'
		-- Check if Badge Type exists or not.
		WHEN (@BadgeType!= '' AND NOT EXISTS
				(SELECT agrtid FROM agldimvalue WHERE attribute_id = @FR_DOSITYPE	AND dim_value = @BadgeType
					AND client = @Client AND status = @Status) ) THEN '29009'
		-- Check if Badge Location exists or not.
		WHEN (@BadgeLocation!= '' AND NOT EXISTS
				(SELECT zone FROM aq1zone WHERE zone = @BadgeLocation		
					AND client = @Client AND status = @Status) ) THEN '29010'
		-- Check if Agreement Frequency exists or not.
		WHEN (@AgreementFrequency!= '' AND NOT EXISTS
				(SELECT agrtid FROM agldimvalue WHERE attribute_id = @FR_FREQUENCY	AND dim_value = @AgreementFrequency
					AND client = @Client AND status = @Status) ) THEN '29011'
	ELSE	''-- No Validation Error
	END

	IF(@MsgId != '')
	BEGIN
		SET @IsError = 1
		RETURN;
	END	

	-- Temporary tables.
	IF OBJECT_ID(N'tempdb..#Participant') IS NOT NULL
	BEGIN
		DROP TABLE #Participant	
	END
	CREATE TABLE #Participant
	(
		 participant_id VARCHAR(25),
		 apar_lookup    VARCHAR(25),
		 rowid			INT IDENTITY(1,1)
	)		

	IF OBJECT_ID(N'tempdb..#ParticipantInfo') IS NOT NULL
	BEGIN
		DROP TABLE #ParticipantInfo	
	END
	CREATE TABLE #ParticipantInfo
	(
		participant_id		VARCHAR(25),
		last_name			VARCHAR(40),
		first_name			VARCHAR(40),
		description			VARCHAR(255),
		gender				VARCHAR(25),
		birth_date			VARCHAR(25),
		legacy_part_id		VARCHAR(25),		
		active				VARCHAR(1),
		classified_begin	DATETIME,
		classified_end		DATETIME,
		inception_date		DATETIME,
		deactivation_date	DATETIME,
		client				VARCHAR(25),
		print_name			VARCHAR(25),
		nrrw_flag			TINYINT,
		occup_category		VARCHAR(25),
		dose_category		VARCHAR(25),
		middle_name			VARCHAR(40),
		participant_grp     VARCHAR(25),
		participant_typ     VARCHAR(25),
		status              CHAR(1),
		rowid				INT IDENTITY(1,1)
	)

	IF OBJECT_ID(N'tempdb..#ParticipantIDInfo') IS NOT NULL
	BEGIN
		DROP TABLE #ParticipantIDInfo	
	END
	CREATE TABLE #ParticipantIDInfo
	(
		participant_id VARCHAR(25),
		id_type        VARCHAR(25),
		id_value	   VARCHAR(25),
		primary_flag   TINYINT,
		rowid		   INT IDENTITY(1,1)
	)

	IF OBJECT_ID(N'tempdb..#ParticipantCASInfo') IS NOT NULL
	BEGIN
		DROP TABLE #ParticipantCASInfo	
	END
	CREATE TABLE #ParticipantCASInfo
	(
		participant_id		VARCHAR(25),
		account				VARCHAR(25),
		subaccount			VARCHAR(25),
		series_code			VARCHAR(100), 
		customers_reference VARCHAR(25),
		rowid				INT IDENTITY(1,1)
	)
	
	IF OBJECT_ID(N'tempdb..#ClassifiedPart') IS NOT NULL
	BEGIN
		DROP TABLE #ClassifiedPart	
	END
	CREATE TABLE #ClassifiedPart(client VARCHAR(25),participant_id VARCHAR(25),date_from DATETIME,
	date_to DATETIME,row_id INT IDENTITY(1,1))

	SET @SelectSql = 'INSERT INTO #Participant SELECT DISTINCT  a.participant_id,cas.apar_lookup ' + 
					' FROM '

	---====================================
	--		Join on the Filtered CAS List
	--=====================================
	SET @SelectSql = @SelectSql + 
					' (SELECT client,apar_lookup,cust,account,subaccount,active ' + 
					' FROM aq1vipartcasref ' + 					
					' WHERE client = ''' + @Client + ''''					

	SET @SelectSql = @SelectSql + 					
					CASE	
						-- When SubAccount is provided ignore the Account and Customer.						
						WHEN (@SubAccountId != '') THEN ' AND subaccount= ''' + @SubAccountId + ''' '
						-- When Account is provided ignore Customer.						
						WHEN (@AccountId != '') 
						THEN ' AND account = ''' + @AccountId + ''' '
						-- If both subaccount and account are not provided, join on customer
						ELSE ' AND cust = ''' + @CustomerId + ''' '
					END

	SET @SelectSql = @SelectSql + ') cas '



	---====================================
	-- Join on the Filtered Agreements List
	--=====================================	

	SET @SelectSql = @SelectSql + 
					' JOIN aq1agreeitem a ' +
					' ON cas.client = a.client ' + 
					' AND cas.apar_lookup = a.apar_id ' +					
					' AND a.client = ''' + @Client + ''' '
	IF(@IsActive != '')
	BEGIN
		SET @SelectSql = @SelectSql + 
						' AND cas.active = ''' + @IsActive + ''' '
	END
					
	--===========================================
	-- CustomerReference is given in the input.
	--===========================================
	IF(LTRIM(RTRIM(@CustomerRef))!='')
	BEGIN
		SET @SelectSql =  @SelectSql + 
							'JOIN afxcustref FX 
							ON a.client	= FX.client 
							AND a.apar_id	= FX.dim_value 
							AND FX.customers_reference =''' + @CustomerRef + ''' '
	END
	
	SET @SelectSql = @SelectSql + 
					' JOIN aq1participant p ON a.client = p.client '+
					' AND a.participant_id = p.participant_id ' +
					' AND p.client = ''' + @Client + ''' '  +
					' AND p.status = ''' + @Status + ''' '

	IF(@Description != '')
	BEGIN
		SET @SelectSql = @SelectSql + 
						' AND p.description like (''' + @Description + '%'') ' + ' '
	END

	IF(@Gender != '')
	BEGIN
		SET @SelectSql = @SelectSql + 
						' AND p.gender = ''' + @Gender + ''' '
	END

	IF(@dateOfBirth != '' AND @dateOfBirth !='1900/01/01')
	BEGIN
		SET @SelectSql = @SelectSql + 
						' AND convert(varchar(10),p.birth_date,111) = ''' + @dateOfBirth + ''' '
	END

	IF(@LegacyPartNumber != '')
	BEGIN
		SET @SelectSql = @SelectSql + 
						' AND p.legacy_part_id = ''' + @LegacyPartNumber + ''' '
	END

	IF(@IDType!= '')
	BEGIN
		SET @SelectSql = @SelectSql + 
			' JOIN aq1participantno pn ON p.client = pn.client AND p.participant_id = pn.participant_id ' +
			' AND pn.id_type = ''' + @IDType + ''' '

		IF(@IDNumber!= '')
		BEGIN
			-- CR1513:-
			--If ID Type is ORIGPARTNBR then aq1participantno.id_number like “%-ID Number” 
			--(that is ID Number	exactly matches the characters in the id_number after the “-“ character 
			--but there can be any string before those matching characters).
			--else if ID Type is specified and is not ORIGPARTNBR then aq1participantno.id_value = ID Number provided

			IF(@IDType = @OrigPartNbr)
			BEGIN
				SET @SelectSql = @SelectSql + ' AND pn.id_value LIKE (''%-'+ @IDNumber + ''') '
			END
			ELSE 
			BEGIN
				SET @SelectSql = @SelectSql + ' AND pn.id_value = ''' + @IDNumber + ''' '
			END 
		END
	END


	IF(@BadgeLocation != '')
	BEGIN
		SET @SelectSql = @SelectSql + 
						' AND a.zone = ''' + @BadgeLocation + ''' '						
	END

	IF(@AgreementFrequency != '' OR @BadgeType != '')
	BEGIN
		SET @SelectSql = @SelectSql + 
						' JOIN auiinvdetail inv on a.client = inv.client '+
						' AND a.subscription_id = inv.subscription_id ' +
						' AND a.sequence_no = inv.sequence_no ' 
						

		IF (@AgreementFrequency != '')
		BEGIN
			SET @SelectSql = @SelectSql + 
							' AND inv.frequency = ''' + @AgreementFrequency + ''' ' 
		END 

		IF (@BadgeType != '')
		BEGIN
			SET @SelectSql = @SelectSql + 
							' JOIN aglrelvalue rel on rel.client = inv.client '+
							' AND rel.att_value=inv.article ' +
							' AND rel.attribute_id=''' + @FR_DOSIMETER + ''''+
							' AND rel.rel_attr_id=''' + @FR_DOSITYPE + '''' +
							' AND rel.rel_value =''' + @BadgeType + '''' 			
		END 
	END

	SET @SelectSql = @SelectSql 
					+ ' ORDER BY a.participant_id '
		
	EXECUTE sp_executesql @SelectSql		

	-- If there are no validation errors, then get the list of Participants by AgreementItem.	
	
	-- Get the classified participant details.	
	INSERT INTO #ClassifiedPart
	SELECT a.client,a.participant_id,MAX(a.date_from),a.date_to
	FROM aq1classified a
	JOIN 
	(SELECT client,participant_id,MAX(date_to) as dateto
	FROM aq1classified 
	WHERE client = @Client
	GROUP BY client,participant_id) b
	ON 
	a.client = b.client 
	AND a.participant_id = b.participant_id 
	AND a.date_to = b.dateto
	GROUP BY a.client,a.participant_id,a.date_to				
	
	-- Get ParticipantInfo.
	INSERT INTO #ParticipantInfo
	SELECT 
		DISTINCT
		p.participant_id, 
		ISNULL(p.last_name,''),
		ISNULL(p.first_name,''),
		ISNULL(p.description,''), 
		ISNULL(p.gender,''), 
		CONVERT(varchar(10), p.birth_date, 111) AS birth_date, 
		ISNULL(p.legacy_part_id,''),
		ISNULL(v.Active,''), 
		ISNULL(cl.date_from,'1900/01/01'), 
		ISNULL(cl.date_to,'1900/01/01'), 
		ISNULL(p.inception_date,'1900/01/01'), 
		ISNULL(v.date_to,'1900/01/01'),
		p.client,
		ISNULL(p.print_name,''),
		p.nrrw_flag,
		ISNULL(p.occup_category,''),
		ISNULL(p.dose_category,''),
		ISNULL(p.middle_name,''),
		ISNULL(p.participant_grp,''),
		ISNULL(p.participant_typ,''),
		ISNULL(p.status,'') 
		
	FROM aq1participant p
	JOIN #Participant p1 
		ON p.client = @Client AND p.participant_id = p1.participant_id			
	JOIN aq1vipartcasref v 
		ON v.client = p.client AND v.participant_id = p1.participant_id  and p1.apar_lookup =v.apar_lookup
		AND (@IsActive = '' OR v.Active = @IsActive)
	JOIN aq1agreeitem g ON g.client = v.client and g.apar_id = v.apar_lookup and v.participant_id =g.participant_id
	LEFT JOIN #ClassifiedPart cl 
		ON cl.client = p.client AND cl.participant_id = p1.participant_id
		
	SELECT @Count = count(participant_id) FROM #ParticipantInfo
	
	IF(@Count = 0)
	BEGIN
	-- No Matching Participant.	
		SET @MsgId = '29001'
		SET @IsError = 1
		RETURN;
	END
	ELSE IF(@MaxRecords > 0 AND @Count > @MaxRecords)
	BEGIN
	--	MAX Records Reached.		
		SET @MsgId = '29006'
		SET @Count = @MaxRecords

	--  Get only the MaxRecords.
		DELETE FROM #ParticipantInfo WHERE rowid > @MaxRecords
	END

	-- Get ParticipantIdInfo.
	INSERT INTO #ParticipantIDInfo
	SELECT DISTINCT pn.participant_id, 
		   ISNULL(id_type,''), 
		   ISNULL(id_value,''), 
		   primary_flag 
	FROM aq1participantno pn		
	JOIN #ParticipantInfo p
	ON pn.client =  @Client 
	AND pn.participant_id = p.participant_id

	-- Get Participant CAS Info.
	INSERT INTO #ParticipantCASInfo
	SELECT DISTINCT cas.participant_id, 
		   ISNULL(cas.account,''),
		   ISNULL(cas.subaccount,''),
		   ISNULL(cas.series_code,''), 
		   ISNULL(ref.customers_reference,'') as customers_reference
	FROM aq1vipartcasref cas
	JOIN #ParticipantInfo p on cas.client = @Client AND cas.participant_id = p.participant_id
	RIGHT JOIN afxcustref ref ON cas.client = ref.client AND cas.apar_lookup = ref.dim_value 
	AND (@CustomerRef = '' OR ref.customers_reference = @CustomerRef)
	WHERE cas.client = @Client 
	AND cas.level IN('2','3')
	
	-- Return the Participant Details.
	SELECT	
		participant_id,
		last_name,
		first_name,
		description,
		gender,
		birth_date,
		legacy_part_id,
		active,
		classified_begin,
		classified_end,
		inception_date,
		deactivation_date,
		print_name,
		nrrw_flag,
		occup_category,
		dose_category,
		middle_name,
		participant_grp,
		participant_typ,
		status 
	FROM #ParticipantInfo ORDER BY participant_id
	
	SELECT	participant_id,
			id_type,
			id_value,
			primary_flag
	FROM #ParticipantIDInfo ORDER BY participant_id
	
	SELECT	participant_id,
			account,
			subaccount,
			series_code,
			customers_reference
	FROM #ParticipantCASInfo ORDER By participant_id
	
	RETURN
END

Is there something obvious which I can tune here please? When I display an execution plan, then it is the INSERTs that appear to be hogging all the processing time. The respective tables used with thye INSERTs are indexed.

Hope to hear from somebody soon as I'm a bit in the dark with this one.

Thank you.

M :)

[Oralloy]

I'm going to display my lack of MSSQLServer knowledge here

First off, may I ask why your "temporary" tables are not created as temporary tables? Isn't there a syntax "CREATE TEMPORARY TABLE ..." available? Or am I caught up in Oracle speak here?

If I recall correctly, inserts into perminant, shared tables are generally expensive operations. How much data are you copying?

On the other hand, the select logic you're using to create the tables does some long series of joins. Are all your join fields appropriately indexed? I know that a missing index on a tiny, 1000 record, table can kill a database's query performance.

I observe that your temporary tables aren't indexed. Is there a reason for that? I see that #ParticipantInf o is used in one of your later queries.

Lastly, have you tried profiling your own function by writing trace records with timing information? This will let you isolate the SQL that you're spending wall-clock time on. Wall-clock is what you're worried about right now, later on you can worry about processor and I/O improvement.

Good luck!

[Jerry Winston]

There's a lot of optimizing to do here. This is a really busy piece of code but I think we can trim it down by analyzing our existing data. An analysis which prompts my first question:

Why can't we trust our static table data? There are numerous IFNULL()'s throughout the code that seem to check fields from our static tables. Why can't we trust the data to contain either '' or NULL?

@Oralloy
IN MS SQL temp tables are created with the octothorpe or pound sign single (#) or double (##). Tables created with a single # are only visible/accessible to the current connection. Tables created with ## are visible/accessible to all connections to the server.

[Oralloy]

@b0010100

Thank you for that bit of information about the octothorpe implicitly identifying temporary tables. I learned something (which is why I'm here).

You are right about E11esar's need to analyze/understand his data.

He also needs to be aware of where his performance bottlenecks are. Thus my suggestion that he do some simple profiling. To paraphrase The Great Knuth, 97% of all optimization is wasted.

Anyway, I do apprecaite your feedback. Thank you!