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.
SET profiling = 1; select COUNT(id) from diplomska.`user`; SHOW PROFILES; |3 |1.404277ms |select COUNT(id) from diplomska.`user` u¶LIMIT 0, 200|
EXPLAIN ANALYSE select COUNT(id) from diplomska.`users`; |Execution Time: 58.140 ms
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.
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.
- → I can't do a foreign key, constraint error
- → How to implement DbDongle::convertTimestamps as workaround of invalid timestamps with MySql strict
- → MySQL error "Foreign key constraint is incorrectly formed"
- → Eloquent Multitable query
- → "Laravel 5.1" add user and project with userId
- → Database backup with custom code in laravel 5 and get the data upto 10 rows from per table in database
- → Laravel 5.1 QueryException when trying to delete a project
- → Using Array in '->where()' for Laravel Query Building
- → Chaining "Count of Columns" of a Method to Single Query Builder
- → Laravel Eloquent Joining Strange query
- → convert time using mysql laravel 5
- → How to update a column after an expiration date in MySQL?
- → Foreign key constraint fails on existing key