Solved

Calling a user defined function in a query is slow

Posted on 2008-06-12
7
335 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
Free Gift Card with Acronis Backup Purchase!

Backup any data in any location: local and remote systems, physical and virtual servers, private and public clouds, Macs and PCs, tablets and mobile devices, & more! For limited time only, buy any Acronis backup products and get a FREE Amazon/Best Buy gift card worth up to $200!

 
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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how the fundamental information of how to create a table.

757 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

21 Experts available now in Live!

Get 1:1 Help Now