Re: [EXTERNAL] Re: Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

WHERE x IN carray($PTR, $DIM)

With $PTR being the address of the array and $DIM ist cardinality. Should be tons faster than parsing and binding a gazillion parameters.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[email protected]] Im Auftrag von Deon Brewis
Gesendet: Dienstag, 4. Februar 2020 20:14
An: SQLite mailing list <[email protected]>
Betreff: [EXTERNAL] Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

WHERE x IN (?1,?2,?3,?4...,?1000 )

And the IN clause is filled by a list or array that's held inside the calling application memory rather than in SQLITE.

The alternate to this is to create a virtual table wrapper over the internal datasets of the app. Which is of course better, but harder. (We need an STL for SQLite. SqliteTL?).

PS: Doesn't SQLITE internally order an IN list and do a join across it? It seems to perform better than I would expect from a flat array.

- Deon

-----Original Message-----
From: sqlite-users <[email protected]> On Behalf Of Simon Slavin
Sent: Tuesday, February 4, 2020 10:59 AM
To: SQLite mailing list <[email protected]>
Subject: Re: [sqlite] Please increase the default for SQLITE_MAX_VARIABLE_NUMBER

On 4 Feb 2020, at 6:27pm, Alex Bronstein <[email protected]> wrote:

> In such cases, you can easily end
> up with more than 999 parameters.

I'm curious.  Can you show us some place where using 999 parameters is a reasonable way to use SQLite ?

> PostgreSQL and some other databases support a 16 bit parameter count (64K parameters). Given current memory availability, can sqlite's default be raised to something similar to that?

Might help to know that using the 64K'th parameter would cause SQLite to maintain a list 64K items long.  It's an array, not a key/value lookup.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[email protected]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users