SQL VIEW works on its own but not when combined

  • thenewbee

    SSC Eights!

    Points: 847

    Hi there,

    I have around 12 views and that works perfect if I execute them individually but when I combine them using UNION ALL it takes long time to execute and results in a time out error.

    Each view is bit complicated and brings back huge volume of data and they are grouped

    Please throw your thoughts on this

    Regards

    tnb

    • This topic was modified 1 year, 2 months ago by  thenewbee.
  • Jeff Moden

    SSC Guru

    Points: 996663

    thenewbee wrote:

    Each view is bit complicated and brings back huge volume of data and they are grouped

    I know it's not the answer you're looking for but the two items above are the likely cause of your problem and you're going to need to do some serious analysis.  It's not like the UNION ALLs are going to create a separate entity that can be easily queried... they all become a one huge execution plan and you may need to "Divide'n'Conquer".  Set-Based code doesn't necessarily mean "all in one query".   It  could also be that the views are written in such a fashion as to be a primary cause of the problem themselves.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 719974

    I agree with Jeff. Without knowing more about the code, it's hard to make recommendations on what to do here. I am often wary of views being used as code encapsulation to the point where they are combined on top of one another. Often there are simpler ways to get the data, but views are being re-used because they're there.

  • Grant Fritchey

    SSC Guru

    Points: 396560

    Combining views, nesting views, and taking large, large data sets without filtering through views, are very common code smells that inevitably lead to trouble. Because views act like tables within the T-SQL code, people tend to think of them as if they are tables. They're not. With the exception of an indexed view (which, is a table, just a fancy one), views do not store data. They have to go to the tables for that data every time. Also, the optimizer has to deal with the execution plan creation and management and all the rest. A very large view combined with a very large view is not the same as one table joined to another. You're literally multiplying the work done by the engine.

    The single biggest problem with T-SQL is that it doesn't lend itself well to code reuse. Almost every mechanism people use to avoid writing the same T-SQL two times, views, functions, actually can lead to problems. It's painful for people to hear, but I usually suggest writing a query twice (or six times) if you can adjust it to fit each of the two (or six) situations rather than slap it into a view. It goes against the grain (code reuse is a vital part of development), but that's just how SQL Server works (and Oracle, and MySQL and PostgreSQL (although a little less than the others in this regard), and...).

    ----------------------------------------------------
    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

  • Jeff Moden

    SSC Guru

    Points: 996663

    I've got to disagree about SQL Server not being good about code reuse and say "It Depends".  And it really depends when it comes to views because they're frequently written in the same fashion that someone would create a report instead of a proper "multi-use object" that can be used in multiple (for example) stored procedures as a summarization of truly common code.  People also have the tendency to write "God" objects that are "infinitely flexible" instead of following the basic rules of programming, especially the rule of "write the code so that it does one thing really, really well".  Case in point is the DelimitedSplit8K function.

     

    Grant already stated this in different words but it's important enough to repeat.  People also forget that a view doesn't necessarily materialize a separate result set when used in a query like a table would.  The code in the view becomes code in the query just as surely as if you had included the code for the view in the query.  The same holds true for CTEs unless you can force materialization with a blocking operator.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".
    "If "pre-optimization" is the root of all evil, then what does the resulting no optimization lead to?"

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • thenewbee

    SSC Eights!

    Points: 847

    Thanks for your replies. what other information do you need ?

  • Grant Fritchey

    SSC Guru

    Points: 396560

    If you want to understand why things are slow, get the execution plan and evaluate what is happening there. If you want us to help, share the execution plan. By the way, the whole plan, the XML, not just a picture. The details behind the operators is how you interpret execution plans, not just looking at the operators. The plan will include the code, which is the other thing that we would need.

    ----------------------------------------------------
    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

  • Isabella Sokolovksaya-426655

    Grasshopper

    Points: 19

    Hope you use the following format for From clause when building the view:

    from TableName WITH(NOLOCK)

  • Chris Harshman

    SSC-Forever

    Points: 42108

    I'm not sure what benefit you believe adding the NOLOCK hint will have for the queries.  It is well documented that this can cause problems with the results.  Just Google "sql server nolock problems"

    In addition, this query could still cause other queries to be blocked by it, since NOLOCK hint really means "ignore locks that other sessions have on the items I'm querying", and it will put its own locks on those tables.

  • steve.powell 14027

    SSC Veteran

    Points: 243

    To simplify what you've said: You are performing a union on several large views and it's timing out.

    This sound like SQL is paging out memory to disk so it can work on the next block of data. AKA, it's run out of actual memory so uses virtual memory to try to complete the operation. Disk I/O is incredibly slow compared to memory, hence the long run time and eventual timeout.

    You could try using CTE's to reduce the number of rows involved so you get a smaller data set to work with, or use table functions rather than views: This might work for you, too.

    Either way, to see if this is the problem: Use Resource monitor (or your preferred disk monitoring software) to see what the I/O is like when running the query. If it's spiking high, (and if you can see it's using the system swap file), then yes, the dataset is too large to hold in memory so the OS is writing it to disk instead resulting in a notable performance hit.

  • TUellner

    SSCrazy

    Points: 2578

    Isabella Sokolovksaya-426655 wrote:

    Hope you use the following format for From clause when building the view: from TableName WITH(NOLOCK)

    I have to agree with Chris. Please don't do this.

  • thenewbee

    SSC Eights!

    Points: 847

    Thanks all for the advise, I'm trying to figure it out by adding few index.

    Unfortunately my It don't allow to upload code and hence I'm helpless on that. Is there a better tool to undertsnad Query plan.

    From the execution plan I can see that there are some worktables. is that something to be avoided

  • Grant Fritchey

    SSC Guru

    Points: 396560

    Reading execution plans is fairly complicated. Follow the link below, or look it up online to find a free book I wrote on the topic. Work tables aren't bad (or good). They're an indication of the need for the query to temporarily store data as it processes it. I can't say more without seeing the plan.

    ----------------------------------------------------
    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

  • Chris Harshman

    SSC-Forever

    Points: 42108

    SentryOne Plan Explorer is a free tool that can help you better understand what is happening in an execution plan.  It also has the ability to "anonymize" the plan so that you can submit it to a forum like this without submitting actual code or table names:

    https://www.sentryone.com/plan-explorer

  • Matt Miller (4)

    SSC Guru

    Points: 124208

    Since you're not at liberty to share the queries - the only input I would have is - eat the elephant one bite a t a time.  The one query with 12 massive views is trying to do too much, so break it into pieces.

    In short - change your process to dump each of the 12 views into an actual table then group by from there.

    If that's too slow - then look at adding an index to said work table to improve the grouping.

    If that's still too slow - then look at how you might pre-aggregate some of the data ahead of time: since you mentioned 12 I am assuming 12 months, so did the older months actually change or do they stay static after some time.

    Using what you know about the data patterns is allowed - if the older data is static don't keep reaggregating over and over.

     

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Viewing 15 posts - 1 through 15 (of 21 total)

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