How to correctly identify execution time "select" and other DML-commands in milliseconds (TOraQuery, in Delphi 7)

[ProRockX]

Hello, dear. Really need your help.
The question, in fact, in the name of the topic.

Having read about the milliseconds in the Internet, and taking out an example, I wrote a function:

Code: Select all

function TfrSQL.pr_calc_time_work: Extended;
var
  Fr, t1, t2: Int64;
  Dt: Extended;
begin
    // Determine the processor speed (the number of cycles per second).
    QueryPerformanceFrequency (Fr);
    if Fr = 0 then begin
      ShowMessage ('Unable to get information about the frequency.');
      Exit;
    end;
    // Reading the meter reading cycles.
    QueryPerformanceCounter (t1);
    // Start the process under investigation.
   timeBeginPeriod (1);
    if fn_decode_sqltype = 'SELECT' then // handwritten function, which returns the type of executable object
    // (checked property TOraQuery.sqltype)
      ORAQuery1.Open;
    if (fn_decode_sqltype = 'UPDATE') or (fn_decode_sqltype = 'INSERT') or (fn_decode_sqltype = 'DELETE') then
      ORAQuery1.ExecSQL;
    // Readout cycles.
    QueryPerformanceCounter (t2);
    // Calculating time.
      Dt: = RoundTo ((t2 - t1) / Fr, -3);
    result: = Dt;
end;

I tried to compare the results of the "select" and other DML-commands of my program and PLSQL Developer. The results differ. And yet, when it starts in PLSQL Developer same "select" several times most of the results are the same (or slightly different). And if you do the same in my program - the results greatly jump.

I understand, to guess why someone runs a program without seeing it full of code, it is desirable to have telepathic abilities. But I hope that you will have some ideas.

[MaximG]

The approach you are using to measure query execution time is rather correct. At this, it is not quite correct to compare measurement results you got using this approach with measurement results of some other program, when you don't know how exactly the third-party program measures execution time. In addition, note that query execution time (from the moment of execution start to retrieving the first data) can be roughly divided into 2 periods: query execution by the DB server and time to process retrieved data by the client. While the server executes the query, a number of operations is executed (query parsing, obtaining query execution plan, parameter binding, etc.). and the duration of these operations may vary depending on various conditions — server load, availability of required data in the buffer cache, availability of relevant statistics, and a range of others. In turn, processing of query results on the client may also be different. For example, when selecting data, you can obtain all the data at once (OraQuery.FetchAll := True) - and in this case, the total query execution time increase. Therefore, it is complicated to compare execution time of the same query by different programs if their measurement approaches are unknown.