Sometimes we see huge difference between totals collected by ASA Profiler and analysis of ASA request log via stored procedure sa_get_request_profile. We capture data for all connections without any filters.
Our application is written on PowerBuilder v12 and used ODBC.
For example please see picture:
Profiler gets 213 msecs for 29 invocations of procedure sp_claim_d_claim_main, while Request Log shows 383564 (!) msecs for same executions.
Returned result set is not huge : 10-20 rows.
asked 28 Mar '13, 11:47
There are separate steps taken by the database when executing a query.
(Note: The following answer is responding with the assumption that the software is minimally SQL Anywhere 10 and that you are using the new diagnostic profiling tools. I unfortunately don't readily have the definition of the system tables for ASA 9's 'tracing' capabilities and a query to get back the same results. However, I believe the explanation of the behaviour remains the same).
The Application Profiling / Diagnostic Tracing 'Summary Tab' examines how long a statement took to execute/materialize inside the database server / optimizer (PREPARE / DESCRIBE / OPEN). The
The same cursor results are also stored in the Application Profiling / Diagnostic Tracing tables, although I don't believe these times are displayed readily in the Sybase Central view.
Here's a sample SQL query against the diagnostic tracing data tables to get at the 'cursor result' timings, and should be similar times to what you see in
select top 10 sdr.connection_number, sdr.request_id, sdc.cursor_id, sdc.total_fetch_time_ms, sdr.start_time, sdr.finish_time, sdr.duration_ms from sa_diagnostic_cursor sdc, sa_diagnostic_request sdr where sdr.cursor_id = sdc.cursor_id order by sdc.total_fetch_time_ms desc;