Hi All,

I am tuning the below stored procedure. The response time for this stored procedure is around 2 seconds. It is nested up to 5 levels. I don't see any missing index on the corresponding tables. I don't see anything wrong with the execution plan. Any help here will be highly appreciated.

ALTER PROCEDURE [dbo].[p_get_dep] @a_mship_id NUMERIC(9, 0)
AS
BEGIN
	SET NOCOUNT ON;

--Declare Local Variables

	DECLARE @person_id NUMERIC(9, 0)
		,@wo_hosp_days NUMERIC(9, 0)
		,@days_wo_hospital NUMERIC(9, 0);

--Create Temp Tables 

	CREATE TABLE #stud_year (
		person_id NUMERIC(9, 0)
		,given_names VARCHAR(40) NULL
		,surname VARCHAR(40) NULL
		,gender CHAR(1) NULL
		,date_of_birth DATETIME NULL
		,age INT NULL
		,title NUMERIC(5) NULL
		,deceased_flag CHAR(1) NULL
		,clearance_status CHAR(1) NULL
		,student_year NUMERIC(4) NULL
		,termination_date DATETIME NULL
		,termination_code CHAR(1) NULL
		,join_date DATETIME NULL
		,membership_id NUMERIC(9, 0)
		,status_flag CHAR(1) NULL
		,relationship CHAR(10) NULL
		,create_operator CHAR(16) NULL
		,create_datetime DATETIME NULL
		,waiting_period CHAR(60) NULL
		,age_at_entry NUMERIC(3, 0) NULL
		,no_hospital_days INT NULL
		,update_operator CHAR(16) NULL
		,update_datetime DATETIME NULL
		,promotion CHAR(60) NULL
		,remove_lhc CHAR(1) NULL
		,relationship_sort_order INT NULL
		,status_flag_sort_order INT NULL
		,paid_lhc_since DATETIME NULL
		);

	CREATE TABLE #wo_hospday (
		person_id NUMERIC(9, 0)
		,no_hospital_days INT
		);

--Insert Data into Temp tables 
	INSERT #stud_year
	SELECT p.person_id
		,left(rtrim(ltrim(isNull(rtrim(p.first_name), '') + ' ' + isNull(p.other_names, ''))), 40)
		,p.surname
		,p.gender
		,p.date_of_birth
		,CASE 
			WHEN sysdb.ssma_syb.ssma_datepart('mm', p.date_of_birth) < sysdb.ssma_syb.ssma_datepart('mm', getdate())
				THEN sysdb.ssma_syb.ssma_datediff('yy', p.date_of_birth, getdate())
			WHEN sysdb.ssma_syb.ssma_datepart('mm', p.date_of_birth) = sysdb.ssma_syb.ssma_datepart('mm', getdate())
				AND sysdb.ssma_syb.ssma_datepart('dd', p.date_of_birth) <= sysdb.ssma_syb.ssma_datepart('dd', getdate())
				THEN sysdb.ssma_syb.ssma_datediff('yy', p.date_of_birth, getdate())
			ELSE sysdb.ssma_syb.ssma_datediff('yy', p.date_of_birth, getdate()) - 1
			END
		,p.title
		,p.deceased_flag
		,p.clearance_status
		,isnull(ps.student_year, 0)
		,pm.termination_date
		,pm.termination_code
		,pm.join_date
		,pm.membership_id
		,pm.status_flag
		,pm.relationship
		,pm.create_operator
		,pm.create_datetime
		,pm.eligibility_status
		,p.age_at_entry
		,p.no_hospital_days
		,pm.update_operator
		,pm.update_datetime
		,CONVERT(VARCHAR(60), NULL)
		,p.remove_lhc
		,CASE 
			WHEN pm.relationship = 1
				THEN 1
			WHEN pm.relationship = 2
				THEN 2
			WHEN pm.relationship = 3
				THEN 2
			WHEN pm.relationship > 3
				THEN 3
			END
		,CASE 
			WHEN pm.status_flag = 'A'
				THEN 1
			WHEN pm.status_flag <> 'A'
				THEN 2
			END
		,NULL
	FROM dbo.person_membership pm
	INNER JOIN dbo.person p
		ON p.person_id = pm.person_id
	LEFT JOIN dbo.person_studentyear ps
		ON p.person_id = ps.person_id
	WHERE pm.membership_id = @a_mship_id
		AND p.person_id = pm.person_id

--Update the Temp Tables 

	UPDATE sy
	SET sy.promotion = p.description
	FROM dbo.promotion p
	INNER JOIN dbo.promotion_reference pr
		ON p.promotion_id = pr.promotion_id
		AND pr.main_ref_type = 'P'
	INNER JOIN #stud_year sy
		ON sy.person_id = CONVERT(NUMERIC(9), pr.main_ref)
		AND pr.main_ref_type='P'
	WHERE pr.status_date = (
			SELECT max(pr1.status_date)
			FROM dbo.promotion_reference pr1
			WHERE pr1.main_ref_type = 'P'
				AND sy.person_id = CONVERT(NUMERIC(9), pr1.main_ref)
			)

--**********************************************************************        
--***  Update The No. hospital days
--***********************************************************************

	DECLARE csr_wohosp CURSOR FAST_FORWARD
	FOR
	SELECT DISTINCT sy.person_id
	FROM #stud_year sy

	OPEN csr_wohosp

	FETCH NEXT
	FROM csr_wohosp
	INTO @person_id

	WHILE @@FETCH_STATUS = 0
	BEGIN
		SELECT @wo_hosp_days = 0

		EXECUTE dbo.p_get_days_wo_hospital @person_id
			,NULL
			,NULL
			,@wo_hosp_days OUTPUT
			,'N'
			,NULL

		IF @wo_hosp_days = NULL
			SELECT @wo_hosp_days = 0

		INSERT #wo_hospday
		SELECT @person_id
			,@wo_hosp_days

		FETCH NEXT
		FROM csr_wohosp
		INTO @person_id
	END

	CLOSE csr_wohosp
	DEALLOCATE csr_wohosp

	UPDATE #stud_year
	SET no_hospital_days = #wo_hospday.no_hospital_days
	FROM #stud_year
		,#wo_hospday
	WHERE #stud_year.person_id = #wo_hospday.person_id

	UPDATE #stud_year
	SET #stud_year.paid_lhc_since = (
			SELECT TOP (1) paid_lhc_since
			FROM person
			WHERE person_id = @person_id
			)
	WHERE #stud_year.relationship = 1

	SELECT sy.relationship
		,sy.given_names
		,sy.surname
		,sy.gender
		,sy.date_of_birth
		,sy.join_date
		,max(sy.student_year) AS student_year
		,sy.termination_date
		,sy.termination_code
		,sy.person_id
		,sy.membership_id
		,sy.title
		,sy.deceased_flag
		,sy.status_flag
		,sy.age
		,sy.clearance_status
		,sy.waiting_period
		,sy.create_operator
		,sy.create_datetime
		,sy.age_at_entry
		,sy.no_hospital_days
		,sy.update_operator
		,sy.update_datetime
		,sy.promotion
		,sy.remove_lhc
		,sy.paid_lhc_since
	FROM #stud_year sy
	GROUP BY sy.relationship
		,sy.given_names
		,sy.surname
		,sy.gender
		,sy.date_of_birth
		,sy.join_date
		,sy.termination_date
		,sy.termination_code
		,sy.person_id
		,sy.membership_id
		,sy.title
		,sy.deceased_flag
		,sy.status_flag
		,sy.age
		,sy.clearance_status
		,sy.create_operator
		,sy.create_datetime
		,sy.waiting_period
		,sy.age_at_entry
		,sy.no_hospital_days
		,sy.update_operator
		,sy.update_datetime
		,sy.promotion
		,sy.remove_lhc
		,sy.paid_lhc_since
		,sy.relationship_sort_order
		,sy.status_flag_sort_order
	ORDER BY sy.status_flag_sort_order ASC
		,sy.relationship_sort_order ASC
		,sy.date_of_birth ASC
END

What problem with that query? Response time? Can  you post a query execution plan here?

I see a cursor...

On my web site you can find the stored procedure sp_sqltrace, written by Lee Tudor. It sets up a trace filtered for your spid, and then aggregates the trace. This is an excellent tool to troubleshoot performance issue in procedure with loops, as you can find a statement that runs many times and take a wee bit too much time every time.

http://www.sommarskog.se/sqlutil/sqltrace.html

Hi Erland,

Looking the sp_sqltrace it list down the below the reason why it is taking time. Let me know if you have help here. I can't remove temp tables fully, but I have added the indexes to it.

Factor : 19%
Duration: 5298.48
Waits: 5292ms=NETWORK_IO(4) | 0ms=SOS_SCHEDULER_YIELD(2) | 0ms=PAGELATCH_EX(1)


	IF EXISTS (
			SELECT TOP 1(1)
			FROM dbo.c_prod
				,prod
				,#tpcp
			WHERE dbo.c_prod.mem_id = #tpcp.mem_id
				AND dbo.c_prod.cover_version = #tpcp.mcover_version
				AND dbo.c_prod.product_id = prod.product_id
				AND prod.product_type NOT IN (
					'H'
					,'A'
					,'B'
					)
				AND prod.product_id <> #tpcp.other1_product_id
				AND dbo.c_prod.product_id <> #tpcp.other1_product_id
			)
		UPDATE #tpcp
		SET other2_product_id = (
				SELECT min(dbo.c_prod.product_id)
				FROM dbo.c_prod
					,prod
				WHERE dbo.c_prod.mem_id = #tpcp.mem_id
					AND dbo.c_prod.cover_version = #tpcp.mcover_version
					AND dbo.c_prod.product_id = prod.product_id
					AND prod.product_type NOT IN (
						'H'
						,'A'
						,'B'
						)
					AND prod.product_id <> #tpcp.other1_product_id
					AND dbo.c_prod.product_id <> #tpcp.other1_product_id
				)



			
Factor: 16%
Duration: 4606.91
Waits: 4599ms=NETWORK_IO(3)

         IF EXISTS (
			SELECT TOP 1 (1)
			FROM dbo.c_prod
				,dbo.prod
				,#tpcp
			WHERE dbo.c_product.membership_id = #tpcp.membership_id
				AND dbo.c_prod.cover_version = #tpcp.mcover_version
				AND dbo.c_prod.product_id = dbo.prod.product_id
				AND dbo.prod.product_type = 'H'
			)



Factor: 11%
Duration: 3007.35
Waits: 2009ms=NETWORK_IO(1) | 0ms=SOS_SCHEDULER_YIELD(1)

SELECT #memcvr.membership_id
		,min(#memcvr.cover_from_date) AS cover_from_date
	INTO #minmemcvr
	FROM #memcvr
	GROUP BY #memcvr.membership_id


Factor:15%
Duration: 4381.90
Waits: 0ms=SOS_SCHEDULER_YIELD(1)

INSERT #memterm (
		membership_id
		,termination_date
		,per_or_mem
		)
	SELECT dbo.p_membership.membership_id
		,dbo.p_membership.termination_date
		,'P'
	FROM dbo.p_membership
		,#permem
	WHERE #permem.membership_id = dbo.p_membership.membership_id
		AND dbo.p_membership.person_id = @person_id
		AND dbo.p_membership.termination_date IS NOT NULL
	
	UNION
	
	SELECT dbo.p_mem_history.membership_id
		,dbo.p_mem_history.termination_date
		,'P'
	FROM dbo.p_mem_history
		,#permem
	WHERE #permem.membership_id = dbo.p_mem_history.membership_id
		AND dbo.p_mem_history.person_id = @person_id
		AND dbo.p_mem_history.termination_date IS NOT NULL		

I'm a little puzzled here. Look at this:

Factor : 19%
Duration: 5298.48
Waits: 5292ms=NETWORK_IO(4) | 0ms=SOS_SCHEDULER_YIELD(2) | 0ms=PAGELATCH_EX(1)

So the entire duration is almost only this NETWORK_IO wait. But I am not able to work out what this wait is. A well-known wait type is ASYNC_NETWORK_IO, but it occurs when communicating with the client, and this is inside an SP where you update a temp table. At the same time, it is certainly not a wait type that is due to a poorly written query.

If you run the sp_sqltrace over the batch repeatedly, do you consistently get a result like this?

By the way, did you use the @plans parameter when you ran this? Getting actual execution plans with @plans is powerful, but it distorts execution times, because generating the plan information is costly.

Factor:15%
Duration: 4381.90
Waits: 0ms=SOS_SCHEDULER_YIELD(1)

At last! Here is a query that really runs for 4.3 seconds and which we could tune. Obviously, since I don't know your tables, I can't help you with tuning. However, apparently, this is inside this stored procedure dbo.p_get_days_wo_hospital.

In the typical use case of the outer procedure of p_get_dep, how many distinct person ids rows will you typically get in the #stud_year temp table? 1? 2? 4? 8? 16? What I'm getting at is that if you typically have many persons, it may be worth to rewrite the inner SP to work with set-based input so that you don't need the loop.

Yes, I enabled actual execution plan when getting the result. We don't have more than 4 person ids from the outer stored procedure. I rewrote some part of the stored procedure it actually increased in the execution time from 2 seconds to 3 seconds. Even though it showed some significant improve individual query performance the overall performance is degraded. I have also implemented clustered and non-clustered index on the temp tables. But the performance is not significant.