asked
Convert Function Output in HTML
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.
My best guess is that your multistatement TVFs are causing the performance problems. Using multi statement TVFs (and scalar functions), especially when doing data access but also otherwise, are generally bad ideas. But yes - providing some sample code would help in advising.
It takes a long time - I get that. But it's not at all clear what it is that you are doing, whih is taking a long time. My best bet is that your functions are not performing as fast as they could/should. But that's just a guess, because I don't know anything about where your bottlenecks are or how the functions are written. What have you done to research the performance issue so far? Have you found your worse pain points? Just saying that you have UDFs with calculations doesn't give much info. What kind of UDFs are you using (scalar, multi statement table valued, inline table valued), what kind of calculations and/or data access is performed inside the UDFs?
there are 5 multi table valued functions and 4 inline tvf and i am performing join in these functions and moreover a concat function is there
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.
16 People are following this question.
