Performance Issue using CTE and user defined function

Question

Post reply

Performance Issue using CTE and user defined function

ZZartin

SSC-Dedicated

Points: 30894
More actions
December 29, 2011 at 6:29 pm

#252793

I'm not sure if this should be here on the TSQL forum but anyways, I'm working on this query and when I add a function to change one of the columns in the output the run time goes from <5 seconds to a very long time. It doesn't look like it's doing that in our dev environment but that might just be because of the number of records. I've used that function a number of times on much larger output sets and I've never had this problem with it, it only returns about 70 rows in production.
WITH TEMP_PAYMENTS AS (
SELECT PAY_ACCNT_NUM, ORDER_NUM, FST_NAME, LAST_NAME, EMP_NUM, STATUS_CD, ROW_ID, CREATED AS CREATED, COUNT(PAY_ACCNT_NUM) OVER(PARTITION BY PAY_ACCNT_NUM) AS PAY_COUNT FROM V_RECENT_ORDERS
)
SELECT ORDERS_ONE.PAY_ACCNT_NUM,
ORDERS_ONE.ORDER_NUM,
ORDERS_ONE.FST_NAME + ' ' + ORDERS_ONE.LAST_NAME AS CUST_NAME,
ORDERS_ONE.EMP_NUM,
ORDERS_ONE.STATUS_CD,
dbo.UTC_TO_PST(ORDERS_ONE.CREATED),
ORDERS_ONE.PAY_COUNT
FROM TEMP_PAYMENTS ORDERS_ONE
WHERE ORDERS_ONE.PAY_ACCNT_NUM IN(SELECT PAY_ACCNT_NUM FROM TEMP_PAYMENTS ORDERS_TWO WHERE
ORDERS_TWO.CREATED >= DATEADD(day, -7, getdate()) AND ORDERS_TWO.PAY_COUNT > 1)
ORDER BY ORDERS_ONE.PAY_COUNT DESC, ORDERS_ONE.PAY_ACCNT_NUM
I bolded the function call in the code, without it it runs really quickly. With it it just seems to hang and spike the processor. It generates the same execution plan with or without the function call from what I can tell.
Any idea what I should check?

Viewing 4 posts - 1 through 3 (of 3 total)

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

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 1

Can you post the code for the function?

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

Devendra Shirbad SSC-Forever Points: 42493 More actions · Answer 2

Please post DDLs, sample data & Execution Plans (actual) as well. It’s required for performance tuning exercise.

ZZartin SSC-Dedicated Points: 30894 More actions · Answer 3

Thanks for the replies, it looks like the problem was due to bad statistics on one of the tables. After updating the query runs fine with the function in it.