Results 1 to 8 of 8
  1. #1
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40

    Unanswered: General question about massive SP use

    Hi

    Would you say that it's ok for a web site code to make ALL of it's access to a db through SP and views? And I mean everything including inserting new records and updating others with no use with SQL in the code.

    The advantage would be very strict control over the access, but in order to achieve this it would take many many SP and views to cover all types of actions, can you think about a disadvantage except all the work creating those SP?? what about the server resources and performance? how demanding it would be?

    Thanks,
    Inon.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I would say that it's PREFERABLE for a web site code to make all of it's access to a db through SP and views.
    Applications should not be allowed to have direct access to tables.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40
    Quote Originally Posted by blindman
    I would say that it's PREFERABLE for a web site code to make all of it's access to a db through SP and views.
    Applications should not be allowed to have direct access to tables.
    I agree and understand, BUT, would you still support this method even if it means a lot of hard work for the DB?? And I will explain why...
    For example if you have a web page for browsing some records in pages (page 1, page 2…) and allows sorting by several fields, it's very not efficient to send the field for the order by to one SP, because the execution plan will constantly change, so in order to keep things efficient you'll have to create several SP for the same process but only with different order by clauses, now, take this and add a search on it with several optional criteria fields, so a user could narrow the results by one or more fields, each selection combination has it's own SQL, usually the code dynamically builds the query, a SP can do it, but is it clever for it to?? This might cause both poor performance and drain resources from the server for a job that was already made (the code did the check of what fields were selected and sent only them, it could easily be a made query).
    And last thing, is too many queries can act as too many indexes? (Meaning do more harm than good) Because even when not used SP’s use resources which are limited, so too many of them (for the reasons mentioned before) can harm performance, am I right with all my assumptions?

    I'm not trying to convince or be convinced, just to think loudly with you about the advantages and disadvantages in order to make my own choice.

    Thanks,
    Inon.

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    no you are not right about a bunch of things. as the blind dog might point out, sorting is a presentation issue that is better left to the presentation layer of your application. Google your programming language and "bubble sort".

    I amnot sure what you mean by...
    Code:
    And last thing, is too many queries can act as too many indexes?
    Too many indices can degrade insert\update performance but this is true of regular old inline sql and stored procedures. an index is an index no matter how you query it. A stored proc may better use the index for selects, where clauses and joins if the execution plan is cached.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    You can write a sproc to dynamically sort your data, but Thrasy is correct that this is better left to your presentation layer.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Jun 2004
    Location
    Tel Aviv - Israel
    Posts
    40
    Quote Originally Posted by Thrasymachus
    no you are not right about a bunch of things. as the blind dog might point out, sorting is a presentation issue that is better left to the presentation layer of your application. Google your programming language and "bubble sort".
    This is why I said that the presentation is in pages.., which means that if you have 10,000 records for example, and you want to present the first page (lets say 10 records) sorted by some field, sorting in the presentation layer will require to select all 10,000 records and bubble sort it or whatever, when you could select and order by with an index and use TOP to send only the needed records... saving massive network traffic and hard work for the web server that makes a bubble sort that can not match the sorting power of an Index.
    For this scenario do you still think sorting in the presentation layer is the best option?? I don't.

    And about the indices compared to SP, forget what I said, just tell me, are SP require resources even when not used? (For compile or cache or whatever) and therefore too many might harm the server performance?? Or can I create as many as I want with no hesitation?

    I feel I wasn't fully understood before, maybe it's my fault... English is not my native language but I do my best...

    Inon.

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Inon

    I thought this was something you were getting at before but I wasn't sure. Sprocs incure no additional overhead. They require space on the disk of course. However pretty well all statements executed on the server result in a cached plan - sprocs are not unique in this regard. Your current set up of sending inline SQL to the server is resulting in cached plans too.

    There isn't really much performance gain or loss using sprocs as such (other than issues regarding parameter sniffing, recompilations etc). The main thing is security and a single-point-of-interaction with the database which makes enforcement of business rules (arguably) easier.

    I have seen some quite well argued cases for using inline SQL on the web but sadly I seem to have misplaced my links. I doubt you would find much sympathy for them here - personnally speaking I think the case for using sprocs is stronger than the case for not using them.

    You can get round your problem re changing execution plans made in post two with the below (link courtesy of blindman):
    http://www.sqljunkies.com/WebLog/ama...reCaching.aspx

    Just my tuppenneth worth
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Inoni
    This is why I said that the presentation is in pages.., which means that if you have 10,000 records for example, and you want to present the first page (lets say 10 records) sorted by some field, sorting in the presentation layer will require to select all 10,000 records and bubble sort it or whatever, when you could select and order by with an index and use TOP to send only the needed records... saving massive network traffic and hard work for the web server that makes a bubble sort that can not match the sorting power of an Index.
    For this scenario do you still think sorting in the presentation layer is the best option?? I don't.
    In this scenario you are filtering out a subset of the data to display as a single page, and filtering DOES fall within the scope of the database engine.
    There are several methods of paging through data depending upon the specific business requirements.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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