Re: [sqlite] Memory usage when increasing SQLITE_MAX_VARIABLE_NUMBER to 32768

[Keith Medcalf]

On Tuesday, 14 January, 2020 16:04, Simon Slavin  wrote:

>On 14 Jan 2020, at 10:56pm, Alexandre Doubov  
>wrote:

>> 1) Does the act of bumping this limit up have an effect on memory at
>all (assuming that no more than 999 arguments are passed into
>statements)?

>Section 9:

>

>If I read this correctly, memory is reserved only to the highest number
>you actually bind to, not to the number you set as
>SQLITE_MAX_VARIABLE_NUMBER.

You mean of course COULD bind to, or rather the number of bind parameter slots 
used in the prepared statement, not the number that you actually bind to.  For 
example, you could prepare the following statement:

select * from t where x == ?473

which will require the space that the required 473 bound variable "slots" take, 
even though all those slots are pointing to nothing (which is interpreted as a 
null value).  If you bind something to one of these slots you will also consume 
whatever space is required for the value object that you created for that slot 
to point at.

That is, when you call the one of the sqlite3_bind* interfaces you are 
requiring SQLite to do two things:
(1) convert your "external" data that you passed into an internal value object 
of some description
(2) having the (already allocated slot) point to that internal value object 
(and releasing the value object that slot was previously pointing to, if any)

When you do an sqlite3_clear_bindings the internal value objects pointed to by 
the slots is released, and the slot it set to point to nothing.
When you do an sqlite3_finalize on the prepared statement, the bindings are 
cleared (as above) and the whole statement and slot management space is 
released.

So in the above select, when it is prepared, you can expect that it will 
require space for 473 pointers to represent the parameter slots, plus of course 
whatever space is required for ancilliary management stuff such as the name <-> 
slot mapping hash table for the non-anonymous parameters.  This storage is 
allocated at prepare time and is not released until the statement is finalized. 
 SQLITE_MAX_VARIABLE_NUMBER operates so as to place an upper bound on the size 
of this array.

-- 
The fact that there's a Highway to Hell but only a Stairway to Heaven says a 
lot about anticipated traffic volume.



___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Memory usage when increasing SQLITE_MAX_VARIABLE_NUMBER to 32768

[Simon Slavin]

On 14 Jan 2020, at 10:56pm, Alexandre Doubov  wrote:

> 1) Does the act of bumping this limit up have an effect on memory at all 
> (assuming that no more than 999 arguments are passed into statements)?

Section 9:



If I read this correctly, memory is reserved only to the highest number you 
actually bind to, not to the number you set as SQLITE_MAX_VARIABLE_NUMBER.
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Memory usage when increasing SQLITE_MAX_VARIABLE_NUMBER to 32768

[Alexandre Doubov]

Hello,

I have a few questions with regards to memory impact when bumping the
default SQLITE_MAX_VARIABLE_NUMBER limit from 999 to 32768.

For reference, this is the issue that I created that brought me to this
mailing list:
https://github.com/requery/sqlite-android/issues/124

1) Does the act of bumping this limit up have an effect on memory at all
(assuming that no more than 999 arguments are passed into statements)? I'm
assuming that's not the case and that the extra memory consumption comes
from the actual arguments passed into sqlite statements that SQLite needs
to allocate memory for. So if the limit bumped up but the actual # of
arguments that's passed in is always less than 1000, then there's no memory
penalty to bumping this limit up. Is that correct?
2) If we pass the MAX # of variables, what's the overall memory
consumption? For example, if we pass 36 character Strings (32768 of them),
will SQLite allocate 18 MBs for this data? (how I arrived at 18 MBs can be
seen in the linked github issue)

Thanks,
Alex
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users