SQL Server Performance Forum – Threads Archive

Table function takes too long to run

Hi, I’ve created a SQL statement that takes < 2 sec to run (469 rows) But when i put that SQL statement inside a Function that returns a table (inline or multi), it now takes over 45 seconds to complete Any ideas ? Thanks
AndrÃ©

Can you post your code here??? Thanks,
Ram "It is easy to write code for a spec and walk in water, provided, both are freezed…"

Hi Ram, Here’s the code you requested. First of all, i’ve sent the whole statement in one whole block. Normally it should be seperated in multiple functions (some code is beeing repeated). The premise of this statement is to show all work (or no work) that is beeing done on two days in all rooms of our building. Thanks for the help AndrÃ© DECLARE @OrganizationChar(2) DECLARE @Departement char(6) DECLARE @Start_Date char(20) DECLARE @Res_Start_Code char(12) DECLARE @Res_End_Code char(12) DECLARE @Date_rech char(10) SET @Organization= ’01′ SET @Start_Date = ‘2006-07-06′ SET @Departement = ‘SCEAME' SET @Res_Start_Code = ‘2030-001’ SET @Res_End_Code = ‘2030-199′ SELECT DISTINCT U0429_ORG_CODE, U0429_SORT_ROOM, U0429_ROOM_GROUP, U0429_DATE_TRANS, U0431_EVT_ID, U0431_DAY_FUNC_ID, U0431_ROOM_FUNC_ID, U0431_SPACE, U0431_STATUS_CODE, U0431_START_HR, U0431_END_HR, 0 FROM (SELECT @Organization as U0429_ORG_CODE, ’20’ as U0429_SORT_ROOM, ” as U0429_ROOM_BASE, ” as U0429_ROOM_GROUP, coalesce (convert (char(10), EV001_ISO_DATE, 23), ”) as U0429_DATE_TRANS FROM ev001_mstr_date_sched WHERE (convert (char(10), EV001_ISO_DATE, 23) = @Start_Date OR convert (char(10), EV001_ISO_DATE, 23) = convert (char(10), dateadd (day, 1, @Start_Date), 23)) UNION SELECT U0428_ORG_CODE as U0429_ORG_CODE, U0428_SORT_ROOM as U0429_SORT_ROOM, U0428_ROOM_BASE as U0429_ROOM_BASE, U0428_ROOM_GROUP as U0429_ROOM_GROUP, coalesce (convert (char(10), EV001_ISO_DATE, 23), ”) as U0429_DATE_TRANS FROM (SELECT DISTINCT EV801_ORG_CODE as U0428_ORG_CODE, ’10’ as U0428_SORT_ROOM, coalesce (EV800_BASE.EV800_SPACE_CODE, ”) as U0428_ROOM_BASE, coalesce ((SELECT TOP 1 EV800_GROUP.EV800_SPACE_CODE FROM ev800_space_master EV800_GROUP INNER JOIN ev801_space_relation EV801_GROUP on (EV800_GROUP.EV800_ORG_CODE = EV801_GROUP.EV801_ORG_CODE) and (EV800_GROUP.EV800_SPACE_CODE = EV801_GROUP.EV801_SPACE_CODE) and (EV801_GROUP.EV801_REL_SPACE = EV800_BASE.EV800_SPACE_CODE) WHERE EV800_GROUP.EV800_SPACE_ACCT = ‘@INTERN’), EV800_BASE.EV800_SPACE_CODE, ”) as U0428_ROOM_GROUP FROM ev801_space_relation EV801_BASE INNER JOIN ev800_space_master EV800_BASE ON (ev801_base.ev801_org_code = ev800_base.ev800_org_code) AND (ev801_base.ev801_space_code = ev800_base.ev800_space_code) AND (ev800_base.ev800_base_comb_flag = ‘B’) WHERE EV801_ORG_CODE = @Organization UNION SELECT @Organization as U0428_ORG_CODE, ’20’ as U0428_SORT_ROOM, ” as U0428_ROOM_BASE, ” as U0428_ROOM_GROUP) U0428 LEFT OUTER JOIN ev001_mstr_date_sched ON (ev001_mstr_date_sched.ev001_day > 0) WHERE (convert (char(10), EV001_ISO_DATE, 23) = @Start_Date OR convert (char(10), EV001_ISO_DATE, 23) = convert (char(10), dateadd (day, 1, @Start_Date), 23))) U0429 LEFT OUTER JOIN (SELECT EV700_JOUR.EV700_ORG_CODE as U0431_ORG_CODE, coalesce (convert (char(10), EV700_JOUR.EV700_START_DATE_ISO, 23), ”) as U0431_DATE_TRANS, coalesce (EV700_JOUR.EV700_FUNC_ID, 0) as U0431_DAY_FUNC_ID, coalesce (EV700_JOUR.EV700_EVT_ID, 0) as U0431_EVT_ID, coalesce (EV700_ROOM.EV700_FUNC_ID, 0) as U0431_ROOM_FUNC_ID, coalesce (EV700_ROOM.EV700_SPACE, ”) as U0431_SPACE, coalesce (EV700_ROOM.EV700_STATUS_CODE, ”) as U0431_STATUS_CODE, coalesce (LEFT (CONVERT (CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, EV700_ROOM.EV700_START_DATE_ISO, 10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, 5), ”) as U0431_START_HR, coalesce (LEFT (CONVERT (CHAR(<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, EV700_ROOM.EV700_END_DATE_ISO, 10<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />, 5), ”) as U0431_END_HR, coalesce (EV200.EV200_EVT_DESC, ”) as U0431_EVT_DESC, U0428.U0428_SORT_ROOM as U0431_SORT_ROOM, U0428.U0428_ROOM_BASE as U0431_ROOM_BASE, U0428.U0428_ROOM_GROUP as U0431_ROOM_GROUP, coalesce ((SELECT count (*) FROM ER100_ACCT_ORDER ER100, ER101_ACCT_ORDER_DTL ER101 WHERE ER100.ER100_ORG_CODE = ER101.ER101_ORG_CODE AND ER100.ER100_ORD_NBR = ER101.ER101_ORD_NBR AND EV700_ROOM.EV700_ORG_CODE = ER101.ER101_ORG_CODE AND EV700_ROOM.EV700_EVT_ID = ER101.ER101_EVT_ID AND EV700_ROOM.EV700_FUNC_ID = ER101.ER101_FUNC_ID AND ER101.ER101_DEPT = @Departement AND (ER101.ER101_RES_CODE >= @Res_Start_Code AND ER101.ER101_RES_CODE <= @Res_End_Code) AND ER101.ER101_INTERNAL <> ‘Y' AND ER101.ER101_COMPL_STS <> ‘X' AND ER100.ER100_ORD_STS <> ‘X’), 0) as U0431_NB_TRANS FROM (((EV700_FUNC_MASTER EV700_JOUR LEFT OUTER JOIN EV700_FUNC_MASTER EV700_ROOM ON (EV700_JOUR.EV700_ORG_CODE = EV700_ROOM.EV700_ORG_CODE) AND (EV700_JOUR.EV700_EVT_ID = EV700_ROOM.EV700_EV T_ID) AND (EV700_JOUR.EV700_FUNC_ID = EV700_ROOM.EV700_PARENT_FUNC_ID) AND (EV700_ROOM.EV700_FUNC_TYPE = ‘500’) AND ((EV700_ROOM.EV700_STATUS_CODE is null) OR (EV700_ROOM.EV700_STATUS_CODE <> ’80’))) LEFT OUTER JOIN EV801_SPACE_RELATION EV801 ON (EV700_ROOM.EV700_ORG_CODE = EV801.EV801_ORG_CODE) AND (EV700_ROOM.EV700_SPACE = EV801.EV801_REL_SPACE)) INNER JOIN (SELECT DISTINCT EV801_ORG_CODE as U0428_ORG_CODE, ’10’ as U0428_SORT_ROOM, coalesce (EV800_BASE.EV800_SPACE_CODE, ”) as U0428_ROOM_BASE, coalesce ((SELECT TOP 1 EV800_GROUP.EV800_SPACE_CODE FROM ev800_space_master EV800_GROUP INNER JOIN ev801_space_relation EV801_GROUP on (EV800_GROUP.EV800_ORG_CODE = EV801_GROUP.EV801_ORG_CODE) and (EV800_GROUP.EV800_SPACE_CODE = EV801_GROUP.EV801_SPACE_CODE) and (EV801_GROUP.EV801_REL_SPACE = EV800_BASE.EV800_SPACE_CODE) WHERE EV800_GROUP.EV800_SPACE_ACCT = ‘@INTERN' ), EV800_BASE.EV800_SPACE_CODE, ”) as U0428_ROOM_GROUP FROM ev801_space_relation EV801_BASE INNER JOIN ev800_space_master EV800_BASE ON (ev801_base.ev801_org_code = ev800_base.ev800_org_code) AND (ev801_base.ev801_space_code = ev800_base.ev800_space_code) AND (ev800_base.ev800_base_comb_flag = ‘B’) WHERE EV801_ORG_CODE = @Organization UNION SELECT @Organization as U0428_ORG_CODE, ’20’ as U0428_SORT_ROOM, ” as U0428_ROOM_BASE, ” as U0428_ROOM_GROUP) U0428 ON (EV801.EV801_ORG_CODE = U0428.U0428_ORG_CODE) AND (EV801.EV801_SPACE_CODE = U0428.U0428_ROOM_BASE) OR ((EV700_ROOM.EV700_SPACE = ”) AND (U0428.U0428_ROOM_BASE = ”))) INNER JOIN EV200_EVENT_MASTER EV200 ON (EV700_JOUR.EV700_ORG_CODE = EV200.EV200_ORG_CODE) AND (EV700_JOUR.EV700_EVT_ID = EV200.EV200_EVT_ID) WHERE EV700_JOUR.EV700_ORG_CODE = @Organization AND EV700_JOUR.EV700_FUNC_TYPE = ‘490’ AND (convert (char(10), EV700_JOUR.EV700_START_DATE_ISO, 23) = @Start_Date OR convert (char(10), EV700_JOUR.EV700_START_DATE_ISO, 23) = convert (char(10), dateadd (day, 1, @Start_Date), 23))) U0431 ON (U0429.U0429_ORG_CODE = U0431.U0431_ORG_CODE) and (U0429.U0429_ROOM_GROUP = U0431.U0431_ROOM_GROUP) and (U0429.U0429_DATE_TRANS = U0431.U0431_DATE_TRANS)

