Beefy Boxes and Bandwidth Generously Provided by pair Networks
go ahead... be a heretic
 
PerlMonks  

DBIx startup slow

by anjalis (Novice)
on Sep 04, 2011 at 21:55 UTC ( #924127=perlquestion: print w/ replies, xml ) Need Help??
anjalis has asked for the wisdom of the Perl Monks concerning the following question:

Hi, i'm creating perl app and using DBIx, but when i'm searching first time in it,it takes about 1 minute, i looked at DBIx cookbook and crawled google but i think i have done everything to optimise it. i've created resultset statically. This is base Schema:

package MyDictionary::Schema; use base 'DBIx::Class::Schema'; __PACKAGE__->load_classes('Engword','Skword','En_To_Sk','En_Phrase','E +n_Irregular'); 1; and like this look other 5 tables: package MyDictionary::Schema::Skword; use base 'DBIx::Class::Core'; __PACKAGE__->table('skwordmeaning'); __PACKAGE__->add_columns( sk_id => { data_type => 'integer', is_auto_increment => 1, }, skword => { data_type => 'text', }, ); __PACKAGE__->set_primary_key('sk_id'); __PACKAGE__->has_many(en_to_sk => 'MyDictionary::Schema::En_To_Sk', 's +k_id'); __PACKAGE__->many_to_many(engwords => 'en_to_sk','engword' ); 1;

I have many to many relationships and two tables with more then 400 000 records, can you someone please advice how can i optimise startup? Thanks a lot

Comment on DBIx startup slow
Download Code
Re: DBIx startup slow
by onelesd (Pilgrim) on Sep 05, 2011 at 03:52 UTC
    Need more info. What does your search look like?
Re: DBIx startup slow
by CountZero (Bishop) on Sep 05, 2011 at 06:34 UTC
    Did you profile your code to see where the delay is happening?

    CountZero

    A program should be light and agile, its subroutines connected like a string of pearls. The spirit and intent of the program should be retained throughout. There should be neither too little or too much, neither needless loops nor useless variables, neither lack of structure nor overwhelming rigidity." - The Tao of Programming, 4.1 - Geoffrey James

Re: DBIx startup slow
by Anonymous Monk on Sep 05, 2011 at 07:04 UTC

    Hi, i didn't profile it but i think nothing else should make a problem because, everything else used there is just simple classes and methods and shouldn't have any initialization. I will try to profile in the evening and i will provide also more code.

Re: DBIx startup slow
by chrestomanci (Priest) on Sep 05, 2011 at 09:33 UTC

    Slow database performance when you run a query on a large table sounds like a lack of an index on that table.

    What database engine are you using? (SQLite, MySQL, Postgres etc?)

    Are there indexes on the relevant columns in your tables? You can specify indexes in your DBIC table definition with __PACKAGE__->add_index(name => 'idx_name', fields => ['name']); See: The DBIx::Class Cookbook.

    Have you tried running the query by hand using raw SQL to see what performance you get? (You can get DBIx::Class to emit each SQL call it makes by setting the DBIC_TRACE environment variable to a true value. See: DBIx::Class::Storage. It might also be worth using the SQL EXPLAIN command on your keyword to understand a bit more how the database treats your query, and if there is anything expensive in there.

    You say that you have a many to many relationship bridge. When is the link table populated? If by some mechanism it does not exist at start-up, but is created on the fly on first use, then that would explain the slow performance when you first use it, though I have never come across a system where that creates link tables automatically.

      I'm using SQLite engine, i have primary key index. I'will also look on that SQL's, but slow is just first search, second and etc.. are fast, thanks

        You don't just need indexes on the primary keys, you also need them on the foreign keys, as when you do a search on a many_to_many relationship, DBIC will do a query with a foreign key join.

        Take a look at the examples in DBIx:Class:Relationship under many_to_many.

        In one of those examples, if you do $actor->roles(), then your database has to get the primary key of the actor, run a query on the ActorRoles table for all entries matching that actor_id, look at the role_id in each matching row, and then return all roles that match. The query will be something like this:

        select * from tblRoles r where r.role_id IN ( select role_id from tblA +ctorRole ar where ar.actor_id = ? )

        Unless there is an index on actor_id in the ActorRoles table, the query will be very slow.

Re: DBIx startup slow
by Anonymous Monk on Sep 05, 2011 at 11:06 UTC

    i forgot, and i'm not creating linking table on the fly there is linking table, i don't know how much rows are there now but more than 400 000 perhaps

Re: DBIx startup slow
by norbert.csongradi (Beadle) on Sep 05, 2011 at 11:36 UTC

    You still have not provided any info on the query you try to run against your database... some random thoughts:

    • when you use any conditions, matching on a text field without index is pretty expensive CPU-wise
    • when you fetch the whole DB, I'm pretty sure that after the first invocation, all the SQLite data will be in memory cache (by the kernel), hence the subsequent runs are fast
    You should profile it, or - to say the least - put some comments in to see where your program takes the most of the time.
Reaped: Re: DBIx startup slow
by NodeReaper (Curate) on Sep 05, 2011 at 13:47 UTC
Re: DBIx startup slow
by anjalis (Novice) on Sep 05, 2011 at 21:46 UTC

    Hi all, so i done some profiling and here are the results: 1. DBIx profiling:

    E:\Documents\ProgramovanieProgramyAskripty\Symphaty0.4>perl -MDBIx::Pr +ofile symphaty.pl Gtk-CRITICAL **: gtk_tree_view_column_cell_layout_pack_start: assertio +n `! gtk_t ree_view_column_get_cell_info (column, cell)' failed at symphaty.pl li +ne 250. ================================================================= 2444 "SELECT me.eng_id, me.engword FROM englishword me WHERE ( engword + = ? )" Total wall clock time: 1.197475s execute --------------------------------------- Total Count : 2 Wall Clock : 1.1974750 s 0.5987375 s Cpu Time : 0.2020000 s 0.1010000 s returned 0E0 Count : 2 Wall Clock : 1.1974750 s 0.5987375 s Cpu Time : 0.2020000 s 0.1010000 s ================================================================= 2444 "SELECT me.eng_id, me.engphrase, me.skphrase, me.phrase_id FROM p +hrase me W HERE ( me.eng_id = ? )" Total wall clock time: 0.790816s execute --------------------------------------- Total Count : 1 Wall Clock : 0.7908160 s 0.7908160 s Cpu Time : 0.0160000 s 0.0160000 s returned 0E0 Count : 1 Wall Clock : 0.7908160 s 0.7908160 s Cpu Time : 0.0160000 s 0.0160000 s ================================================================= 2444 "SELECT skword.sk_id, skword.skword FROM en_to_sk me JOIN skword +meaning sk word ON skword.sk_id = me.sk_id WHERE ( me.eng_id = ? )" Total wall +clock time : 0.369762s execute --------------------------------------- Total Count : 1 Wall Clock : 0.3697620 s 0.3697620 s Cpu Time : 0.1250000 s 0.1250000 s returned 0E0 Count : 1 Wall Clock : 0.3697620 s 0.3697620 s Cpu Time : 0.1250000 s 0.1250000 s ================================================================= 2444 "SELECT me.eng_id, me.infinitive, me.past, me.participle, me.irre +gular_id F ROM irregular me WHERE ( ( infinitive = ? OR past = ? OR participle = +? ) )" T otal wall clock time: 0.102356s execute --------------------------------------- Total Count : 1 Wall Clock : 0.1023560 s 0.1023560 s Cpu Time : 0.0000000 s 0.0000000 s returned 0E0 Count : 1 Wall Clock : 0.1023560 s 0.1023560 s Cpu Time : 0.0000000 s 0.0000000 s ================================================================= 2444 "SELECT me.sk_id, me.skword FROM skwordmeaning me WHERE ( skword += ? )" T otal wall clock time: 0.071004s execute --------------------------------------- Total Count : 1 Wall Clock : 0.0710040 s 0.0710040 s Cpu Time : 0.0160000 s 0.0160000 s returned 0E0 Count : 1 Wall Clock : 0.0710040 s 0.0710040 s Cpu Time : 0.0160000 s 0.0160000 s ================================================================= 2444 "SELECT engword.eng_id, engword.engword FROM en_to_sk me JOIN en +glishword engword ON engword.eng_id = me.eng_id WHERE ( me.sk_id = ? )" Total +wall clock time: 0.004899s execute --------------------------------------- Total Count : 1 Wall Clock : 0.0048990 s 0.0048990 s Cpu Time : 0.0160000 s 0.0160000 s returned 0E0 Count : 1 Wall Clock : 0.0048990 s 0.0048990 s Cpu Time : 0.0160000 s 0.0160000 s

    2. whole script profiling, most important restults:

    # spent 28.7s (0ns+28.7) within Searcher::searchWord which was called +2 times, avg 14.4s/call: # 2 times (0ns+28.7s) by Searcher::search at + line 62, avg 14.4s/call sub searchWord($) { my $self = shift; my $searchedString = shift; my @word = (); my @engwords = (); my $engword; my @skwordsSet = (); my $skwordSet; my @engwordsSet = (); my $engwordSet; my @skwords = (); my $skword; if($self->{direction} eq "eng") { my @engwords = $schema->resultset('Engword')->search({engword =>search +edString}); # spent 12.8s making 1 call to DBIx::Class::ResultSet::search # spent +1.00ms making 1 call to DBIx::Class::Schema::resultset foreach $engword(@engwords){ my @skwordsSet = $engword->skwords; foreach $skwordSet(@skwordsSet){ push(@word,$skwordSet->skword); } # foreach } # foreach } # if if($self->{direction} eq "svk") { my @skwords = $schema->resultset('Skword')->search({skword => $searche +dString}); # spent 9.87s making 1 call to DBIx::Class::ResultSet::search # spent +1.00ms making 1 call to DBIx::Class::Schema::resultset foreach $skword(@skwords){ my @engwordsSet = $skword->engwords; # spent 6.07s making 1 call to MyDictionary::Schema::Skword::engwords foreach $engwordSet(@engwordsSet){ push(@word,$engwordSet->engword); # spent 0s making 4 calls to MyDictionary::Schema::Engword::engword, a +vg 0s/call } # foreach } # foreach } # if return @word; } # method searchWord

    So my opinion is that queries are fast, but there is something with fetching the results. Also look at this:

    # spent 21.3s (0ns+21.3) within DBIx::Class::Storage::DBI::Cursor::_db +h_all which was called 5 times, avg 4.26s/call: # 5 times (0ns+21.3s) + by DBIx::Class::Storage::DBI::dbh_do at line 547 of C:/Perl/site/lib +/DBIx/Class/Storage/DBI.pm, avg 4.26s/call sub _dbh_all { 120 30 18.8s 628ms my ($storage, $dbh, $self) = @_; 121 122 $self->_check_dbh_gen; # spent 0s making 5 calls to DBIx::Class::Storage::DBI::Cursor::_check +_dbh_gen, avg 0s/call 123 $self->{sth}->finish if $self->{sth}->{Active}; 124 delete $self->{sth}; 125 my ($rv, $sth) = $storage->_select(@{$self->{args}} +); # spent 2.46s making 5 calls to DBIx::Class::Storage::DBI::_select, av +g 492ms/call 126 return @{$sth->fetchall_arrayref}; # spent 18.8s making 5 calls to DBI::st::fetchall_arrayref, avg 3.77s/ +call 127 }
Reaped: Re: DBIx startup slow
by NodeReaper (Curate) on Sep 06, 2011 at 14:11 UTC

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://924127]
Approved by davies
Front-paged by chrestomanci
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others having an uproarious good time at the Monastery: (5)
As of 2014-08-22 05:51 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The best computer themed movie is:











    Results (147 votes), past polls