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.
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.
16 People are following this question.
