October 16, 2002 at 3:18 pm
OK - I'll try to explain...
I have a "piece of code" which will most likely by reused many times and it has to do with "Billable vs. Non-Billable" Holdings.
There are several factors which can make an account B or N - so - I'd like to reuse code whenever possible. What I've done is create a UDF that implements the "exclusion" code and returns a TABLE data type list of accounts who meet the criteria. I was then going to join to this "Table" and use it whenever I needed it.
Next scene - I've created the UDF and am returning a table - I can join to the table(function) in my other Stored Procedures.
Good huh ????
NO - Why? - It's SUPER SLOW !!!!!
So slow in fact that it's worthless and I need to find another solution.
Here a shell of the UDF:
CREATE FUNCTION dbo.GetBillableHoldings ()
RETURNS table
AS
RETURN
(
SELECT
H.id_Holding,
H.ID_Account,
H.id_Security,
H.cTotalUSDValueAmt
FROM
cdAccount AC
JOIN cdiHolding H
ON AC.id_Account = H.ID_Account
WHERE
AC.ID_VehicleType != 'SD'
AND AC.OverrideBillingYes = 'N'
)
This UDF returns only "Billable" holdings
Now - here's a piece of the SLOW!!! SP:
SELECT
H.id_Security,
H.cTotalUSDValueAmt
FROM
cdAccount AC
JOIN dbo.GetBillableHoldings(@g_BillingDate) H
ON AC.id_Account = H.ID_Account
The join works fine - all appears fine - but it goes from overall time of ~3 seconds for 800 rows to ~3 seconds per row !!!!!!
Any suggestions ???
Thanks all in advance - B
October 16, 2002 at 3:32 pm
I think the problem is that data in table variables is always the subject of a Table Scan in the query parser - is it not possible to convert your function into a view which could even be indexed?
October 16, 2002 at 5:21 pm
Your function in the example is not restricting on the billingdate is this an issue.
I thought in line functions get optimised in a similiar fashion to views, but I would agree a view would be better, maybe even an indexed views. (Not really looked into them)
Simon Sabin
Co-author of SQL Server 2000 XML Distilled
http://www.amazon.co.uk/exec/obidos/ASIN/1904347088
Simon Sabin
SQL Server MVP
http://sqlblogcasts.com/blogs/simons
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply