diff options
Diffstat (limited to 'sql-bench')
| -rwxr-xr-x | sql-bench/server-cfg.sh | 13 | ||||
| -rwxr-xr-x | sql-bench/test-insert.sh | 53 |
2 files changed, 62 insertions, 4 deletions
diff --git a/sql-bench/server-cfg.sh b/sql-bench/server-cfg.sh index a49893c53e0..fa273ab8bd8 100755 --- a/sql-bench/server-cfg.sh +++ b/sql-bench/server-cfg.sh @@ -161,6 +161,7 @@ sub new $limits{'func_extra_in_num'} = 1; # Has function in $limits{'limit'} = 1; # supports the limit attribute $limits{'unique_index'} = 1; # Unique index works or not + $limits{'insert_select'} = 1; $smds{'time'} = 1; $smds{'q1'} = 'b'; # with time not supp by mysql ('') @@ -378,6 +379,7 @@ sub new $limits{'group_func_extra_std'} = 0; $limits{'limit'} = 1; # supports the limit attribute $limits{'unique_index'} = 1; # Unique index works or not + $limits{'insert_select'} = 0; $limits{'func_odbc_mod'} = 0; $limits{'func_extra_%'} = 0; @@ -576,6 +578,7 @@ sub new $limits{'max_text_size'} = 7000; # 8000 crashes pg 6.3 $limits{'query_size'} = 16777216; $limits{'unique_index'} = 1; # Unique index works or not + $limits{'insert_select'} = 1; # the different cases per query ... $smds{'q1'} = 'b'; # with time @@ -849,6 +852,7 @@ sub new $limits{'NEG'} = 1; $limits{'func_extra_in_num'} = 1; $limits{'unique_index'} = 1; # Unique index works or not + $limits{'insert_select'} = 1; # for the smds small benchmark test .... # the different cases per query ... @@ -1087,6 +1091,7 @@ sub new $limits{'NEG'} = 1; $limits{'func_extra_in_num'} = 0; $limits{'unique_index'} = 1; # Unique index works or not + $limits{'insert_select'} = 1; # for the smds small benchmark test .... # the different cases per query ... EMPRESS @@ -1364,6 +1369,7 @@ sub new $limits{'NEG'} = 1; # Supports -id $limits{'func_extra_in_num'} = 1; # Has function in $limits{'unique_index'} = 1; # Unique index works or not + $limits{'insert_select'} = 1; $smds{'time'} = 1; $smds{'q1'} = 'b'; # with time not supp by mysql ('') @@ -1612,6 +1618,7 @@ sub new $limits{'subqueries'} = 1; # Doesn't support sub-queries. $limits{'table_wildcard'} = 1; # Has SELECT table_name.* $limits{'unique_index'} = 1; # Unique index works or not + $limits{'insert_select'} = 1; return $self; } @@ -1809,6 +1816,7 @@ sub new $limits{'NEG'} = 1; # Supports -id $limits{'func_extra_in_num'} = 1; # Has function in $limits{'unique_index'} = 1; # Unique index works or not + $limits{'insert_select'} = 1; return $self; } @@ -1980,6 +1988,7 @@ sub new $limits{'NEG'} = 1; # Supports -id $limits{'func_extra_in_num'} = 0; # Has function in $limits{'unique_index'} = 1; # Unique index works or not + $limits{'insert_select'} = 1; return $self; } @@ -2163,6 +2172,7 @@ sub new $limits{'NEG'} = 1; # Supports -id $limits{'func_extra_in_num'} = 0; # Has function in $limits{'unique_index'} = 1; # Unique index works or not + $limits{'insert_select'} = 1; return $self; } @@ -2349,6 +2359,7 @@ sub new $limits{'NEG'} = 1; # Supports -id $limits{'func_extra_in_num'} = 1; # Has function in $limits{'unique_index'} = 1; # Unique index works or not + $limits{'insert_select'} = 1; $smds{'time'} = 1; $smds{'q1'} = 'b'; # with time not supp by mysql ('') @@ -2558,6 +2569,7 @@ sub new $limits{'NEG'} = 1; # Supports -id $limits{'func_extra_in_num'} = 0; # Has function in $limits{'unique_index'} = 1; # Unique index works or not + $limits{'insert_select'} = 1; return $self; } @@ -2726,6 +2738,7 @@ sub new $limits{'func_extra_in_num'} = 1; # Has function in $limits{'limit'} = 0; # Does not support the limit attribute $limits{'unique_index'} = 1; # Unique index works or not + $limits{'insert_select'} = 1; $smds{'time'} = 1; $smds{'q1'} = 'b'; # with time not supp by mysql ('') diff --git a/sql-bench/test-insert.sh b/sql-bench/test-insert.sh index 93f6745742e..129c37fac03 100755 --- a/sql-bench/test-insert.sh +++ b/sql-bench/test-insert.sh @@ -97,6 +97,8 @@ goto select_test if ($opt_skip_create); print "Creating tables\n"; $dbh->do("drop table bench1"); +$dbh->do("drop table bench2"); +$dbh->do("drop table bench3"); do_many($dbh,$server->create("bench1", ["id int NOT NULL", "id2 int NOT NULL", @@ -240,7 +242,7 @@ if ($limits->{'unique_index'}) } $end_time=new Benchmark; -print "Time for insert_duplicates (" . ($total_rows) . "): " . +print "Time for insert_duplicates (" . ($opt_loop_count) . "): " . timestr(timediff($end_time, $loop_time),"all") . "\n\n"; #if ($opt_fast && defined($server->{vacuum})) @@ -611,7 +613,7 @@ if ($limits->{'functions'}) } $end_time=new Benchmark; - print "Time for update_of_key ($range_loop_count): " . + print "Time for update_of_key ($update_loop_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n"; if ($opt_lock_tables) @@ -775,7 +777,7 @@ for ($i=0 ; $i < $opt_loop_count*3 ; $i++) } $end_time=new Benchmark; -print "Time for update_with_key ($opt_loop_count): " . +print "Time for update_with_key (" . ($opt_loop_count*3) . "): " . timestr(timediff($end_time, $loop_time),"all") . "\n"; print "\nTesting update of all rows\n"; @@ -785,7 +787,7 @@ for ($i=0 ; $i < $small_loop_count ; $i++) $sth = $dbh->do("update bench1 set dummy1='updated $i'") or die $DBI::errstr; } $end_time=new Benchmark; -print "Time for update_big ($range_loop_count): " . +print "Time for update_big ($small_loop_count): " . timestr(timediff($end_time, $loop_time),"all") . "\n"; @@ -860,6 +862,49 @@ if ($server->small_rollback_segment()) } #### +#### Test INSERT INTO ... SELECT +#### + +if ($limits->{'insert_select'}) +{ + print "\nTesting INSERT INTO ... SELECT\n"; + do_many($dbh,$server->create("bench2", + ["id int NOT NULL", + "id2 int NOT NULL", + "id3 int NOT NULL", + "dummy1 char(30)"], + ["primary key (id,id2)"])); + do_many($dbh,$server->create("bench3", + ["id int NOT NULL", + "id2 int NOT NULL", + "id3 int NOT NULL", + "dummy1 char(30)"], + ["primary key (id,id2)", + "index index_id3 (id3)"])); + $loop_time=new Benchmark; + $sth = $dbh->do("INSERT INTO bench2 SELECT * from bench1") || + die $DBI::errstr; + $end_time=new Benchmark; + print "Time for insert_select_1_key (1): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + $loop_time=new Benchmark; + $sth = $dbh->do("INSERT INTO bench3 SELECT * from bench1") || + die $DBI::errstr; + $end_time=new Benchmark; + print "Time for insert_select_2_keys (1): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; + $loop_time=new Benchmark; + $sth = $dbh->do("DROP TABLE bench2") || + die $DBI::errstr; + $sth = $dbh->do("DROP TABLE bench3") || + die $DBI::errstr; + $end_time=new Benchmark; + print "Time for drop table(2): " . + timestr(timediff($end_time, $loop_time),"all") . "\n"; +} + + +#### #### Do some deletes on the table #### |