Post reply

Views Effects Performance

  • October 9, 2005 at 11:55 pm

    #110617

    In Stored Procedure if i Write SQL like

    SELECT Column1, Column2, Column3 FROM Table1 ORDER BY Column1

    OR

    CREATE VIEW View_Table1 AS

    SELECT Column1, Column2, Column3 FROM Table1 ORDER BY Column1

    & Then in SP

    SELECT * FROM View_Table1

    This will effect Performance / Not

    And what if i have

    CREATE View View2 AS

              Select * From View1

    CREATE View View3 AS

               SELECT * FROM View2

    CREATE View View4 AS

               SELECT * FROM View3

    This will effect Performance / Not

    If Yes then what is solution for Same Type Of Query Using in Multiple Stored Procedure

    If write seperate Query for Every Stored Performance can lead to miss any logic

    Waiting for reply

    Thanks in Advance

  • October 10, 2005 at 3:00 am

    #596227

    1)There is no difference between a view and a simple select for performance.

    Beware of making views based upon views ->effect performance (possible to much data joined)

    2)Views usually haven't and don't need an order by clause since this is handled in the query itself.

    3)Writing select * is asking for trouble later on, use the specific columnnames. Query Analyzer can script them out for you.

    4)Always query a view/table/... by prefixing its owner (performance reasons, ambiguitiy...)

    5)

    If Yes then what is solution for Same Type Of Query Using in Multiple Stored Procedure

    If write seperate Query for Every Stored Performance can lead to miss any logic

    Do you have an example?

  • October 10, 2005 at 10:40 pm

    #596435

    Exapmle 1

    1st Page

    Select FName, LName From Member Table Order By FName

    2nd Page - Same Query

    3rd Page - Same Query

    Now if Client say that Instead of Order By FName I want LName

    If i don't use Views i have to change everywhere, otherwise only in view i change order By Clause

    Example 2

    Select Emp_Name, Sum(Salary) As TotalSalary From Employee Table

    If i create view of Above Query i can use Same Query everywhere

    So in Future also i have to put some where condition to calculate Salary, i have to change at one place only

    Thanks for your reply

  • October 11, 2005 at 7:08 am

    #596519

    If each view contains an Order By clause, then you may eventually end up writing a view for every possible combination of Order By clauses. That is, potentially, a LOT of views to create...and modify when you add your Salary calculation.

    Consider a single view used by a stored procedure that applies the correct Order By based on a parameter. It will save a LOT of coding.

    ------------
    Buy the ticket, take the ride. -- Hunter S. Thompson

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply