Solved

Calling a user defined function in a query is slow

Posted on 2008-06-12
7
339 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
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

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.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

822 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