powlin
asked on
Calling a user defined function in a query is slow
I'm having problem performance from a query. This query is using a function in the WHERE section.
I have 2 query very similar at the exception of a join
in the fast one (about 300ms) the join part is
Lang_ItemDataText <> 'TELECOM'
and when chaning <> to a =
Lang_ItemDataText = 'TELECOM'
the query take about 20 secs.
I noticed that the execution plan is slightly different when changing the operator from <> to =
If I remove the line
AND EmailValidator.dbo.ValidEm
they both are fast.
These query return most of the time from 0 to 3-4 rows max so the function is supposed to be called only 3-4 times.
It seem that when the execution plan change it call my function on more row. This function is not very fast but calling it on 3-4 rows should be no problem.
Anyone having idea to resolve this performance problems, maybe changing some part of the query to be sure that the function is only called last.
Thanks
I have 2 query very similar at the exception of a join
in the fast one (about 300ms) the join part is
Lang_ItemDataText <> 'TELECOM'
and when chaning <> to a =
Lang_ItemDataText = 'TELECOM'
the query take about 20 secs.
I noticed that the execution plan is slightly different when changing the operator from <> to =
If I remove the line
AND EmailValidator.dbo.ValidEm
they both are fast.
These query return most of the time from 0 to 3-4 rows max so the function is supposed to be called only 3-4 times.
It seem that when the execution plan change it call my function on more row. This function is not very fast but calling it on 3-4 rows should be no problem.
Anyone having idea to resolve this performance problems, maybe changing some part of the query to be sure that the function is only called last.
Thanks
/* this one is fast */
SELECT Call.*
FROM Call
INNER JOIN (
SELECT CA.ACT_Call_ID AS Call_ID, CE.EMA_ID, CE.EMA_CreationDate
FROM C2_Email CE INNER JOIN C2_Activities CA ON CA.ACT_ACT_ID = CE.EMA_ID
WHERE EMA_Read = 0
AND EMA_SentMail = 0
AND EMA_CreationDate > (SELECT COND_LASTTIMERUN FROM IA_CONDITION WHERE Cond_SQLWhere LIKE '%/*IDMAILFERMTELECOM*/%') /* 1 row */
) CA ON Call.Call_ID = CA.Call_ID
INNER JOIN Lang L ON Call.Call_LST_CALLCAT = L.Lang_Id AND Lang_ItemDataText <> 'TELECOM' /* return about 20 rows */
INNER JOIN Client CL ON CL.Client_ID = Call.Call_Client_ID AND CL.Client_RessFlag <> 0
INNER JOIN ivProdType IPT ON IPT.ivProdType_ID = Call.Call_ivProdType_ID
WHERE
/*IDMAILFERMTELECOM*/
Call_HistoFlag = 1
AND EMA_CreationDate > Call_DateCompleted
AND EmailValidator.dbo.ValidEmailFast(Client_Email, Call_LST_QUEUE, Call_LST_CALLCAT, ivProdType_Hierarchy, Call_LST_CALLUDF4, Call_LST_CALLUDF3, Call_LST_CALLUDF6) = 1
/* this one is slow */
SELECT Call.*
FROM Call
INNER JOIN (
SELECT CA.ACT_Call_ID AS Call_ID, CE.EMA_ID, CE.EMA_CreationDate
FROM C2_Email CE INNER JOIN C2_Activities CA ON CA.ACT_ACT_ID = CE.EMA_ID
WHERE EMA_Read = 0
AND EMA_SentMail = 0
AND EMA_CreationDate > (SELECT COND_LASTTIMERUN FROM IA_CONDITION WHERE Cond_SQLWhere LIKE '%/*IDMAILFERMTELECOM*/%') /* 1 row */
) CA ON Call.Call_ID = CA.Call_ID
INNER JOIN Lang L ON Call.Call_LST_CALLCAT = L.Lang_Id AND Lang_ItemDataText = 'TELECOM' /* this join return about 3 rows */
INNER JOIN Client CL ON CL.Client_ID = Call.Call_Client_ID AND CL.Client_RessFlag = 0
INNER JOIN ivProdType IPT ON IPT.ivProdType_ID = Call.Call_ivProdType_ID
WHERE
/*IDMAILFERMTELECOM*/
Call_HistoFlag = 1
AND EMA_CreationDate > Call_DateCompleted
AND EmailValidator.dbo.ValidEmailFast(Client_Email, Call_LST_QUEUE, Call_LST_CALLCAT, ivProdType_Hierarchy, Call_LST_CALLUDF4, Call_LST_CALLUDF3, Call_LST_CALLUDF6) = 1
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Zberteoc : like MikeToole the modification resulted is the exactly same execution plan and the speed is still slow.
I expected that but I thought it was worth a try. The two versions of the query are equivalent and SQL in the background optimizes them to the best execution plan it can. Try to get the row first as a derived table to which you only apply the function. Make sure that in the inner select you return all the column to be used in the function.
SELECT
*
FROM
(
SELECT Call.*
FROM Call
INNER JOIN (
SELECT CA.ACT_Call_ID AS Call_ID, CE.EMA_ID, CE.EMA_CreationDate
FROM C2_Email CE INNER JOIN C2_Activities CA ON CA.ACT_ACT_ID = CE.EMA_ID
WHERE EMA_Read = 0
AND EMA_SentMail = 0
AND EMA_CreationDate > (SELECT COND_LASTTIMERUN FROM IA_CONDITION WHERE Cond_SQLWhere LIKE '%/*IDMAILFERMTELECOM*/%') /* 1 row */
) CA ON Call.Call_ID = CA.Call_ID
INNER JOIN Lang L ON Call.Call_LST_CALLCAT = L.Lang_Id
INNER JOIN Client CL ON CL.Client_ID = Call.Call_Client_ID
INNER JOIN ivProdType IPT ON IPT.ivProdType_ID = Call.Call_ivProdType_ID
WHERE
/*IDMAILFERMTELECOM*/
Call_HistoFlag = 1
AND EMA_CreationDate > Call_DateCompleted
-- extra ON clause conditions here
AND L.Lang_ItemDataText = 'TELECOM' /* this join return about 3 rows */
AND CL.Client_RessFlag = 0
)
WHERE
EmailValidator.dbo.ValidEmailFast(Client_Email, Call_LST_QUEUE, Call_LST_CALLCAT, ivProdType_Hierarchy, Call_LST_CALLUDF4, Call_LST_CALLUDF3, Call_LST_CALLUDF6) = 1ASKER
I found a way to force the execution plan to execute as I writted the query
I added
OPTION (FORCE ORDER)
at the end of the query and now it's fast.
But this hint seem not working in a view (and this query will be one)
I added
OPTION (FORCE ORDER)
at the end of the query and now it's fast.
But this hint seem not working in a view (and this query will be one)
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
It look like SQL Server ignore my subquery and reorder it.