Биллинговая система Nodeny

#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect(
	"DBI:mysql:host=localhost;database=bill",
	"root","passwd",
	{
		PrintError=>0,
		RaiseError=>0
	}
);
$dbh or die(DBI::errstr);
$dbh->do("SET NAMES utf8");
my $str = $dbh->selectall_hashref("SELECT _adr_telefon,street,name_street v FROM p_street",'street');
$str or die(DBI::errstr);
my $sth = $dbh->prepare("SELECT id,fio FROM users ORDER BY id");
$sth->execute or die($dbh->errstr);
while(my $row=$sth->fetchrow_hashref){
	my @user = ();
	push(@user,$row->{id},$row->{fio});
	my $adr = $dbh->selectall_hashref(
		"SELECT dv.revision,df.field_alias,dv.field_value v FROM dopvalues dv ".
		"INNER JOIN rev_users r ".
		"INNER JOIN dopfields df ".
		"ON r.rev=dv.revision AND r.id=dv.parent_id AND df.id=dv.dopfield_id ".
		"WHERE r.template_num=2 AND r.id=$row->{id}",
		'field_alias'
	);
	$adr && do{
		push(@user,
			$str->{$adr->{'_adr_telefon:p_street:street:name_street'}->{v}}->{v},
			$adr->{_adr_house}->{v},
			$adr->{_adr_block}->{v},
			$adr->{_adr_room}->{v},
		);
	};
	print join(';',@user).";\n";
}

my $str = $dbh->selectall_hashref("SELECT _adr_telefon,street,name_street v FROM p_street",'street');

$str->{$adr->{'_adr_telefon:p_street:street:name_street'}->{v}}->{v},

#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect(
	"DBI:mysql:host=localhost;database=bill",
	"root","passwd",
	{
		PrintError=>0,
		RaiseError=>0
	}
);
$dbh or die(DBI::errstr);
$dbh->do("SET NAMES utf8");
my $str = $dbh->selectall_hashref("SELECT street,name_street v FROM p_street",'street');
$str or die(DBI::errstr);
my $sth = $dbh->prepare(
        "SELECT u.id,u.fio,ifnull(g.grp_name,u.grp) grp FROM users u ".
        "LEFT JOIN user_grp g ON g.grp_id=u.grp ".
        "ORDER BY id"
);
$sth->execute or die($dbh->errstr);
while(my $row=$sth->fetchrow_hashref){
	my @user = ();
	push(@user,$row->{id},$row->{fio},$row->{grp_name});
	my $dd = $dbh->selectall_hashref(
		"SELECT dv.revision,df.field_alias,dv.field_value v FROM dopvalues dv ".
		"INNER JOIN rev_users r ".
		"INNER JOIN dopfields df ".
		"ON r.rev=dv.revision AND r.id=dv.parent_id AND df.id=dv.dopfield_id ".
		"WHERE r.template_num IN (1,2) AND r.id=$row->{id}",
		'field_alias'
	);
	$dd && do{
		push(@user,
			$str->{$dd->{'p_street:street:name_street'}->{v}}->{v},
			$dd->{_adr_house}->{v},
			$dd->{_adr_block}->{v},
			$dd->{_adr_room}->{v},
			$dd->{_adr_telefon}->{v},
			$dd->{_mac}->{v},
		);
	};
	print join(';',@user).";\n";
}

my $sth = $dbh->prepare("SELECT id,fio,grp FROM users ORDER BY id")

#!/usr/bin/perl

use DBI;

my $dbh = DBI->connect(
	"DBI:mysql:host=localhost;database=bill",
	"root","passwd",
	{
		PrintError=>0,
		RaiseError=>0
	}
);
$dbh or die(DBI::errstr);
$dbh->do("SET NAMES utf8");
my $str = $dbh->selectall_hashref("SELECT street,name_street v FROM p_street",'street');
$str or die(DBI::errstr);
my $sth = $dbh->prepare(
        "SELECT u.id,u.fio,ifnull(g.grp_name,u.grp) grp FROM users u ".
        "LEFT JOIN user_grp g ON g.grp_id=u.grp ".
        "ORDER BY id"
);
$sth->execute or die($dbh->errstr);
while(my $row=$sth->fetchrow_hashref){
	my @user = ();
	push(@user,$row->{id},$row->{fio},$row->{grp_name});
	my $dd = $dbh->selectall_hashref(
		"SELECT dv.revision,df.field_alias,dv.field_value v FROM dopvalues dv ".
		"INNER JOIN rev_users r ".
		"INNER JOIN dopfields df ".
		"ON r.rev=dv.revision AND r.id=dv.parent_id AND df.id=dv.dopfield_id ".
		"WHERE r.template_num IN (1,2) AND r.id=$row->{id}",
		'field_alias'
	);
	$dd && do{
		push(@user,
			$str->{$dd->{'p_street:street:name_street'}->{v}}->{v},
			$dd->{_adr_house}->{v},
			$dd->{_adr_block}->{v},
			$dd->{_adr_room}->{v},
			$dd->{_adr_telefon}->{v},
			$dd->{_mac}->{v},
		);
	};
	print join(';',@user).";\n";
}

mysql> SELECT dv.revision,df.field_alias,dv.field_value v FROM dopvalues dv
    -> INNER JOIN rev_users r
    -> INNER JOIN dopfields df
    -> ON r.rev=dv.revision AND r.id=dv.parent_id AND df.id=dv.dopfield_id
    -> WHERE r.template_num IN (1,2) AND r.id=991;
+----------+-----------------------------+-------------------+
| revision | field_alias                 | v                 |
+----------+-----------------------------+-------------------+
|    67898 | _mac                        | 00:E0:30:93:00:AE |
|    67898 | _no_statpw                  | 1                 |
|    67898 | _port                       |                   |
|    67898 | _vlan                       |                   |
|    67898 | _speed_in                   | 0                 |
|    67898 | _speed_out                  | 0                 |
|    67898 | _open_ports                 | 1                 |
|    67112 | p_street:street:name_street | 1                 |
|    67112 | _adr_house                  | 43                |
|    67112 | _adr_block                  |                   |
|    67112 | _adr_front_door             |                   |
|    67112 | _adr_floor                  |                   |
|    67112 | _adr_room                   |                   |
|    67112 | _adr_telefon                |                   |
|    67112 | _adr_mobile                 | 093_______        |
|    67112 | _adr_comment                |                   |
+----------+-----------------------------+-------------------+
16 rows in set (0.57 sec)

$ time ./slow.pl >data_slow.dat

real	20m42.488s
user	0m1.333s
sys	0m0.133s

$ time ./fast.pl >data_fast.dat 

real	0m1.029s
user	0m0.367s
sys	0m0.027s

$ diff data_slow.dat data_fast.dat

$ wc -l data_fast.dat 
3193 data_fast.dat