Table Value Function

Question

Table Value Function

nwinningham

SSC-Addicted

Points: 405
More actions
February 17, 2009 at 6:53 am

#201212

I have a user who created the following function. He is using cursors and I do not know where to start to make this function more efficient. The total CPU time(%) is 98.86 and the total logical IO is 99.80. Any help will be greatly appreciated.
/****** Object: UserDefinedFunction [dbo].[ccsc_admis_numbers3] Script Date: 02/16/2009 11:11:50 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[ccsc_admis_numbers3](
@type varchar(1),
@year int,
@term varchar(2),
@date datetime
)
RETURNS @t TABLE (
message varchar(100) NULL,
year1_group1 int,
year1_group2 int,
year1_group3 int,
year1_total int,
year2_group1 int,
year2_group2 int,
year2_group3 int,
year2_total int,
comp_group1 int,
comp_group2 int,
comp_group3 int,
comp_total int,
type varchar(15)
)
AS
BEGIN
/* ****************************************************************************************************
FUNCTION: ccsc_admis_numbers3
DESCRIPTION:The ccsc_admis_numbers3 function calculates the number of candidates having a given
stage on a specific date and compares that to the previous year on the same date. It
must be run for a specific year/term combination and a specific group (D,E,G). The
group corresponds to the first letter of the stages the report will use (DAP,EAP,GAP).
This function is called by an InfoMaker report: F:\Paul\reports.pbl:r_admis_numbers.
USES:Tables - STAGE_CONFIG, STAGE_HISTORY_TRAN, CANDIDACY
**************************************************************************************************** */
--INSERT @t VALUES ('', '', @year+' '+@term, '', '', CAST(CAST(@year AS INT)-1 AS varchar(10))+' '+@term, '', '', 'DIFF', '', 'header')
--INSERT @t VALUES ('', 'FD', 'TD', 'Total', 'FD', 'TD', 'Total', 'FD', 'TD', 'Total', 'header')
DECLARE @type1 char(1), @type2 char(1), @type3 char(1)
SET @type1=(CASE @type WHEN 'D' THEN 'F' WHEN 'E' THEN '1' END)
SET @type2=(CASE @type WHEN 'D' THEN 'T' WHEN 'E' THEN '2' END)
SET @type3=(CASE @type WHEN 'D' THEN 'R' WHEN 'E' THEN '3' END)
SET @date=@date+1
--INSERT the statuses into the table to scroll through to build the queries
INSERT INTO @t (message,type)
SELECT stage,'current' FROM stage_config WHERE stage LIKE @type+'%' OR stage LIKE 'R%' ORDER BY stage_ord
--SELECT DISTINCT status,statusnum,'current' from ccsc_stage_status WHERE status<>'' ORDER BY StatusNum
--create cursor
DECLARE @message varchar(100), @year1_group1 varchar(10), @year1_group2 varchar(10), @year1_group3 varchar(10), @year2_group1 varchar(10), @year2_group2 varchar(10), @year2_group3 varchar(10)
DECLARE t CURSOR FOR SELECT message, year1_group1, year1_group2, year1_group3, year2_group1, year2_group2, @year2_group3 FROM @t WHERE type='current'
--COUNT YEAR 1 GROUP 1
OPEN t
FETCH NEXT FROM t INTO @message, @year1_group1, @year1_group2, @year1_group3, @year2_group1, @year2_group2, @year2_group3
WHILE @@FETCH_STATUS = 0 BEGIN
--UPDATE @t SET year1_group1=(SELECT COUNT(*) FROM (SELECT DISTINCT id_num, (SELECT TOP 1 [hist_stage] FROM [STAGE_HISTORY_TRAN] LEFT JOIN ccsc_stage_status ON STAGE_HISTORY_TRAN.hist_stage=ccsc_stage_status.stage WHERE ID_NUM=candidacy.id_num AND hist_stage_dte<@date ORDER BY ID_NUM, [HIST_STAGE_DTE] DESC, transaction_seq) AS Status FROM candidacy WHERE yr_cde=@year AND trm_cde=@term AND student_type=@type1) a WHERE status=@message) WHERE CURRENT OF t
UPDATE @t SET year1_group1=(SELECT COUNT(*) FROM (SELECT DISTINCT id_num, (SELECT TOP 1 [hist_stage] FROM [STAGE_HISTORY_TRAN] WHERE ID_NUM=candidacy.id_num AND hist_stage_dte<@date AND yr_cde=@year AND trm_cde=@term ORDER BY ID_NUM, [HIST_STAGE_DTE] DESC, transaction_seq DESC) AS Status FROM candidacy WHERE yr_cde=@year AND trm_cde=@term AND candidacy_type=@type1) a WHERE status=@message) WHERE CURRENT OF t
FETCH NEXT FROM t INTO @message, @year1_group1, @year1_group2, @year1_group3, @year2_group1, @year2_group2, @year2_group3
END
CLOSE t
--COUNT YEAR 1 GROUP 2
OPEN t
FETCH NEXT FROM t INTO @message, @year1_group1, @year1_group2, @year1_group3, @year2_group1, @year2_group2, @year2_group3
WHILE @@FETCH_STATUS = 0 BEGIN
UPDATE @t SET year1_group2=(SELECT COUNT(*) FROM (SELECT DISTINCT id_num, (SELECT TOP 1 [hist_stage] FROM [STAGE_HISTORY_TRAN] WHERE ID_NUM=candidacy.id_num AND hist_stage_dte<@date AND yr_cde=@year AND trm_cde=@term ORDER BY ID_NUM, [HIST_STAGE_DTE] DESC, transaction_seq DESC) AS Status FROM candidacy WHERE yr_cde=@year AND trm_cde=@term AND candidacy_type=@type2) a WHERE status=@message) WHERE CURRENT OF t
--UPDATE @t SET message = (SELECT @message+' '+CAST(@date as varchar(12))+' '+cast(@year as varchar(4))+' '+@term+' '+@type2)
FETCH NEXT FROM t INTO @message, @year1_group1, @year1_group2, @year1_group3, @year2_group1, @year2_group2, @year2_group3
END
CLOSE t
--COUNT YEAR 1 GROUP 3
OPEN t
FETCH NEXT FROM t INTO @message, @year1_group1, @year1_group2, @year1_group3, @year2_group1, @year2_group2, @year2_group3
WHILE @@FETCH_STATUS = 0 BEGIN
UPDATE @t SET year1_group3=(SELECT COUNT(*) FROM (SELECT DISTINCT id_num, (SELECT TOP 1 [hist_stage] FROM [STAGE_HISTORY_TRAN] WHERE ID_NUM=candidacy.id_num AND hist_stage_dte<@date AND yr_cde=@year AND trm_cde=@term ORDER BY ID_NUM, [HIST_STAGE_DTE] DESC, transaction_seq DESC) AS Status FROM candidacy WHERE yr_cde=@year AND trm_cde=@term AND candidacy_type=@type3) a WHERE status=@message) WHERE CURRENT OF t
FETCH NEXT FROM t INTO @message, @year1_group1, @year1_group2, @year1_group3, @year2_group1, @year2_group2, @year2_group3
END
CLOSE t
--COUNT YEAR 2 GROUP 1
OPEN t
FETCH NEXT FROM t INTO @message, @year1_group1, @year1_group2, @year1_group3, @year2_group1, @year2_group2, @year2_group3
WHILE @@FETCH_STATUS = 0 BEGIN
UPDATE @t SET year2_group1=(SELECT COUNT(*) FROM (SELECT DISTINCT id_num, (SELECT TOP 1 [hist_stage] FROM [STAGE_HISTORY_TRAN] WHERE ID_NUM=candidacy.id_num AND hist_stage_dte<DATEADD(yy,-1,@date) AND yr_cde=@year-1 AND trm_cde=@term ORDER BY ID_NUM, [HIST_STAGE_DTE] DESC, transaction_seq DESC) AS Status FROM candidacy WHERE yr_cde=@year-1 AND trm_cde=@term AND candidacy_type=@type1) a WHERE status=@message) WHERE CURRENT OF t
FETCH NEXT FROM t INTO @message, @year1_group1, @year1_group2, @year1_group3, @year2_group1, @year2_group2, @year2_group3
END
CLOSE t
--COUNT YEAR 2 GROUP 2
OPEN t
FETCH NEXT FROM t INTO @message, @year1_group1, @year1_group2, @year1_group3, @year2_group1, @year2_group2, @year2_group3
WHILE @@FETCH_STATUS = 0 BEGIN
UPDATE @t SET year2_group2=(SELECT COUNT(*) FROM (SELECT DISTINCT id_num, (SELECT TOP 1 [hist_stage] FROM [STAGE_HISTORY_TRAN] WHERE ID_NUM=candidacy.id_num AND hist_stage_dte<DATEADD(yy,-1,@date) AND yr_cde=@year-1 AND trm_cde=@term ORDER BY ID_NUM, [HIST_STAGE_DTE] DESC, transaction_seq DESC) AS Status FROM candidacy WHERE yr_cde=@year-1 AND trm_cde=@term AND candidacy_type=@type2) a WHERE status=@message) WHERE CURRENT OF t
FETCH NEXT FROM t INTO @message, @year1_group1, @year1_group2, @year1_group3, @year2_group1, @year2_group2, @year2_group3
END
CLOSE t
--COUNT YEAR 2 GROUP 3
OPEN t
FETCH NEXT FROM t INTO @message, @year1_group1, @year1_group2, @year1_group3, @year2_group1, @year2_group2, @year2_group3
WHILE @@FETCH_STATUS = 0 BEGIN
UPDATE @t SET year2_group3=(SELECT COUNT(*) FROM (SELECT DISTINCT id_num, (SELECT TOP 1 [hist_stage] FROM [STAGE_HISTORY_TRAN] WHERE ID_NUM=candidacy.id_num AND hist_stage_dte<DATEADD(yy,-1,@date) AND yr_cde=@year-1 AND trm_cde=@term ORDER BY ID_NUM, [HIST_STAGE_DTE] DESC, transaction_seq DESC) AS Status FROM candidacy WHERE yr_cde=@year-1 AND trm_cde=@term AND candidacy_type=@type3) a WHERE status=@message) WHERE CURRENT OF t
FETCH NEXT FROM t INTO @message, @year1_group1, @year1_group2, @year1_group3, @year2_group1, @year2_group2, @year2_group3
END
CLOSE t
DEALLOCATE t
--TOTAL year1 then year2
UPDATE @t SET year1_total = CAST(year1_group1 AS INT) + CAST(year1_group2 AS INT) + CAST(year1_group3 AS INT) WHERE type='current'
UPDATE @t SET year2_total = CAST(year2_group1 AS INT) + CAST(year2_group2 AS INT) + CAST(year2_group3 AS INT) WHERE type='current'
--COMPARE year1 to year2
UPDATE @t SET comp_group1 = CAST(year1_group1 AS INT) - CAST(year2_group1 AS INT) WHERE type='current'
UPDATE @t SET comp_group2 = CAST(year1_group2 AS INT) - CAST(year2_group2 AS INT) WHERE type='current'
UPDATE @t SET comp_group3 = CAST(year1_group3 AS INT) - CAST(year2_group3 AS INT) WHERE type='current'
UPDATE @t SET comp_total = CAST(comp_group1 AS INT) + CAST(comp_group2 AS INT) + CAST(comp_group3 AS INT) WHERE type='current'
--TOTAL numbers
INSERT INTO @t
SELECT 'Total',
SUM(CAST(year1_group1 AS INT)), SUM(CAST(year1_group2 AS INT)), SUM(CAST(year1_group3 AS INT)), SUM(CAST(year1_total AS INT)),
SUM(CAST(year2_group1 AS INT)), SUM(CAST(year2_group2 AS INT)), SUM(CAST(year2_group3 AS INT)), SUM(CAST(year2_total AS INT)),
SUM(CAST(comp_group1 AS INT)), SUM(CAST(comp_group2 AS INT)), SUM(CAST(comp_group3 AS INT)), SUM(CAST(comp_total AS INT)), 'current'
FROM @t WHERE type='current'
--INSERT INTO @t VALUES ('','','','','','','','','','','')
--SUMMARY cumulative statuses
INSERT INTO @t
SELECT 'Applicants',SUM(year1_group1),SUM(year1_group2),SUM(year1_group3),SUM(year1_total),SUM(year2_group1),SUM(year2_group2),SUM(year2_group3),SUM(year2_total),SUM(comp_group1),SUM(comp_group2),SUM(comp_group3),SUM(comp_total),'cumulative'
FROM @t
WHERE message IN ('DAPJ','DAP','DAPT','DAC','DACT','DAZ','DRJ','DCO','DMS','DWI','DWA','DWC','EAP','EAC','EAZ','ERJ','ECO','EMS','EWI','EWA','EWC','GAP','GAC','GAZ','GRJ','GCO','GMS','GWI','GWA','GWC','RAP','RAC','RAZ','RRJ','RCO','RMS')
INSERT INTO @t
SELECT 'Accepted',SUM(year1_group1),SUM(year1_group2),SUM(year1_group3),SUM(year1_total),SUM(year2_group1),SUM(year2_group2),SUM(year2_group3),SUM(year2_total),SUM(comp_group1),SUM(comp_group2),SUM(comp_group3),SUM(comp_total),'cumulative'
FROM @t
WHERE message IN ('DAC','DACT','DAZ','DCO','DMS','DWA','DWC','EAC','EAZ','ECO','EMS','EWA','EWC','GAC','GAZ','GCO','GMS','GWA','GWC','RAC','RAZ','RCO','RMS')
INSERT INTO @t
SELECT 'Confirmed',SUM(year1_group1),SUM(year1_group2),SUM(year1_group3),SUM(year1_total),SUM(year2_group1),SUM(year2_group2),SUM(year2_group3),SUM(year2_total),SUM(comp_group1),SUM(comp_group2),SUM(comp_group3),SUM(comp_total),'cumulative'
FROM @t
WHERE message IN ('DCO','DMS','DWC','ECO','EMS','EWC','GCO','GMS','GWC','RCO','RMS')
--SUMMARY active statuses
INSERT INTO @t
SELECT 'Applicants',SUM(year1_group1),SUM(year1_group2),SUM(year1_group3),SUM(year1_total),SUM(year2_group1),SUM(year2_group2),SUM(year2_group3),SUM(year2_total),SUM(comp_group1),SUM(comp_group2),SUM(comp_group3),SUM(comp_total),'active'
FROM @t
WHERE message IN ('DAPJ','DAP','DAPT','DAC','DACT','DAZ','DCO','DMS','EAP','EAC','EAZ','ECO','EMS','GAP','GAC','GAZ','GCO','GMS','RAP','RAC','RAZ','RCO','RMS')
INSERT INTO @t
SELECT 'Accepted',SUM(year1_group1),SUM(year1_group2),SUM(year1_group3),SUM(year1_total),SUM(year2_group1),SUM(year2_group2),SUM(year2_group3),SUM(year2_total),SUM(comp_group1),SUM(comp_group2),SUM(comp_group3),SUM(comp_total),'active'
FROM @t
WHERE message IN ('DAC','DACT','DAZ','DCO','DMS','EAC','EAZ','ECO','EMS','GAC','GAZ','GCO','GMS','RAC','RAZ','RCO','RMS')
INSERT INTO @t
SELECT 'Confirmed',SUM(year1_group1),SUM(year1_group2),SUM(year1_group3),SUM(year1_total),SUM(year2_group1),SUM(year2_group2),SUM(year2_group3),SUM(year2_total),SUM(comp_group1),SUM(comp_group2),SUM(comp_group3),SUM(comp_total),'active'
FROM @t
WHERE message IN ('DCO','DMS','ECO','EMS','GCO','GMS','RCO','RMS')
RETURN
END

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

