Post reply

Stored Procs vs Normal Query

  • July 20, 2011 at 7:25 am

    #241848

    Hi

    I know that stored procs are a lot better than executing the same script all the time because it is able to cache, but becasue caching will mainly help select queries is it ok to have inserts and updates not as stored procs? or will this create performance loss?

    Thanks 🙂

    The Fastest Methods aren't always the Quickest Methods

  • July 20, 2011 at 11:32 pm

    #1356152

    BinaryDigit (7/20/2011)

    .. but becasue caching will mainly help select queries is it ok to have inserts and updates not as stored procs?

    I don't agree with.

    Are you talking about the data pages in the memory?

    I think you are confused with query plan cache and data buffer.

  • July 21, 2011 at 6:50 am

    #1356312

    BinaryDigit (7/20/2011)

    Hi

    I know that stored procs are a lot better than executing the same script all the time because it is able to cache, but becasue caching will mainly help select queries is it ok to have inserts and updates not as stored procs? or will this create performance loss?

    Thanks 🙂

    I thought a big reason for SPROCs is thaey are already compiled versus just running them and they get compiled then. I think the data gets cashed either way.

    I may be all wet though:-D

  • July 21, 2011 at 6:53 am

    #1356316

    BinaryDigit (7/20/2011)

    Hi

    I know that stored procs are a lot better than executing the same script all the time because it is able to cache, but becasue caching will mainly help select queries is it ok to have inserts and updates not as stored procs? or will this create performance loss?

    Thanks 🙂

    It'll help any DML statement.

    BTW adhoc queries are cached too. Depending on how they are constructed they might never be reused... which is what the real issue is.

  • July 21, 2011 at 7:25 am

    #1356364

    Thanks for the replies 😀

    The Fastest Methods aren't always the Quickest Methods

  • July 21, 2011 at 7:29 am

    #1356368

    Is there something I can put into query to make it never cache?

    The Fastest Methods aren't always the Quickest Methods

  • July 21, 2011 at 7:34 am

    #1356372

    BinaryDigit (7/21/2011)

    Is there something I can put into query to make it never cache?

    OPTION (RECOMPILE) for statement level

    WITH RECOMPILE at the proc creation level to not save the plan.

  • July 21, 2011 at 7:49 am

    #1356379

    cool, thanks 🙂

    The Fastest Methods aren't always the Quickest Methods

Viewing 8 posts - 1 through 7 (of 7 total)

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