The execution of sub queries in a view

  • Yes, we have nested views alright! Way more than I am accustomed to. In two of the sub queries there is a view VStudents which is based on a view which is based on a view based on tables. I guess that would be three layers of nesting.

  • ...and that's when the fight started...

    😀

    Yeah, that alone is going to cause you performance issues. SQL Server is going to have a hard time resolving everything and optimizing the queries for that sort of process. When you think about it, it's doing a great job with this. Not the job you want of course...

    Don't worry. I've seen worse. We had a bunch of developers go off and build a database using table valued user defined functions layered four and five deep. It was a beautiful design. Ran like molasses at the North Pole, but it looked pretty.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I think I am going to try and diagram the binding process for these views for the developers so they can see how expensive the mask is for the application's performance. They have been using the views for several years now and changing their ways will be difficult. We are definitely experiencing some performance issues with these.

    Thanks for your help Grant, it has been immensely valuable. Please post any additional observations you may have from further testing.

  • Glad I could be at all useful. I'll post anything I find and I'm running up a blog post about this too.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • I found the URL to your blog in your profile. I'll look for your post.

    Thanks,

    Steve

Viewing 5 posts - 16 through 19 (of 19 total)

You must be logged in to reply to this topic. Login to reply