Can you post the source table definitions, some sample data, expected output, and the business reason(s) for the code? See

the links in my signature for how to. That's a lot of code to try to digest whereas posting table definitions, data,

and expected results will help us develop a solution without having to figure out all the code.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

nwinningham SSC-Addicted Points: 405 More actions · Answer 2

I have just spoken with the person who created this function. He said that he runs this report at night. So, since he runs this report at night we should not have any problems during the day.

Thanks for your response.

Jeff Moden SSC Guru Points: 1004307 More actions · Answer 3

nwinningham (2/17/2009)
I have just spoken with the person who created this function. He said that he runs this report at night. So, since he runs this report at night we should not have any problems during the day.
Thanks for your response.

I guess I'm a little astounded that you let users write their own stuff against a production database and that you don't really care about how slow it is just because it runs at night... someday, that user is going to want to run it during the day. Then what?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 4

Jeff Moden (3/3/2009)
nwinningham (2/17/2009)
I have just spoken with the person who created this function. He said that he runs this report at night. So, since he runs this report at night we should not have any problems during the day.
Thanks for your response.
I guess I'm a little astounded that you let users write their own stuff against a production database and that you don't really care about how slow it is just because it runs at night... someday, that user is going to want to run it during the day. Then what?

I have to agree with Jeff. Regardless of what the individual who wrote this code says, it is in serious need of a rewrite. I just started reformatting the code to see what I could figure out and not only is the code using cursors heavily, you have nested correlated queries inside the fetch loops of the cursors.

