How to show query time in Perl, DBI?

Question

I use Perl and DBI to manage my MySQL tables, queries, etc. How can I show the query execution time?

If I do 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 . Is there any way in DBI to show runtime in Perl, something like this?

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

Answer 1

I can not find anything in DBI. I think that nothing is already implemented out of the box, although it may be interesting information.

Another way to do this is to get time before and after execution, and then make a simple distinction. You can do this from within your Perl script by simply getting the timestamp before executing the request, and then, subtract these 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"; 

Answer 2

You take the timestamp before starting the request and after the timestamp. The difference is the query execution time. For high-resolution time stamps, see Time :: HiRes

Answer 3

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

Answer 4

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

Output Example:

 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)