May 29, 2015 at 7:53 am
Hi,
I created a new View using some other Views.
The new View works, but if I try and use a WHERE clause - it just ignores it.
Can someone explain why this is happening?
May 29, 2015 at 8:25 am
TJT (5/29/2015)
Hi,I created a new View using some other Views.
The new View works, but if I try and use a WHERE clause - it just ignores it.
Can someone explain why this is happening?
Well without any details there is absolutely no chance anybody can help here. We are really good at t-sql but not good at guessing details about how your views, tables, etc are defined.
I can however tell you that you need to run away from views calling views immediately if not sooner. Nested views are a performance nightmare. The optimizer gets confused when views are nested. It seems so logical to nest them but just don't do it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2015 at 8:39 am
Sorry, solved - small issue on my end
May 29, 2015 at 8:41 am
TJT (5/29/2015)
Sorry, solved - small issue on my end
No offense but if your view is like the pseudocode below your issue is only beginning. The performance of this kind of thing is shockingly awful.
create View MyNewView as
select *
from MyOldView
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2015 at 8:57 am
Sean Lange (5/29/2015)
TJT (5/29/2015)
Sorry, solved - small issue on my endNo offense but if your view is like the pseudocode below your issue is only beginning. The performance of this kind of thing is shockingly awful.
create View MyNewView as
select *
from MyOldView
I saw a presentation by Jonathan Kehayias once where he showed a query plan that used views on views on views, etc and took 20-some hours to run. When he boiled it down to the 5 base tables, it ran in a few minutes. There's a serious lesson to learn from this - one that I took to heart.
May 29, 2015 at 9:15 am
Ed Wagner (5/29/2015)
Sean Lange (5/29/2015)
TJT (5/29/2015)
Sorry, solved - small issue on my endNo offense but if your view is like the pseudocode below your issue is only beginning. The performance of this kind of thing is shockingly awful.
create View MyNewView as
select *
from MyOldView
I saw a presentation by Jonathan Kehayias once where he showed a query plan that used views on views on views, etc and took 20-some hours to run. When he boiled it down to the 5 base tables, it ran in a few minutes. There's a serious lesson to learn from this - one that I took to heart.
I ran into this in the real world several years ago and I broke apart the views and it ran way faster. In more recent years I have learned about how the optimizer deals with that and glad I had made the right decision then.
That sounds like it would have been an interesting presentation.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
May 29, 2015 at 2:17 pm
I can't honestly say the number of times I've seen this go badly. Views calling other views that are joined to views which contain other nested views... It feels so logical, but it absolutely doesn't work well within SQL Server.
"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
May 29, 2015 at 7:37 pm
Sean Lange (5/29/2015)
Ed Wagner (5/29/2015)
Sean Lange (5/29/2015)
TJT (5/29/2015)
Sorry, solved - small issue on my endNo offense but if your view is like the pseudocode below your issue is only beginning. The performance of this kind of thing is shockingly awful.
create View MyNewView as
select *
from MyOldView
I saw a presentation by Jonathan Kehayias once where he showed a query plan that used views on views on views, etc and took 20-some hours to run. When he boiled it down to the 5 base tables, it ran in a few minutes. There's a serious lesson to learn from this - one that I took to heart.
I ran into this in the real world several years ago and I broke apart the views and it ran way faster. In more recent years I have learned about how the optimizer deals with that and glad I had made the right decision then.
That sounds like it would have been an interesting presentation.
Yeah, it was very interesting. I wish I had a screen shot of the execution plan. It looked like a football and you had to zoom out so far so see the whole thing that the individual steps were almost indistinguishable from one another.
May 29, 2015 at 7:40 pm
Grant Fritchey (5/29/2015)
I can't honestly say the number of times I've seen this go badly. Views calling other views that are joined to views which contain other nested views... It feels so logical, but it absolutely doesn't work well within SQL Server.
Add in a couple implicit casts and a non-SARGable predicate or two and you've got a recipe for disaster. We had one like that where the procedure that called it had 6 IN clauses, each one of which hit against an inchworm splitter. No wonder it took forever to return anything. The reads where larger than the size of the entire database. It was quite impressive.
June 4, 2015 at 6:08 pm
TJT (5/29/2015)
Sorry, solved - small issue on my end
What was the problem and how did you fix it? It might help someone else having a similar problem. Thanks.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply