June 8, 2011 at 5:42 am
We are using Long queries Combining results sets using Unions. The results sets are returned from table Valued functions and combined in the SPs by unions. When ever some of ours long sps run we face blocking and LCK_M_SH wait time become more prominent and queries goes to suspended mode.
To over Come this problem we are considering Indexed Views to save the time of summation and calculation of big joins.
We want indexed view to be replaced by table value functions.
My question is that Should we use the indexed views or not.
What are the pros and Cons of Indexed views?
I would appreciate the other solutions to tune up the Sps to avoid blocking if we dont use indexed views.
Script of one of our Sp and table value function is attached. This will definitely help.
I would be very thankful for any suggestions. Thanks in Advance.
😀
June 8, 2011 at 2:11 pm
My question is that Should we use the indexed views or not.
Maybe. Maybe not. Depends on various items (e.g. business process, table size, hardware, numberof calls etc.).
What are the pros and Cons of Indexed views?
see BOL
I would appreciate the other solutions to tune up the Sps to avoid blocking if we dont use indexed views.
I think a sproc with more than 800 lines of code referencing a function with more than 300 lines of code again referencing yet another function (dbo.GET_ADM_PARENT_OFFICES_SUBOFFICES_ROWS in the WHERE clause!) with an unknown number of lines is most definitely beyond the scope of a forum question.
I strongly recommend to get a tuning expert in (most probably for more than just a day...).
What definitely needs to be evaluated is the function mentioned above. And a lot more, I'd say...
June 8, 2011 at 2:36 pm
There are so many problems with that code it is hard to know where to start. Every single parameter is a varchar regardless of the actual datatype (dates, ints, etc). This then gets passed along to a super bloated function that has about 10 times as many columns as anything that is using it (at least from what we can see). Then we have a whole collection of unioned queries that keep hitting that same function with slightly different parameters. Add to this the fact that is screams of a financial application. Loans of some sort. You desperately need a real consultant to help with this. You can't possibly expect to use information found on the internet from joe blow to provide anything remotely solid for an application that is dealing with people's money. The function is really scary. It joins to another function and what is most likely a view. :w00t:
If you are looking for ways to make performance improvements there is lots of low hanging fruit in there but until that process is completely rewritten you are going to have all sorts of challenges. Just my 2¢.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply