Post reply

SET FMTONLY Query Times out...

  • November 8, 2004 at 2:04 pm

    #85977

    I have the following Profiler Trace Results...

    13 SET FMTONLY ON select LNUM,CBSOURCE,BOR_NUM,BOR_DBID,CNTR,DBID from CB_TRADE SET FMTONLY OFF

    40 SET FMTONLY ON

    41 SET FMTONLY ON

    40 select LNUM,CBSOURCE,BOR_NUM,BOR_DBID,CNTR,DBID, from CB_TRADE

    40 select LNUM,CBSOURCE,BOR_NUM,BOR_DBID,CNTR,DBID, from CB_TRADE

    ...  (occurs 1522 times)

    40 select LNUM,CBSOURCE,BOR_NUM,BOR_DBID,CNTR,DBID, from CB_TRADE

    40 select LNUM,CBSOURCE,BOR_NUM,BOR_DBID,CNTR,DBID, from CB_TRADE

    12 SET FMTONLY ON select LNUM,CBSOURCE,BOR_NUM,BOR_DBID,CNTR,DBID from CB_TRADE SET FMTONLY OFF

    16 NULL

    The query that is going through ODBC actually has a WHERE clause, however, for some reason,

    ODBC is stripping it off and running the query with the SET FMTONLY statements.  These statements

    are timing out at 30 secs.  As you can see from the trace, the eventclass 40 (SQL:StmtStarting) is

    executed 1522 times.

    We see the same or similar results running through Query Analyzer and osql.

    The server is a SQL2K SP3.  The table is large, having > 7 million rows. However, I can run the same

    query on another box with the same number of rows and the query runs correctly, returning an empty

    rowset in less than a second.

    Any insight would be greatly appreciated.

    Elizabeth

  • November 10, 2004 at 5:44 am

    #529596

    from bol;

    *********************************************

    SET FMTONLY

    Returns only meta data to the client.

    Syntax

    SET FMTONLY { ON | OFF }

    Remarks

    No rows are processed or sent to the client as a result of the request when SET FMTONLY is turned ON.

    The setting of SET FMTONLY is set at execute or run time and not at parse time.

    Permissions

    SET FMTONLY permissions default to all users.

    Examples

    This example changes the SET FMTONLY setting to ON and executes a SELECT statement. The setting causes the statement to return the column information only; no rows of data are returned.

    SET FMTONLY ONGOUSE pubsGOSELECT * FROM pubs.dbo.authorsGO
    *********************************************
    A) after FMTONLY on you have to put it OFF again somewhere in you script.
    B) I think stripping the where clause is resulting in a full tablescan 
( on 7 million rows !! ) which results in a long waiting period. But to
be exact on this I need to know the SQL script running.
    Regards 
GKramer
     

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

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