Hi Arcady,

Maybe the PB tracing and profiling will help you.
John Strano explained it very well in this video:

Catching the Runtime Error in the Act - Tracing and Profiling Your Application at Runtime

There are different views in which you can even display the execution times of individual lines of code (e.g. retrieve or embedded SQL lines).

I hope that helps you.

Regards Tobi

TRANSLATE with x

English

Arabic	Hebrew	Polish
Bulgarian	Hindi	Portuguese
Catalan	Hmong Daw	Romanian
Chinese Simplified	Hungarian	Russian
Chinese Traditional	Indonesian	Slovak
Czech	Italian	Slovenian
Danish	Japanese	Spanish
Dutch	Klingon	Swedish
English	Korean	Thai
Estonian	Latvian	Turkish
Finnish	Lithuanian	Ukrainian
French	Malay	Urdu
German	Maltese	Vietnamese
Greek	Norwegian	Welsh
Haitian Creole	Persian

 

TRANSLATE with

COPY THE URL BELOW

Back

EMBED THE SNIPPET BELOW IN YOUR SITE

Enable collaborative features and customize widget: Bing Webmaster Portal

Back

I've recently been debugging a very slow (2-3minutes) retrieval and started doing some tracing of my own. 3 DB transaction objects, TRACE XXX on all 3 of them, also using SQL tracing features too.

Each Transaction object can have a different file assigned, and when using TRACE in the DBMS, it should prompt for the filename for each connection (it does for me), so you can have each trace end up in a different file.

Here's how I connect to the DB:

if ib_dbtrace then
	sqlca.DbParm += "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT;SQL_OPT_TRACE,SQL_OPT_TRACE_ON;SQL_OPT_TRACEFILE,c:\temp\odbctrace_sqlca.log'"
	sqlca.dbms="TRACE " + GetSettingString(gs_IniFile, "Database", "DBMS", "ODBC")
else
	sqlca.DbParm += "ConnectOption='SQL_DRIVER_CONNECT,SQL_DRIVER_NOPROMPT'"
	sqlca.dbms=GetSettingString(gs_IniFile, "Database", "DBMS", "ODBC")
end if

Further on in the code we replace _sqlca with the appropriate transaction name in the DBParm to differentiate the trace files.

trans_main.DbParm = srchrepl(sqlca.dbparm, "_sqlca", "_trans_main")

From this, I've been able to see what's going on through each part of the system, and can compare retrieval times quite easily. (though it still hasn't helped me solve the speed issue yet)

Hi Arcady;

  Unfortunately,  the SQLCA.DBMS = "TRACE XXX" command outputs to only one SQL trace file no matter how many Transaction Objects you have this feature enabled on.

  However, ODBC (via each of its DSN) has it's own SQL tracing feature. So you could trace multiple DBMS interactions simultaneously via the ODBC trace feature.  HTH

Regards ... Chris

Hi.

I don't know what database you are using, but I wonder if it provides some monitoring tools to do that. This has more sense as you can see that information from server side. Some database do have a profiler (example: sql anywhere does have one). With profiler you don't see only which statement gets to long but you may also get hints about missing indexes e.t.c.

Andreas.