Ad

Best Way To Measure Query Time In Different DBMS (MySQL, SQlite, Postgres, MariaDB, Oracle)

For college I'm doing a thesis on performance in different DBMS. I have set of tables with same set of data in 5 different DBMS (MySQL, MariaDB, SQLite, Postgres, Oracle) and I will be doing queries on them. For each DBMS I will have database, which will hold 1k, 10k, 100k, 1mio, 10mio rows.

And I want to research how much time does each DBMS need for same query on same data and compare that time between them.

As for the hardware, I will create a virtual machine, so that hardware won't have as much impact on query performance.

Which brings me to time measurement. What would be the most accurate way to measure time across all DBMS? Each DBMS has its own way of query measuring, but implementation of each one is different from one another and I don't think that this is representative data for comparison.

MySQL:

SET profiling = 1;
select COUNT(id) from diplomska.`user`;
SHOW PROFILES;


|3       |1.404277ms |select COUNT(id) from diplomska.`user` u¶LIMIT 0, 200|

PostgreSQL

EXPLAIN ANALYSE select COUNT(id) from diplomska.`users`;


|Execution Time: 58.140 ms

Oracle

declare
  t1 timestamp;
  t2 timestamp;
begin
    t1 := systimestamp;
    select COUNT(id) from diplomska.`users`
    t2 := systimestamp;
    dbms_output.put_line('Elapsed Seconds: '||TO_CHAR(t2-t1, 'SSSS.FF'));
end;

Elapsed Seconds: +000000 00:00:00.000091000

Another option would be writing script in some programming language (Python, C#,...) and have that measure time and execute queries. But still this brings us to also measuring time of code compilation, DBMS controller initializing and fetching data and we can't isolate query time only.

Any suggestions?

Ad

Answer

In all listed DBMSs except SQLite supports the next code:

-- create service table which will store data
CREATE TABLE service (id INT, stamp TIMESTAMP(3));

-- store timestamp before test query execution
INSERT INTO service (id, stamp) VALUES (1, CURRENT_TIMESTAMP(3));

-- execute query to test
-- SELECT ... ;

-- store timestamp after test query execution
INSERT INTO service (id, stamp) VALUES (2, CURRENT_TIMESTAMP(3));

-- retrieve elapsed time
SELECT t1.stamp - t2.stamp elapsed
FROM service t1
CROSS JOIN service t2
WHERE t1.id = 2 AND t2.id = 1;

sample fiddle - you may test using MySQL, MariaDB, Oracle, PostgreSQL.

Ad
source: stackoverflow.com
Ad