How can I show the query time in Perl, DBI?

Question

I use Perl and DBI to manage my MySQL tables, querys, etc. How can I show the running time of a query?

If I do a SELECT in the console, the result will be like this:

+-----+-------------+
| id  | name        |
+-----+--------------
|   1 | Jack        |
|   2 | Joe         |
|   3 | Mary        |
+-----+-------------+
3 rows in set (0.17 sec)

I need to show 0.17 sec. There is any way in DBI to show the running time in Perl, something like this?

my $dbh = $db->prepare("SELECT id, name FROM names ORDER BY id;");
$dbh->execute;
print $dbh->runnin_time; # ???

Kendalkendall

Answer 1

Using Time::HiRes could also be an easy way to find the query time. Here is an example that uses Time::HiRes:

use Time::HiRes;

$start_time = Time::HiRes::gettimeofday();

my $dbh = $db->prepare("SELECT id, name FROM names ORDER BY id;");
$dbh->execute;

$end_time = Time::HiRes::gettimeofday();

my $elapsedtime = sprintf("%.6f", $end_time - $start_time);
print "Execution time(seconds) : $elapsedtime \n";

Nolasco

Answer 2

You take a timestamp before you run the query, and a timestamp after. The difference is your query execution time. For obtaining high-resolution timestamps, see Time::HiRes

Elea

Isn't there any way under DBI? – Kendalkendall

I was checked before asked the question. – Kendalkendall

Answer 3

DBI#Profile, DBI::Profile, DBI::ProfileData, DBI::ProfileDumper, dbiprof

Platypus

Rather than provide a series of links, please provide excerpts showing why those are useful. This helps summarize the content, plus provides useful information in case the links rot and break. – Poikilothermic

Answer 4

I can't find anything in DBI. I think that there is nothing already implemented out of the box, though could be interesting information.

The other way to do this would be to get the time before and after the execution and then make a simple difference. You can do it from within your Perl script simply getting the time stamp before the query execution, and after, then subtract the two to find the execution time.

my $start = DateTime->now;
my $dbh = $db->prepare("SELECT id, name FROM names ORDER BY id;");
$dbh->execute;
my $end = DateTime->now;


my $elapsedtime = ($end->subtract_datetime($start))->seconds;
print "Execution time(seconds) : $elapsedtime \n";

Dorina

Using Benchmark with :hireswallclock instead of DateTime would also work well. – Edette

This won't be very helpful for sub-second query times which are quite common – Perissodactyl

Answer 5

Reading @daxim's links to documentation, there is a simple way to achieve this by running your script with DBI_PROFILE=2 which is from DBI::Profile

Example output:

DBI::Profile: 53.203692s 50.67% (6725 calls) script.pl @ 2016-01-21 11:51:49
'INSERT INTO FOO ("BAR") VALUES (?)' =>
    0.057596s / 2 = 0.028798s avg (first 0.051621s, min 0.005975s, max 0.051621s)
'INSERT INTO BAZ ("QUX") VALUES (?)' =>
    0.367184s / 44 = 0.008345s avg (first 0.039410s, min 0.002445s, max 0.039410s)

Perissodactyl