If you could post the DDL (CREATE TABLE statements), Index definitions, sample data (as INSERT statements that can be cut and pasted into SSMS and executed), expected results based on the sample data I'm sure you will get several tested code samples to work with in short order.

All that is going to happen with this code is it is going to get slower and slower as you get more data in your system. This code will not scale well.

nwinningham SSC-Addicted Points: 405 More actions · Answer 5

At this time no one is complaining about the database being slow. I was trying to be proactive and see what could potentially cause us some problems. We have discuss this issue with no real resolution yet. My manager knows about this issue and so does the user's manager who created the function. At this time this is the only query that is using a lot of CPU. This user was working here before me and I was told to give him rights so he can create queries for his manager.

I do understand your point.

Thanks

nwinningham SSC-Addicted Points: 405 More actions · Answer 6

I did not see any index information. I will attached the data soon.

This is the query they run to get the information:

SELECT * FROM ccsc_admis_numbers(:Group, :Year, :Term, :Date)

This is the function:

/****** Object: UserDefinedFunction [dbo].[ccsc_admis_numbers3] Script Date: 03/04/2009 08:37:35 ******/

SET ANSI_NULLS OFF

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[ccsc_admis_numbers3](

@type varchar(1),

@year int,

@term varchar(2),

@date datetime

)

