May 11, 2007 at 2:37 am
Hello All,
i have one stored procedure. in which there is query which is retriving data from table. also the same query calls a function.
now my problem is i have to call that function only once and not for all records which is getting displayed.
for eg.
SELECT
@iResType AS [ResType],
1 as [AllowPlot],
1 as [groupID],
'IN/OUT' AS groupName,
cast(ID as varchar(32)) AS resultID,
[NAME] AS resultName,
[DT],
cast([DATA] as varchar(128)) AS [Value],
DATA as [fDataValue],
dbo.concatenateTextsWithTimeStamp(comment, annot, DT) AS resultNotes,
dbo.f_CalculateSummaryTime([DT], 1, 3) as [NursingDate],
dbo.f_GetSummaryValue(@szPatientID,[NAME],@endDate,@startDate,'IN/OUT') as SummaryValue
FROM
dbo.nursing_out
WHERE
MRN = @szPatientID
AND DT >= @startDate
AND DT <= @endDate
here i have called function f_getSummaryvalue. now wht i want is it should only call once for each id as query is returning multiple id. so that it will reduce the execution time of query.
how should i deal with this matter??? anyone plz help me out
May 11, 2007 at 4:13 am
If the output needs to change for all ids, but that all ids are repeated quite often you may have an option (this is assuming that the function really takes a long time to run).
1 - Run the query with the function and the again without the function call, how much of a difference does it make?
2 - Can we see the function source code? Maybe we can spot performance optimisations you don't know.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply