%s

#!/usr/bin/perl use strict; use warnings; use utf8; use DBI; use Time::HiRes; use List::Util qw( sum ); use English qw( -no_match_vars ); for my $table ( qw( pages_1000 pages_10000 pages_100000 pages_1000000 pages_10000000 ) ) { # for my $table ( qw( pages_1000 pages_10000 ) ) { my @times = (); for my $i ( 1 .. 10 ) { my $start_time = Time::HiRes::time(); my $filename = export_data( $table ); my $runtime = Time::HiRes::time - $start_time; push @times, $runtime; unlink $filename if $i < 10; } my @sorted_times = sort { $a <=> $b } @times; # Average without extremes my $avg = sum( @sorted_times[ 2 .. 7 ] ) / 6; printf "%-15s : %5.3fs : %s\n", $table, $avg, join(", ", map { sprintf '%.3fs', $_ } @sorted_times ); } exit; sub export_data { my $table_name = shift; my $dbh = DBI->connect( 'dbi:Pg:dbname=depesz', undef, undef, { 'RaiseError' => 1, 'PrintError' => 1, 'AutoCommit' => 0, } ); my $output_file_name = $table_name . '.xml'; open my $fh, '>:encoding(UTF-8)', $output_file_name or die "Cannot write to $output_file_name: $OS_ERROR\n"; print $fh <<_END_OF_HEADER_; _END_OF_HEADER_ $dbh->do( 'SET TRANSACTION READ ONLY' ); $dbh->do( 'DECLARE export NO SCROLL CURSOR FOR SELECT id, title, body, extract(epoch from created) FROM ' . $table_name ); while ( 1 ) { my $rows = $dbh->selectall_arrayref( 'FETCH 100 FROM export' ); last if 0 == scalar @{ $rows }; for my $r ( @{ $rows } ) { printf $fh '%s', $r->[ 0 ], "\n"; printf $fh '%s%s', xml_escape( $r->[ 1 ] ), "\n"; printf $fh '%s%s', xml_escape( $r->[ 2 ] ), "\n"; printf $fh '%u%s', $r->[ 3 ], "\n"; printf $fh '%s', "\n"; } } $dbh->rollback(); $dbh->disconnect(); print $fh "\n"; close $fh; return $output_file_name; } sub xml_escape { my $what = shift; $what =~ s/&/\&/g; $what =~ s/"/\"/g; $what =~ s/'/\'/g; $what =~ s//\>/g; return $what; }