RETURNS @t TABLE (

message varchar(100) NULL,

year1_group1 int,

year1_group2 int,

year1_group3 int,

year1_total int,

year2_group1 int,

year2_group2 int,

year2_group3 int,

year2_total int,

comp_group1 int,

comp_group2 int,

comp_group3 int,

comp_total int,

type varchar(15)

)

AS

BEGIN

/* ****************************************************************************************************

DATE:1/17/2008

DESCRIPTION:The ccsc_admis_numbers3 function calculates the number of candidates having a given

stage on a specific date and compares that to the previous year on the same date. It

must be run for a specific year/term combination and a specific group (D,E,G). The

group corresponds to the first letter of the stages the report will use (DAP,EAP,GAP).

This function is called by an InfoMaker report: F:\Paul\reports.pbl:r_admis_numbers.

USES:Tables - STAGE_CONFIG, STAGE_HISTORY_TRAN, CANDIDACY

**************************************************************************************************** */

--INSERT @t VALUES ('', '', @year+' '+@term, '', '', CAST(CAST(@year AS INT)-1 AS varchar(10))+' '+@term, '', '', 'DIFF', '', 'header')

--INSERT @t VALUES ('', 'FD', 'TD', 'Total', 'FD', 'TD', 'Total', 'FD', 'TD', 'Total', 'header')

