UDF returns same results while using different parameters

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Kerry - 29 Aug 2008 05:59 GMT

Hi I'm having a strange caching problem with a UDF. I have a UDF which takes
10 parameters...I call the UDF twice within a single SQL JOIN statement
because the UDF returns a TABLE. The two UDF's are called with slighlty
different parameters, the problem is that both calls return the same result
set even though one of the parameters should cause the UDFs to return a
different result set. Basically the second call to the UDF stomps on the
results of the first call to the UDF. I have determined this to be a fact
because when I execute the UDF's separatly they return results ok. This
problem only happens on our SQL2005 server which is a MONSTER 12GB ram 4 quad
processors VERY FAST...slower machines do not show this problem. SQL has SP2
applied. I heard that some sp's show this problem but its solved by using
the WITH RECOMPILE command which does not exist for UDF's.

Thanks

Reply to this Message

Eric Isaacs - 29 Aug 2008 06:26 GMT

It would be really helpful if you could post your UDF and your SQL
statement that calls your UDF twice. You would get a more helpful
answer if you did that.

You could try adding WITH SCHEMABINDING to your UDF (assuming you
haven't already) to see if that makes a difference. It should with
the performance,

Assuming you're using your SQL to return 1 row of data, the UDFs could
be called before you execute the SQL. If you're returning multiple
rows in your SQL, the UDFs won't scale well and will take a long time
to process. This depends on how many lines that SQL returns, of
course. If you pull that logic out of the UDF into the SQL, it will
probably work just fine (again, assuming that's reasonable.) If
you're UDF returns a TABLE VALUE and you CROSS APPLY the results from
the table, it will scale well and probably would side-step the issue
you're having.

http://sqlblog.com/blogs/alexander_kuznetsov/archive/2008/05/23/reuse-your-code-
with-cross-apply.aspx?CommentPosted=true#commentmessage

Please post DDL, so we can give you a better response.

-Eric Isaacs

Reply to this Message

Kerry - 29 Aug 2008 14:48 GMT

Thanks for your reply I haven't tried the schmabinding command, I thought
that was to maintain integrity of object-to-object dependancies. The UDF
returns a small return set so performance it not really an issues, this
result set is JOINED to a result set from the call stored procedure, the
reason is the UDF returns monthly Aggregates AVG, MIN,MAX depending upon the
6th param passed to the UDF which is what should cause the UDF to return
different results.

This is the stored procedure that calls the UDF called
fn_GetBackOfficeChargebacks:
SELECT a.RptPeriod,
a.BidPercentage as BidPercentage,
b.AggregatePercentage as AggregatePercentage,
c.Percentage as AvgPercentage,
a.AggregateType
FROM (SELECT RptPeriod,
CONVERT(DECIMAL(19,6),SUM(case when MetricElement =
@Numerator then DollarValue else 0 end)) / case WHEN
CONVERT(DECIMAL(19,6),SUM(case when MetricElement = @Denominator then
DollarValue else 0 end)) = 0 THEN 1 ELSE CONVERT(DECIMAL(19,6),SUM(case when
MetricElement = @Denominator then DollarValue else 0 end)) END as
BidPercentage,
'MIN' as AggregateType
FROM vwMetricDetails
WHERE Bid = @Bid
AND Region = @Region
AND RptPeriod Between @StartDate AND @SubmittedDate
AND IsAcquirerData = @AcquirerIssuer
AND ProductTypeID = 'C'
AND MetricElement IN (@Numerator,@Denominator)GROUP BY
RptPeriod ) AS a
JOIN (SELECT RptPeriod,MIN(Percentage) as AggregatePercentage FROM
fn_GetBackOfficeChargebacks(@StartDate,@SubmittedDate,@OrgID,@AcquirerIssuer,@Region,@Min,@ReportElement,@Numerator,@Denominator,@CountOrDollar)
GROUP BY RptPeriod) AS b on a.RptPeriod = b.RptPeriod
JOIN
fn_GetBackOfficeChargebacks(@StartDate,@SubmittedDate,@OrgID,@AcquirerIssuer,@Region,@Avg,@ReportElement,@Numerator,@Denominator,@CountOrDollar)
c on a.RptPeriod = c.RptPeriod
ORDER BY 1

Here is the SELECT in the function, it returns Aggregates AVG or MIN, MAX
depending upon the value of the 6th param:

INSERT @Table
SELECT BID, Organization_NameShort,RptPeriod,
SUM(case when MetricElement = @Numerator then DollarValue else 0
end) as TotalNumerator,
SUM(case when MetricElement = @Denominator then DollarValue else
0 end) as TotalDenominator,
CONVERT(DECIMAL(19,4),SUM(case when MetricElement = @Numerator
then DollarValue else 0 end)) / CONVERT(DECIMAL(19,4),SUM(case when
MetricElement = @Denominator then DollarValue else 0 end)) as Percentage
FROM vwMetricDetails
WHERE Region = @Region
AND RptPeriod Between @RptStart AND @RptEnd
AND IsAcquirerData = @AcquirerIssuer
AND ProductTypeID = 'C'
AND MetricElement IN (@Numerator,@Denominator)
AND BID IN (select BID from vwPeerGrouping WHERE ReportElement =
@ReportElement AND PeerGroupingID = @PeerGroupingID
AND BID NOT IN (Select BID From vwPeerExclusions WHERE ApplyTypeID =
@ApplyType AND ReportElement = @ReportElement AND Period = @RptEnd))
Group By BID,Organization_NameShort,RptPeriod

If I create a copy of fn_GetBackOfficeChargebacks and call it
fn_GetBackOfficeChargebacks2 then use the copy for the second JOIN statement
it works fine...remind you this only happens on our super fast servers.

Thanks

> It would be really helpful if you could post your UDF and your SQL
> statement that calls your UDF twice. You would get a more helpful
[quoted text clipped - 19 lines]
>
> -Eric Isaacs

Reply to this Message

Eric Isaacs - 29 Aug 2008 20:15 GMT

Kelly,

When you call these two functions, it looks like the only parameters
that could change between the calls are the @Min and the @Avg, but you
didn't provide any clues what values these hold when the functions
are executed, nor how these values are used within the table value
function.

fn_GetBackOfficeChargebacks(@StartDate, @SubmittedDate, @OrgID,
@AcquirerIssuer, @Region, @Min, @ReportElement, @Numerator,
@Denominator, @CountOrDollar

fn_GetBackOfficeChargebacks(@StartDate, @SubmittedDate, @OrgID,
@AcquirerIssuer, @Region, @Avg, @ReportElement, @Numerator,
@Denominator, @CountOrDollar

The @Min and @Avg don't map to anything obvious within your function.
All the other parameters will match exactly. If both are NULL or the
same, the results will be identical and I would expect that they would
be cached within the same SQL statement.

You should also consider using the CROSS APPLY clause instead of the
JOIN when joining to the table value function. You don't need three
subqueries. One query with two cross applies should do the trick.

There's still not quite enough information to give you a better
answer, as we can't replicate your issue with the example you
provided. We need a little more SQL, or possibly a simpiler example
with all the code?

I hope that helps!

-Eric Isaacs

Reply to this Message

Eric Isaacs - 29 Aug 2008 20:28 GMT

If you really want to fix the problem and keep the table valued udfs,
why not just do those two function calls into two temp tables before
your statement that joins them together? Then you're not joining
table valued function calls to a table. Instead you would be joining
3 tables. It will probably help you debug the issue as well, since
you can then compare what's returned in those two temp tables. As
separate statements, they should be executed separately.

-Eric Isaacs

Reply to this Message

Kerry - 30 Aug 2008 04:21 GMT

Hi, the values of @Avg and @Min are hardcoded in the stored proc so its not
possible that the values are the same they are set like this:

SET @Max = 1
SET @Min = 2
SET @Avg = 3

The @StartDate variable is calculated using the code below
SET @StartDate= DATEADD(month,-12,@SubmittedDate), @SubmittedDate is a
parameter passed into the proc such as '2008-07-01' then @StartDate would be
'2007-07-01' giving a date range of 13 months. All the other params are seen
below and are parameters the user selects when generating an RS report, for
the purposes of debugging I hard coded them in the proc.

SET @AcquirerIssuer = 0
SET @Region = 1000
SET @Numerator = 100200
SET @Denominator = 100000
SET @OrgID = 6
set @CountOrDollar = 'D'
SET @BID = '10021249'
SET @ReportElement = 1110

Regarding the Temp tables yes that was one of my work arounds when
diagnosising this problem I ran two selects (basically the same selects in
the function) into ##TempTable1 and ##TempTable2 then joined my main query to
the two temp tables, this runs fine as well. However another test I tried,
if just call the two functions using a JOIN statement trying to create one
##TempTable and then JOIN ##TempTable to the main query in the PROC I get the
same issue as originally mentioned...so it definitely looks like two calls to
the same function within a query cannot work on this SUPER FAST server, SQL
get confused and clobbers the first results with the second results. As
mentioned before another work around is to copy the function and call it
fn_GetBackOfficeChargebacks2 and use this function along with the original
fn_GetBackOfficeChargebacks everything works ok. I will investigate CROSS
APPLY further, I've never used this command.

Thanks

> If you really want to fix the problem and keep the table valued udfs,
> why not just do those two function calls into two temp tables before
[quoted text clipped - 5 lines]
>
> -Eric Isaacs

Reply to this Message

Sylvain Lafontaine - 29 Aug 2008 06:54 GMT

Quick test: create a copy of the UDF with a different name and use it for
the second call.

Also, like the other poster has said, you should provide more details on
what your code is doing exactly.

Signature

Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)

> Hi I'm having a strange caching problem with a UDF. I have a UDF which
> takes
[quoted text clipped - 14 lines]
>
> Thanks

Reply to this Message

Kerry - 29 Aug 2008 14:42 GMT

Thanks for your reply, Yes I did do that, I created a copy of the UDF and
named it differently then used this one for the second call, this solved the
problem which led me to beleive there is a cache problem. I will post the
code.

This is the stored procedure that calls the UDF called
fn_GetBackOfficeChargebacks:
SELECT a.RptPeriod,
a.BidPercentage as BidPercentage,
b.AggregatePercentage as AggregatePercentage,
c.Percentage as AvgPercentage,
a.AggregateType
FROM (SELECT RptPeriod,
CONVERT(DECIMAL(19,6),SUM(case when MetricElement =
@Numerator then DollarValue else 0 end)) / case WHEN
CONVERT(DECIMAL(19,6),SUM(case when MetricElement = @Denominator then
DollarValue else 0 end)) = 0 THEN 1 ELSE CONVERT(DECIMAL(19,6),SUM(case when
MetricElement = @Denominator then DollarValue else 0 end)) END as
BidPercentage,
'MIN' as AggregateType
FROM vwMetricDetails
WHERE Bid = @Bid
AND Region = @Region
AND RptPeriod Between @StartDate AND @SubmittedDate
AND IsAcquirerData = @AcquirerIssuer
AND ProductTypeID = 'C'
AND MetricElement IN (@Numerator,@Denominator)GROUP BY
RptPeriod ) AS a
JOIN (SELECT RptPeriod,MIN(Percentage) as AggregatePercentage FROM
fn_GetBackOfficeChargebacks(@StartDate,@SubmittedDate,@OrgID,@AcquirerIssuer,@Region,@Min,@ReportElement,@Numerator,@Denominator,@CountOrDollar)
GROUP BY RptPeriod) AS b on a.RptPeriod = b.RptPeriod
JOIN
fn_GetBackOfficeChargebacks(@StartDate,@SubmittedDate,@OrgID,@AcquirerIssuer,@Region,@Avg,@ReportElement,@Numerator,@Denominator,@CountOrDollar)
c on a.RptPeriod = c.RptPeriod
ORDER BY 1

Here is the SELECT in the function, it returns Aggregates AVG or MIN, MAX
depending upon the value of the 6th param:

INSERT @Table
SELECT BID, Organization_NameShort,RptPeriod,
SUM(case when MetricElement = @Numerator then DollarValue else 0
end) as TotalNumerator,
SUM(case when MetricElement = @Denominator then DollarValue else
0 end) as TotalDenominator,
CONVERT(DECIMAL(19,4),SUM(case when MetricElement = @Numerator
then DollarValue else 0 end)) / CONVERT(DECIMAL(19,4),SUM(case when
MetricElement = @Denominator then DollarValue else 0 end)) as Percentage
FROM vwMetricDetails
WHERE Region = @Region
AND RptPeriod Between @RptStart AND @RptEnd
AND IsAcquirerData = @AcquirerIssuer
AND ProductTypeID = 'C'
AND MetricElement IN (@Numerator,@Denominator)
AND BID IN (select BID from vwPeerGrouping WHERE ReportElement =
@ReportElement AND PeerGroupingID = @PeerGroupingID
AND BID NOT IN (Select BID From vwPeerExclusions WHERE ApplyTypeID =
@ApplyType AND ReportElement = @ReportElement AND Period = @RptEnd))
Group By BID,Organization_NameShort,RptPeriod

> Quick test: create a copy of the UDF with a different name and use it for
> the second call.
[quoted text clipped - 20 lines]
> >
> > Thanks

Reply to this Message