Post reply

Improved Query: Better Duration: More reads? More CPU?

  • October 17, 2007 at 6:22 am

    #153901

    Hello forum. I've been updating a stinky query. I've way bettered it's execution time (from 3 mins to 25 seconds) but when looking at it through profiler and comparing to the original the CPU hits are up by 50%. The reads multiplied by a factor of 40! If I were to look at only those stats I would think that I'm moving in the worng direction.

    Thoughts?

    Thanks ST

  • October 25, 2007 at 11:05 am

    #745442

    That's odd. Normally the lesser the logical reads the merrier.

    *Did you use DBCC FREEPROCCACHE to clear the execution plans?

    *A stored procedure can be slower than a regular query because of "parameter sniffing". You might want to add the option WITH RECOMPILE.

    *The original query try to lock too many things at a time?

    Do you have the before and after query?

  • October 28, 2007 at 10:06 pm

    #746078

    What your server is doing those 3 mins then?

    If not doing disk operations, not computing - then what?

    _____________
    Code for TallyGenerator

  • October 28, 2007 at 11:31 pm

    #746084

    Re-write the SP with a new logic by cutting down the lines so as to extract the result set. If you still run into problems try executing the same with DBCC commands by means of profiler help.

  • October 29, 2007 at 4:58 am

    #746157

    Hey SoulTower! Wondered when you'd get a login... especially after the apparent demise of Belution.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • October 29, 2007 at 10:36 am

    #746324

    Thanks for the replies. I'll put them to the test when I get back to the office next week. This week I'm in a training course.

    Hi, Jeff. It does look like Belution is dead. Bummer. I liked it, or at least was used to it.

    Peace

  • October 30, 2007 at 5:02 am

    #746559

    One situation that 'could' cause the observed behavior is the original query was (attempting ) doing one or more larger-scale locks that kept getting delayed due to heavy activity on the resource(s). The new query could be doing something such as forced nested-loop lookups and keeping out of an escalation situation and thus getting the resources when asked for in the stream?? A factor of 40 increase is pretty big though!

    Best,
    Kevin G. Boles
    SQL Server Consultant
    SQL MVP 2007-2012
    TheSQLGuru on googles mail service

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

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