DECLARE @type1 char(1), @type2 char(1), @type3 char(1)

SET @type1=(CASE @type WHEN 'D' THEN 'F' WHEN 'E' THEN '1' END)

SET @type2=(CASE @type WHEN 'D' THEN 'T' WHEN 'E' THEN '2' END)

SET @type3=(CASE @type WHEN 'D' THEN 'R' WHEN 'E' THEN '3' END)

SET @date=@date+1

--INSERT the statuses into the table to scroll through to build the queries

INSERT INTO @t (message,type)

SELECT stage,'current' FROM stage_config WHERE stage LIKE @type+'%' OR stage LIKE 'R%' ORDER BY stage_ord

--SELECT DISTINCT status,statusnum,'current' from ccsc_stage_status WHERE status<>'' ORDER BY StatusNum

--create cursor

DECLARE @message varchar(100), @year1_group1 varchar(10), @year1_group2 varchar(10), @year1_group3 varchar(10), @year2_group1 varchar(10), @year2_group2 varchar(10), @year2_group3 varchar(10)

DECLARE t CURSOR FOR SELECT message, year1_group1, year1_group2, year1_group3, year2_group1, year2_group2, @year2_group3 FROM @t WHERE type='current'

--COUNT YEAR 1 GROUP 1

OPEN t