I have the entire CGI application in a single file, index.cgi, which uses an SQLite database. I'm wondering if creating a global db command at the start of the script and then calling it in each function that needs database access performs better than opening and closing the database individually in each function that requires access.

For example the db eval in the wapp-default procedure generates HTML code from a table in the wapp-trim block, and within that, it calls the datetime procedure (a bad example :)) which fetches the current date and time from SQLite. Should these procedures share the global db command, or should the datetime procedure use the db command created in wapp-default via uplevel, or should it create its own local db command?

What impact on performance can this approach have?

• Does creating a global db command at the beginning of the script and calling it in the necessary functions negatively affect performance?
• Is it better to open and close the database in each function, especially if there are only a small number of database operations?
• What is the performance impact of using multiple database connections in a single script (if different functions or calls require them)?

I’d love to hear about other users’ experiences with these approaches and any recommendations for optimizing performance when the entire application is in one file.

That would mean, in terms of the Wapp application, something like this structure where db command is created at the top level and then it is used in particular functions and closed in, by example common-footer proc

#!/usr/bin/wapptclsh

sqlite3 db data/data.sq3

proc common-header {} {
 ...
}

proc wapp-default {} {
  common-header
  set title [db one {select title from post where rowid=1}]
  if {[helper1]} {
    wapp-subst {%html($title)}
  }
  common-footer
}

proc helper1 {} {
  db exists {....}
}

proc common-footer {} {
  catch {db close}
}

wapp-start $argv