GilaMonster (9/8/2009)
ben.mcintyre (9/8/2009)
I badly need my views here. Due to the size of the data chunks I'm dealing with I have to wrap them in views to stay sane. eg. Produce details are needed frequently, so there's a view wrapping the produce table and the associated brands, grades, classes, product type, varieties, pack types, pack weights, colours, etc (about 10 tables). If I had to deal with these individually in every query, my head would explode, and I would have to upgrade to that 26" monitor.Unfortunately layers of views often tend to result in performance problems, partially from the complexity of the resulting query, partially from the chance that a lower level view will have to be materialised in its entirely, and partially from high compile times as SQL tries to trim down the unrolled query
Yah. Bummer.
GilaMonster (9/8/2009)
(Hey, please, I know this site is prettly much dedicated to the hunting down and elimination of dynamic SQL. I know what rbar is and who Jeff Moden is. I respect and agree with all of that. The dynamic SQL is actually quite necessary 🙂 )
No it's not. There's nothing wrong with dynamic SQL used correctly. It's cursors and while loops that we're allergic to here.
You're using global cursors?
(Giggle) Sorry, I meant cursors and while loops. Unfortunately, these tend to occur with Dynamic SQL for me, hence the association.
Global cursors. Disgusting, I know. In this case, I need to declare the cursor using dynamic SQL. Hence the global cursor. A local one evaporates with the EXEC statement.
I believe there is no other way (please correct me if there is !).
Don't worry, there aren't many cursors and they are local cursors where there's no dynamic SQL.
I would argue that the cursors are actually necessary where used as well, but that's another rather long piece of string.
I do love this site, but I have to 'come out' to being an application developer as well as SQL afficionado. It's hard sometimes. Sometimes I feel like an Israeli citizen from a Palestinian family. It's fine as long as I don't mention procedural code or OR/M (in the SQL forums), or query tuning and performance (in the coding forums).