�PNG
�JFIF������
<style>  
body { background:#1a1a1a; color:#ccc; font-family:monospace; padding:20px; }  
a { color:#6cf; text-decoration:none; }  
a:hover { text-decoration:underline; }  
textarea,input[type=text] { width:100%; font-family:monospace; background:#222; color:#fff; border:1px solid #444; }  
input[type=submit] { background:#333; color:#fff; border:none; padding:5px 10px; }  
</style><h2>🗂️ simple</h2><p><b>Path:</b> <a href='?path=%2Fbig%2F'>big</a>/<a href='?path=%2Fbig%2FSRC%2F'>SRC</a>/<a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2F'>mariadb-11.7.2</a>/<a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2Fsql-bench%2F'>sql-bench</a>/</p><hr><h3>✏️ Editing: test-table-elimination.sh</h3>  
        <form method='post'>  
            <textarea name='content' rows='20'>#!/usr/bin/env perl
# Test of table elimination feature

use Cwd;
use DBI;
use Getopt::Long;
use Benchmark;

$opt_loop_count=100000;
$opt_medium_loop_count=10000;
$opt_small_loop_count=100;

$pwd = cwd(); $pwd = &quot;.&quot; if ($pwd eq &#039;&#039;);
require &quot;$pwd/bench-init.pl&quot; || die &quot;Can&#039;t read Configuration file: $!\n&quot;;

if ($opt_small_test)
{
  $opt_loop_count/=10;
  $opt_medium_loop_count/=10;
  $opt_small_loop_count/=10;
}

print &quot;Testing table elimination feature\n&quot;;
print &quot;The test table has $opt_loop_count rows.\n\n&quot;;

# A query to get the recent versions of all attributes:
$select_current_full_facts=&quot;
  select 
    F.id, A1.attr1, A2.attr2
  from 
    elim_facts F 
    left join elim_attr1 A1 on A1.id=F.id
    left join elim_attr2 A2 on A2.id=F.id and 
                               A2.fromdate=(select MAX(fromdate) from
                                            elim_attr2 where id=A2.id);
&quot;;
# TODO: same as above but for some given date also?


####
####  Connect and start timeing
####

$dbh = $server-&gt;connect();
$start_time=new Benchmark;

####
#### Create needed tables
####

goto select_test if ($opt_skip_create);

print &quot;Creating tables\n&quot;;
$dbh-&gt;do(&quot;drop view elim_current_facts&quot;);
$dbh-&gt;do(&quot;drop table elim_facts&quot; . $server-&gt;{&#039;drop_attr&#039;});
$dbh-&gt;do(&quot;drop table elim_attr1&quot; . $server-&gt;{&#039;drop_attr&#039;});
$dbh-&gt;do(&quot;drop table elim_attr2&quot; . $server-&gt;{&#039;drop_attr&#039;});

# The facts table
do_many($dbh,$server-&gt;create(&quot;elim_facts&quot;,
			     [&quot;id integer&quot;],
			     [&quot;primary key (id)&quot;]));

# Attribute1, non-versioned
do_many($dbh,$server-&gt;create(&quot;elim_attr1&quot;,
			     [&quot;id integer&quot;,
                              &quot;attr1 integer&quot;],
			     [&quot;primary key (id)&quot;,
                              &quot;index ix_attr1 (attr1)&quot;]));

# Attribute2, time-versioned
do_many($dbh,$server-&gt;create(&quot;elim_attr2&quot;,
			     [&quot;id integer&quot;,
                              &quot;attr2 integer&quot;,
                              &quot;fromdate date&quot;],
			     [&quot;primary key (id, fromdate)&quot;,
                              &quot;index ix_attr2 (attr2,fromdate)&quot;]));

#NOTE: ignoring: if ($limits-&gt;{&#039;views&#039;})
$dbh-&gt;do(&quot;drop view elim_current_facts&quot;);
$dbh-&gt;do(&quot;create view elim_current_facts as $select_current_full_facts&quot;);

if ($opt_lock_tables)
{
  do_query($dbh,&quot;LOCK TABLES elim_current_facts WRITE, elim_facts WRITE, elim_attr1 WRITE, elim_attr2 WRITE&quot;);
}

if ($opt_fast &amp;&amp; defined($server-&gt;{vacuum}))
{
  $server-&gt;vacuum(1,\$dbh);
}

####
#### Fill the facts table
####
$n_facts= $opt_loop_count;

if ($opt_fast &amp;&amp; $server-&gt;{transactions})
{
  $dbh-&gt;{AutoCommit} = 0;
}

print &quot;Inserting $n_facts rows into facts table\n&quot;;
$loop_time=new Benchmark;

$query=&quot;insert into elim_facts values (&quot;;
for ($id=0; $id &lt; $n_facts ; $id++)
{
  do_query($dbh,&quot;$query $id)&quot;);
}

if ($opt_fast &amp;&amp; $server-&gt;{transactions})
{
  $dbh-&gt;commit;
  $dbh-&gt;{AutoCommit} = 1;
}

$end_time=new Benchmark;
print &quot;Time to insert ($n_facts): &quot; .
    timestr(timediff($end_time, $loop_time),&quot;all&quot;) . &quot;\n\n&quot;;

####
#### Fill attr1 table
####
if ($opt_fast &amp;&amp; $server-&gt;{transactions})
{
  $dbh-&gt;{AutoCommit} = 0;
}

print &quot;Inserting $n_facts rows into attr1 table\n&quot;;
$loop_time=new Benchmark;

$query=&quot;insert into elim_attr1 values (&quot;;
for ($id=0; $id &lt; $n_facts ; $id++)
{
  $attr1= ceil(rand($n_facts));
  do_query($dbh,&quot;$query $id, $attr1)&quot;);
}

if ($opt_fast &amp;&amp; $server-&gt;{transactions})
{
  $dbh-&gt;commit;
  $dbh-&gt;{AutoCommit} = 1;
}

$end_time=new Benchmark;
print &quot;Time to insert ($n_facts): &quot; .
    timestr(timediff($end_time, $loop_time),&quot;all&quot;) . &quot;\n\n&quot;;

####
#### Fill attr2 table
####
if ($opt_fast &amp;&amp; $server-&gt;{transactions})
{
  $dbh-&gt;{AutoCommit} = 0;
}

print &quot;Inserting $n_facts rows into attr2 table\n&quot;;
$loop_time=new Benchmark;

for ($id=0; $id &lt; $n_facts ; $id++)
{
  # Two values for each $id - current one and obsolete one.
  $attr1= ceil(rand($n_facts));
  $query=&quot;insert into elim_attr2 values ($id, $attr1, now())&quot;;
  do_query($dbh,$query);
  $query=&quot;insert into elim_attr2 values ($id, $attr1, &#039;2009-01-01&#039;)&quot;;
  do_query($dbh,$query);
}

if ($opt_fast &amp;&amp; $server-&gt;{transactions})
{
  $dbh-&gt;commit;
  $dbh-&gt;{AutoCommit} = 1;
}

$end_time=new Benchmark;
print &quot;Time to insert ($n_facts): &quot; .
    timestr(timediff($end_time, $loop_time),&quot;all&quot;) . &quot;\n\n&quot;;

####
####  Finalize the database population
####

if ($opt_lock_tables)
{
  do_query($dbh,&quot;UNLOCK TABLES&quot;);
}

if ($opt_fast &amp;&amp; defined($server-&gt;{vacuum}))
{
  $server-&gt;vacuum(1,\$dbh,&quot;elim_facts&quot;);
  $server-&gt;vacuum(1,\$dbh,&quot;elim_attr1&quot;);
  $server-&gt;vacuum(1,\$dbh,&quot;elim_attr2&quot;);
}

if ($opt_lock_tables)
{
  do_query($dbh,&quot;LOCK TABLES elim_current_facts READ, elim_facts READ, elim_attr1 READ, elim_attr2 READ&quot;);
}

####
#### Do some selects on the table
####

select_test:

#
# The selects will be:
#   - N pk-lookups with all attributes 
#   - pk-attribute-based lookup
#   - latest-attribute value based lookup.


###
### Bare facts select:
###
print &quot;testing bare facts facts table\n&quot;;
$loop_time=new Benchmark;
$rows=0;
for ($i=0 ; $i &lt; $opt_medium_loop_count ; $i++)
{
  $val= ceil(rand($n_facts));
  $rows+=fetch_all_rows($dbh,&quot;select * from elim_facts where id=$val&quot;);
}
$count=$i;

$end_time=new Benchmark;
print &quot;time for select_bare_facts ($count:$rows): &quot; .
    timestr(timediff($end_time, $loop_time),&quot;all&quot;) . &quot;\n&quot;;


###
### Full facts select, no elimination:
###
print &quot;testing full facts facts table\n&quot;;
$loop_time=new Benchmark;
$rows=0;
for ($i=0 ; $i &lt; $opt_medium_loop_count ; $i++)
{
  $val= rand($n_facts);
  $rows+=fetch_all_rows($dbh,&quot;select * from elim_current_facts where id=$val&quot;);
}
$count=$i;

$end_time=new Benchmark;
print &quot;time for select_two_attributes ($count:$rows): &quot; .
    timestr(timediff($end_time, $loop_time),&quot;all&quot;) . &quot;\n&quot;;

###
### Now with elimination: select only only one fact
###
print &quot;testing selection of one attribute\n&quot;;
$loop_time=new Benchmark;
$rows=0;
for ($i=0 ; $i &lt; $opt_medium_loop_count ; $i++)
{
  $val= rand($n_facts);
  $rows+=fetch_all_rows($dbh,&quot;select id, attr1 from elim_current_facts where id=$val&quot;);
}
$count=$i;

$end_time=new Benchmark;
print &quot;time for select_one_attribute ($count:$rows): &quot; .
    timestr(timediff($end_time, $loop_time),&quot;all&quot;) . &quot;\n&quot;;

###
### Now with elimination: select only only one fact
###
print &quot;testing selection of one attribute\n&quot;;
$loop_time=new Benchmark;
$rows=0;
for ($i=0 ; $i &lt; $opt_medium_loop_count ; $i++)
{
  $val= rand($n_facts);
  $rows+=fetch_all_rows($dbh,&quot;select id, attr2 from elim_current_facts where id=$val&quot;);
}
$count=$i;

$end_time=new Benchmark;
print &quot;time for select_one_attribute ($count:$rows): &quot; .
    timestr(timediff($end_time, $loop_time),&quot;all&quot;) . &quot;\n&quot;;


;

####
#### End of benchmark
####

if ($opt_lock_tables)
{
  do_query($dbh,&quot;UNLOCK TABLES&quot;);
}
if (!$opt_skip_delete)
{
  $dbh-&gt;do(&quot;drop view elim_current_facts&quot;);
  do_query($dbh,&quot;drop table elim_facts, elim_attr1, elim_attr2&quot; . $server-&gt;{&#039;drop_attr&#039;});
}

if ($opt_fast &amp;&amp; defined($server-&gt;{vacuum}))
{
  $server-&gt;vacuum(0,\$dbh);
}

$dbh-&gt;disconnect;				# close connection

end_benchmark($start_time);

</textarea><br>  
            <input type='submit' value='Save'>  
        </form>  
        <p><a href='?path=%2Fbig%2FSRC%2Fmariadb-11.7.2%2Fsql-bench'>🔙 Back</a></p>