none
Strange Performance Issue With CommandBehavior.SchemaOnly RRS feed

  • Question

  • I have a reporting application that uses .NET SqlClient to get the field list for the report using command.ExecuteReader(CommandBehavior.SchemaOnly).  On the SQL backend, this translates to

     

    SET FMTONLY OFF; SET FMTONLY ON;

    <SELECT query specified in CommandText>.

     

    With one particular report, this query runs very slowly, timing out after 30 minutes (the currently configured CommandTimeout) even though it's only getting schema information.  The select query for the problem report is in the following format:

    SET ROWCOUNT 100000

    SELECT DISTINCT  [huge column list]

    FROM [multiple tables/views joined with inner and left joins]

    WHERE  [filter conditions] 

    SET ROWCOUNT 0

     

    What is interesting when I run the query (with the SET FMTONLY statements) from SSMS, on the first run (in a test environment, where I'm able to reproduce the issue) it takes over 1.5 hours, and on subsequent runs it completes instantly.  After running the report from the front-end (where it will still time out), it again takes forever in SSMS on the first run.  It almost acts like an issue with a stale (or nonexistent) execution plan that needs to be refreshed, but it's apparently being caused by something in the client code (ADO.NET layer).

    It appears that I might be able to work around the issue by changing the SELECT query in the CommandText to "SELECT TOP (0)" (this always completes quickly on the backend), but I'm wondering if someone has any insight into why this might be happening-I searched online and couldn't find much in the way of known issues with SET FMTONLY ON (or CommandBehavior.SchemaOnly), other than something with the MSDAC, which doesn't seem to apply here.  Also, this is only an issue with this one specific report; it completes quite quickly for all others.

    Thanks,

    -Dave

    Wednesday, August 11, 2010 12:27 AM

Answers

All replies

  • I found this on the Internet, but can't think why it should matter:

    http://www.developmentnow.com/g/113_2004_10_0_0_434638/Poor-performance-using-Set-FMTONLY-On.htm

    > it takes over 1.5 hours

    FMTONLY might be a red herring.  Might there be something slow with the SP compilation.  In test environment, run DBCC FREEPROCCACHE, then instead of doing a FMTONLY, actually execute the SP (non-FMTONLY mode).

     

    Wednesday, August 11, 2010 2:06 AM
  • Hi BC,

    Thanks for the reply.  There is no SP-it's running an ad hoc SELECT query.  Along the same lines of what you're suggesting, I've tried the query with OPTION (RECOMPILE) and there definitely seems to be something slow with compilation-it always runs slowly with OPTION (RECOMPILE) (and afterwards).

    I checked the article also, but none of the base tables referenced in the query lack a clustered index.

    Thanks,

    -Dave

    Wednesday, August 11, 2010 6:16 PM
  • If I understand correctly, OPTION (RECOMPILE) reproduces the case where it takes an extremely long among of time.  Are there any parameters?  Try replacing them all with reasonable hard-coded values and compare, leaving OPTION (RECOMPILE) in tack.  Write back with the effect of doing that.

    This is probably going to come down to you (1) writing the query in an alternative way, (2) using some kind of query hint, or (3) having to submit a bug report with Microsoft.

    Wednesday, August 11, 2010 10:13 PM