Solved

Calling a user defined function in a query is slow

Posted on 2008-06-12
7
336 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 200 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 26

Assisted Solution

by:Zberteoc
Zberteoc earned 300 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 26

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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

Suggested Solutions

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
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.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

911 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now