Memory usage when increasing SQLITE_MAX_VARIABLE_NUMBER to 32768

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Memory usage when increasing SQLITE_MAX_VARIABLE_NUMBER to 32768

Simon Slavin-3
On 14 Jan 2020, at 10:56pm, Alexandre Doubov <[hidden email]> 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:

<https://sqlite.org/limits.html>

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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: Memory usage when increasing SQLITE_MAX_VARIABLE_NUMBER to 32768

Keith Medcalf

On Tuesday, 14 January, 2020 16:04, Simon Slavin <[hidden email]> wrote:

>On 14 Jan 2020, at 10:56pm, Alexandre Doubov <[hidden email]> 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:

><https://sqlite.org/limits.html>

>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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users