June 1, 2010 at 12:31 pm
This function uses a Table variable to return a result set. The issue is the db is getting large enough that we are seeing a significant slow down when its running. I have changed some Stored Procs to use temp tables with indexes to speed things up but I'm not sure how to do it in a function. Any help would be great.
ALTER FUNCTION [dbo].[GetEventTrackSearchResultsForDateRange]
(
@DateRangeFrom datetime,
@DateRangeTo datetime
)
RETURNS @EventTrackMatch_DateRange TABLE(EventTrackID int)
AS
BEGIN
IF @DateRangeFrom IS NOT NULL AND @DateRangeTo IS NOT NULL
BEGIN
INSERT INTO @EventTrackMatch_DateRange(EventTrackID)
SELECT et.EventTrackID
FROM EventTrack et
WHERE DateDiff(d, et.DateOccurred, @DateRangeFrom) <= 0
AND DateDiff(d, et.DateOccurred, @DateRangeTo) >= 0
--et.DateOccurred BETWEEN @DateRangeFrom AND @DateRangeTo
END
RETURN
END
June 1, 2010 at 12:39 pm
Well, I cannot test any of the code I just did, but can you test this, and let me know if you get any improvement?
ALTER FUNCTION [dbo].[GetEventTrackSearchResultsForDateRange]
(
@DateRangeFrom DATETIME ,
@DateRangeTo DATETIME
)
RETURNS @EventTrackMatch_DateRange TABLE ( EventTrackID INT )
AS
RETURN
( SELECT et.EventTrackID
FROM EventTrack et
WHERE DATEDIFF(d, et.DateOccurred, @DateRangeFrom) <= 0
AND DATEDIFF(d, et.DateOccurred, @DateRangeTo) >= 0
AND @DateRangeFrom IS NOT NULL
AND @DateRangeTo IS NOT NULL
)
The key here, if it works nicely, is to create an inline table valued function, that can scale better, since it has no "Code" inside the function, just a select, that can create a single execution plan for the query, depending on how you use it in the main query.
Try it, and let me know.
Cheers,
J-F
June 1, 2010 at 12:41 pm
Next thing to check, is if it scales well, depending on your indexes for the table you are using within the function. Can you execute it once, and post the execution plan? We can check it out.
Cheers,
J-F
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply