Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Calling a user defined function in a query is slow

Posted on 2008-06-12
7
Medium Priority
?
351 Views
Last Modified: 2012-06-21
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.ValidEmailFast(Client_Email, Call_LST_QUEUE, Call_LST_CALLCAT, ivProdType_Hierarchy, Call_LST_CALLUDF4, Call_LST_CALLUDF3, Call_LST_CALLUDF6) = 1
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

Open in new window

0
Comment
Question by:powlin
  • 4
  • 2
7 Comments
 
LVL 27

Assisted Solution

by:MikeToole
MikeToole earned 800 total points
ID: 21770470
If all the parameters for ValidEmail() are in the final selection then this approach should do it, if not, you'll need to add them to the inner Select

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 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 )
WHERE EmailValidator.dbo.ValidEmailFast(Client_Email, Call_LST_QUEUE, Call_LST_CALLCAT, ivProdType_Hierarchy, Call_LST_CALLUDF4, Call_LST_CALLUDF3, Call_LST_CALLUDF6) = 1
 
0
 
LVL 27

Assisted Solution

by:Zberteoc
Zberteoc earned 1200 total points
ID: 21770564
Try to pull out all the secondaru ON cluase condition out in the WHERE clause. :eave in the ON clause only the primary keys or conditions for the join, the ones that relate the tables only:
/* 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 
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 
AND EmailValidator.dbo.ValidEmailFast(Client_Email, Call_LST_QUEUE, Call_LST_CALLCAT, ivProdType_Hierarchy, Call_LST_CALLUDF4, Call_LST_CALLUDF3, Call_LST_CALLUDF6) = 1
-- extra ON clause conditions here
AND L.Lang_ItemDataText = 'TELECOM' /* this join return about 3 rows */
AND CL.Client_RessFlag = 0 

Open in new window

0
 
LVL 1

Author Comment

by:powlin
ID: 21770826
MikeToole : I tried your suggestion but the speed is still slow and the execution plan is the exactly the same.
It look like SQL Server ignore my subquery and reorder it.
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 1

Author Comment

by:powlin
ID: 21770844
Zberteoc : like MikeToole the modification resulted is the exactly same execution plan and the speed is still slow.
0
 
LVL 27

Expert Comment

by:Zberteoc
ID: 21771566
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) = 1

Open in new window

0
 
LVL 1

Author Comment

by:powlin
ID: 21772025
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)
0
 
LVL 1

Accepted Solution

by:
powlin earned 0 total points
ID: 21772295
Great I found a way to trick the execute plan while reading the documentation of TOP

If a SELECT statement that includes TOP also has an ORDER BY clause, the rows to be returned are selected from the ordered result set.

I added a TOP 100 PERCENT in the subquery and an ORDER BY Call_ID so it must evaluate the subquery before the others JOIN.
	SELECT Call.Call_ID, Client_Email, Call_LST_QUEUE, Call_LST_CALLCAT, ivProdType_Hierarchy, Call_LST_CALLUDF4, Call_LST_CALLUDF3, Call_LST_CALLUDF6 
	FROM Call 
	INNER JOIN (
		SELECT TOP 100 PERCENT CA.ACT_Call_ID AS Call_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 TOP 1 COND_LASTTIMERUN FROM IA_CONDITION WHERE Cond_SQLWhere LIKE '%/*IDMAILFERMTELECOM*/%')
		ORDER BY Call_ID
	) CA ON Call.Call_ID = CA.Call_ID 
	INNER JOIN Lang L ON Call.Call_LST_CALLCAT = L.Lang_Id AND Lang_ItemDataText = 'TELECOM' 
	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

Open in new window

0

Featured Post

Ask an Anonymous Question!

Don't feel intimidated by what you don't know. Ask your question anonymously. It's easy! Learn more and upgrade.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

877 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question