April 23, 2012 at 7:18 am
Hi,
i have a query in SQL2008 which uses a view. I will post the execution plans plus schema later, but would like to know few little things that I am not able to search anywhere on the web.
Lets think that my view is a subset of 20 columns and doing all sorts of casts in the select list. now someone reads only 5 columns fom this view i.e
create view_ 1 as view
as
select col1,col2....col20
now when I run, select col1,col2 from view_1 where ...
the execution plans shows me that the complete view was evaluated. So does it mean that, it worked with the complete view along with some filters I applied later and then chose to display only 2 columns as desired.
I am asking this because, if i run the query without a view and a simple select, performance is drastically fast.
Please share your thoughts.
Thanks
Chandan
April 23, 2012 at 7:26 am
That's pretty common with views.
Think of it as having the whole View definition as a sub-table in your From clause. If you won't use the whole thing, you'll still query all of it.
create View dbo.MyView
as
select Col1, ColA
from dbo.Table1
inner join dbo.TableA;
go
create proc dbo.MyProc
as
select ColA
from dbo.MyView;
is the same as:
create proc dbo.MyProc
as
select ColA
from
(select Col1, ColA
from dbo.Table1
inner join dbo.TableA) as MySubquery;
Nobody would ever knowingly build the second one, without a good reason (like Table1 being used as a filter on TableA, not just for extra columns), but people build the first one all the time. As far as SQL Server is concerned, those two versions of MyProc are the same.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 23, 2012 at 7:35 am
Thanks for you reply. If this is true, then it is going to kick my a** for ages because the select list in the view definition has around 20-25 columns and a lot of casts and conversions are done to display the result in the desired format. so even if i want just 5 columns, SQL is going to evaluate aqll 25 columsn will complete operations and will display just the required 5 at the end.
Any hints or performance improvement tips here?
Thanks
chandan
April 23, 2012 at 7:43 am
computed columns on the source table?
user defined functions (UDFs) to simplify the casting
multiple views
April 23, 2012 at 7:49 am
Ok. Now a new twist in the tale. I forgot the view and coiped its definition of select command which displays so many columns and applied the proper where filters which i was applying on view earlier.
the query displays all the columns now and still runs in 2 seconds!!!!:w00t:
It looks as if the view is not able to eveluate the where filter properly.
Edit: I compared both execution plans. The bad plan shows actual rows of a small table in billions.
Good plan shows the actual number of rows for this table in million and actually a select count(*) gives only 1k rows. Howcome actual number of rows can go wrong in an execution plan:-(
Thanks
Chandan
April 24, 2012 at 6:36 am
Can you post any of the queries?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 24, 2012 at 7:33 am
Just guessing without the query, but the optimiser cannot accurately estimate the number rows returned because of some logic in a where clause or join condition.
April 24, 2012 at 7:53 am
GSquared (4/24/2012)
Can you post any of the queries?
I am posting 3 items here. The query which takes 100 seconds, the view definition which is being used and the query plan of this bad query.
If i take out the view definition and run it as simple select and just add more filters which I am adding in the original query, I get the output in 2 seconds. The most important noticeable deifference I find in the table TCN_Xref whose plan gets screwed up when used in the view.(Look at the plan and this table has billions of actual number of rows but actually it has only 1k rows).
Thanks
Chandan
April 24, 2012 at 8:19 am
And here is the query without the view(but same where filters) and its actual plan. this works in 2-3 seconds. I am surprised and not able to find answers. I guess it has to do something with the table TCN_XRef being joined in the end.
thanks
chandan
April 24, 2012 at 8:21 am
I see two things that are almost certainly the performance killers.
The first is this:
LEFT OUTER JOIN MOC_DT.dbo.TCN_Xref tcn
ON tcn.recid = (SELECT MIN(recid) FROM MOC_DT.dbo.TCN_Xref
WHERE DsLocationCode = CAST(term.TerminalID AS VARCHAR)
AND (sup.SupplierCode LIKE DsSupcode + '-%')
)
The second is the Group By clause and some of the aggregates. It has aggregates being done on converted datatypes, and inside of Case statements. What are the original datatypes of the NetGallons and GrossGallons columns in OrderProdItem?
There are some other odd bits. Like:
TerminalControl# = CASE
WHEN ISNULL(tcn.TerminalNumber,'')='' THEN ''
ELSE tcn.TerminalNumber
END
It does a Case on an IsNull that simply duplicates the functionality of the IsNull. That could be replaced by:
TerminalControl# = ISNULL(tcn.TerminalNumber,'')
It would do the same thing, but with less work for the CPU.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 24, 2012 at 8:30 am
The query plan suggests an index that it thinks would help. You're getting a lot of scans, and it's possible you need them for this, but have you tried the suggested index to see what that does?
Based on the plan, I'd definitely look into cleaning up that sub-query on TCN_Xref. Perhaps a persisted, indexed computed column, or an indexed view, would help resolve that one. More likely, you can rewrite that piece of code to avoid the left outer join and turn it into something more efficient. Maybe an inline sub-query in the Select clause would behave better.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 24, 2012 at 10:50 am
Only one comment. Both plans show the reason for early termination within the optimizer as Timeout. That means that neither plan can be trusted. Did the optimizer come up with a slightly different plan for the query than the view? Yeah, but that just means the difference in approach from the optimizer using a single select list instead of the combined list means it started at a slightly different place. Neither plan can be trusted, so you can't really compare one to the other. Neither one is likely to be stable.
"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
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply