Re: [naviserver-devel] commit/nsdbi: 3 new changesets

[Gustaf N. <ne...@wu...>]

>> Without a C implementation only Tcl command dispatch overhead is 
>> being tested.
> ...
> Maybe i find some time-slots towards end of the week or on
> the weekend.

Dear all,

here is the result of todays experiments, which compares the following
structured output formats:

   - list of dicts
   - attribute+value lists, where every single list is converted to a 
dict from tcl
   - attribute+value lists, where every single list is converted to an 
associative array
   - list of lists (every sublist list is a list of values, similar to 
db_list_of_lists on OpenACS)
   - the result is a single dict, as suggested by wiwo
   - the result is a list of ns-sets

These formats are generated either
- directly from dbi_rows ("direct"), or
- dbi returns a flat list, but a separate converter function is called 
("convert"),
   implemented in C.

In each cases, i've tried to optimize the code by e.g. reducing the 
number of
append cases by creating the full list in one step, or by sharing objects
(e.g. the column names in the dict / atttribute+values, etc.)

The results are used on a SQL  query returning 10,000 rows à 14 columns.
The flat list has a length of 140,000 elements.

The performance differences between "direct" and "convert" often small,
larger for "dict" and "sets". If one adds all times, the difference is 9%,
in favor of "direct".

These numbers give no good reason for asking the user to use always two 
commands
instead of a single command.

 From the experiences with the abstraction layer, another disadvantage of
"dbi_rows -colums c {*sql*)" became apparent. In order to define a 
compatible
abstraction for a "db_*"- command one has to use "uplevel" in order to  
resolve
local bind variables. if one uses "uplevel" then the output variable 
passed via
"-columns" clobbers the calling environment. This problem affects all 
"convert"
cases, which requires the "-columns" argument. One implement as well a 
"-uplevel"
argument, of having some convention that "-bind 2" means "uplevel 2", 
but that's
not nice at all. The "direct" variants do not have this problem.

I'll commit the changes to bitbucket later for inspection and comments.
all the best
-gustaf neumann

PS: The wiki on OpenACS.org is now fully based on dbi.




The raw numbers:

		convert		direct
dicts		161102.82	154323.49
avlistd		157773.26	156649.43
avlistsa	153875.77	157220.76
lists		136289.30	138773.77
dict		190415.81	158263.39
sets		201872.61	150604.29


=========================================================
set ::sql {select * from acs_objects limit 10000}


proc convert-dicts {} {
     set sum 0
     set count 0
     set rows [dbi_rows -columns cols -max 1000000 -- $::sql]
     foreach d [dbi_convert -result dicts $cols $rows] {
	incr sum [dict get $d object_id]
	incr count
     }
     return [list $count $sum]
}

proc convert-avlistsd {} {
     set sum 0
     set count 0
     set rows [dbi_rows -columns cols -max 1000000 -- $::sql]
     foreach d [dbi_convert -result avlists $cols $rows] {
	incr sum [dict get $d object_id]
	incr count
     }
     return [list $count $sum]
}

proc convert-avlistsa {} {
     set sum 0
     set count 0
     set rows [dbi_rows -columns cols -max 1000000 -- $::sql]
     foreach d [dbi_convert -result avlists $cols $rows] {
	array set a $d
	incr sum $a(object_id)
	incr count
     }
     return [list $count $sum]
}

proc convert-lists {} {
     set sum 0
     set count 0
     set rows [dbi_rows -columns cols -max 1000000 -- $::sql]
     foreach d [dbi_convert -result lists $cols $rows] {
	incr sum [lindex $d 0]
	incr count
     }
     return [list $count $sum]
}

proc convert-dict {} {
     set sum 0
     set count 0
     set rows [dbi_rows -columns cols -max 1000000 -- $::sql]
     dict for {k v} [dbi_convert -result dict $cols $rows] {
	incr sum [dict get $v object_id]
	incr count
     }
     return [list $count $sum]
}

proc convert-sets {} {
     set sum 0
     set count 0
     set rows [dbi_rows -columns cols -max 1000000 -- $::sql]
     foreach s [dbi_convert -result sets $cols $rows] {
	incr sum [ns_set get $s object_id]
	incr count
     }
     return [list $count $sum]
}

#########################

proc direct-dicts {} {
     set sum 0
     set count 0
     foreach d [dbi_rows -result dicts -max 1000000 -- $::sql] {
	incr sum [dict get $d object_id]
	incr count
     }
     return [list $count $sum]
}

proc direct-avlistsd {} {
     set sum 0
     set count 0
     foreach d [dbi_rows -result avlists -max 1000000 -- $::sql] {
	incr sum [dict get $d object_id]
	incr count
     }
     return [list $count $sum]
}

proc direct-avlistsa {} {
     set sum 0
     set count 0
     foreach d [dbi_rows -result avlists -max 1000000 -- $::sql] {
	array set a $d
	incr sum $a(object_id)
	incr count
     }
     return [list $count $sum]
}


proc direct-lists {} {
     set sum 0
     set count 0
     foreach d [dbi_rows -result lists -max 1000000 -- $::sql] {
	incr sum [lindex $d 0]
	incr count
     }
     return [list $count $sum]
}

proc direct-dict {} {
     set sum 0
     set count 0
     dict for {k v} [dbi_rows -result dict -max 1000000 -- $::sql] {
	incr sum [dict get $v object_id]
	incr count
     }
     return [list $count $sum]
}

proc direct-sets {} {
     set sum 0
     set count 0
     foreach s [dbi_rows -result sets -max 1000000 -- $::sql] {
	incr sum [ns_set get $s object_id]
	incr count
     }
     return [list $count $sum]
}

lappend _ \n
foreach d {dicts avlistsd avlistsa lists dict sets} {
    set p convert-$d; lappend _ $p [$p] " [time $p 100]" \n
    set p direct-$d; lappend _ $p [$p] " [time $p 100]" \n
}
set _