question

hnb13686 avatar image
hnb13686 asked

Optimize query having an CLR call

I have a query that joins two tables based on a join condition. The join condition is the result of a CLR function which essentially is supplied a test from one table and regular expression from another table and returns true if there is a match, false otherwise. I believe that it is calling the assembly each time. that makes it slow but if the assembly is supplied a table and it works on the dataset, the execution will be faster. Currently, I only know of SqlXML datatype by which I can pass a data table and get a result data table. Do you think its a good ideaa dn will help query go faster? is there a better approach? I am currently working on creating that and seeing for myself but i would like to know if there is soemthing better that i dont know about.
sqloptimizationclr
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Can you post an example of the code you're currently using?
0 Likes 0 ·

1 Answer

· Write an Answer
Grant Fritchey avatar image
Grant Fritchey answered
This is effectively a function on a column in the ON, HAVING or WHERE clauses. It's going to prevent statistics use in the optimizer which will lead to bad cardinality estimates for the query. That results in execution plans that are sub-optimal. First suggestion, don't use CLR for this. Do it within T-SQL as a query, but not a function. Second option, and absolutely more likely to not work and/or lead to other issues, you could experiment with query hints attempting to force different join types into the query. Note, I'm suggesting you get rid of the CLR on the JOIN operation, I don't have a problem with CLR in general, just that this situation is not a good use.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.