how to make udf in join more efficient..

when I use the udf in join, it takes lot more time.. can we index it?
LVL 5
25112Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

25112Author Commented:
In the below example,

SELECT  COUNT(*) from
dbo.UDF_PastsAssessment(2009,2013,'2014-05-01') R  
 INNER JOIN  temp I ON I.CartID = R.PartID
 LEFT JOIN LockCart LC ON LC.PartID = R.PartID  
 LEFT  JOIN  temp X ON  X.CartID = R.PartID  
 INNER JOIN CAPBin CAP ON CAP.PID = R.PPID AND CAp.PartID = R.PartID  

takes 22 seconds..

but if i dump

SELECT  * into temp1 from dbo.UDF_PastsAssessment(2009,2013,'2014-05-01')

and use temp1 instead it runs in 1 second. (temp1 is not indexed)

is there anything we do to index the UDF to make it join as good as a regular table.
0
Shaun KlineLead Software EngineerCommented:
The reason, I believe, that it takes longer when directly included is because the UDF runs for each row that is joined. If you are going to use static values in the UDF, the temp table or a CTE is a good alternative.
0
25112Author Commented:
dbo.UDF_PastsAssessment(2009,2013,'2014-05-01') return 220k records. still it will only JOIN on the condition, and not every record, right?

dbo.UDF_PastsAssessment(@x,@y,@z) - I just used the parameters as static for this example.. They will passed (different each time) in real time...
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

Shaun KlineLead Software EngineerCommented:
By static, I mean that the values passed to your UDF will not vary based on data from other tables. For example, if the last parameter of your UDF (the date) came from the LockCart table, it could vary across your data set and the UDF would need to be evaluated for each row. However, if you are passing the three parameters in on a stored procedure call, then the parameters will not vary, and moving the results of the UDF evaluation into either a temp table or CTE would prevent the UDF from being evaluated for each row in the data set.
0
PortletPaulfreelancerCommented:
>>"is there anything we do to index the UDF to make it join as good as a regular table."
you cannot index the output of a function, because it is calculated then and there; based on the parameters.

so, the simple answer to your question is: No.

Dumping to a temp table may be a good solution.

Without knowing anything more about the function itself there's not much that can be added IMHO
0
25112Author Commented:
thank you..

yes, If I dump the value to temp table, it is helping..

as per your suggestion, as I dump the UDF recordset into a temptable..
(select * into temptable from udf_abc)
would indexing temptable help? Two NonClustered indexs..... on PPID and PartID ? I tried them, and It did not seem to help.

will CTE be better than temp table idea in this case?
0
Scott PletcherSenior DBACommented:
You can't index the function, but you can get vastly better performance if the existing multi-line function can be changed to an inline-table-valued function.


>> would indexing temptable help? Two NonClustered indexs..... on PPID and PartID ? I tried them, and It did not seem to help. <<

Indexing should help, but only a clustered indx on both columns ( PartID, PPID ).  99+% of the time, nonclustered index(es) on a temp table are useless.
0
25112Author Commented:
a)
>>Indexing should help, but only a clustered indx on both columns ( PartID, PPID ).  

a clustered index of 2 columns.. (even though the PartID and PPID are distinct in the joins, right?)

b)
>>you can get vastly better performance if the existing multi-line function can be changed to an inline-table-valued function.

can you give a small example from the join?

SELECT  COUNT(*) from
 dbo.UDF_PastsAssessment(2009,2013,'2014-05-01') R  
  INNER JOIN  temp I ON I.CartID = R.PartID
  LEFT JOIN LockCart LC ON LC.PartID = R.PartID  
  LEFT  JOIN  temp X ON  X.CartID = R.PartID  
  INNER JOIN CAPBin CAP ON CAP.PID = R.PPID AND CAp.PartID = R.PartID
0
Scott PletcherSenior DBACommented:
a) yes.

b) the query calls the function the same way "from dbo.udf_....", it's the coding of the function that's different.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.