Odd FMTONLY statements

Hi,

I recently started using ADO

for connecting to a remote SQL Server from Delphi.

Prior to this I used BDE (Borland Database Engine).

I noticed that all SQL communication seemed slow – only

half the speed of using BDE!

I ran a trace using the Profiler (from MS SQL Server)

and noticed that ADO

generated roughly twice the amount of traffic compared to BDE. Every SQL statement (SELECT,

INSERT and UPDATE) send from my application was preceded by the exact same

statement encapsulated in ‘SET FMTONLY ON/OFF’.

-Example Start-

SET FMTONLY ON SELECT * FROM TabelVersion SET FMTONLY

OFF

SELECT * FROM TabelVersion

-Example End-

Why are these odd statements being passed to the

server?

Shouldn’t the provider be able to handle the returned

dataset without sending the FMTONLY statement in advance? And if not, why doesn’t

it cache this information?

Is it possible to minimize this traffic?

I also spotted quite a lot ‘SET NO_BROWSETABLE ON/OFF’

statements. What is the use for these?

I found this article (http://support.microsoft.com/kb/836830/en-us)

on MSKB, but I should already have the hotfix for this installed by MDAC 2.8 SP1!

This slow pace is killing me – please help - egeskov

---

My configuration:

Workstation:

WinXP SP2

MDAC 2.8 SP1

(2.81.1117.0)

ConnectionString=’Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist

Security Info=False;Application Name=PD:KE;Data Source=SMIL;Initial

Catalog=PDDebug’

Server:

Windows

Server 2000

SP4

MS SQL

Server 8

FMTONLY is an indication that the provider needs parameter or result metadata prior to execution because the application has not made this available. It would help to see some sample code, but essentially you need to avoid things like cmd.Parameters.Refresh and always set the Type property of parameters, etc. If the application doesn't provide all the metadata the provider needs then it costs a round trip to the server to get it. In most cases applications can be written to avoid this and will perform much better as a result.|||

Chris has well explained why the FMTONLY statements come.

My question is that what is the duration you see of these FMTONLY statements in Profiler trace? How is it compared with the duration of the actual select statement? Based on my experience, these FMTONLY statements usually completes instantly and should not cause severe/noticable performance hit.

|||I have made a small code sample that only connects to the server, runs a single query and disconnects:

---

// Procedure that runs then the main form is shown
procedure TForm1.FormShow(Sender: TObject);
var Conn: TADOConnection;
Q: TADOQuery;
begin
// The Connection object is constructed and initialized with the connection string
Conn:=TADOConnection.Create(Nil);
Conn.ConnectionString:='Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Application Name=PD:KE;Data Source=Main;Initial Catalog=ProduktData';

// Connecting to the server
Conn.Open;

// The query object is constructed and binded to the connection object
Q:=TADOQuery.Create(Nil);
Q.Connection:=Conn;

// SQL is feed to the query
Q.SQL.Add('SELECT COUNT(*) FROM Param');

// The query is executed
Q.Open;

// The resulting single cell resultset is written to the forms title
Caption:=Q.Fields[0].AsString;

// Objects are destructed
Q.Free;
Conn.Free;
end;

---

My trace from running this sample code is visible here:
http://www.diku.dk/hjemmesider/studerende/egeskov/ADOTrace.trc

I have no clue what parameters my code mught be missing.|||FMTONLY itself should execute fairly quickly at the server and profiler will show you the actual cost. The main concern is the rount trip to the server. This may or may not be significant to your application. It depends if the client and server are on the same system, how scalable the app has to be, how heavily loaded the server is, etc.|||i agree that in this case it's hard to see why the FMTONLY is needed. Maybe Delphi always checks for parameters and somehow is somehow focing this. I don't have access to a copy of Delphi and couldn't find any online documentation so can't investigate further myself. They key thing to try is to supply parameter information before executing the query when the query does have parameters, and check if there is a way to tell Delphi that there are no parameters otherwise.|||

Note the FMTONLY statements will be generated by the ADO client cursor code when it is trying to create the client side cursor.

For example look at ->

http://groups.google.com/group/borland.public.delphi.database.sqlservers/browse_thread/thread/99a3a60f8fefa14f/9f7edb9e622d7cfb%239f7edb9e622d7cfb

My recommendation (note I don't know much about TADOQuery but I culled this from searching around the internet) ->

Switch CursorLocation to clUseServer, CursorType to ctOpenForwardOnly and LockType to ltReadOnly

This should avoid it.

|||Thanks for

your interest.

Due to your

suggestions a got hold of the ADOQuery’s ParamCheck property. By disabling

this, I got rid of the FMTONLY statements which increased the performance by

almost 100 % – the number of round trips really is significant!
I have also

done some fiddling about with the CursorLocation, CursorType and LockType, but

my first attempts didn’t improve performance compared to disabling ParamCheck.

I’ll definitely have to look further into this.|||Nice. I have the same problem. I will try this. Did you find a solution for the NO_BROWSE. I have the same thing. Bill.|||No I haven't really looked

into it jet.