Thread Performanzproblem (DBIx::Class + mehrere Tabellen) (6 answers)
Opened by pktm at 2009-02-13 15:01

pktm
 2009-02-15 18:22
#118886 #118886
User since
2003-08-07
2921 Artikel
BenutzerIn
[Homepage]
user image
Sooooo! Danke für die Tipps! Die und ein paar Stunden im irc.perl.org#dbix-class habn nun folgendes zustande gebracht:
Code: (dl )
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
sub overview {
my $self = shift;

return $self->authz->forbidden() unless $self->authz('privileges')->authorize(__PACKAGE__, $self->get_current_runmode());

my $schema = $self->schema();
my @topic_loop = ();

my $trs = $schema->resultset('Topic')->search(
undef,
{
order_by => 'me.position ASC',
prefetch => [qw/boards/],
}
);
while( my $topic = $trs->next() ) {

my @board_loop = ();
my $board_rs = $topic->boards(
undef,
{
'+select' => [{count => 'thread_id'}],
'+as' => ['thread_count'],,
join => [qw/threads/],
group_by => [qw/board_id/],
},
);
while( my $board = $board_rs->next() ) {

# -- get the latest thread
my ($lastthread) = $board->threads(
undef,
{
order_by => 'date_of_creation DESC',
}
)->first();

my $lastpost = undef;
if( defined $lastthread ) {
($lastpost) = $lastthread->posts(
undef,
{
'+select' => [qw/user.username user.user_id/],
'+as' => [qw/username user_id/],
select => 'timestamp',
order_by => 'timestamp DESC',
join => [qw/user/],
}
)->first();
}

my $reply_count = 0;
if( defined $lastthread ) { # if there is a thread
my $rs = $board->threads(
undef,
{
'+select' => {count => 'posts.post_id'},
'+as' => 'reply_count',
join => [qw/posts/],
group_by => [qw/me.thread_id/],
}
);
foreach my $t ( $rs->all() ) {
$reply_count += $t->get_column('reply_count') - 1;
}
}

push @board_loop, {
board_id => $board->board_id(),
title => $board->title(),
thread_count => $board->get_column('thread_count'),
reply_count => $reply_count,

last_post_username => (defined $lastpost ? $lastpost->get_column('username') : undef),
last_post_timestamp => (defined $lastpost ? $lastpost->timestamp() : undef),
last_post_user_id => (defined $lastpost ? $lastpost->get_column('user_id') : undef),

last_thread_subject => (defined $lastthread ? $lastthread->subject() : undef),
last_thread_id => (defined $lastthread ? $lastthread->thread_id() : undef),
};
}

push @topic_loop, {
topic_id => $topic->topic_id(),
topic => $topic->topic(),
boards => \@board_loop,
};
}

$self->param(topics => \@topic_loop);

my $t = $self->load_tmpl('_overview.tmpl', associate => $self);
return $t->output();
} # /overview


Damit bin ich im normalen Betrieb (CGI) bei 1 wallclock secs ( 0.14 usr + 0.03 sys = 0.17 CPU). Sieht gleich viel besser aus :)
Mal schauen, ob ich die Antwortsumme pro Board noch ein Stück schneller berechnen kann.
http://www.intergastro-service.de (mein erstes CMS :) )

View full thread Performanzproblem (DBIx::Class + mehrere Tabellen)