[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 193
  • Last Modified:

how to make udf in join more efficient..

when I use the udf in join, it takes lot more time.. can we index it?
0
25112
Asked:
25112
  • 4
  • 2
  • 2
  • +1
4 Solutions
 
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
Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

 
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
 
PortletPaulCommented:
>>"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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now