Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Posts
    134

    Unanswered: Views and stored procs.

    Is there any performance loss by setting up a view and creating a stored procedure to return a recordset from a view versus setting up the stored procedure to return the recordset from the tables directly?

    What way should I do this?

    Mike B

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    You could loose a few milliseconds the first time the procedure is loaded into the procedure cache ifyou use the view, but the difference is too small to care about, and probably too small to measure.

    Using views can make an application much simpler to understand, particularly if there are a lot of complex things going on in the SQL. I would recommend using views, as long as you don't go nuts with them. In other words, don't build a view as a SELECT * FROM myTable unless that somehow might buy you something down the road, but feel free to take that fourteen table join on forty columns and make that puppy into a view pronto!

    -PatP

  3. #3
    Join Date
    Feb 2004
    Posts
    134
    Thanks for your reply Pat. The reason I asked is because I thought I read somewhere that a view is "compiled" each and everytime it is executed. This seems to be the equivalent of running a query where each time the query is run, it is first compiled then put into action.

    The whole purpose of a stored procedure, is that the procedure is compiled once, when it is first executed, hence better performance is the outcome compared to sending a "SELECT ...." SQL statement.

    So, it seems that using views would be a performance loss comparable to sending a SQL statement rather then using a stored proc.

    Do I make any sense?

    Mike B

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •