Results 1 to 5 of 5

Thread: Set FMTONLY ON

  1. #1
    Join Date
    Apr 2002
    Posts
    8

    Unanswered: Set FMTONLY ON

    Here is our problem

    We're using ADO and SQLOLEDB to connect to a Sql Server database. We use ADO Dataset objects, to set the commandtext property and then open the dataset. Nothing fancy.

    For reasons unknown, ADO seems to like to replicate *some* of our select queries and wrapp them inside SET FMTONLY ON and SET FMTONLY OFF. So for a simple select statement, two queries are sent to the server resulting in a performance hit due to network round trips.

    The thing that confuses the heck out of me is that some queries work fine (without the SET FMTONLY) whereas others don't, and I can't see a difference in the ADO Dataset objects we're using.

    The issue is basically to do with the performance hit involved with an additional, unnecessary network round trip.

    Can we stop this from happening?

    Any Suggestions would be much appreciated.

  2. #2
    Join Date
    Apr 2002
    Posts
    3
    First, I assume you are referring to ado recordsets? If so, when you see the fmtonly on and off, are you getting data returned or are you only getting the column information?
    Can you send an example of one that gets the fmtonly and one that doesn't?

  3. #3
    Join Date
    Apr 2002
    Posts
    8

    file showing sql trace

    Thanks for your response.

    I have attached the sql trace file that shows some queries wrapped in the Set FMTONLY tag.

    In answer to your question we are using The ADO Dataset object (for Delphi) that has an underlying recordset - we don't play with it
    directly. Likewise, it's the ADO components that are sending the two
    queries to the server, the first directly before the second. We want the
    data, it's ADO that seems to think that in some circumstances it needs to do two queries to retrieve it.
    Attached Thumbnails Attached Thumbnails sqltrace.gif  

  4. #4
    Join Date
    Feb 2002
    Posts
    2,232
    It would be helpful to actually see the code that generics these sql statements - both with fmtonly and the ones which do not have fmtonly. My gut feeling is that the sql statements that have fmtonly are being prepared first, then executed (whether it is done this way in the code or by Delphi) - where the non-fmtonly sql statements are being directly executed. Also, it may be attributed to how Delphi handles the statements.

    If you have access to the code, do a comparison. There are several circumstances where the fmtonly additions might be triggered.

    What type of ADO components are you using ???
    Last edited by rnealejr; 04-24-02 at 16:05.

  5. #5
    Join Date
    Dec 2001
    Location
    Toronto, Canada
    Posts
    335
    What it looks like to me is that the application may be issuing the command SET FMTONLY ON / SET FMTONLY OFF to get a list of columns in the recordset. I know in the past working with Powerbuilder this was done.

    The data may be displayed in the dynamic grid, that is why you see the SELECT * FROM, so that all of the columns are returned.
    MCDBA

Posting Permissions

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