Dear Geeks, Good day to you!! SQL SERVER 2012 I have 9 udfs which return different modules data for a customer like one for personal another for financial third for Life insurances there is some calculations also in these functions now i need to send html of this data to my client i did that as described in below link no issue
http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/10/24/format-query-output-into-an-html-table-the-easy-way.aspx but when i did it for 2000 clients at a time using outer apply it takes 3-4 minutes, how can i do it what is best practice of it. Please assist me as it irritated me a lot.
Multi-statement table valued functions are notorious for horrific performance. They don't have any statistics available to them, so the optimizer works off of it's assumption of one row of data. As long as you only have one row of data, performance might be OK, but as soon as you have more than one row, performance is going to tank and tank badly. Adding to that, a very common problem is to start joining and nesting these objects. This seriously exacerbates the issue because, again, of the lack of statistics leading to the optimizer making bad choices. This is a very common code smell that simply needs to be replaced with more traditional T-SQL approaches.