From Newsgroup: comp.lang.tcl

I'm writing numerical lists to a sqlite DB and I'm wondering, if the way
I do it is the fastest possible, since the lists are pretty large and it
seems that most of the processing time is spent on writing the data to
the DB instead on the actual computation of the lists.

So I currently do:

sqlite3 db $rstfile2
db eval {CREATE TABLE general(key TEXT, data TEXT)}
db eval {CREATE TABLE results(key TEXT, data TEXT)}
db eval "INSERT INTO general VALUES('dt','$dt')"
db eval "INSERT INTO general VALUES('nsteps','$nsteps')"

db eval "INSERT INTO results VALUES('$step-global','$Im')"

Here, the list "Im" is the large list.

Is this already optimal of are there some tricks to make it much faster?

Many thanks
Alex
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

meshparts <alexandru.dadalau@meshparts.de> wrote:

I'm writing numerical lists to a sqlite DB and I'm wondering, if the way
I do it is the fastest possible, since the lists are pretty large and it seems that most of the processing time is spent on writing the data to
the DB instead on the actual computation of the lists.

So I currently do:

sqlite3 db $rstfile2
db eval {CREATE TABLE general(key TEXT, data TEXT)}
db eval {CREATE TABLE results(key TEXT, data TEXT)}
db eval "INSERT INTO general VALUES('dt','$dt')"
db eval "INSERT INTO general VALUES('nsteps','$nsteps')"

db eval "INSERT INTO results VALUES('$step-global','$Im')"

This causes Tcl to create a string, containing the string rep of the
list, and then causes sqlite to reparse the SQL string to extract that
string therefrom.

Use sqlite's ability to directly address Tcl variables instead:

db eval {INSERT INTO results VALUES($step-global,$Im);}

And you may find that the time to insert goes down by some factor.

Here, the list "Im" is the large list.

Care to define what "llength" constitutes "large" for you?

Is this already optimal of are there some tricks to make it much faster?

Many thanks
Alex

--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

Am 29.04.2026 um 17:50 schrieb Rich:

Use sqlite's ability to directly address Tcl variables instead:

Thanks Rich! I wasn't aware, that this trick also works for sqlight (I
know it from Tcl itself when using "expr".

This indeed cut the time by 50%.

I had to put the single quotes back though:

db eval {INSERT INTO results VALUES('$step-global','$Im')}

Without the quotes, I got some weird errors.
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

Am 29.04.2026 um 17:50 schrieb Rich:

Care to define what "llength" constitutes "large" for you?

Well, the size of the list is anything between 1e3 to 1e9 as an order of magnitude.

For my performance improvement it doesn't realy matter because the
processing of the list is also proportional to the length of the list.

So I'm only searching for a relative improvement of the speed.
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

Am 29.04.2026 um 19:13 schrieb meshparts:

Am 29.04.2026 um 17:50 schrieb Rich:

Use sqlite's ability to directly address Tcl variables instead:

Thanks Rich! I wasn't aware, that this trick also works for sqlight (I
know it from Tcl itself when using "expr".

This indeed cut the time by 50%.

I had to put the single quotes back though:

db eval {INSERT INTO results VALUES('$step-global','$Im')}

Without the quotes, I got some weird errors.

I hat to roll back, since the new solution write empty string to the DB.

At least my old command to retrieve the data does returns an empty string:

db eval "SELECT data FROM results WHERE key='$setnum-global'"

I don't understand why...
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

Am 29.04.2026 um 19:44 schrieb meshparts:

I hat to roll back, since the new solution write empty string to the DB.

At least my old command to retrieve the data does returns an empty string:

db eval "SELECT data FROM results WHERE key='$setnum-global'"

I don't understand why...

I found something here:

https://sqlite.org/tclsqlite.html

Tcl variable names can appear in the SQL statement of the second
argument in any position where it is legal to put a string or number
literal. The value of the variable is substituted for the variable name.
If the variable does not exist a NULL values is used. For example:

db1 eval {INSERT INTO t1 VALUES(5,$bigstring)}

Note that it is not necessary to quote the $bigstring value. That
happens automatically. If $bigstring is a large string or binary object,
this technique is not only easier to write, it is also much more
efficient since it avoids making a copy of the content of $bigstring.

So I should work. I'll try again without the single quotes around the
variable name.
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

Am 29.04.2026 um 19:58 schrieb meshparts:

I'll try again without the single quotes around the variable name.

Nope, same result, empty string...
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

Am 29.04.2026 um 19:44 schrieb meshparts:

Am 29.04.2026 um 19:13 schrieb meshparts:

Am 29.04.2026 um 17:50 schrieb Rich:

Use sqlite's ability to directly address Tcl variables instead:

Thanks Rich! I wasn't aware, that this trick also works for sqlight (I
know it from Tcl itself when using "expr".

This indeed cut the time by 50%.

I had to put the single quotes back though:

db eval {INSERT INTO results VALUES('$step-global','$Im')}

Without the quotes, I got some weird errors.

I hat to roll back, since the new solution write empty string to the DB.

At least my old command to retrieve the data does returns an empty string:

db eval "SELECT data FROM results WHERE key='$setnum-global'"

I don't understand why...

Got it: I'm using a variable in the first argument of VALUES, which is
not supported, when using curly braces.
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

Am 29.04.2026 um 23:29 schrieb meshparts:

Am 29.04.2026 um 19:44 schrieb meshparts:

Am 29.04.2026 um 19:13 schrieb meshparts:

Am 29.04.2026 um 17:50 schrieb Rich:

Use sqlite's ability to directly address Tcl variables instead:

Thanks Rich! I wasn't aware, that this trick also works for sqlight
(I know it from Tcl itself when using "expr".

This indeed cut the time by 50%.

I had to put the single quotes back though:

db eval {INSERT INTO results VALUES('$step-global','$Im')}

Without the quotes, I got some weird errors.

I hat to roll back, since the new solution write empty string to the DB.

At least my old command to retrieve the data does returns an empty
string:

db eval "SELECT data FROM results WHERE key='$setnum-global'"

I don't understand why...

Got it: I'm using a variable in the first argument of VALUES, which is
not supported, when using curly braces.

So now I'm doing:

db eval "INSERT INTO results VALUES('$step-global',\$Im)"

Turns out this method is not faster than the original one.
The stated 50% cut in time was due to the previously wrong usage.
So back to the drawing board, still in need of a way to make INSERT of
large lists faster.
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

meshparts <alexandru.dadalau@meshparts.de> wrote:

Am 29.04.2026 um 17:50 schrieb Rich:

Care to define what "llength" constitutes "large" for you?

Well, the size of the list is anything between 1e3 to 1e9 as an order of magnitude.

I presume you mean 10e3 to 10e9, as 1 to the third power, and 1 to the
ninth power, are both one... :)

Yes, those that approach 10e9 in length will consume a significant
amount of time creating the string serialization, which sqlite has to
unparse to then insert into the column.

--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

meshparts <alexandru.dadalau@meshparts.de> wrote:

Am 29.04.2026 um 19:44 schrieb meshparts:

Am 29.04.2026 um 19:13 schrieb meshparts:

Am 29.04.2026 um 17:50 schrieb Rich:

Use sqlite's ability to directly address Tcl variables instead:

Thanks Rich! I wasn't aware, that this trick also works for sqlight (I
know it from Tcl itself when using "expr".

This indeed cut the time by 50%.

I had to put the single quotes back though:

db eval {INSERT INTO results VALUES('$step-global','$Im')}

Without the quotes, I got some weird errors.

I hat to roll back, since the new solution write empty string to the DB.

At least my old command to retrieve the data does returns an empty string: >>
db eval "SELECT data FROM results WHERE key='$setnum-global'"

I don't understand why...

Got it: I'm using a variable in the first argument of VALUES, which is
not supported, when using curly braces.

Note this sentence from your other posting quoting the sqlite tcl api documentation:

Tcl variable names can appear in the SQL statement of the second
argument in any position where it is legal to put a string or number
literal.

As it is legal to put a string or number literal in the first slot, it
is also legal to put a variable reference there as well.

As to your other posts, I'm not following what problem you are finding.
Is the database storing empty string when the list had X elements? Or
is the list empty (empty string), but the database is storing something
other than "empty string"?

--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

Am 29.04.2026 um 23:54 schrieb Rich:

I presume you mean 10e3 to 10e9, as 1 to the third power, and 1 to the
ninth power, are both one... 🙂

expr 1e3 => 1000
expr 1e9 => 1000000000
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

Am 29.04.2026 um 23:56 schrieb Rich:

meshparts <alexandru.dadalau@meshparts.de> wrote:

Am 29.04.2026 um 19:44 schrieb meshparts:

Am 29.04.2026 um 19:13 schrieb meshparts:

Am 29.04.2026 um 17:50 schrieb Rich:

Use sqlite's ability to directly address Tcl variables instead:

Thanks Rich! I wasn't aware, that this trick also works for sqlight (I >>>> know it from Tcl itself when using "expr".

This indeed cut the time by 50%.

I had to put the single quotes back though:

db eval {INSERT INTO results VALUES('$step-global','$Im')}

Without the quotes, I got some weird errors.

I hat to roll back, since the new solution write empty string to the DB. >>>
At least my old command to retrieve the data does returns an empty string: >>>
db eval "SELECT data FROM results WHERE key='$setnum-global'"

I don't understand why...

Got it: I'm using a variable in the first argument of VALUES, which is
not supported, when using curly braces.

Note this sentence from your other posting quoting the sqlite tcl api documentation:

Tcl variable names can appear in the SQL statement of the second
argument in any position where it is legal to put a string or number
literal.

As it is legal to put a string or number literal in the first slot, it
is also legal to put a variable reference there as well.

As to your other posts, I'm not following what problem you are finding.
Is the database storing empty string when the list had X elements? Or
is the list empty (empty string), but the database is storing something
other than "empty string"?

No, the key argument of VALUES is the problem.
This works:

set step 1
set Im "1 2 3"
sqlite3 db "test.db"
db eval {CREATE TABLE results(key TEXT, data TEXT)}
db eval "INSERT INTO results VALUES('$step-global',\$Im)"
puts [db eval "SELECT data FROM results WHERE key='$step-global'"]
db close


This does not work:

set step 1
set Im "1 2 3"
sqlite3 db "test.db"
db eval {CREATE TABLE results(key TEXT, data TEXT)}
db eval {INSERT INTO results VALUES('$step-global',$Im)}
puts [db eval "SELECT data FROM results WHERE key='$step-global'"]
db close

--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

meshparts <alexandru.dadalau@meshparts.de> wrote:

Am 29.04.2026 um 23:56 schrieb Rich:

meshparts <alexandru.dadalau@meshparts.de> wrote:

Am 29.04.2026 um 19:44 schrieb meshparts:

Am 29.04.2026 um 19:13 schrieb meshparts:

Am 29.04.2026 um 17:50 schrieb Rich:

Use sqlite's ability to directly address Tcl variables instead:

Thanks Rich! I wasn't aware, that this trick also works for sqlight (I >>>>> know it from Tcl itself when using "expr".

This indeed cut the time by 50%.

I had to put the single quotes back though:

db eval {INSERT INTO results VALUES('$step-global','$Im')}

Without the quotes, I got some weird errors.

I hat to roll back, since the new solution write empty string to the DB. >>>>
At least my old command to retrieve the data does returns an empty string: >>>>
db eval "SELECT data FROM results WHERE key='$setnum-global'"

I don't understand why...

Got it: I'm using a variable in the first argument of VALUES, which is
not supported, when using curly braces.

Note this sentence from your other posting quoting the sqlite tcl api
documentation:

Tcl variable names can appear in the SQL statement of the second
argument in any position where it is legal to put a string or number
literal.

As it is legal to put a string or number literal in the first slot, it
is also legal to put a variable reference there as well.

As to your other posts, I'm not following what problem you are finding.
Is the database storing empty string when the list had X elements? Or
is the list empty (empty string), but the database is storing something
other than "empty string"?

No, the key argument of VALUES is the problem.
This works:

set step 1
set Im "1 2 3"
sqlite3 db "test.db"
db eval {CREATE TABLE results(key TEXT, data TEXT)}
db eval "INSERT INTO results VALUES('$step-global',\$Im)"
puts [db eval "SELECT data FROM results WHERE key='$step-global'"]
db close

This does not work:

set step 1
set Im "1 2 3"
sqlite3 db "test.db"
db eval {CREATE TABLE results(key TEXT, data TEXT)}
db eval {INSERT INTO results VALUES('$step-global',$Im)}
puts [db eval "SELECT data FROM results WHERE key='$step-global'"]
db close

Hyphens are not valid tcl variable name characters for the "$"
resolver. Your $step-global will be resolved as 1-global by the Tcl
parser, but it seesm the sqlite parser acts differently.

Your first code snippet:

% sqlite3 db :memory:
% db eval {CREATE TABLE results(key TEXT, data TEXT)}
% db eval "INSERT INTO results VALUES('$step-global',\$Im)"
% db eval {select * from results} X { parray X }
X(*) = key data
X(data) = 1 2 3
X(key) = 1-global

The key column contains '1-global'. And your where clause in the
select statement also resolves to 1-global, so sqlite finds the row.

Your second code snippet (after a db close):

% sqlite3 db :memory:
% db eval {CREATE TABLE results(key TEXT, data TEXT)}
% db eval {INSERT INTO results VALUES('$step-global',$Im)}
% db eval {select * from results} X { parray X }
X(*) = key data
X(data) = 1 2 3
X(key) = $step-global

The key column contains the literal string "$step-global", which does
not match "1-global", so sqlite returns an empty result.

That's why you are getting empty string, you are not selecting using
the string that actually exists in the key column.
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

Am 30.04.2026 um 00:22 schrieb Rich:

The key column contains the literal string "$step-global", which does
not match "1-global", so sqlite returns an empty result.

That's why you are getting empty string, you are not selecting using
the string that actually exists in the key column.

Yes, I also get it now. Which means, that the key argument is
interpreted differently than the variable argument. But it's no
supprise, since the manual only states that the second argument will be replaced with the value when using $.
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

Am 30.04.2026 um 08:00 schrieb meshparts:

Am 30.04.2026 um 00:22 schrieb Rich:

The key column contains the literal string "$step-global", which does
not match "1-global", so sqlite returns an empty result.

That's why you are getting empty string, you are not selecting using
the string that actually exists in the key column.

Yes, I also get it now. Which means, that the key argument is
interpreted differently than the variable argument. But it's no
supprise, since the manual only states that the second argument will be replaced with the value when using $.

Now back to the original issue: performance.
Sqlite does not seem to perform well with large lists.
I tested simply "puts" the lists to a file instead, and it's so much
faster than sqlite.
Since I'm not getting any other solutions, I'll ditch the sqlite
solution entirely.
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl


meshparts <alexandru.dadalau@meshparts.de> posted:

Am 29.04.2026 um 23:29 schrieb meshparts:

Am 29.04.2026 um 19:44 schrieb meshparts:

Am 29.04.2026 um 19:13 schrieb meshparts:

Am 29.04.2026 um 17:50 schrieb Rich:

Use sqlite's ability to directly address Tcl variables instead:

Thanks Rich! I wasn't aware, that this trick also works for sqlight
(I know it from Tcl itself when using "expr".

This indeed cut the time by 50%.

I had to put the single quotes back though:

db eval {INSERT INTO results VALUES('$step-global','$Im')}

Without the quotes, I got some weird errors.

I hat to roll back, since the new solution write empty string to the DB. >>
At least my old command to retrieve the data does returns an empty
string:

db eval "SELECT data FROM results WHERE key='$setnum-global'"

I don't understand why...

Got it: I'm using a variable in the first argument of VALUES, which is
not supported, when using curly braces.

So now I'm doing:

db eval "INSERT INTO results VALUES('$step-global',\$Im)"

Turns out this method is not faster than the original one.
The stated 50% cut in time was due to the previously wrong usage.
So back to the drawing board, still in need of a way to make INSERT of
large lists faster.

There is no need to use "" and escape your variables.
This should work, is faster and save (no code injection).
The eval method will parse your string correctly. Note the {}.

db eval {INSERT INTO results VALUES($step-global,$Im)}

HTH
rene
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

Am 30.04.2026 um 11:22 schrieb rene:

There is no need to use "" and escape your variables.
This should work, is faster and save (no code injection).
The eval method will parse your string correctly. Note the {}.

db eval {INSERT INTO results VALUES($step-global,$Im)}

HTH
rene

Well, it doesn't.
You can use my demo below to test your proposal.
I get the error "no such column: global" when not using the single
quotes for the key argument.

file delete test.db
set step 1
set Im "1 2 3"
sqlite3 db "test.db"
db eval {CREATE TABLE results(key TEXT, data TEXT)}
db eval {INSERT INTO results VALUES('$step-global',$Im)}
puts [db eval "SELECT data FROM results WHERE key='$step-global'"]
db close
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

On 2026-04-29, Rich <rich@example.invalid> wrote:

meshparts <alexandru.dadalau@meshparts.de> wrote:

Well, the size of the list is anything between 1e3 to 1e9 as an order of
magnitude.

I presume you mean 10e3 to 10e9, as 1 to the third power, and 1 to the
ninth power, are both one... :)

Look up scientific notation.


--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

* meshparts <alexandru.dadalau@meshparts.de>
| Am 30.04.2026 um 00:22 schrieb Rich:
| > The key column contains the literal string "$step-global", which does
| > not match "1-global", so sqlite returns an empty result.
| > That's why you are getting empty string, you are not selecting using
| > the string that actually exists in the key column.
| Yes, I also get it now. Which means, that the key argument is
| interpreted differently than the variable argument.

[nitpick] That's not the problem. The difference is not the "key vs
value", but "TCL-Parser vs SQLITE-Parser".

With "$step-global", the TCL parser is used to resolve the construct,
while with {$step-global} the SQLITE parser does the job, and obviously different than the TCL parser.

You could work around this by using

set sqlkey $step-global
db eval {INSERT INTO results VALUES($sqlkey,$Im)}

HTH
R'
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl


meshparts <alexandru.dadalau@meshparts.de> posted:

Am 30.04.2026 um 11:22 schrieb rene:

There is no need to use "" and escape your variables.
This should work, is faster and save (no code injection).
The eval method will parse your string correctly. Note the {}.

db eval {INSERT INTO results VALUES($step-global,$Im)}

HTH
rene

Well, it doesn't.
You can use my demo below to test your proposal.
I get the error "no such column: global" when not using the single
quotes for the key argument.

file delete test.db
set step 1
set Im "1 2 3"
sqlite3 db "test.db"
db eval {CREATE TABLE results(key TEXT, data TEXT)}
db eval {INSERT INTO results VALUES('$step-global',$Im)}
puts [db eval "SELECT data FROM results WHERE key='$step-global'"]
db close

Ok, I have asked on the sqlite mailing list and the answer is:

Only alphanumeric names are allowed. No minus signs in the variable name.

HTH
rene
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

* rene <user4652@newsgrouper.org.invalid>
| > db eval {INSERT INTO results VALUES('$step-global',$Im)}
--<snip-snip>--

| Ok, I have asked on the sqlite mailing list and the answer is:
| Only alphanumeric names are allowed.

So the work-around would be to set a temporary variable to the complete
key, and use that in the statement:

set sqlkey $step-global
db eval {INSERT INTO results VALUES($sqlkey,$Im)}

| No minus signs in the variable name.

Strictly spoken, there are no minus signs in the variable name... :-)

R'
--- Synchronet 3.21f-Linux NewsLink 1.2

From Newsgroup: comp.lang.tcl

Am 30.04.2026 um 15:09 schrieb rene:

Ok, I have asked on the sqlite mailing list and the answer is:

Only alphanumeric names are allowed. No minus signs in the variable name.

Many thanks and good to know.
In the mean while I have implemented another solution that write my own
binary data to a file.
--- Synchronet 3.21f-Linux NewsLink 1.2