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.
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?
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.
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 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.