2
# Copyright (C) 2000-2003 MySQL AB
4
# This library is free software; you can redistribute it and/or
5
# modify it under the terms of the GNU Library General Public
6
# License as published by the Free Software Foundation; version 2
9
# This library is distributed in the hope that it will be useful,
10
# but WITHOUT ANY WARRANTY; without even the implied warranty of
11
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
12
# Library General Public License for more details.
14
# You should have received a copy of the GNU Library General Public
15
# License along with this library; if not, write to the Free
16
# Software Foundation, Inc., 59 Temple Place - Suite 330, Boston,
19
# Test of creating a simple table and inserting $record_count records in it,
20
# $opt_loop_count rows in order, $opt_loop_count rows in reverse order and
21
# $opt_loop_count rows in random order
23
# changes made for Oracle compatibility
24
# - $limits->{'func_odbc_mod'} is OK from crash-me, but it fails here so set we
25
# set it to 0 in server-cfg
26
# - the default server config runs out of rollback segments, so we added a
27
# couple of disconnect/connects to reset
29
##################### Standard benchmark inits ##############################
36
$opt_loop_count=100000; # number of rows/3
37
$small_loop_count=10; # Loop for full table retrieval
38
$range_loop_count=$small_loop_count*50;
39
$many_keys_loop_count=$opt_loop_count;
40
$opt_read_key_loop_count=$opt_loop_count;
42
$pwd = cwd(); $pwd = "." if ($pwd eq '');
43
require "$pwd/bench-init.pl" || die "Can't read Configuration file: $!\n";
48
$many_keys_loop_count=$opt_loop_count/10;
50
$opt_read_key_loop_count=10;
52
elsif ($opt_small_tables)
54
$opt_loop_count=10000; # number of rows/3
55
$many_keys_loop_count=$opt_loop_count;
56
$opt_read_key_loop_count=10;
58
elsif ($opt_small_key_tables)
60
$many_keys_loop_count/=10;
63
if ($opt_loop_count < 100)
65
$opt_loop_count=100; # Some tests must have some data to work!
67
$range_loop_count=min($opt_loop_count,$range_loop_count);
70
print "Testing the speed of inserting data into 1 table and do some selects on it.\n";
71
print "The tests are done with a table that has $opt_loop_count rows.\n\n";
74
#### Generating random keys
77
print "Generating random keys\n";
78
$random[$opt_loop_count]=0;
79
for ($i=0 ; $i < $opt_loop_count ; $i++)
81
$random[$i]=$i+$opt_loop_count;
85
for ($i=0 ; $i < $opt_loop_count ; $i++)
87
$tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
88
$swap=$tmpvar % $opt_loop_count;
89
$tmp=$random[$i]; $random[$i]=$random[$swap]; $random[$swap]=$tmp;
92
$total_rows=$opt_loop_count*3;
95
#### Connect and start timeing
97
$start_time=new Benchmark;
98
$dbh = $server->connect();
100
#### Create needed tables
103
goto keys_test if ($opt_stage == 2);
104
goto select_test if ($opt_skip_create);
106
print "Creating tables\n";
107
$dbh->do("drop table bench1" . $server->{'drop_attr'});
108
$dbh->do("drop table bench2" . $server->{'drop_attr'});
109
$dbh->do("drop table bench3" . $server->{'drop_attr'});
110
do_many($dbh,$server->create("bench1",
115
["primary key (id,id2)",
116
"index ix_id3 (id3)"]));
118
if ($opt_lock_tables)
120
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
124
#### Insert $total_rows records in order, in reverse order and random.
127
$loop_time=new Benchmark;
129
if ($opt_fast_insert)
131
$query="insert into bench1 values ";
135
$query="insert into bench1 (id,id2,id3,dummy1) values ";
138
if ($opt_fast && $server->{transactions})
140
$dbh->{AutoCommit} = 0;
141
print "Transactions enabled\n" if ($opt_debug);
144
if (($opt_fast || $opt_fast_insert) && $server->{'limits'}->{'insert_multi_value'})
146
$query_size=$server->{'limits'}->{'query_size'};
148
print "Inserting $opt_loop_count multiple-value rows in order\n";
150
for ($i=0 ; $i < $opt_loop_count ; $i++)
152
$tmp= "($i,$i,$i,'ABCDEFGHIJ'),";
153
if (length($tmp)+length($res) < $query_size)
159
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
163
print "Inserting $opt_loop_count multiple-value rows in reverse order\n";
164
for ($i=0 ; $i < $opt_loop_count ; $i++)
166
$tmp= "(" . ($total_rows-1-$i) . "," .($total_rows-1-$i) .
167
"," .($total_rows-1-$i) . ",'BCDEFGHIJK'),";
168
if (length($tmp)+length($res) < $query_size)
174
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
178
print "Inserting $opt_loop_count multiple-value rows in random order\n";
179
for ($i=0 ; $i < $opt_loop_count ; $i++)
181
$tmp= "(" . $random[$i] . "," . $random[$i] . "," . $random[$i] .
182
",'CDEFGHIJKL')," or die $DBI::errstr;
183
if (length($tmp)+length($res) < $query_size)
189
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
193
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
197
print "Inserting $opt_loop_count rows in order\n";
198
for ($i=0 ; $i < $opt_loop_count ; $i++)
200
$sth = $dbh->do($query . "($i,$i,$i,'ABCDEFGHIJ')") or die $DBI::errstr;
203
print "Inserting $opt_loop_count rows in reverse order\n";
204
for ($i=0 ; $i < $opt_loop_count ; $i++)
206
$sth = $dbh->do($query . "(" . ($total_rows-1-$i) . "," .
207
($total_rows-1-$i) . "," .
208
($total_rows-1-$i) . ",'BCDEFGHIJK')")
212
print "Inserting $opt_loop_count rows in random order\n";
214
for ($i=0 ; $i < $opt_loop_count ; $i++)
216
$sth = $dbh->do($query . "(". $random[$i] . "," . $random[$i] .
217
"," . $random[$i] . ",'CDEFGHIJKL')") or die $DBI::errstr;
221
if ($opt_fast && $server->{transactions})
224
$dbh->{AutoCommit} = 1;
227
$end_time=new Benchmark;
228
print "Time for insert (" . ($total_rows) . "): " .
229
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
231
if ($opt_lock_tables)
233
$sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
235
if ($opt_fast && defined($server->{vacuum}))
237
$server->vacuum(1,\$dbh,"bench1");
239
if ($opt_lock_tables)
241
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
245
#### insert $opt_loop_count records with duplicate id
248
if ($limits->{'unique_index'})
250
print "Testing insert of duplicates\n";
251
$loop_time=new Benchmark;
253
if ($opt_fast && $server->{transactions})
255
$dbh->{AutoCommit} = 0;
258
for ($i=0 ; $i < $opt_loop_count ; $i++)
260
$tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
261
$tmp=$tmpvar % ($total_rows);
262
$tmpquery = "$query ($tmp,$tmp,2,'D')";
263
if ($dbh->do($tmpquery))
265
die "Didn't get an error when inserting duplicate record $tmp\n";
268
if ($opt_fast && $server->{transactions})
271
$dbh->{AutoCommit} = 1;
274
$end_time=new Benchmark;
275
print "Time for insert_duplicates (" . ($opt_loop_count) . "): " .
276
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
281
#### Do some selects on the table
286
# ----------------- prepared+executed/prepared*executed tests
288
print "Test of prepared+execute/once prepared many execute selects\n";
289
$loop_time=new Benchmark;
291
for ($i=1 ; $i <= $opt_loop_count ; $i++)
293
my ($key_value)=$random[$i];
294
my ($query)= "select * from bench1 where id=$key_value";
295
print "$query\n" if ($opt_debug);
296
$sth = $dbh->prepare($query);
299
die "error in prepare select with id = $key_value : $DBI::errstr";
303
die "cannot execute prepare select with id = $key_value : $DBI::errstr";
305
while ($sth->fetchrow_arrayref) { };
308
$end_time=new Benchmark;
309
print "Time for prepared_select ($opt_loop_count): " .
310
timestr(timediff($end_time, $loop_time),"all") . "\n";
312
$loop_time=new Benchmark;
313
$query= "select * from bench1 where id=?";
314
$sth = $dbh->prepare($query);
317
die "cannot prepare select: $DBI::errstr";
320
for ($i=1 ; $i <= $opt_loop_count ; $i++)
322
my ($key_value)=$random[$i];
323
$sth->bind_param(1,$key_value);
324
print "$query , id = $key_value\n" if ($opt_debug);
327
die "cannot execute prepare select with id = $key_value : $DBI::errstr";
329
while ($sth->fetchrow_arrayref) { };
332
$end_time=new Benchmark;
333
print "Time for once_prepared_select ($opt_loop_count): " .
334
timestr(timediff($end_time, $loop_time),"all") . "\n";
337
print "Retrieving data from the table\n";
338
$loop_time=new Benchmark;
341
# It's really a small table, so we can try a select on everything
344
for ($i=1 ; $i <= $small_loop_count ; $i++)
346
if (($found_rows=fetch_all_rows($dbh,"select id from bench1")) !=
351
print "Warning: Got $found_rows rows when selecting a whole table of " . ($total_rows) . " rows\nContact the database or DBD author!\n";
357
$end_time=new Benchmark;
358
print "Time for select_big ($small_loop_count:$count): " .
359
timestr(timediff($end_time, $loop_time),"all") . "\n";
362
# Do a lot of different ORDER BY queries
365
$loop_time=new Benchmark;
367
for ($i=1 ; $i <= $small_loop_count ; $i++)
369
$rows+=fetch_all_rows($dbh,"select id,id2 from bench1 order by id,id2",1);
370
$end_time=new Benchmark;
371
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
375
{ print "Estimated time"; }
378
print " for order_by_big_key ($small_loop_count:$rows): " .
379
timestr(timediff($end_time, $loop_time),"all") . "\n";
382
$loop_time=new Benchmark;
384
for ($i=1 ; $i <= $small_loop_count ; $i++)
386
$rows+=fetch_all_rows($dbh,"select id,id2 from bench1 order by id desc, id2 desc",1);
387
$end_time=new Benchmark;
388
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
392
{ print "Estimated time"; }
395
print " for order_by_big_key_desc ($small_loop_count:$rows): " .
396
timestr(timediff($end_time, $loop_time),"all") . "\n";
399
$loop_time=new Benchmark;
401
for ($i=1 ; $i <= $small_loop_count ; $i++)
403
$rows+=fetch_all_rows($dbh,"select id from bench1 order by id desc",1);
404
$end_time=new Benchmark;
405
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
409
{ print "Estimated time"; }
412
print " for order_by_big_key_prefix ($small_loop_count:$rows): " .
413
timestr(timediff($end_time, $loop_time),"all") . "\n";
416
$loop_time=new Benchmark;
418
for ($i=1 ; $i <= $small_loop_count ; $i++)
420
$rows+=fetch_all_rows($dbh,"select id3 from bench1 order by id3",1);
421
$end_time=new Benchmark;
422
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
426
{ print "Estimated time"; }
429
print " for order_by_big_key2 ($small_loop_count:$rows): " .
430
timestr(timediff($end_time, $loop_time),"all") . "\n";
433
$sel=$limits->{'order_by_unused'} ? "id2" : "*";
434
$loop_time=new Benchmark;
436
for ($i=1 ; $i <= $small_loop_count ; $i++)
438
$rows+=fetch_all_rows($dbh,"select $sel from bench1 order by id3",1);
439
$end_time=new Benchmark;
440
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
444
{ print "Estimated time"; }
447
print " for order_by_big_key_diff ($small_loop_count:$rows): " .
448
timestr(timediff($end_time, $loop_time),"all") . "\n";
451
$sel=$limits->{'order_by_unused'} ? "id" : "*";
452
$loop_time=new Benchmark;
454
for ($i=1 ; $i <= $small_loop_count ; $i++)
456
$rows+=fetch_all_rows($dbh,"select $sel from bench1 order by id2,id3",1);
457
$end_time=new Benchmark;
458
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
462
{ print "Estimated time"; }
465
print " for order_by_big ($small_loop_count:$rows): " .
466
timestr(timediff($end_time, $loop_time),"all") . "\n";
469
$sel=$limits->{'order_by_unused'} ? "dummy1" : "dummy1,id3";
470
$loop_time=new Benchmark;
472
for ($i=1 ; $i <= $range_loop_count ; $i++)
474
$start=$opt_loop_count/$range_loop_count*$i;
476
$rows+=fetch_all_rows($dbh,"select $sel from bench1 where id>=$start and id <= $end order by id3",1);
477
$end_time=new Benchmark;
478
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
482
{ print "Estimated time"; }
485
print " for order_by_range ($range_loop_count:$rows): " .
486
timestr(timediff($end_time, $loop_time),"all") . "\n";
488
$sel=$limits->{'order_by_unused'} ? "dummy1" : "dummy1,id";
489
$loop_time=new Benchmark;
491
for ($i=1 ; $i <= $range_loop_count ; $i++)
493
$start=$opt_loop_count/$range_loop_count*$i;
495
$rows+=fetch_all_rows($dbh,"select $sel from bench1 where id>=$start and id <= $end order by id",1);
496
$end_time=new Benchmark;
497
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
501
{ print "Estimated time"; }
504
print " for order_by_key_prefix ($range_loop_count:$rows): " .
505
timestr(timediff($end_time, $loop_time),"all") . "\n";
507
$sel=$limits->{'order_by_unused'} ? "id2" : "id2,id3";
508
$loop_time=new Benchmark;
510
for ($i=1 ; $i <= $range_loop_count ; $i++)
512
$start=$opt_loop_count/$range_loop_count*$i;
513
$end=$start+$range_loop_count;
514
$rows+=fetch_all_rows($dbh,"select $sel from bench1 where id3>=$start and id3 <= $end order by id3",1);
515
$end_time=new Benchmark;
516
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,$i,
520
{ print "Estimated time"; }
523
print " for order_by_key2_diff ($range_loop_count:$rows): " .
524
timestr(timediff($end_time, $loop_time),"all") . "\n";
527
# Test of select on 2 different keys with or
528
# (In this case database can only use keys if they do an automatic union).
531
$loop_time=new Benchmark;
535
for ($i=1 ; $i <= $range_loop_count ; $i++)
538
my $rnd2=$random[$i];
539
$rows+=fetch_all_rows($dbh,"select id2 from bench1 where id=$rnd or id3=$rnd2",1);
541
$end_time=new Benchmark;
542
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$count,
546
{ print "Estimated time"; }
549
print " for select_diff_key ($count:$rows): " .
550
timestr(timediff($end_time, $loop_time),"all") . "\n";
553
# Test select that is very popular when using ODBC
555
check_or_range("id","select_range_prefix");
556
check_or_range("id3","select_range_key2");
558
# Check reading on direct key on id and id3
560
check_select_key("*","id","select_key_prefix");
561
check_select_key2("*","id","id2","select_key");
562
check_select_key2("id,id2","id","id2","select_key_return_key");
563
check_select_key("*","id3","select_key2");
564
check_select_key("id3","id3","select_key2_return_key");
565
check_select_key("id,id2","id3","select_key2_return_prim");
568
#### A lot of simple selects on ranges
571
@Q=("select * from bench1 where !id!=3 or !id!=2 or !id!=1 or !id!=4 or !id!=16 or !id!=10",
573
"select * from bench1 where !id!>=" . ($total_rows-1) ." or !id!<1",
575
"select * from bench1 where !id!>=1 and !id!<=2",
577
"select * from bench1 where (!id!>=1 and !id!<=2) or (!id!>=1 and !id!<=2)",
579
"select * from bench1 where !id!>=1 and !id!<=10 and !id!<=5",
581
"select * from bench1 where (!id!>0 and !id!<2) or !id!>=" . ($total_rows-1),
583
"select * from bench1 where (!id!>0 and !id!<2) or (!id!>= " . ($opt_loop_count/2) . " and !id! <= " . ($opt_loop_count/2+2) . ") or !id! = " . ($opt_loop_count/2-1),
585
"select * from bench1 where (!id!>=5 and !id!<=10) or (!id!>=1 and !id!<=4)",
587
"select * from bench1 where (!id!=1 or !id!=2) and (!id!=3 or !id!=4)",
589
"select * from bench1 where (!id!=1 or !id!=2) and (!id!=2 or !id!=3)",
591
"select * from bench1 where (!id!=1 or !id!=5 or !id!=20 or !id!=40) and (!id!=1 or !id!>=20 or !id!=4)",
593
"select * from bench1 where ((!id!=1 or !id!=3) or (!id!>1 and !id!<3)) and !id!<=2",
595
"select * from bench1 where (!id! >= 0 and !id! < 4) or (!id! >=4 and !id! < 6)",
597
"select * from bench1 where !id! <= -1 or (!id! >= 0 and !id! <= 5) or (!id! >=4 and !id! < 6) or (!id! >=6 and !id! <=7) or (!id!>7 and !id! <= 8)",
599
"select * from bench1 where (!id!>=1 and !id!<=2 or !id!>=4 and !id!<=5) or (!id!>=0 and !id! <=10)",
601
"select * from bench1 where (!id!>=1 and !id!<=2 or !id!>=4 and !id!<=5) or (!id!>2 and !id! <=10)",
603
"select * from bench1 where (!id!>1 or !id! <1) and !id!<=2",
605
"select * from bench1 where !id! <= 2 and (!id!>1 or !id! <=1)",
607
"select * from bench1 where (!id!>=1 or !id! <1) and !id!<=2",
609
"select * from bench1 where (!id!>=1 or !id! <=2) and !id!<=2",
613
print "\nTest of compares with simple ranges\n";
614
check_select_range("id","select_range_prefix");
615
check_select_range("id3","select_range_key2");
618
#### Some group queries
621
if ($limits->{'group_functions'})
623
$loop_time=new Benchmark;
627
for ($tests=0 ; $tests < $small_loop_count ; $tests++)
629
$sth=$dbh->prepare($query="select count(*) from bench1") or die $DBI::errstr;
630
$sth->execute or die $sth->errstr;
631
if (($sth->fetchrow_array)[0] != $total_rows)
633
print "Warning: '$query' returned wrong result\n";
637
# min, max in keys are very normal
639
fetch_all_rows($dbh,"select min(id) from bench1");
640
fetch_all_rows($dbh,"select max(id) from bench1");
641
fetch_all_rows($dbh,"select sum(id+0.0) from bench1");
642
fetch_all_rows($dbh,"select min(id3),max(id3),sum(id3-0.0) from bench1");
643
if ($limits->{'group_func_sql_min_str'})
645
fetch_all_rows($dbh,"select min(dummy1),max(dummy1) from bench1");
648
$sth=$dbh->prepare($query="select count(*) from bench1 where id >= " .
649
($opt_loop_count*2)) or die $DBI::errstr;
650
$sth->execute or die $DBI::errstr;
651
if (($sth->fetchrow_array)[0] != $opt_loop_count)
653
print "Warning: '$query' returned wrong result\n";
658
$sth=$dbh->prepare($query="select count(*),sum(id+0.0),min(id),max(id),avg(id-0.0) from bench1") or die $DBI::errstr;
659
$sth->execute or die $DBI::errstr;
660
@row=$sth->fetchrow_array;
661
if ($row[0] != $total_rows ||
662
int($row[1]+0.5) != int((($total_rows-1)/2*$total_rows)+0.5) ||
664
$row[3] != $total_rows-1 ||
665
1-$row[4]/(($total_rows-1)/2) > 0.001)
667
# PostgreSQL 6.3 fails here
668
print "Warning: '$query' returned wrong result: @row\n";
672
if ($limits->{'func_odbc_mod'})
675
if ($limits->{'func_extra_%'})
677
$tmp="id % 10"; # For postgreSQL
680
if ($limits->{'group_by_alias'}) {
681
if (fetch_all_rows($dbh,$query=$server->query("select $tmp as last_digit,count(*) from bench1 group by last_digit")) != 10)
683
print "Warning: '$query' returned wrong number of rows\n";
685
} elsif ($limits->{'group_by_position'}) {
686
if (fetch_all_rows($dbh,$query=$server->query("select $tmp,count(*) from bench1 group by 1")) != 10)
688
print "Warning: '$query' returned wrong number of rows\n";
693
if ($limits->{'order_by_position'} && $limits->{'group_by_position'})
696
if (fetch_all_rows($dbh, $query="select id,id3,dummy1 from bench1 where id < 100+$count-$count group by id,id3,dummy1 order by id desc,id3,dummy1") != 100)
698
print "Warning: '$query' returned wrong number of rows\n";
701
$end_time=new Benchmark;
702
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests,
705
print_time($estimated);
706
print " for select_group ($count): " .
707
timestr(timediff($end_time, $loop_time),"all") . "\n";
709
$loop_time=new Benchmark;
711
for ($tests=1 ; $tests <= $range_loop_count*5 ; $tests++)
714
fetch_all_rows($dbh,"select min(id) from bench1");
715
fetch_all_rows($dbh,"select max(id) from bench1");
716
fetch_all_rows($dbh,"select min(id2) from bench1 where id=$tests");
717
fetch_all_rows($dbh,"select max(id2) from bench1 where id=$tests");
718
if ($limits->{'group_func_sql_min_str'})
720
fetch_all_rows($dbh,"select min(dummy1) from bench1 where id=$tests");
721
fetch_all_rows($dbh,"select max(dummy1) from bench1 where id=$tests");
723
$end_time=new Benchmark;
724
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests,
725
$range_loop_count*5));
728
{ print "Estimated time"; }
731
print " for min_max_on_key ($count): " .
732
timestr(timediff($end_time, $loop_time),"all") . "\n";
734
$loop_time=new Benchmark;
736
for ($tests=1 ; $tests <= $small_loop_count ; $tests++)
739
fetch_all_rows($dbh,"select min(id2) from bench1");
740
fetch_all_rows($dbh,"select max(id2) from bench1");
741
fetch_all_rows($dbh,"select min(id3) from bench1 where id2=$tests");
742
fetch_all_rows($dbh,"select max(id3) from bench1 where id2=$tests");
743
if ($limits->{'group_func_sql_min_str'})
745
fetch_all_rows($dbh,"select min(dummy1) from bench1 where id2=$tests");
746
fetch_all_rows($dbh,"select max(dummy1) from bench1 where id2=$tests");
748
$end_time=new Benchmark;
749
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests,
753
{ print "Estimated time"; }
756
print " for min_max ($count): " .
757
timestr(timediff($end_time, $loop_time),"all") . "\n";
759
$loop_time=new Benchmark;
761
$total=$opt_loop_count*3;
762
for ($tests=0 ; $tests < $total ; $tests+=$total/100)
765
fetch_all_rows($dbh,"select count(id) from bench1 where id < $tests");
767
$end_time=new Benchmark;
768
print "Time for count_on_key ($count): " .
769
timestr(timediff($end_time, $loop_time),"all") . "\n";
771
$loop_time=new Benchmark;
773
for ($tests=0 ; $tests < $total ; $tests+=$total/100)
776
fetch_all_rows($dbh,"select count(dummy1) from bench1 where id2 < $tests");
778
$end_time=new Benchmark;
779
print "Time for count ($count): " .
780
timestr(timediff($end_time, $loop_time),"all") . "\n";
782
if ($limits->{'group_distinct_functions'})
784
$loop_time=new Benchmark;
786
for ($tests=1 ; $tests <= $small_loop_count ; $tests++)
789
fetch_all_rows($dbh,"select count(distinct dummy1) from bench1");
790
fetch_all_rows($dbh,"select dummy1,count(distinct id) from bench1 group by dummy1");
791
$end_time=new Benchmark;
792
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$tests,
796
{ print "Estimated time"; }
799
print " for count_distinct_big ($count): " .
800
timestr(timediff($end_time, $loop_time),"all") . "\n";
805
if ($server->small_rollback_segment())
807
$dbh->disconnect; # close connection
808
$dbh = $server->connect();
812
#### Some updates on the table
815
$loop_time=new Benchmark;
817
if ($limits->{'functions'})
819
print "\nTesting update of keys with functions\n";
820
my $update_loop_count=$opt_loop_count/2;
821
for ($i=0 ; $i < $update_loop_count ; $i++)
823
my $tmp=$opt_loop_count+$random[$i]; # $opt_loop_count*2 <= $tmp < $total_rows
824
$sth = $dbh->do("update bench1 set id3=-$tmp where id3=$tmp") or die $DBI::errstr;
827
$end_time=new Benchmark;
828
print "Time for update_of_key ($update_loop_count): " .
829
timestr(timediff($end_time, $loop_time),"all") . "\n";
831
if ($opt_lock_tables)
833
do_query($dbh,"UNLOCK TABLES");
835
if ($opt_fast && defined($server->{vacuum}))
837
$server->vacuum(1,\$dbh,"bench1");
839
if ($opt_lock_tables)
841
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
844
if ($server->small_rollback_segment())
846
$dbh->disconnect; # close connection
847
$dbh = $server->connect();
850
$loop_time=new Benchmark;
852
$step=int($opt_loop_count/$range_loop_count+1);
853
for ($i= 0 ; $i < $opt_loop_count ; $i+= $step)
856
$sth=$dbh->do("update bench1 set id3= 0-id3 where id3 >= 0 and id3 <= $i") or die $DBI::errstr;
859
if ($server->small_rollback_segment())
861
$dbh->disconnect; # close connection
862
$dbh = $server->connect();
865
$sth=$dbh->do("update bench1 set id3= 0-id3 where id3 >= 0 and id3 < $opt_loop_count") or die $DBI::errstr;
867
if ($server->small_rollback_segment())
869
$dbh->disconnect; # close connection
870
$dbh = $server->connect();
873
$sth=$dbh->do("update bench1 set id3= 0-id3 where id3 >= $opt_loop_count and id3 < ". ($opt_loop_count*2)) or die $DBI::errstr;
876
# Check that everything was updated
877
# In principle we shouldn't time this in the update loop..
880
if ($server->small_rollback_segment())
882
$dbh->disconnect; # close connection
883
$dbh = $server->connect();
886
if (($sth=$dbh->prepare("select count(*) from bench1 where id3>=0"))
889
($row_count)=$sth->fetchrow;
891
$result=1 + $opt_loop_count-$update_loop_count;
892
if ($row_count != $result)
894
print "Warning: Update check returned $row_count instead of $result\n";
898
if ($server->small_rollback_segment())
900
$dbh->disconnect; # close connection
901
$dbh = $server->connect();
903
#restore id3 to 0 <= id3 < $total_rows/10 or 0<= id3 < $total_rows
905
my $func=($limits->{'func_odbc_floor'}) ? "floor((0-id3)/20)" : "0-id3";
907
$sth=$dbh->do($query="update bench1 set id3=$func where id3<0") or die $DBI::errstr;
909
$end_time=new Benchmark;
910
print "Time for update_of_key_big ($count): " .
911
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
915
print "\nTesting update of keys in loops\n";
917
# This is for mSQL that doesn't have functions. Do we really need this ????
920
$sth=$dbh->prepare("select id3 from bench1 where id3 >= 0") or die $DBI::errstr;
921
$sth->execute or die $DBI::errstr;
923
while (@tmp = $sth->fetchrow_array)
925
my $tmp1 = "-$tmp[0]";
926
my $sth1 = $dbh->do("update bench1 set id3 = $tmp1 where id3 = $tmp[0]");
928
$end_time=new Benchmark;
929
if (($end_time->[0] - $loop_time->[0]) > $opt_time_limit)
931
print "note: Aborting update loop because of timeout\n";
936
# Check that everything except id3=0 was updated
937
# In principle we shouldn't time this in the update loop..
939
if (fetch_all_rows($dbh,$query="select * from bench1 where id3>=0") != 1)
941
if ($count == $total_rows)
943
print "Warning: Wrong information after update: Found '$row_count' rows, but should have been: 1\n";
946
#restore id3 to 0 <= id3 < $total_rows
947
$sth=$dbh->prepare("select id3 from bench1 where id3 < 0") or die $DBI::errstr;
948
$sth->execute or die $DBI::errstr;
949
while (@tmp = $sth->fetchrow_array)
952
my $tmp1 = floor((0-$tmp[0])/10);
953
my $sth1 = $dbh->do("update bench1 set id3 = $tmp1 where id3 = $tmp[0]");
956
$end_time=new Benchmark;
957
$estimated=predict_query_time($loop_time,$end_time,\$count,$count,
960
{ print "Estimated time"; }
963
print " for update_of_key ($count): " .
964
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
967
if ($opt_fast && defined($server->{vacuum}))
969
if ($opt_lock_tables)
971
do_query($dbh,"UNLOCK TABLES");
973
$server->vacuum(1,\$dbh,"bench1");
974
if ($opt_lock_tables)
976
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
981
# Testing some simple updates
984
print "Testing update with key\n";
985
$loop_time=new Benchmark;
986
for ($i=0 ; $i < $opt_loop_count*3 ; $i++)
988
$sth = $dbh->do("update bench1 set dummy1='updated' where id=$i and id2=$i") or die $DBI::errstr;
991
$end_time=new Benchmark;
992
print "Time for update_with_key (" . ($opt_loop_count*3) . "): " .
993
timestr(timediff($end_time, $loop_time),"all") . "\n";
995
$loop_time=new Benchmark;
997
for ($i=1 ; $i < $opt_loop_count*3 ; $i+=3)
999
$sth = $dbh->do("update bench1 set dummy1='updated' where id=$i") or die $DBI::errstr;
1000
$end_time=new Benchmark;
1001
last if ($estimated=predict_query_time($loop_time,$end_time,\$i,($i-1)/3,
1005
{ print "Estimated time"; }
1008
print " for update_with_key_prefix (" . ($opt_loop_count) . "): " .
1009
timestr(timediff($end_time, $loop_time),"all") . "\n";
1011
print "\nTesting update of all rows\n";
1012
$loop_time=new Benchmark;
1013
for ($i=0 ; $i < $small_loop_count ; $i++)
1015
$sth = $dbh->do("update bench1 set dummy1='updated $i'") or die $DBI::errstr;
1017
$end_time=new Benchmark;
1018
print "Time for update_big ($small_loop_count): " .
1019
timestr(timediff($end_time, $loop_time),"all") . "\n";
1023
# Testing left outer join
1026
if ($limits->{'func_odbc_floor'} && $limits->{'left_outer_join'})
1028
if ($opt_lock_tables)
1030
$sth = $dbh->do("LOCK TABLES bench1 a READ, bench1 b READ") || die $DBI::errstr;
1032
print "\nTesting left outer join\n";
1033
$loop_time=new Benchmark;
1035
for ($i=0 ; $i < $small_loop_count ; $i++)
1037
$count+=fetch_all_rows($dbh,"select count(*) from bench1 as a left outer join bench1 as b on (a.id2=b.id3)");
1039
$end_time=new Benchmark;
1040
print "Time for outer_join_on_key ($small_loop_count:$count): " .
1041
timestr(timediff($end_time, $loop_time),"all") . "\n";
1043
$loop_time=new Benchmark;
1045
for ($i=0 ; $i < $small_loop_count ; $i++)
1047
$count+=fetch_all_rows($dbh,"select count(a.dummy1),count(b.dummy1) from bench1 as a left outer join bench1 as b on (a.id2=b.id3)");
1049
$end_time=new Benchmark;
1050
print "Time for outer_join ($small_loop_count:$count): " .
1051
timestr(timediff($end_time, $loop_time),"all") . "\n";
1054
$loop_time=new Benchmark;
1055
for ($i=0 ; $i < $small_loop_count ; $i++)
1057
$count+=fetch_all_rows($dbh,"select count(a.dummy1),count(b.dummy1) from bench1 as a left outer join bench1 as b on (a.id2=b.id3) where b.id3 is not null");
1059
$end_time=new Benchmark;
1060
print "Time for outer_join_found ($small_loop_count:$count): " .
1061
timestr(timediff($end_time, $loop_time),"all") . "\n";
1063
$count=$estimated=0;
1064
$loop_time=new Benchmark;
1065
for ($i=1 ; $i <= $small_loop_count ; $i++)
1067
$count+=fetch_all_rows($dbh,"select count(a.dummy1),count(b.dummy1) from bench1 as a left outer join bench1 as b on (a.id2=b.id3) where b.id3 is null");
1068
$end_time=new Benchmark;
1069
last if ($estimated=predict_query_time($loop_time,$end_time,
1071
$range_loop_count));
1074
{ print "Estimated time"; }
1077
print " for outer_join_not_found ($range_loop_count:$count): " .
1078
timestr(timediff($end_time, $loop_time),"all") . "\n";
1080
if ($opt_lock_tables)
1082
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
1086
if ($server->small_rollback_segment())
1088
$dbh->disconnect; # close connection
1089
$dbh = $server->connect();
1093
### Test speed of IN( value list)
1096
if ($limits->{'left_outer_join'})
1098
if ($opt_lock_tables)
1100
$sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
1103
do_many($dbh,$server->create("bench2",
1104
["id int NOT NULL"],
1105
["primary key (id)"]));
1107
$max_tests=min(($limits->{'query_size'}-50)/6, $opt_loop_count);
1109
if ($opt_lock_tables)
1111
$sth = $dbh->do("LOCK TABLES bench1 READ, bench2 WRITE") ||
1114
test_where_in("bench1","bench2","id",1,10);
1115
test_where_in("bench1","bench2","id",11,min(100,$max_tests));
1116
test_where_in("bench1","bench2","id",101,min(1000,$max_tests));
1117
if ($opt_lock_tables)
1119
$sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
1121
$sth = $dbh->do("DROP TABLE bench2" . $server->{'drop_attr'}) ||
1123
if ($opt_lock_tables)
1125
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
1130
#### Test INSERT INTO ... SELECT
1133
if ($limits->{'insert_select'})
1135
if ($opt_lock_tables)
1137
$sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
1139
print "\nTesting INSERT INTO ... SELECT\n";
1140
do_many($dbh,$server->create("bench2",
1145
["primary key (id,id2)"]));
1146
do_many($dbh,$server->create("bench3",
1151
["primary key (id,id2)",
1152
"index index_id3 (id3)"]));
1153
$loop_time=new Benchmark;
1154
$sth = $dbh->do("INSERT INTO bench2 SELECT * from bench1") ||
1156
$end_time=new Benchmark;
1157
print "Time for insert_select_1_key (1): " .
1158
timestr(timediff($end_time, $loop_time),"all") . "\n";
1159
$loop_time=new Benchmark;
1160
$sth = $dbh->do("INSERT INTO bench3 SELECT * from bench1") ||
1162
$end_time=new Benchmark;
1163
print "Time for insert_select_2_keys (1): " .
1164
timestr(timediff($end_time, $loop_time),"all") . "\n";
1165
$loop_time=new Benchmark;
1166
$sth = $dbh->do("DROP TABLE bench2" . $server->{'drop_attr'}) ||
1168
$sth = $dbh->do("DROP TABLE bench3" . $server->{'drop_attr'}) ||
1170
$end_time=new Benchmark;
1171
print "Time for drop table(2): " .
1172
timestr(timediff($end_time, $loop_time),"all") . "\n";
1174
if ($opt_fast && defined($server->{vacuum}))
1176
$server->vacuum(1,\$dbh);
1178
if ($server->small_rollback_segment())
1180
$dbh->disconnect; # close connection
1181
$dbh = $server->connect();
1183
if ($opt_lock_tables)
1185
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
1190
#### Do some deletes on the table
1193
if (!$opt_skip_delete)
1195
print "\nTesting delete\n";
1196
$loop_time=new Benchmark;
1198
for ($i=0 ; $i < $opt_loop_count ; $i+=10)
1201
$tmp=$opt_loop_count+$random[$i]; # $opt_loop_count*2 <= $tmp < $total_rows
1202
$dbh->do("delete from bench1 where id3=$tmp") or die $DBI::errstr;
1205
$end_time=new Benchmark;
1206
print "Time for delete_key ($count): " .
1207
timestr(timediff($end_time, $loop_time),"all") . "\n";
1209
if ($server->small_rollback_segment())
1211
$dbh->disconnect; # close connection
1212
$dbh = $server->connect();
1216
$loop_time=new Benchmark;
1217
for ($i= 0 ; $i < $opt_loop_count ; $i+=$opt_loop_count/10)
1219
$sth=$dbh->do("delete from bench1 where id3 >= 0 and id3 <= $i") or die $DBI::errstr;
1223
if ($server->small_rollback_segment())
1225
$dbh->disconnect; # close connection
1226
$dbh = $server->connect();
1228
$sth=$dbh->do("delete from bench1 where id3 >= 0 and id3 <= $opt_loop_count") or die $DBI::errstr;
1229
if ($server->small_rollback_segment())
1231
$dbh->disconnect; # close connection
1232
$dbh = $server->connect();
1235
$sth=$dbh->do("delete from bench1 where id >= $opt_loop_count and id <= " . ($opt_loop_count*2) ) or die $DBI::errstr;
1237
if ($server->small_rollback_segment())
1239
$dbh->disconnect; # close connection
1240
$dbh = $server->connect();
1244
$sth=$dbh->do("delete from bench1") or die $DBI::errstr;
1248
$sth = $dbh->do("delete from bench1 where id3 < " . ($total_rows)) or die $DBI::errstr;
1251
$end_time=new Benchmark;
1252
print "Time for delete_range ($count): " .
1253
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
1255
if ($opt_lock_tables)
1257
$sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
1259
$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr;
1262
if ($server->small_rollback_segment())
1264
$dbh->disconnect; # close connection
1265
$dbh = $server->connect();
1267
if ($opt_fast && defined($server->{vacuum}))
1269
$server->vacuum(1,\$dbh);
1275
# Test of insert in table with many keys
1276
# This test assumes that the server really create the keys!
1279
my @fields=(); my @keys=();
1280
$keys=min($limits->{'max_index'},16); # 16 is more than enough
1281
$seg= min($limits->{'max_index_parts'},$keys,16); # 16 is more than enough
1283
print "Insert into table with $keys keys and with a primary key with $seg parts\n";
1285
# Make keys on the most important types
1286
@types=(0,0,0,1,0,0,0,1,1,1,1,1,1,1,1,1,1); # A 1 for each char field
1287
push(@fields,"field1 tinyint not null");
1288
push(@fields,"field_search tinyint not null");
1289
push(@fields,"field2 mediumint not null");
1290
push(@fields,"field3 smallint not null");
1291
push(@fields,"field4 char(16) not null");
1292
push(@fields,"field5 integer not null");
1293
push(@fields,"field6 float not null");
1294
push(@fields,"field7 double not null");
1295
for ($i=8 ; $i <= $keys ; $i++)
1297
push(@fields,"field$i char(6) not null"); # Should be relatively fair
1300
# First key contains many segments
1301
$query="primary key (";
1302
for ($i= 1 ; $i <= $seg ; $i++)
1304
$query.= "field$i,";
1306
substr($query,-1)=")";
1307
push (@keys,$query);
1308
push (@keys,"index index2 (field_search)");
1311
for ($i=3 ; $i <= $keys ; $i++)
1313
push(@keys,"index index$i (field$i)");
1316
do_many($dbh,$server->create("bench1",\@fields,\@keys));
1317
if ($opt_lock_tables)
1319
$dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
1322
if ($server->small_rollback_segment())
1324
$dbh->disconnect; # close connection
1325
$dbh = $server->connect();
1328
$loop_time=new Benchmark;
1329
if ($opt_fast && $server->{transactions})
1331
$dbh->{AutoCommit} = 0;
1335
if (($opt_fast || $opt_fast_insert) && $server->{'limits'}->{'insert_multi_value'})
1337
$query_size=$server->{'limits'}->{'query_size'};
1338
$query="insert into bench1 values ";
1340
for ($i=0; $i < $many_keys_loop_count; $i++)
1344
$tmp="($id,$id,$rand," . ($i & 32766) . ",'ABCDEF$rand',0,$rand,$rand.0,";
1346
for ($j=8; $j <= $fields ; $j++)
1348
$tmp.= ($types[$j] == 0) ? "$rand," : "'$rand',";
1350
substr($tmp,-1)=")";
1351
if (length($tmp)+length($res) < $query_size)
1357
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
1358
$res=$query . $tmp . ",";
1361
$sth = $dbh->do(substr($res,0,length($res)-1)) or die $DBI::errstr;
1365
for ($i=0; $i < $many_keys_loop_count; $i++)
1369
$query="insert into bench1 values ($id,$id,$rand," . ($i & 32767) .
1370
",'ABCDEF$rand',0,$rand,$rand.0,";
1372
for ($j=8; $j <= $fields ; $j++)
1374
$query.= ($types[$j] == 0) ? "$rand," : "'$rand',";
1376
substr($query,-1)=")";
1377
print "query1: $query\n" if ($opt_debug);
1378
$dbh->do($query) or die "Got error $DBI::errstr with query: $query\n";
1382
if ($opt_fast && $server->{transactions})
1385
$dbh->{AutoCommit} = 1;
1388
$end_time=new Benchmark;
1389
print "Time for insert_key ($many_keys_loop_count): " .
1390
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
1392
if ($server->small_rollback_segment())
1394
$dbh->disconnect; # close connection
1395
$dbh = $server->connect();
1397
if ($opt_fast && defined($server->{vacuum}))
1399
if ($opt_lock_tables)
1401
do_query($dbh,"UNLOCK TABLES");
1403
$server->vacuum(1,\$dbh,"bench1");
1404
if ($opt_lock_tables)
1406
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
1411
# update one key of the above
1414
print "Testing update of keys\n";
1415
$loop_time=new Benchmark;
1417
if ($opt_fast && $server->{transactions})
1419
$dbh->{AutoCommit} = 0;
1422
for ($i=0 ; $i< 256; $i++)
1424
$dbh->do("update bench1 set field5=1 where field_search=$i")
1425
or die "Got error $DBI::errstr with query: update bench1 set field5=1 where field_search=$i\n";
1428
if ($opt_fast && $server->{transactions})
1431
$dbh->{AutoCommit} = 1;
1434
$end_time=new Benchmark;
1435
print "Time for update_of_primary_key_many_keys (256): " .
1436
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
1438
if ($server->small_rollback_segment())
1440
$dbh->disconnect; # close connection
1441
$dbh = $server->connect();
1443
if ($opt_fast && defined($server->{vacuum}))
1445
if ($opt_lock_tables)
1447
do_query($dbh,"UNLOCK TABLES");
1449
$server->vacuum(1,\$dbh,"bench1");
1450
if ($opt_lock_tables)
1452
$sth = $dbh->do("LOCK TABLES bench1 WRITE") || die $DBI::errstr;
1456
if ($server->small_rollback_segment())
1458
$dbh->disconnect; # close connection
1459
$dbh = $server->connect();
1463
# Delete everything from table
1466
print "Deleting rows from the table\n";
1467
$loop_time=new Benchmark;
1470
for ($i=0 ; $i < 128 ; $i++)
1473
$dbh->do("delete from bench1 where field_search = $i") or die $DBI::errstr;
1476
$end_time=new Benchmark;
1477
print "Time for delete_big_many_keys ($count): " .
1478
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
1480
if ($opt_lock_tables)
1482
$sth = $dbh->do("UNLOCK TABLES") || die $DBI::errstr;
1485
print "Deleting everything from table\n";
1489
$query= ($limits->{'truncate_table'} ? "truncate table bench1" :
1490
"delete from bench1");
1491
$dbh->do($query) or die $DBI::errstr;
1495
$dbh->do("delete from bench1 where field1 > 0") or die $DBI::errstr;
1498
$end_time=new Benchmark;
1499
print "Time for delete_all_many_keys ($count): " .
1500
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
1502
$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr;
1503
if ($opt_fast && defined($server->{vacuum}))
1505
$server->vacuum(1,\$dbh);
1509
# Test multi value inserts if the server supports it
1512
if ($limits->{'insert_multi_value'})
1514
$query_size=$limits->{'query_size'}; # Same limit for all databases
1516
$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'});
1517
do_many($dbh,$server->create("bench1",
1522
["primary key (id,id2)",
1523
"index index_id3 (id3)"]));
1525
$loop_time=new Benchmark;
1527
if ($opt_lock_tables)
1529
$sth = $dbh->do("LOCK TABLES bench1 write") || die $DBI::errstr;
1531
if ($opt_fast && $server->{transactions})
1533
$dbh->{AutoCommit} = 0;
1536
print "Inserting $opt_loop_count rows with multiple values\n";
1537
$query="insert into bench1 values ";
1539
for ($i=0 ; $i < $opt_loop_count ; $i++)
1541
my $tmp= "($i,$i,$i,'EFGHIJKLM'),";
1542
if (length($i)+length($res) < $query_size)
1548
do_query($dbh,substr($res,0,length($res)-1));
1552
do_query($dbh,substr($res,0,length($res)-1));
1554
if ($opt_lock_tables)
1556
$sth = $dbh->do("UNLOCK TABLES ") || die $DBI::errstr;
1558
if ($opt_fast && $server->{transactions})
1561
$dbh->{AutoCommit} = 1;
1564
$end_time=new Benchmark;
1565
print "Time for multiple_value_insert (" . ($opt_loop_count) . "): " .
1566
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
1568
if ($opt_lock_tables)
1570
$sth = $dbh->do("UNLOCK TABLES ") || die $DBI::errstr;
1573
# A big table may take a while to drop
1574
$loop_time=new Benchmark;
1575
$sth = $dbh->do("drop table bench1" . $server->{'drop_attr'}) or die $DBI::errstr;
1576
$end_time=new Benchmark;
1577
print "Time for drop table(1): " .
1578
timestr(timediff($end_time, $loop_time),"all") . "\n\n";
1582
#### End of benchmark
1585
$dbh->disconnect; # close connection
1587
end_benchmark($start_time);
1590
### Some help functions
1594
# Do some sample selects on direct key
1595
# First select finds a row, the second one doesn't find.
1597
sub check_select_key
1599
my ($sel_columns,$column,$check)= @_;
1600
my ($loop_time,$end_time,$i,$tmp_var,$tmp,$count,$row_count,$estimated);
1603
$loop_time=new Benchmark;
1605
for ($i=1 ; $i <= $opt_read_key_loop_count; $i++)
1608
$tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
1609
$tmp=$tmpvar % ($total_rows);
1610
fetch_all_rows($dbh,"select $sel_columns from bench1 where $column=$tmp")
1611
or die $DBI::errstr;
1613
defined($row_count=fetch_all_rows($dbh,"select $sel_columns from bench1 where $column=$tmp")) or die $DBI::errstr;
1614
die "Found $row_count rows on impossible id: $tmp\n" if ($row_count);
1615
$end_time=new Benchmark;
1616
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i,
1620
{ print "Estimated time"; }
1623
print " for $check ($count): " .
1624
timestr(timediff($end_time, $loop_time),"all") . "\n";
1627
# Same as above, but select on 2 columns
1629
sub check_select_key2
1631
my ($sel_columns,$column,$column2,$check)= @_;
1632
my ($loop_time,$end_time,$i,$tmp_var,$tmp,$count,$row_count,$estimated);
1635
$loop_time=new Benchmark;
1637
for ($i=1 ; $i <= $opt_read_key_loop_count; $i++)
1640
$tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
1641
$tmp=$tmpvar % ($total_rows);
1642
fetch_all_rows($dbh,"select $sel_columns from bench1 where $column=$tmp and $column2=$tmp")
1643
or die $DBI::errstr;
1645
defined($row_count=fetch_all_rows($dbh,"select $sel_columns from bench1 where $column=$tmp and $column2=$tmp")) or die $DBI::errstr;
1646
die "Found $row_count rows on impossible id: $tmp\n" if ($row_count);
1647
$end_time=new Benchmark;
1648
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$i,
1652
{ print "Estimated time"; }
1655
print " for $check ($count): " .
1656
timestr(timediff($end_time, $loop_time),"all") . "\n";
1660
# Search using some very simple queries
1663
sub check_select_range
1665
my ($column,$check)= @_;
1666
my ($loop_time,$end_time,$i,$tmp_var,$tmp,$query,$rows,$estimated);
1669
$loop_time=new Benchmark;
1671
for ($test=1 ; $test <= $range_loop_count; $test++)
1674
for ($i=0 ; $i < $#Q ; $i+=2)
1678
$query =~ s/!id!/$column/g;
1679
if (($row_count=fetch_all_rows($dbh,$query)) != $rows)
1681
if ($row_count == undef())
1683
die "Got error: $DBI::errstr when executing $query\n";
1685
die "'$query' returned wrong number of rows: $row_count instead of $rows\n";
1689
$end_time=new Benchmark;
1690
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$test,
1691
$range_loop_count));
1694
{ print "Estimated time"; }
1697
print " for $check ($count:$found): " .
1698
timestr(timediff($end_time, $loop_time),"all") . "\n";
1703
# SELECT * from bench where col=x or col=x or col=x ...
1708
my ($column,$check)= @_;
1709
my ($loop_time,$end_time,$i,$tmp_var,$tmp,$columns,$estimated,$found,
1710
$or_part,$count,$loop_count);
1712
$columns=min($limits->{'max_columns'},50,($limits->{'query_size'}-50)/13);
1713
$columns=$columns- ($columns % 4); # Make Divisible by 4
1716
$loop_time=new Benchmark;
1718
# The number of tests must be divisible by the following
1719
$tmp= $limits->{'func_extra_in_num'} ? 15 : 10;
1720
# We need to calculate the exact number of test to make 'Estimated' right
1721
$loop_count=$range_loop_count*10+$tmp-1;
1722
$loop_count=$loop_count- ($loop_count % $tmp);
1724
for ($count=0 ; $count < $loop_count ; )
1726
for ($rowcnt=0; $rowcnt <= $columns; $rowcnt+= $columns/4)
1728
my $query="select * from bench1 where ";
1729
my $or_part= "$column = 1";
1732
for ($i=1 ; $i < $rowcnt ; $i++)
1734
$tmpvar^= ((($tmpvar + 63) + $i)*3 % $opt_loop_count);
1735
$tmp=$tmpvar % ($opt_loop_count*4);
1736
$or_part.=" or $column=$tmp";
1738
print $query . $or_part . "\n" if ($opt_debug);
1739
($rows=fetch_all_rows($dbh,$query . $or_part)) or die $DBI::errstr;
1742
if ($limits->{'func_extra_in_num'})
1744
my $in_part=$or_part; # Same query, but use 'func_extra_in_num' instead.
1745
$in_part=~ s/ = / IN \(/;
1746
$in_part=~ s/ or $column=/,/g;
1748
fetch_all_rows($dbh,$query . $in_part) or die $DBI::errstr;
1751
# Do it a little harder by setting a extra range
1752
defined(($rows=fetch_all_rows($dbh,"$query($or_part) and $column < 10"))) or die $DBI::errstr;
1755
$end_time=new Benchmark;
1756
last if ($estimated=predict_query_time($loop_time,$end_time,\$count,$count,
1761
{ print "Estimated time"; }
1764
print " for $check ($count:$found): " .
1765
timestr(timediff($end_time, $loop_time),"all") . "\n";
1769
# General test of SELECT ... WHERE id in(value-list)
1774
my ($t1,$t2,$id,$from,$to)= @_;
1776
return if ($from >= $to);
1778
$query="SELECT $t1.* FROM $t1 WHERE $id IN (";
1779
for ($i=1 ; $i <= $to ; $i++)
1783
$query=substr($query,0,length($query)-1) . ")";
1785
# Fill join table to have the same id's as 'query'
1786
for ($i= $from ; $i <= $to ; $i++)
1788
$dbh->do("insert into $t2 values($i)") or die $DBI::errstr;
1790
if ($opt_fast && defined($server->{vacuum}))
1792
$server->vacuum(1,\$dbh,"bench1");
1795
time_fetch_all_rows("Testing SELECT ... WHERE id in ($to values)",
1796
"select_in", $query, $dbh,
1798
time_fetch_all_rows(undef, "select_join_in",
1799
"SELECT $t1.* FROM $t2 left outer join $t1 on ($t1.$id=$t2.$id)",
1800
$dbh, $range_loop_count);
added
removed
tests/kewpie/sql-bench/test-insert
