April 24, 2019 at 11:47 am
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
April 24, 2019 at 2:04 pm
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
Change is inevitable... Change for the better is not.
April 24, 2019 at 2:10 pm
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.
April 24, 2019 at 2:13 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 24, 2019 at 2:32 pm
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
Change is inevitable... Change for the better is not.
April 24, 2019 at 3:10 pm
Thanks for your replies. what other information do you need ?
April 24, 2019 at 3:27 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 25, 2019 at 3:46 pm
Hope you use the following format for From clause when building the view:
from TableName WITH(NOLOCK)
April 25, 2019 at 6:12 pm
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.
April 26, 2019 at 2:23 pm
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.
April 29, 2019 at 12:58 pm
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
April 29, 2019 at 2:13 pm
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
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
April 29, 2019 at 3:02 pm
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:
April 29, 2019 at 8:24 pm
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 20 total)
You must be logged in to reply to this topic. Login to reply