Results 1 to 3 of 3

Thread: fmtonly

  1. #1
    Join Date
    May 2004

    Exclamation Unanswered: fmtonly

    I have a very weird situation with one of the sp.
    when I create it and try to run it takes about 10 min to execute,
    BUT! If before executing i ran set fmtonly on then set fmtonly off It execute within 2-6 seconds.

    As far as I know fmtonly to sp is the same as noexec to query.
    What does it do that makes sp to run that much faster?
    What can i do so sp will run faster without fmtonly????
    Any help is greatly appreciated!

    Adaptive Server Enterprise/15.5/EBF 17795 SMP ESD#1/P/x86_64/Enterprise Linux/asear155/2495/64-bit/FBO/
    Last edited by treneryy; 01-29-14 at 15:49.

  2. #2
    Join Date
    Jan 2004
    Provided Answers: 4
    Noexec does not execute retrieve data and therefore does not need to do IO, and neither does fmtonly. That is why it runs so much faster.
    What does the sp do, and how many records does it have to process?
    And have you looked at the query plan? Maybe the query already runs as fast as it can.
    Last edited by Martijnvs; 01-29-14 at 17:05.
    I'm not crazy, I'm an aeroplane!

  3. #3
    Join Date
    May 2004
    Thank you for the reply. But that is not what i meant.

    I meant that sp hangs without returning result. BUT if before executing sp I run

    set fmtonly on
    set fmtonly off
    exec sp

    It will execute and return 300K rows in 3-6 seconds.
    So my question is : what does fmtonly does that makes sp to execute?

Posting Permissions

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