I did build two sql results files, one using JOIN and one without and runned
directly through mysql (cat sql | mysql -B DATABASE > /dev/null) and it
turns out that simple request are quite faster:
simple: 9s (took 1min40 through DBIC) (2s without the main query)
join : 30s (took 2min50 through DBIC) (23s without the main query)
So mysql IS slower when using joined query (but he is not responsible for
everything, since there is still 40s that are unaccounted for).
PS: As an exercice i timed a simple perl/DBI script, which prepared the
second
query which is runned 27k times using only execute => it took 37s
(compared to 23s).
Preparing each query and running with argument took 40s
Preparing each query with argument 'integrated' => 27s ....
Kinda funny
Matt S Trout a écrit :
On Thu, May 31, 2007 at 03:37:47PM +0200, Benoit Plessis wrote:
In fact i think it's the generation of the SQL query that take all the
time or it could be the prefetching.
It's unlikely to be on the DBIC side.
Try setting DBIC_TRACE=1 and running the query against your DB yourself.
MySQL is notoriously crap at handling prefetches across many rels.
Is it a way to prepare the resultset with all arguments except the
search condition value, and just run the query in each loop ?
Like the famous prepare / execute combo in DBI ?
DBIC effectively does that - for a given query it'll re-use the same
already-prepared $sth.
Don't try and micro-optimise your usage - somebody once built almost
exactly what you describe shortly before we implemented the current approach
and found it provided about a 2% speed improvement.
--
Benoit Plessis +33 4 67 36 42 59
<[EMAIL PROTECTED]>
begin:vcard
fn:Benoit Plessis
n:Plessis;Benoit
email;internet:[EMAIL PROTECTED]
tel;home:+33 9 52 49 25 06
tel;cell:+33 6 77 42 78 32
x-mozilla-html:FALSE
version:2.1
end:vcard
_______________________________________________
List: http://lists.rawmode.org/cgi-bin/mailman/listinfo/dbix-class
Wiki: http://dbix-class.shadowcatsystems.co.uk/
IRC: irc.perl.org#dbix-class
SVN: http://dev.catalyst.perl.org/repos/bast/trunk/DBIx-Class/
Searchable Archive: http://www.mail-archive.com/[email protected]/
