Tcl arrays vs. PostgreSQL temporary tables - Benchmark

# ---------------------------------------------------
# Find 10 records out of 100 (100x)
# ---------------------------------------------------

for {set i 0 } {$i < 100} {incr i} {
	set x "abcd.$i.efg.hijklmnopqrstuvwxyz"
	set y "abcd.$i.efg.hijklmnopqrstuvwxyz"
	set arr($x) $y
}

set min [lindex [time {set y $arr(abcd.50.efg.hijklmnopqrstuvwxyz)} 100] 0]
for {set j 0} {$j < 10} {incr j} {
	set y "abcd.[expr 51 +$j].efg.hijklmnopqrstuvwxyz" 
	set tmp [lindex [time {set y $arr($y)} 100] 0 ]
	if {$tmp <$min} {set min $tmp}
}
ns_log Notice "ttt - Find 10 records out of 100: $i $min"
array unset arr

# ---------------------------------------------------
# Find 10 records out of 10 000 (100x)
# ---------------------------------------------------

for {set i 0 } {$i < 10000} {incr i} {
	set x "abcd.$i.efg.hijklmnopqrstuvwxyz"
	set y "abcd.$i.efg.hijklmnopqrstuvwxyz"
	set arr1($x) $y
}

set min [lindex [time {set y $arr1(abcd.5000.efg.hijklmnopqrstuvwxyz)} 100] 0]
for {set j 0} {$j < 10} {incr j} {
	set y "abcd.[expr 5001 +$j].efg.hijklmnopqrstuvwxyz" 
	set tmp [lindex [time {set y $arr1($y)} 100] 0 ]
	if {$tmp <$min} {set min $tmp}
}

ns_log Notice "ttt - Find 10 records out of 10 000: $i $min"
array unset arr1

# ---------------------------------------------------
# Find 10 records out of 100 000 (100x)
# ---------------------------------------------------

for {set i 0 } {$i < 100000} {incr i} {
	set x "abcd.$i.efg.hijklmnopqrstuvwxyz"
	set y "abcd.$i.efg.hijklmnopqrstuvwxyz"
	set arr2($x) $y
}

set min [lindex [time {set y $arr2(abcd.50000.efg.hijklmnopqrstuvwxyz)} 100] 0]
for {set j 0} {$j < 10} {incr j} {
	set y "abcd.[expr 50001 +$j].efg.hijklmnopqrstuvwxyz" 
	set tmp [lindex [time {set y $arr2($y)} 100] 0 ]
	if {$tmp <$min} {set min $tmp}
}

ns_log Notice "ttt - Find 10 records out of 100 000: $i $min"
array unset arr2


# ---------------------------------------------------
# Find 10 records out of 500 000 (100x)
# ---------------------------------------------------

for {set i 0 } {$i < 500000} {incr i} {
	set x "abcd.$i.efg.hijklmnopqrstuvwxyz"
	set y "abcd.$i.efg.hijklmnopqrstuvwxyz"
	set arr3($x) $y
}

set min [lindex [time {set y $arr3(abcd.250000.efg.hijklmnopqrstuvwxyz)} 100] 0]
for {set j 0} {$j < 10} {incr j} {
	set y "abcd.[expr 250001 +$j].efg.hijklmnopqrstuvwxyz" 
	set tmp [lindex [time {set y $arr3($y)} 100] 0 ]
	if {$tmp <$min} {set min $tmp}
}

ns_log Notice "ttt - Find 10 records out of 500 000: $i $min"
array unset arr3


# ---------------------------------------------------
# Find 10 records out of 1 000 000 (100x)
# ---------------------------------------------------

for {set i 0 } {$i < 1000000} {incr i} {
	set x "abcd.$i.efg.hijklmnopqrstuvwxyz"
	set y "abcd.$i.efg.hijklmnopqrstuvwxyz"
	set arr3($x) $y
}

set min [lindex [time {set y $arr3(abcd.500000.efg.hijklmnopqrstuvwxyz)} 100] 0]
for {set j 0} {$j < 10} {incr j} {
	set y "abcd.[expr 500001 +$j].efg.hijklmnopqrstuvwxyz" 
	set tmp [lindex [time {set y $arr3($y)} 100] 0 ]
	if {$tmp <$min} {set min $tmp}
}

ns_log Notice "ttt - Find 10 records out of 1 000 000: $i $min"
array unset arr3

