March 5, 2004 at 10:30 am
I have two UDF's joined in a SP. They both have a datetime param that is to be passed from the SP. The SP has a param of date time to pass to the UDFs. When I pass the SP param the SP runs forever and will not return a result. If I hard code the date in the SP for the UDFs instead of passing the SP param it work fine and is efficient.
I have the same problem when calling the UDFs alone in script. If I declare a datetime variable and pass it to the UDF it won't work. If I hard code the date it work fine. Also, the UDFs call other UDFs with the same datetime param. Again, hard coding the date runs fine and all nested UDFs process and it is efficient. Passing a datetime param won't run, the process runs forever. I have also tried passing the param as a varchar and this does not work as well.
Do I need to do something else to get the UDFs to take a variable in script?
Thanks
March 5, 2004 at 10:35 am
Could you post the code?
I had a similar issue but I was passing the dates in as a delimited string and using another UDF to parse that string into the start and end dates. The issue I had was that SQL was not implicitly converting the varchar fields into dates. So i changed my UDF to accept the dates as datetime variables and have not had problems.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 5, 2004 at 10:59 am
This is the script I am using to run the function:
-- This just runs forever
DECLARE @dtDateEnd datetime
SELECT @dtDateEnd = '03/05/04'
SELECT * FROM [dbo].[fnCMStatsMTD_Summary](@dtDateEnd)
--- This work fine
SELECT * FROM [dbo].[fnCMStatsMTD_Summary]('03/05/04')
--If I hard code the date in the procedure it runs fine (50sec)
ALTER PROCEDURE usp_CMStats_MTDTest @dtDateEnd datetime
AS
SELECT fnAdmissionsMTD_CR_CM.Agency,
fnAdmissionsMTD_CR_CM.Team_Number, fnAdmissionsMTD_CR_CM.Case_Number,
fnAdmissionsMTD_CR_CM.Last_Name, fnAdmissionsMTD_CR_CM.First_Name,
fnCMStatsMTD_Summary.F2F, fnCMStatsMTD_Summary.Collateral
FROM dbo.fnAdmissionsMTD_CR_CM('3/5/04') fnAdmissionsMTD_CR_CM
LEFT OUTER JOIN dbo.fnCMStatsMTD_Summary('3/5/04') fnCMStatsMTD_Summary
ON fnAdmissionsMTD_CR_CM.Consumer_ID = fnCMStatsMTD_Summary.Consumer_ID
--If I try to pass the date time variable to the functions in the procedure it runs forever.
ALTER PROCEDURE usp_CMStats_MTDTest @dtDateEnd datetime
AS
SELECT fnAdmissionsMTD_CR_CM.Agency,
fnAdmissionsMTD_CR_CM.Team_Number, fnAdmissionsMTD_CR_CM.Case_Number,
fnAdmissionsMTD_CR_CM.Last_Name, fnAdmissionsMTD_CR_CM.First_Name,
fnCMStatsMTD_Summary.F2F, fnCMStatsMTD_Summary.Collateral
FROM dbo.fnAdmissionsMTD_CR_CM(@dtDateEnd) fnAdmissionsMTD_CR_CM
LEFT OUTER JOIN dbo.fnCMStatsMTD_Summary(@dtDateEnd) fnCMStatsMTD_Summary
ON fnAdmissionsMTD_CR_CM.Consumer_ID = fnCMStatsMTD_Summary.Consumer_ID
The UDF is a parameterized view that uses the date passed to select records by a date field.
Is there some coversion or something that needs to be done to the variable is the procedure to get it to pass to the function?
March 5, 2004 at 1:05 pm
Is the date field being referenced smalldatetime or datetime? How many rows would you expect to be returned? I would not consider 50 sec good performance depending on the rows returned. One issue is that I don't know how a UDF uses indexes and showing the query plan in Query Analyzer does not show you the plan. Do you have an index on the field in the filter?
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
March 7, 2004 at 10:21 pm
The date is being referenced datetime in the functions. The stored procedure returns about 4000 rows in under a minute when the date is hardcoded in the procedure. The date fields used don't have indexes in any of the tables.
Anything else? What I can't figure out is why hardcoding the date passed to the function works, but passing a datetime variable with the same date to the function does not.
Thanks,
Cammy
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply