Quote from Tom Holden on 2023-07-23, 9:39 pm

This post compares the performance of custom functions in SQLiteStudio to the matching functionless scripts from which they were derived.

I first reported on trying this feature of SQLiteStudio in a comment on the recent discussion of an INCLUDE function led by @thejerrybryan . That comment illustrated a SQL function to decode EventTable.Date. I developed a second SQL custom function to decode XML data as found in SourceTable.Fields and CitationTable.Fields. It is based on the RM4-9 version of MasterSources.sql by @patjones

Both functions are pretty complex and greatly simplify the query that uses them. For example, the attached screenshot illustrates how the rmxmldecode() function enables an easily understandable script ("SQL editor 2" pane on the right) compared to the query without the function. There's a similar simplification associated with my rmdatedecode() function.

Simplification of scripts is great but my limited knowledge of software is that the use of function calls rather than inline code has some adverse impact on speed, especially with interpreters. Sure enough, that's the case here:

Test	Inline	Function
Date Decode	1s	50s
XML Decode	1s	3s

The Date Decode test was limited to 10,000 results from the 22,000 row EventTable. XML Decode returned all results from the 5500 row SourceTable. The times are what I observed, not the milliseconds SQLiteStudio reports as the query execution time, so it includes the time the software took to render the results. The queries using a function were often reported as having 0 execution time so SQLite Studio has a blind spot for that feature.

So, SQLiteStudio custom functions are:

1. easy to create if you can code in SQL, TCL, or Python.
2. good for simplifying scripts, making them easier to code, more understandable.
3. less efficient than inline coding
4. not transferable with the database to other devices running SQLite Studio nor to any other application. I cannot find where the function definitions are stored. Edit: see addendum.
5. are useful, effective and advantageous for some purposes provided the costs in performance and portability are acceptable.

Addendum 2023-07-26:

There is potential to transfer, but limited:

1. A portable version of SQLiteStudio can be installed and transported with the associated database(s).
2. Function definitions are stored in one record in the SQLiteStudio settings file, a SQLite3 database. It is technically possible to transfer this record from one instance of SQLiteStudio on one device to one on another, replacing the latter's record; the developer thinks it should work but has not tested it. SQLiteStudio is multi-platform so inter-platform transfer could work, too.
3. There is no mechanism to export/import selected function definitions.