# ---------------------------------------------------
# Find all days in a given month using an array
# ---------------------------------------------------

# 1 year
set years 5
set days [expr 365 * $years]
set a 0

set start_date "2010-01-01"
for {set i 0 } {$i < $days} {incr i} {
    set x [clock format [clock scan "+$i days" -base [clock scan $start_date] ] -format %Y-%m-%d]
    set y "abcd.$i.efg.hijklmnopqrstuvwxyz"
    set arr($x) $y
}

set tmp [lindex [time {
    foreach key [array names arr] { 
        if { [string match "2010-01-*" $key] } {
            set arr_target($key) $arr($key) 
			incr a
        }
    }       
} 1] 0 ]

ns_log Notice "ttt Find all days in Januar using an array - ($days / $a): $tmp"
# ad_return_complaint xx "($days / $a): $tmp"
array unset arr

# -------------------------------------------------------
# Find all days in a given month using an array & lsort
# -------------------------------------------------------

# 1 year
set years 5
set days [expr 365 * $years]
set a 0

array unset arr
set start_date "2010-01-01"
for {set i 0 } {$i < $days} {incr i} {
    set x [clock format [clock scan "+$i days" -base [clock scan $start_date] ] -format %Y-%m-%d]
    set y "abcd.$i.efg.hijklmnopqrstuvwxyz"
    set arr($x) $y
}

set tmp [lindex [time {
    foreach key [lsort -dictionary [array names arr]] { 
        if { [string match "2010-01-*" $key] } {
            set arr_target($key) $arr($key) 
			incr a
        }
    }       
} 1] 0 ]

ns_log Notice "ttt Find all days in Januar using an array & lsort - ($days / $a): $tmp"
# ad_return_complaint xx "($days / $a): $tmp"


# -----------------------------------------------------------------------
# Find all days in a given month using an temporary table without index
# -----------------------------------------------------------------------

set years 5
set days [expr 365 * $years]
set a 0

if {[catch { db_dml sql "DROP TABLE tmp_table" } err_msg]} {}

db_dml sql "
        CREATE TEMPORARY TABLE tmp_table(
                id                 	varchar         NOT NULL,
                txt                 varchar 
        )
"

# Seed table
set start_date "2010-01-01"
for {set i 0 } {$i < $days} {incr i} {
    set x [clock format [clock scan "+$i days" -base [clock scan $start_date] ] -format %Y-%m-%d]
    set y "abcd.$i.efg.hijklmnopqrstuvwxyz"
	db_dml sql "insert into tmp_table (id, txt) values ('$x', '$y')"
}

set tmp [lindex [time {
	db_foreach r "select id, txt from tmp_table where id like '2010-01-%'" {
		set arr_target(id) $txt
		incr a
	}	
} 1] 0 ]

ns_log Notice "ttt Find all days in Januar using an temporary table without index - ($days / $a): $tmp"
# ad_return_complaint xx "($days / $a): $tmp"

db_dml sql "DROP TABLE tmp_table" 


# --------------------------------------------------------------------
# Find all days in a given month using an temporary, indexed table 
# -------------------------------------------------------------------

set years 5
set days [expr 365 * $years]
set a 0

if {[catch { db_dml sql "DROP TABLE tmp_table" } err_msg]} {}

db_dml sql "
        CREATE TEMPORARY TABLE tmp_table(
                id                 	varchar         NOT NULL,
                txt                 varchar 
        )
"
db_dml sql "CREATE UNIQUE INDEX idx ON tmp_table (id)"

# Seed table
set start_date "2010-01-01"
for {set i 0 } {$i < $days} {incr i} {
    set x [clock format [clock scan "+$i days" -base [clock scan $start_date] ] -format %Y-%m-%d]
    set y "abcd.$i.efg.hijklmnopqrstuvwxyz"
	db_dml sql "insert into tmp_table (id, txt) values ('$x', '$y')"
}

set tmp [lindex [time {
	db_foreach r "select id, txt from tmp_table where id like '2010-01-%'" {
		set arr_target(id) $txt
		incr a
	}	
} 1] 0 ]

ns_log Notice "ttt Find all days in Januar using an temporary, indexed table - ($days / $a): $tmp"
# ad_return_complaint xx "($days / $a): $tmp"

db_dml sql "DROP TABLE tmp_table" 

# ---------------------------------------------------
# END
# ---------------------------------------------------