March 8, 2015 at 4:51 am
We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.
So rather then the following exemplary fragment
select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = B.id
this now looks like
select <many items>
from A
left join (B
join C on C.id_B = B.id
) on B.id_A = A.id
Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.
I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.
March 9, 2015 at 5:58 am
The two statements are not the same obviously and the outputs are the same because you understand the data. I'm guessing the same ID's existing in all tables. Let outers are where data exists in one but may not exist in the other. Do all tables have the same number of rows (same ID's exist in all tables) ?
Are all the objects involved in the query tables or are any views ?
How may rows (1000's, millions or billions) ?
Are there any where clauses ? Viewing the execution plan will tell you where and when SQL is evaluating them.
If they are all tables do they all have appropriate indexes, clustered or non-clustered ? If non-clustered, the query may need to do lookups to get the colums you are selecting in your query which will be a huge drain on resources.
Is ID unique table and if so is there a constraint on every table telling SQL that ? If it is then SQL can make certain assumptions so will not spend time working out cardinality of the values in the join.
Check PLE and watch I/O, disk and CPU on windows perfmon while the query compiles to see if the query optimizer is getting blocked somewhere.
Once you get the compiler to produce a usable plan, you can force that plan for all future executions. If data in the table changes a lot you may get unnecessary recompiles going on. Having said that forcing a plan is not a good idea usually.
You might try creating a few views to break up the query.
Without knowing the context if you wanted a slightly radical way to get the output you need you could create a table and use triggers on the child tables to update that table. A bit like an indexed view but without the compile issues you are seeing. May slow down updates and double disk but worth throwing out there if read performance is whats important in your case and you can afford the tradeoffs.
March 9, 2015 at 6:29 am
The two statements are not the same obviously and the outputs are the same because you understand the data. I'm guessing the same ID's existing in all tables. Let outers are where data exists in one but may not exist in the other. Do all tables have the same number of rows (same ID's exist in all tables) ?
This was an example of how left joins were rewritten to joins. The relations are always fulfilled because of foreign key relations. This is what I did throughout the view and then the plan is compiled much faster.
Are all the objects involved in the query tables or are any views ?
Tables
How may rows (1000's, millions or billions) ?
Some tables few 100000s, most 100s or 100s
Are there any where clauses ? Viewing the execution plan will tell you where and when SQL is evaluating them.
If they are all tables do they all have appropriate indexes, clustered or non-clustered ? If non-clustered, the query may need to do lookups to get the colums you are selecting in your query which will be a huge drain on resources.
Something to look into but for now I am only interested why the plan compilation takes this long.
Is ID unique table and if so is there a constraint on every table telling SQL that ? If it is then SQL can make certain assumptions so will not spend time working out cardinality of the values in the join.
Check PLE and watch I/O, disk and CPU on windows perfmon while the query compiles to see if the query optimizer is getting blocked somewhere.
Not sure if I can see a relation between PLE and compilation time. There is no pressure on CPU or memory. I can reproduce the behaviour consistently.
Once you get the compiler to produce a usable plan, you can force that plan for all future executions. If data in the table changes a lot you may get unnecessary recompiles going on. Having said that forcing a plan is not a good idea usually.
Sounds like a last resort. And I already have a view with less compilation time.
You might try creating a few views to break up the query.
I myself an against views altogether. The current view is part of an application. I can change the query but not get rid of the view.
Views just introduce an extra layer of complexity and I have seen examples with big performance issues when these views are joined. The current view is just used to select with a simple filter, it is not joined. Multiple views do not really break down the query in complexity. Stored procedures might be an option there.
Without knowing the context if you wanted a slightly radical way to get the output you need you could create a table and use triggers on the child tables to update that table. A bit like an indexed view but without the compile issues you are seeing. May slow down updates and double disk but worth throwing out there if read performance is whats important in your case and you can afford the tradeoffs.
March 9, 2015 at 6:31 am
Without seeing the query and the plan, just speculation, but, timeout means that the optimizer went through the number of optimizations that it determined should arrive at a good enough plan. It didn't work. So, it timed out. Now that, could be because of the complexity of the plan, and yes, left joins add to that complexity. But, it could be a number of other factors as well. If your indexing scheme isn't adequate, the optimizer has to try a lot more different calculations to arrive at a good enough plan. If your referential integrity is missing, or worse yet, there, but not enforced through the WITH CHECK operation, the optimizer has to do a lot more work to arrive at a good plan. Also, the load on the system affects how many optimizations can be done, so that could also affect it.
"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
March 9, 2015 at 6:33 am
Bouke Bruinsma (3/8/2015)
We have a view with many left joins. The original creators of this view might have been lazy or sloppy, I don't know. I have rewritten the query to proper inner joins where required and also nested left joins.So rather then the following exemplary fragment
select <many items>from A
left join B on B.id_A = A.id
left join C on C.id_B = B.id
this now looks like
select <many items>from A
left join (B
join C on C.id_B = B.id
) on B.id_A = A.id
Compilation time of the original view was 18s, of the new rewritten view 4s. The performance of execution is also better (not counting the compile of course). The results of the query are identical. There are about 30 left joins in the original view.
I can imagine that the optimizer has difficulty with all these left joins. But 14s is quite a big difference. I haven't looked into detail in the execution plans yet. I noticed that in both cases the Reason for Early Termination of Statement Optimization was Time Out.
The optimiser has difficulty with more than seven or eight tables in the FROM list regardless of how they are joined. More than that and you are very likely to see a timeout. With 30 tables the likelyhood is somewhere between very high and certain.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 9, 2015 at 6:34 am
Bouke Bruinsma (3/9/2015)
You might try creating a few views to break up the query.
I myself an against views altogether. The current view is part of an application. I can change the query but not get rid of the view.
Views just introduce an extra layer of complexity and I have seen examples with big performance issues when these views are joined. The current view is just used to select with a simple filter, it is not joined. Multiple views do not really break down the query in complexity. Stored procedures might be an option there.
Absolutely. JOINs and nesting of views is a very common code smell that leads to poor execution plans and therefore poor performance. This should be avoided.
"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
March 9, 2015 at 6:44 am
ChrisM@Work (3/9/2015)
The optimiser has difficulty with more than seven or eight tables in the FROM list regardless of how they are joined. More than that and you are very likely to see a timeout. With 30 tables the likelyhood is somewhere between very high and certain.
I'd be very cautious about throwing out numbers like that. I've seen very large numbers of tables compile and run just fine. It completely depends on the T-SQL syntax, the referential integrity and the indexing. I've also got examples of four table joins that time out the optimizer because of problems with the above. Seven or eight is very low. I wouldn't sweat a query, just based on the number of tables being joined, until we got to around 20.
Your mileage may vary, etc.
"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
March 9, 2015 at 6:55 am
Grant Fritchey (3/9/2015)
ChrisM@Work (3/9/2015)
The optimiser has difficulty with more than seven or eight tables in the FROM list regardless of how they are joined. More than that and you are very likely to see a timeout. With 30 tables the likelyhood is somewhere between very high and certain.
I'd be very cautious about throwing out numbers like that. I've seen very large numbers of tables compile and run just fine. It completely depends on the T-SQL syntax, the referential integrity and the indexing. I've also got examples of four table joins that time out the optimizer because of problems with the above. Seven or eight is very low. I wouldn't sweat a query, just based on the number of tables being joined, until we got to around 20.
Your mileage may vary, etc.
Fair point, Grant. I've heard different numbers thrown around recently by folks who know far better than me. I'll take an average weighted heavily by your observations and comments 🙂 Personally, I always look.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
March 9, 2015 at 8:42 am
Grant Fritchey (3/9/2015)
ChrisM@Work (3/9/2015)
The optimiser has difficulty with more than seven or eight tables in the FROM list regardless of how they are joined. More than that and you are very likely to see a timeout. With 30 tables the likelyhood is somewhere between very high and certain.
I'd be very cautious about throwing out numbers like that. I've seen very large numbers of tables compile and run just fine. It completely depends on the T-SQL syntax, the referential integrity and the indexing. I've also got examples of four table joins that time out the optimizer because of problems with the above. Seven or eight is very low. I wouldn't sweat a query, just based on the number of tables being joined, until we got to around 20.
Your mileage may vary, etc.
There are many queries in this app with up to 40 joins that do no have this problem. The current one however seems special. It was the only view with almost only left joins. I have not rewritten left joins to inner joins based on what I know of the data but what I know of the referential constraints. Even the order of the left joins was a mess. This should not affect the optimizer as far as I know (although I do recall a remark from you Grant that in very rare cases the order of the joins affects the plan) and I thought that the optimizer recognizes that left joins are in fact joins when the referential constraints specify it.
March 9, 2015 at 9:12 am
After reading other posts spotted something I would consider a problem for the compiler.
select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = B.id
It's joining A to B and then B to C. I'm not a optimizer gury but I would cant see how it could decide on a strategy for the first join until it's chained through the other 30 joins.
Cant it join both A to B and A to C
select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = A.id
The table indexing is important and a factor in how long the compiler will take to work out an efficient plan. If there is a clustered index on ID on all tables then the optimizer should be fairly quick with a strategy as its a no-brainer join on clustered index (unless there are non-clustered indexes on ID that it has to decide whether its optimal to use but I'ev rarely seen a query choose non-clustered especially for a view where you don't have parameter sniffing like in a sproc). If these indexes don't exist the optimiser will be trying to work out how long it takes to build hash tables, sort and cross reference. If its doing that then it will be looking at statistics which could be off if things like unique constraints dont exist to help it.
A sproc might be be a work around.
March 9, 2015 at 9:44 am
Bouke Bruinsma (3/9/2015)
Grant Fritchey (3/9/2015)
ChrisM@Work (3/9/2015)
The optimiser has difficulty with more than seven or eight tables in the FROM list regardless of how they are joined. More than that and you are very likely to see a timeout. With 30 tables the likelyhood is somewhere between very high and certain.
I'd be very cautious about throwing out numbers like that. I've seen very large numbers of tables compile and run just fine. It completely depends on the T-SQL syntax, the referential integrity and the indexing. I've also got examples of four table joins that time out the optimizer because of problems with the above. Seven or eight is very low. I wouldn't sweat a query, just based on the number of tables being joined, until we got to around 20.
Your mileage may vary, etc.
There are many queries in this app with up to 40 joins that do no have this problem. The current one however seems special. It was the only view with almost only left joins. I have not rewritten left joins to inner joins based on what I know of the data but what I know of the referential constraints. Even the order of the left joins was a mess. This should not affect the optimizer as far as I know (although I do recall a remark from you Grant that in very rare cases the order of the joins affects the plan) and I thought that the optimizer recognizes that left joins are in fact joins when the referential constraints specify it.
Specifically in the case of timeouts. It may not pick a good order, or even try re-ordering if it's busy with other stuff. The real hard part is, we don't really know what it's doing internally. We can just see the outcome, the plan, and whether or not it finished optimizing, Good Enough Plan Found, or gave up for some reason unknown to us, Timeout.
"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
March 10, 2015 at 7:39 am
roger.price-1150775 (3/9/2015)
After reading other posts spotted something I would consider a problem for the compiler.select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = B.id
It's joining A to B and then B to C. I'm not a optimizer gury but I would cant see how it could decide on a strategy for the first join until it's chained through the other 30 joins.
Cant it join both A to B and A to C
select <many items>
from A
left join B on B.id_A = A.id
left join C on C.id_B = A.id ....
Your code assumes b.id_A and C.id_B hold the same value. The original example has B.id_A = A.id and C.id_B = B.id. Different fields are being used in the joins.
Yesterday I made a view doing what Bouke's original code is doing right now, joining two tables with an intersect, in my case to report from a many to many join.
I won't comment on the original question of nested joins, I don't have any recent experience with that syntax.
-----
[font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply