Poor performance with functions in selects

Hi

We have the following query that we were trying to execute and it performs very poorly...

SELECT ITEM_ID,

PART_NO,

PART_TITLE,

dbo.SFDB_NVL_VARCHAR(DRAWING_NO_DISP,'N/A') AS DRAWING_NO,

dbo.SFDB_NVL_VARCHAR(DRAWING_CHG_DISP,'N/A') AS DRAWING_CHG,

DRAWING_NO_DISP,

DRAWING_CHG_DISP,

REV_SOURCE,

ITEM_ID AS ASSY_ITEM_ID,

dbo.SFDB_NVL_VARCHAR(DRAWING_NO_DISP,'N/A') AS ASSY_DRAWING_NO,

SFMFG.SFDB_NVL_VARCHAR(DRAWING_CHG_DISP,'N/A') AS ASSY_DRAWING_CHG

FROM SFSQA_CHAD_PART_DSPTCH_DSP_SEL

ORDER BY PART_NO

The function SFDB_NVL_VARCHAR is nothing but a replication of nvl function of oracle.

CREATE

FUNCTION [dbo].[SFDB_NVL_VARCHAR]

(

@.VI_SOURCE VARCHAR(4000),

@.VI_VALU_IF_NULL VARCHAR(4000)

)

RETURNS VARCHAR(4000)

AS

BEGIN

IF ISNULL(@.VI_SOURCE,'') =''

RETURN @.VI_VALU_IF_NULL

RETURN @.VI_SOURCE

END

Now if the same query was re-written to remove the function and instead use a CASE WHEN block the performance is up significantly....

SELECT ITEM_ID,

PART_NO,

PART_TITLE,

CASE WHEN DRAWING_NO_DISP IS NULL THEN 'N/A'

WHEN DRAWING_NO_DISP = '' THEN 'N/A'

ELSE DRAWING_NO_DISP

END DRAWING_NO,

CASE WHEN DRAWING_CHG_DISP IS NULL THEN 'N/A'

WHEN DRAWING_CHG_DISP = '' THEN 'N/A'

ELSE DRAWING_CHG_DISP

END DRAWING_CHG,

DRAWING_NO_DISP,

DRAWING_CHG_DISP,

REV_SOURCE,

ITEM_ID AS ASSY_ITEM_ID,

CASE WHEN DRAWING_NO_DISP IS NULL THEN 'N/A'

WHEN DRAWING_NO_DISP= '' THEN 'N/A'

ELSE DRAWING_NO_DISP

END ASSY_DRAWING_NO,

CASE WHEN DRAWING_CHG_DISP IS NULL THEN 'N/A'

WHEN DRAWING_CHG_DISP = '' THEN 'N/A'

ELSE DRAWING_CHG_DISP

END ASSY_DRAWING_CHG

FROM SFSQA_CHAD_PART_DSPTCH_DSP_SEL

ORDER BY PART_NO

Now the execution plan in both cases shows that cost of execution of the funtion is 0%. This is weird. How can function perform so badly for as simple as the one I mentioned,.

Regards

Imtiaz

Calling functions in a SELECT statement is itself not a very good idea for the same reason that for every row that was fetched in the SELECT result, SQL Server has to execute the function (for which it has to check for an available execution plan, find the most optimum plan, use it, then return the result).|||

Tiaz:

I think Dinakar is right. I am afraid that this is just "the nature of the beast". Code that does not use functions will tend to execute better. Here are a couple of threads earlier this year that discussed around the issues of functions; reviewing these might be helpful.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=868589&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=701264&SiteID=1

Dave

|||But even with such a function the execution should be fast...What is happening here...?|||

Well, I also just noticed this line:

SFMFG.SFDB_NVL_VARCHAR(DRAWING_CHG_DISP,'N/A') AS ASSY_DRAWING_CHG

Is this a typo in which you really want DBO?

|||OKAT THAT WAS A TYPO.....I HAD THAT SCHEMA BUT REMOVED IT NOT TO CONFUSE U FOLKS....IT IS DBO