Complex views

  • sizal0234

    SSCrazy

    Points: 2194

    Hello,

    This post is more of asking suggestion on optimizing views.

    Does anyone have a checklist or list of things to check while tuning complex view.

    Please share any good strategies you have or reading articles which can help more on this.

    I am looking at below things. I know things change based on expected data so any general advice is also appreciated.

    1. Join order  - how to make sure what is the correct join order ( Do we have to start from small tbl?)
    2. if the left joins is using the same table
    3.  any cross joins
    4.  execution time of the view - does this look good for checking the timeset statistics time on

      select * from [dbo].[ViewName]

      set statistics time off

      Source for above code.. (https://stackoverflow.com/questions/31941690/how-do-i-get-the-execution-time-of-an-view)

  • Phil Parkin

    SSC Guru

    Points: 244589

    As well as a visual check looking for poor practices (the ones you have mentioned are not necessarily poor, by the way), you should be analysing the actual execution plan.

    I'd be looking for triangular joins, non-sargable predicates, scalar UDFs, joins and predicates without supporting indexes, unnecessarily complex expressions ... the list goes on, and a lot comes down to experience.

     

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • Chris Harshman

    SSC-Forever

    Points: 42108

    Also it should be noted that trying to optimize a query by rearranging the join order in the vast majority of cases will not help.  SQL Server's optimizer will try to re-evaluate all the potential join orders each time the query is compiled anyway, so focus join order on what makes the query more readable and logical.

  • Jeffrey Williams

    SSC Guru

    Points: 88549

    One thing I have seen a lot is using the *wrong* join...often times I see code that uses a left outer join when an inner join is appropriate.  This will often cause SQL Server to ignore optimizations related to the outer table - and instead of getting a hash or merge join a nested loop join is used.

    Another thing to consider is that a view is not materialized in any way - the code from the view will be incorporated into the calling query and then optimized.  Any timings based on selecting directly from the view are meaningless for the queries using that view - and, depending on how the view is structured - joins and outer/cross apply's in the view can often be factored out of the calling query if none of the relevant columns from those joins/apply's are utilized in the calling query.

    Cross Joins are not an issue - if the requirement for that query is to use a cross join.  More important are *accidental* cross joins and incorrect joins (those not specifying the full PK when the PK is a multi-column primary key).

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Grant Fritchey

    SSC Guru

    Points: 396560

    Since a view is a definition of a query, but is not the actual query that is run when the view is accessed, tuning a view is quite difficult. For example, Let's say you have a view that accesses five tables. You run a query against the view that pulls columns from only two of those tables. The optimizer does a thing it calls simplification. It tosses the tables it doesn't need to satisfy a query, so if you look at the execution plan for the query, you'll see that only the two tables needed are accessed (just an example, it could have to access three of the tables, depending on the foreign keys, etc., you get the idea).

    The single most important thing you can do for views is do not, DO NOT, nest them, meaning, put views inside of views. Also, do not, DO NOT, join views, meaning, treat views as tables and start joining them together. These two common code smells lead to all sorts of major performance issues because you're demanding the optimizer to unpack increasingly complex queries (the five tables of this view, the five tables of that view, and the five tables of the third view, some of which overlap, some don't, some are being accessed, some aren't, etc., etc.).

    Remember, above all when dealing with views, views are just a query. They do not define data storage. That's what tables are. Further, the view definition is not necessarily how the optimizer is going to see things.

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

    The Scary DBA
    Author of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd Edition
    Product Evangelist for Red Gate Software

  • as1981

    SSCrazy

    Points: 2733

    Phil Parkin wrote:

    ...

    I'd be looking for triangular joins...

    I've not heard the phrase triangular joins before. Does it refer to the following situation?:

    Table A is joined to Table B

    Table A is joined to Table C

    Table B is joined to Table C

    It would cause extra work for the optimiser as it has to remove one of the joins from the query?

     

  • Phil Parkin

    SSC Guru

    Points: 244589

    as1981 wrote:

    Phil Parkin wrote:

    ...

    I'd be looking for triangular joins...

    I've not heard the phrase triangular joins before. Does it refer to the following situation?:

    Table A is joined to Table B

    Table A is joined to Table C

    Table B is joined to Table C

    It would cause extra work for the optimiser as it has to remove one of the joins from the query?

    I see where you are coming from, but that's not it (the situation you describe is not necessarily an issue).

    Instead, the 'triangular' bit refers to the 'shape' of the data which is returned by the join. The following article makes things very clear:

    https://www.sqlservercentral.com/articles/hidden-rbar-triangular-joins

     

     

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • as1981

    SSCrazy

    Points: 2733

    Thanks for your reply and information.

Viewing 8 posts - 1 through 8 (of 8 total)

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