June 11, 2015 at 3:21 am
Hi SQL folks,
I'm just wondering what is the resolution or execution order for a query like this (simplified version):
CREATE VIEW ClosedSales AS
SELECT *
from NatSales
where CurrentFlag = 1
and ValidTo <> '9999-99-99'
Select * from ClosedSales
where CustomerId = 10
The thing I want to know is:
- If first the complete dataset from the view is calculated and the the CustomerId=10 filter is applied
- Or the CustomerId=10 condition is added to the where clause in the view definition
My real case is much more complex but that is an interesting point in my performance analysis.
Any help would be highly appreciated.
Kind Regards,
June 11, 2015 at 3:28 am
The optimiser doesn't know what a view is. Neither does the query processor. Views are in-lined as part of the parsing of the query, prior to optimisation.
You submit this:
Select * from ClosedSales
where CustomerId = 10
As part of the parsing of the query, the view's name is replaced by it's definition and the resulting query simplified:
Select * from
(SELECT *
from NatSales
where CurrentFlag = 1
and ValidTo <> '9999-99-99') ClosedSales
where CustomerId = 10
->
SELECT *
from NatSales
where CurrentFlag = 1
and ValidTo <> '9999-99-99'
and CustomerId = 10
This in-lining and simplification can take time. Grant tells of a query against nested views that took several minutes to parse and several seconds to execute. Hence why views upon views upon views is not usually a good thing to do. Firstly the parsing takes time, second the resultant query may be hard to simplify, especially if there are aggregates in, and hence may be slow.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
June 11, 2015 at 6:10 am
Hi Gail,
thanks for your useful answer.
In my scenario I am not nesting views. I am just trying to improve an ETL Process, where the source queries are embedded in views for each target table. The import is almost a one-to-one select --> insert/update from these views, the only external view filter is a where clause to compare the creation date of the source records against the last load date time, in order to perform an incremental load.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply