minimizing executaion time in database operations with DBIX

siddheshsawant has asked for the wisdom of the Perl Monks concerning the following question:

Hello freinds

I am working on the perl API project and in the back end of it I am using MySQL. I have written a perl API script to extract data from database. I am using DBIX module to do so !!!

The General form of the query which I am using is as follows -

 my $tr_id_rs =
        $schema->resultset("TestRunStatus")->search(
            {
             "DATE(test_run_id.start_date)" => { ">=" => $start_date,
                                                "<=" => $end_date,
                                                },
             "test_run_status_type_id.name" => $status_type,
             },
            { select    => "test_run_id.id",
              join      => ["test_run_id",
                            "test_run_status_type_id"],
              group_by  => "test_run_id.id",
              order_by  => "test_run_id.id",}
        );
[download]

I am extracting a database ID for the matching i/p conditions. Then depending on user's input I extract the information related to that database ID. I found that if I have to extract larger data sets then it's time also gets increased a lot. I wants to minimize the time of execution.

Can any body tell me how to achieve that ? Is there any other way extracting data from the MySQL DB ?

Comment on minimizing executaion time in database operations with DBIX Download Code

Replies are listed 'Best First'.
Re: minimizing execution time in database operations with DBIx::Class by moritz (Cardinal) on Jun 09, 2010 at 20:02 UTC
Is it the database that is slow, or the Perl code? You can set an environment variable (iirc it's `DBIC_TRACE`) to force DBIx::Class to show you its SQL queries. You can try them independently from DBIx::Class, and measure their timings If it's actually the database that's slow, consider adding an index to the `start_date` column. You can also let mysql explain the query execution plan to you, and see if it uses indexes for all operations. If not, you might have to add another index, or restructure your query in a way that mysql can optimize. And finally, when you refer to the short form of DBIx::Class, plese use say dbic, not dbix - there are many DBI eXtension modules out there. Perl 6 - links to (nearly) everything that is Perl 6.	[reply] [d/l] [select]

Replies are listed 'Best First'.

Re: minimizing execution time in database operations with DBIx::Class
by moritz (Cardinal) on Jun 09, 2010 at 20:02 UTC

You can set an environment variable (iirc it's DBIC_TRACE) to force DBIx::Class to show you its SQL queries. You can try them independently from DBIx::Class, and measure their timings

If it's actually the database that's slow, consider adding an index to the start_date column.

You can also let mysql explain the query execution plan to you, and see if it uses indexes for all operations. If not, you might have to add another index, or restructure your query in a way that mysql can optimize.

And finally, when you refer to the short form of DBIx::Class, plese use say dbic, not dbix - there are many DBI eXtension modules out there.

Perl 6 - links to (nearly) everything that is Perl 6.

[reply]
[d/l]
[select]