locked
Nested views and performance RRS feed

  • Question

  • Hi

    I have some queries I'm investigating... Select * from View1 is significantly faster than Select column3 from View1.

    View1 does not have any indexes. View1 itself is pretty complex (perhaps unnecessarily so??), in that it uses other views which are also nested.

     

    I have 2 questions : 1. Can someone shed light on the performance discrepancy?

    2. What is the best/recommended practice for using nested views (views within views)?

     

    Im using SQL2005.

     

    Thanks.

    Emlyn

     

    Friday, September 12, 2008 7:59 AM

Answers

  •  EmlynKM wrote:

    Hi

    I have some queries I'm investigating... Select * from View1 is significantly faster than Select column3 from View1.

     

     

    Emlyn,

     

    Can you retest your view following the example below? If you don't use dbcc dropcleanbuffers, measurements are not reliable.

     

    Code Snippet

    use AdventureWorks2008

    dbcc dropcleanbuffers

    declare @StartTime datetime

    set @StartTime = getdate()

    select * from Sales.vSalesPersonSalesByFiscalYears

    select [TimingMsec]=datediff(millisecond, @StartTime, getdate())

    go

    -- 250 msec

     

    dbcc dropcleanbuffers

    declare @StartTime datetime

    set @StartTime = getdate()

    select JobTitle from Sales.vSalesPersonSalesByFiscalYears

    select [TimingMsec]=datediff(millisecond, @StartTime, getdate())

    go

    -- 90 msec

     

     

    As far as nesting views, it adds complexity, so query execution is impacted.

     

    Let us know what happens.

    • Marked as answer by Kalman Toth Wednesday, December 28, 2011 12:35 AM
    Friday, September 12, 2008 10:04 AM

All replies

  •  EmlynKM wrote:

    Hi

    I have some queries I'm investigating... Select * from View1 is significantly faster than Select column3 from View1.

     

     

    Emlyn,

     

    Can you retest your view following the example below? If you don't use dbcc dropcleanbuffers, measurements are not reliable.

     

    Code Snippet

    use AdventureWorks2008

    dbcc dropcleanbuffers

    declare @StartTime datetime

    set @StartTime = getdate()

    select * from Sales.vSalesPersonSalesByFiscalYears

    select [TimingMsec]=datediff(millisecond, @StartTime, getdate())

    go

    -- 250 msec

     

    dbcc dropcleanbuffers

    declare @StartTime datetime

    set @StartTime = getdate()

    select JobTitle from Sales.vSalesPersonSalesByFiscalYears

    select [TimingMsec]=datediff(millisecond, @StartTime, getdate())

    go

    -- 90 msec

     

     

    As far as nesting views, it adds complexity, so query execution is impacted.

     

    Let us know what happens.

    • Marked as answer by Kalman Toth Wednesday, December 28, 2011 12:35 AM
    Friday, September 12, 2008 10:04 AM
  • Hi there... tx for the reply...

     

    Timings...

     

    2530 msec

    and 

    3126

     

    respectively...

     

     

    The view concerned, is a multi-level nested views. Seems like im going to have to flatten them...

    Suggestions???

    Friday, September 12, 2008 10:27 AM