Post reply

stored procedure bias

  • January 28, 2009 at 10:09 am

    #127133

    Is there any reason not to use a stored procedure instead of running scripts?

  • January 28, 2009 at 10:15 am

    #933960

    Here's how i understand the advantage:

    if we are talking about a script that is doing something like updating data or something, a procedure has an advantage:

    I'm sure you've seen this: run a script, and it takes a while as an execution plan is created. run it a second time, and it is blazingly fast compared to the first pass.

    problem is, if the script is not run for a while, it gets dropped from the cache, and the next time it gets called, it is slow as a new execution plan is created.

    for a procedure witht eh same code, the execution plan is saved, and does not drop out of cache, so the second time it is called, and all calls thereafter should all be quick...even if it's days months years later.

    If I'm wrong, i'm sure someone will box my ears, but that is my understanding.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • January 28, 2009 at 12:22 pm

    #934081

    Cover your ears...

    An execution plan is an execution plan. They all age and when the cache cleanup process comes along, they get removed from cache. No differences for stored procedures.

    The one single big difference between parameterized queries and a stored procedure is security. You can grant execute privileges to a proc that does data manipulation, but you don't need to expose the underlying tables & structures. To do the same thing with client side code, you have to expose the tables.

    Then you get into some esoteric stuff such as an obfuscation layer, data code is easier to maintain & deploy than client side code... other stuff.

    THERE IS NO PERFORMANCE DIFFERENCE!

    Maybe screaming will keep that argument at bay.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • January 28, 2009 at 1:20 pm

    #934117

    One difference is in the reusability of the plans.

    With ad-hoc sql, the text has to match precisely (including whitespace) before an existing plan will be reused. The following two generate two execution plans

    select * from sometable where somecolumn = 7

    select * from sometable s where somecolumn = 7

    The other problem with unparameterised ad-hoc queries is that the type of any literal is set to the smallest data type possible

    The following 3 queries will generate three execution plans

    select * from sometable where somecolumn = 7 -- tinyint

    select * from sometable where somecolumn = 258 -- smallint

    select * from sometable where somecolumn = 7.0 -- probably float

    Procedures, however are matched only by schema and name and the plans are far more likely to be reused (which may or may not be a good thing)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • January 28, 2009 at 1:27 pm

    #934125

    The only reason I use a script instead of a proc is if I never expect to have to run it again. Even then, I'll often save it as a file, just in case.

    If I expect to run stuff again, I'll create a proc.

    Sometimes, while a process is evolving against an existing line of business, I'll have a script that evolves over time till it eventually makes sense to turn it into a proc. Sometimes, each run through requires different code, till it has enough analysis to turn into a set of procs that can be called based on criteria that can be codified. In those cases, it starts out as a script and evolves into a proc or set of procs.

    But for code that's got any re-use at all, it should be a proc.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • January 28, 2009 at 3:48 pm

    #934292

    To expand a little on what Gail started - in a parameterized query where the objects are not schema qualified, even if it is exactly the same it will generate a separate plan for every user.

    So, if you have userA and userB with the query:

    SELECT col1, col2 FROM tableA WHERE val1 = 10;

    Two execution plans will be generated, unless you have explictly set the default schema of each user to the same schema. The other option is to change the query and schema qualify the objects:

    SELECT col1, col2 FROM dbo.tableA WHERE val1 = 10;

    The above will always generate a single execution plan, regardless of the user that submits the query. Well, as long as the text is exactly the same 😉

    Jeffrey Williams
    “We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

    ― Charles R. Swindoll

    How to post questions to get better answers faster
    Managing Transaction Logs

Viewing 6 posts - 1 through 6 (of 6 total)

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