March 16, 2011 at 9:14 am
I have a function that seems to take about 12 seconds to run but if I run the same code in SQL Query Analyzer it run instantly. Any thoughts on how I can get my function to run just as fast?
My Function:
CREATE FUNCTION dbo.fnGetCarrierLoadsNoResponse(@iGRP_ID int, @sStartDt varchar(30), @sEndDt varchar(30))
RETURNS int
AS
BEGIN
DECLARE @iCount int
IF Len(@sStartDt) = 0 and Len(@sEndDt) = 0
BEGIN
SELECT @iCount = count(*)
FROM k_tblAltCarrierLink acl
JOIN k_tblQuoteMain qm ON qm.qm_id = acl.qm_id
LEFT JOIN k_tblCarrierLoads cl ON cl.qm_id = acl.qm_id AND cl.usr_group_id = @iGRP_ID
JOIN k_tblUsers u ON u.usr_id = acl.usr_id
WHERE u.usr_group_id = @iGRP_ID
AND cload_id is null
END
ELSE
BEGIN
SELECT @iCount = count(*)
FROM k_tblAltCarrierLink acl
JOIN k_tblQuoteMain qm ON qm.qm_id = acl.qm_id
LEFT JOIN k_tblCarrierLoads cl ON cl.qm_id = acl.qm_id AND cl.usr_group_id = @iGRP_ID
JOIN k_tblUsers u ON u.usr_id = acl.usr_id
WHERE u.usr_group_id = @iGRP_ID
AND cload_id is null
AND qm.qm_entry_dt > @sStartDt
AND qm.qm_entry_dt < @sEndDt
END
RETURN @iCount
END
If in SQL Query Analyzer I run:
DECLARE @iGRP_ID int
DECLARE @iCount int
SET @iGRP_ID = 16
SELECT @iCount = count(*)
FROM k_tblAltCarrierLink acl
JOIN k_tblQuoteMain qm ON qm.qm_id = acl.qm_id
LEFT JOIN k_tblCarrierLoads cl ON cl.qm_id = acl.qm_id AND cl.usr_group_id = @iGRP_ID
JOIN k_tblUsers u ON u.usr_id = acl.usr_id
WHERE u.usr_group_id = @iGRP_ID
AND cload_id is null
SELECT @iCount
Returns in 1 second
select dbo.fnGetCarrierLoadsNoResponse(16, '', '') as loads_never_responded
Returns in 12 seconds
Any thoughts on how to speed my function up?
March 16, 2011 at 9:25 am
my knee jerk reaction is to use a table value function to get whatever data you need;
for now lets see if a slightly different scalar function will work.
first thing i'd want to do is make sure all data types are the same...you are using varchar(30) for your parameters, but it's pretty clear you are using it to compare datetime columns...
i think by using ISNULL, you can remove the IF...ELSE, and just assume everything's in the right date range...
what do you think of this?:
CREATE FUNCTION dbo.fnGetCarrierLoadsNoResponse(@iGRP_ID int, @sStartDt datetime, @sEndDt datetime)
RETURNS int
AS
BEGIN
DECLARE @iCount int
SELECT
@iCount = count(*)
FROM k_tblAltCarrierLink acl
INNER JOIN k_tblQuoteMain qm
ON qm.qm_id = acl.qm_id
LEFT JOIN k_tblCarrierLoads cl
ON cl.qm_id = acl.qm_id
AND cl.usr_group_id = @iGRP_ID
INNER JOIN k_tblUsers u
ON u.usr_id = acl.usr_id
WHERE u.usr_group_id = @iGRP_ID
AND cload_id is null
AND qm.qm_entry_dt > ISNULL(@sStartDt,0) --the minimum SQL date: 1900-01-01
AND qm.qm_entry_dt < ISNULL(@sStartDt,GETDATE()) --the logical max date...right now.
RETURN @iCount
END
select dbo.fnGetCarrierLoadsNoResponse(16, NULL, NULL) as loads_never_responded
Lowell
March 16, 2011 at 9:25 am
While there is some overhead just in having code in a UDF instead of a script, it's usually about 3-5% as a performance hit.
Here are the things I suspect are affecting your UDF more heavily than that:
Parameter Sniffing
Check out http://sqlinthewild.co.za/index.php/2007/11/27/parameter-sniffing/
If statements and Control Flow
Take a look at the execution plan for the query. It's going to be less optimized than it might, because of the If statements.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 16, 2011 at 9:34 am
is there an index on the [qm_entry_dt] column on table [tblQuoteMain]?
Lowell
March 16, 2011 at 9:44 am
there is no index on qm_entry_dt
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply