Views, UDFs

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • gherrell
    #1

    Views, UDFs

    I know there is a lot of information already out there on this topic,
    but given the following scenario...

    --------------------------------------------------------------------------------------------------------------------------------------
    Create a view like so ( pardon the pseudo-code )...

    CREATE View vwContactAddres ses
    Select * FROM Contact INNER JOIN Address ON Contact.Contact ID =
    Address.Contact ID

    And then do a sargable select from the view using a stored procedure

    CREATE STORED PROCEDURE spSelect_Contac tAddresses
    @ContactID int
    AS
    Select * FROM vwContactAddres ses WHERE ContactID = @ContactID
    --------------------------------------------------------------------------------------------------------------------------------------

    In my understanding, "vwContactAddre sses" would be substituted with the
    actual SQL join statement when the view is accessed.

    So for the stored procedure in question an execution plan for
    "Select * FROM Contact INNER JOIN Address ON Contact.Contact ID =
    Address.Contact ID WHERE ContactID = @ContactID" would be cached.
    Correct?

    With regards to execution plan caching, is this not the same as
    creating an inline UDF that takes parameters or just creating a stored
    procedure that would do the join w/out the view reference?

    Tags: None
    • Erland Sommarskog
      #2
      Re: Views, UDFs

      gherrell (greg.herrell@g mail.com) writes:
      I know there is a lot of information already out there on this topic,
      but given the following scenario...
      >
      --------------------------------------------------------------------------
      ------------------------------------------------------------
      Create a view like so ( pardon the pseudo-code )...
      >
      CREATE View vwContactAddres ses
      Select * FROM Contact INNER JOIN Address ON Contact.Contact ID =
      Address.Contact ID
      >
      And then do a sargable select from the view using a stored procedure
      >
      CREATE STORED PROCEDURE spSelect_Contac tAddresses
      @ContactID int
      AS
      Select * FROM vwContactAddres ses WHERE ContactID = @ContactID
      --------------------------------------------------------------------------
      ------------------------------------------------------------
      >
      In my understanding, "vwContactAddre sses" would be substituted with the
      actual SQL join statement when the view is accessed.
      >
      So for the stored procedure in question an execution plan for
      "Select * FROM Contact INNER JOIN Address ON Contact.Contact ID =
      Address.Contact ID WHERE ContactID = @ContactID" would be cached.
      Correct?
      Not really. What is in the cache is a plan for the stored procedure
      spSelect_Contac tAdresses.

      While the operators in the plan are likely to be same as in the plans
      for "Select * FROM vwContactAddres ses WHERE ContactID = @ContactID" and
      "Select * FROM Contact INNER JOIN Address ON Contact.Contact ID =
      Address.Contact ID WHERE ContactID = @ContactID" they are three different
      cache entries. In fact, the plan is case- and space-sensitive, so all these
      three are different cache entries:

      SELECT col1 FROM tbl
      Select col1 FROM tbl
      SELECT col1 FROM tbl




      --
      Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

      Books Online for SQL Server 2005 at
      http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

      Books Online for SQL Server 2000 at
      http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

        Comment

        • gherrell
          #3
          Re: Views, UDFs

          That makes sense. However, I didn't express myself very well.
          I am looking for validation that this approach with a view gives the
          same opportunity for a performance benefit as a parameterized UDF or
          straight stored procedure.



          Erland Sommarskog wrote:
          gherrell (greg.herrell@g mail.com) writes:
          I know there is a lot of information already out there on this topic,
          but given the following scenario...

          --------------------------------------------------------------------------
          ------------------------------------------------------------
          Create a view like so ( pardon the pseudo-code )...

          CREATE View vwContactAddres ses
          Select * FROM Contact INNER JOIN Address ON Contact.Contact ID =
          Address.Contact ID

          And then do a sargable select from the view using a stored procedure

          CREATE STORED PROCEDURE spSelect_Contac tAddresses
          @ContactID int
          AS
          Select * FROM vwContactAddres ses WHERE ContactID = @ContactID
          --------------------------------------------------------------------------
          ------------------------------------------------------------

          In my understanding, "vwContactAddre sses" would be substituted with the
          actual SQL join statement when the view is accessed.

          So for the stored procedure in question an execution plan for
          "Select * FROM Contact INNER JOIN Address ON Contact.Contact ID =
          Address.Contact ID WHERE ContactID = @ContactID" would be cached.
          Correct?
          >
          Not really. What is in the cache is a plan for the stored procedure
          spSelect_Contac tAdresses.
          >
          While the operators in the plan are likely to be same as in the plans
          for "Select * FROM vwContactAddres ses WHERE ContactID = @ContactID" and
          "Select * FROM Contact INNER JOIN Address ON Contact.Contact ID =
          Address.Contact ID WHERE ContactID = @ContactID" they are three different
          cache entries. In fact, the plan is case- and space-sensitive, so all these
          three are different cache entries:
          >
          SELECT col1 FROM tbl
          Select col1 FROM tbl
          SELECT col1 FROM tbl
          >
          >
          >
          >
          --
          Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se
          >
          Books Online for SQL Server 2005 at
          http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

          Books Online for SQL Server 2000 at
          http://www.microsoft.com/sql/prodinf...ons/books.mspx

            Comment

            • Erland Sommarskog
              #4
              Re: Views, UDFs

              gherrell (greg.herrell@g mail.com) writes:
              That makes sense. However, I didn't express myself very well.
              I am looking for validation that this approach with a view gives the
              same opportunity for a performance benefit as a parameterized UDF or
              straight stored procedure.
              I'm not really sure what you mean, but using a view should not matter
              in theory. That is, SELECT from the view or the SELECT from the base
              query in the same thing.

              But there is a risk: say that a programmer finds the view and thinks
              "hey I get the value from X from this view, and value of Y from that
              view". When you expand the query, you see that several tables appears
              twice, although it had been sufficient with one. But will the optimizer
              see that?

              In the system I work with, we have very few views. There is one corner
              of the database that I am not inolved with where they choose to use it,
              and I believe makes sense there. Myself, I've only used views a few
              times when I have rearranged tables, and kept the old definition as view
              for compatibility.

              --
              Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

              Books Online for SQL Server 2005 at
              http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx

              Books Online for SQL Server 2000 at
              http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

                Comment

                Previous template Next
                Working...