SQLite Forum

Benefits of using SQLite functions

Benefits of using SQLite functions

(1) By Gary (1codedebugger) on 2021-05-22 05:31:50 [link] [source]

This is a rather novice question I'm sure but could you please tell me if declaring a SQLite function results in efficiency of some sort or is mostly for convenience? Specifically, I was trying to understand if these two are really different in whether or not they bring database data into Tcl or handle it all in SQLite land, so to speak, and whether or not they write one large piece of data to disk in a single operation or perform a separate write operation for each individual update.

Thank you for considering my question.

proc normalize {string {form nfc}} {
    exec uconv -f utf-8 -t utf-8 -x "::$form;" << $string

dbws function normalize -returntype text -deterministic -directonly { normalize }

dbws eval { update src_original set uni_norm = normalize(original) }


dbws eval { select id, original from src_original } rowstep {
 set n [ normalize $rowstep(original) ]
 dbws eval { update src_original set uni_norm = $n where id = $rowstep(id) }

(2) By Larry Brasfield (larrybr) on 2021-05-22 10:58:25 in reply to 1 [source]

Considering your specific example: It would be difficult to detect a runtime efficiency difference among alternate ways to launch a separate process, feed input to it, and collect its output. (That is what the "exec ..." TCL call does.)

Treating your question more generally: Function calls are relatively cheap compared to what else is done in most functions, but the parameter passing, call and return always add some execution time. How significant that is depends on how much useful work the function does in comparison to that calling overhead.