"i’ve sent the whole statement in one whole block. Normally it should be seperated in multiple functions (some code is beeing repeated)." So if you run the query that you posted, it runs faster than with UDFs? And in the other method, you are calling the same UDF a couple of times? That would explain the time difference, as each mention of the UDF will execute separately. With the derived tables, SQL Server knows it’s the same data all over again, with a UDF it doesn’t know that. Other than that, do not use CONVERT on column values in your criteria, but convert the filter value(s) to the appropriate format for the column. Finally, COALESCE((SELECT COUNT(*) FROM blabla), 0) is nonsense: if there is no match in blabla, COUNT(*) will be zero anyway.

Hi Adriaan, Thanks for the insight. I’ve combined 2 UDFs into one and now i get less than 4 sec. Regards
AndrÃ©

After doing some more tests, i found out that the primary reason the statement was taking so much time was because of the command SET ANSI_NULLS TO OFF. I set it back to ON, the original statement is running < 5 sec, without any change to it. AndrÃ©

What happens if you don’t have a SET ANSI_NULLS command at all?

Â«What happens if you don’t have a SET ANSI_NULLS command at all?Â« Hi Adriaan, When executing the full query without any UDF it ran fine. So i began putting back one UDF at a time, and the execution time stayed the same < 4 sec. At the end i was back to the same query as the original, and that’s when i began looking what else whas different… I had the QUOTED_IDENTIFIER OFF and the ANSI_NULLS OFF inside the original queries. So i began with the first…no change…and then the second…bingo By default when i open up SQL Query analyzer the ANSI_NULLS is ON AndrÃ©

]]>