Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    Saint Pete, Florida
    Posts
    5

    Question Unanswered: Dynamically Created Stored Procs???

    Okay. Scenario:

    We as a company have several companies that request custom reports from us. However, the custom reports that they select will generally always have the same fields (and formulas) once they make up their minds on what works for them.

    From what I know, Stored Procs are usually faster at running things unless the parameters are changing too drastically that they get passed.

    This being the case, it would seem like a good case for creating a stored proc per report. This would alleviate the possability that the server chooses an execution plan that works great some of the time but the rest of the time run lousy.

    So these thoughts being laid out, is there a good,nice,easy, convenient way to generate/alter a stored proc, either by another stored/extended proc, or by dynamic sql going to the server?

    Or if not, is there some round-about yet effective way of doing this?


    Thank you in advance for any help.
    -ZanderB

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    I guess I don't understand why you feel you need to be able to alter existing stored procedures on demand. Automating something like this would take a lot more programming skill and development time than just copying an existing procedure and modifying it to create a new one.
    If it's not practically useful, then it's practically useless.

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

  3. #3
    Join Date
    Mar 2004
    Location
    Saint Pete, Florida
    Posts
    5
    The point of the matter is that right now, there is no efficient on demand procedure (that I know of). Any dynamic SQL must rely on having an execution plan made by the server every time (as I understand).

    When you have web enabled reports that already take several minutes, it becomes key that optimizations are made. At the same time, having something that is dynamic, but generally used enough to say it's called all the time is something that would be worthy of being called by a stored proc. Perhaps even something equivical would be nice that is not as static, but there is nothing I know of that does that for dynamic SQL.

    If you had a web server that server for 4 items 80% of the time, but those 4 items changed frequently (like once a week) you could say there would be a good argument for something like this, could you not?

    The structure of the where statement can cause the execution plan to be changed.

    So my question again is why you wouldn't want a dynamically created stored proc for this.

    Are dynamic queries going to have a cached execution plan if the same ones are called enough?

  4. #4
    Join Date
    Mar 2004
    Posts
    1

    Re: Dynamically Created Stored Procs???

    When the procedure is supposed to be used only once, it is perhaps not a good idea to create the procedure and the drop it after having executed it once.

    Another approach that i have tried, is to create what is called an anonymous block. This is the body of the procedure without the create procedure part. This is a T-sql block that is sent to the database and executed as if it where a straight forward select. It can contain exeption handling and give all the functionality you expect from a procedure, but will not impact the system dictionary. The overhead will probably be less than the procedure approach since the system dictionary is not updated.

    Īn both cases you will get one parse, optimize, compile and execute. The database will perform it as optimal as a procedure. I have tried it with code performaing a cursor loop and doing a couple of queries for each row in the cursor. This is code that is much better to perform inside the database than from an external application.

  5. #5
    Join Date
    Mar 2004
    Location
    Saint Pete, Florida
    Posts
    5

    Re: Dynamically Created Stored Procs???

    Nevermind. It is possible through a standard connection to the server (with right permissions) to change/add stored procs. I didn't know if this was a possibility or not. It only surprises me that noone said it's possible with dynamic SQL statements.

  6. #6
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by ZanderB
    When you have web enabled reports that already take several minutes
    Every new request becomes a log of the report in a table.

    The get a list and select what they want... the table stores the proc name which gets executed


    You'll come to a point when you've created almost all variations.


    And if you're telling us that you have to wait several minutes for a web based report...that's waaaaaaaaaaaaaaaaaaaay too long

    You need to build a process to denormalize the data so they pop...

    I would imagine an in/experienced user would think the damn thing is broken...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322

    Re: Dynamically Created Stored Procs???

    Originally posted by ostrande
    anonymous block.
    Don't look now...but your Oracle is showing...

    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    No one said it was possible with dynamic SQL because no one wants to recommend dynamic SQL. Dynamic SQL is usually slower and less efficient, although admittedly in some cases where there are complex conditions dynamic SQL can be faster. In these cases, though, the dynamic SQL is best created and executed within the procedure given the parameters supplied. The procedure itself is not modified.

    And bear in mind that having a pre-compiled stored procedure does save execution time - like 1 or 2 seconds (at the most!) for each time the procedure is executed. It does not take that long for SQL server to compile a procedure. If your querys are taking 3-4 minutes, the problem is not in the compilation time.
    Last edited by blindman; 03-09-04 at 10:29.
    If it's not practically useful, then it's practically useless.

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

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Yo, Blind dude...

    ever wait MINUTES for a web based report?
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

Posting Permissions

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