Best way to measure query time in different DBMS (MySQL, SQlite, Postgres, MariaDB, Oracle)

user_4798218 0
Viewed 183

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?

execution-time postgresql oracle mysql performance
2 Answers

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.

user_101387340

If you're measuring from the dbms client software, you only have a few things you can measure in a black-box way.

You can measure these elapsed times:

  • establishing a connection.
  • preparing the query (some DBMSs get involved in the prepare stage; others do it all on the client side).
  • time from query submission to the first row of the result set.
  • time to complete the query -- to the last row of the result set.

The difference between the last two is significant when your test queries return result sets with many rows.

I don't see how you can measure those numbers accurately without using some kind of programmed test harness. You might consider using php and its PDO database-access package, because it has a whole mess of drivers for different databases.

And, as I'm sure you know:

  • some databases can answer stuff like COUNT(id) FROM tbl instantaneously from the internal table metadata they keep. That varies by database and version.
  • indexes can change query performance by factors of many thousands.
  • in the real world, database performance issues usually come up when the servers are used concurrently by many clients.
user_2056080
Related