sqlite3_stmt limitations

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
10 messages Options
Reply | Threaded
Open this post in threaded view
|

sqlite3_stmt limitations

heribert
Hello,

in my mfc application are 100-10,000 object properties (depending on app
setup) used by two threads. The properties are not threadsafe (most of
them are doubles or int). Upto now (15 years running code) there was no
problem. Only one thread writes to the properties.
Main thread accesses the properties every 5 msec. The second one
accesses on demand (every 50-2000 msec).

Now i have to add an http server interface to my application. The server
is running, but i'm not sure, if the last 15 years of not threadsafe
access will be okay any longer. So i think about to store the properties
in a sqlite memory database. The threads prepares their own
sqlite3_stmt's with select statements to the properties currently needed
- to have fast access to the database values.

My question: Is their any limitation of sqlite3_stmt bound to a
database? How much memory is used by a sqlite3_stmt?

Thx for any hint
heribert
_______________________________________________
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: sqlite3_stmt limitations

Clemens Ladisch
heribert wrote:
> The threads prepares their own sqlite3_stmt's with select statements
> to the properties currently needed

A single statement "SELECT Value FROM T WHERE Name = ?" might suffice.

> Is their any limitation of sqlite3_stmt bound to a database?

Only memory.

> How much memory is used by a sqlite3_stmt?

Not very much.  But preparing a statement is very fast; don't try to be
too clever.


Regards,
Clemens
_______________________________________________
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: sqlite3_stmt limitations

heribert
@Clemens: You wrote

"Not very much.  But preparing a statement is very fast; don't try to be
too clever."

What do you mean with "don't try to be too clever"? Is preparing for
reuse not really necessary?

The select will be like "SELECT Value FROM RgbValues WHERE Object=? AND
Property=?".
Will it be better to prepare the sqlite3_stmt and reuse it? Or
ondemand:  prepare, use and close the sqlite3_stmt. Is the
time-consuming of parsing a statement like above too high, so it will be
better to reuse the sqlite3_stmt (so i will have thousends prepared)?

Thx
heribert

> heribert wrote:
>> The threads prepares their own sqlite3_stmt's with select statements
>> to the properties currently needed
> A single statement "SELECT Value FROM T WHERE Name = ?" might suffice.
>
>> Is their any limitation of sqlite3_stmt bound to a database?
> Only memory.
>
>> How much memory is used by a sqlite3_stmt?
> Not very much.  But preparing a statement is very fast; don't try to be
> too clever.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
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: [EXTERNAL] sqlite3_stmt limitations

Hick Gunter
In reply to this post by heribert
If your "properties" are independant of each other and the reader thread accessing intermediate values is not a problem, you can just continue (good luck is bound to run out in 15 years of non threadsafe operation).

Consider using atomic instructions to read/update single properties. If properties are required to conform to certain conditions (e.g. changing the value of one property requires a corresponding opposing change in another), the classical way would be to require that a mutex be obtained before reading and/or updating any of the "linked" properties and only released when the thread has finished whatever it was doing to them.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von heribert
Gesendet: Mittwoch, 20. September 2017 08:28
An: [hidden email]
Betreff: [EXTERNAL] [sqlite] sqlite3_stmt limitations

Hello,

in my mfc application are 100-10,000 object properties (depending on app
setup) used by two threads. The properties are not threadsafe (most of them are doubles or int). Upto now (15 years running code) there was no problem. Only one thread writes to the properties.
Main thread accesses the properties every 5 msec. The second one accesses on demand (every 50-2000 msec).

Now i have to add an http server interface to my application. The server is running, but i'm not sure, if the last 15 years of not threadsafe access will be okay any longer. So i think about to store the properties in a sqlite memory database. The threads prepares their own sqlite3_stmt's with select statements to the properties currently needed
- to have fast access to the database values.

My question: Is their any limitation of sqlite3_stmt bound to a database? How much memory is used by a sqlite3_stmt?

Thx for any hint
heribert
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
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: sqlite3_stmt limitations

Clemens Ladisch
In reply to this post by heribert
heribert wrote:
> "Not very much.  But preparing a statement is very fast; don't try to be too clever."
>
> What do you mean with "don't try to be too clever"? Is preparing for reuse not really necessary?

In many cases, the difference will not be noticeable.

I was warning against adding complexity to handle the caching of many
prepared statements.

> The select will be like "SELECT Value FROM RgbValues WHERE Object=? AND Property=?".
> Will it be better to prepare the sqlite3_stmt and reuse it?

Yes; for a single statement, this is likely to be simpler, too.


Regards,
Clemens
_______________________________________________
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: [EXTERNAL] Re: sqlite3_stmt limitations

Hick Gunter
In reply to this post by heribert
Make sure each thread has ist own private connection to the SQLite database (see https://sqlite.org/inmemorydb.html)

Prepare the statement once in each reader thread and use the bind functions to set the constraint values

The writer thread will need to prepare statements to populate the db too.

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von heribert
Gesendet: Mittwoch, 20. September 2017 09:09
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] sqlite3_stmt limitations

@Clemens: You wrote

"Not very much.  But preparing a statement is very fast; don't try to be too clever."

What do you mean with "don't try to be too clever"? Is preparing for reuse not really necessary?

The select will be like "SELECT Value FROM RgbValues WHERE Object=? AND Property=?".
Will it be better to prepare the sqlite3_stmt and reuse it? Or
ondemand:  prepare, use and close the sqlite3_stmt. Is the time-consuming of parsing a statement like above too high, so it will be better to reuse the sqlite3_stmt (so i will have thousends prepared)?

Thx
heribert

> heribert wrote:
>> The threads prepares their own sqlite3_stmt's with select statements
>> to the properties currently needed
> A single statement "SELECT Value FROM T WHERE Name = ?" might suffice.
>
>> Is their any limitation of sqlite3_stmt bound to a database?
> Only memory.
>
>> How much memory is used by a sqlite3_stmt?
> Not very much.  But preparing a statement is very fast; don't try to be
> too clever.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
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: [EXTERNAL] Re: sqlite3_stmt limitations

Hick Gunter
In reply to this post by heribert
SQLite will block access to the whole database during a write operation. Readers will have to wait until the write completes, even if the object they are interested in is not affected by updates. Protecting each object's data with a posix read-write lock will allow readers to access any object that is not currently being modified by the writer without delay, giving potentially much greater concurrency..

-----Ursprüngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von heribert
Gesendet: Mittwoch, 20. September 2017 09:09
An: [hidden email]
Betreff: [EXTERNAL] Re: [sqlite] sqlite3_stmt limitations

@Clemens: You wrote

"Not very much.  But preparing a statement is very fast; don't try to be too clever."

What do you mean with "don't try to be too clever"? Is preparing for reuse not really necessary?

The select will be like "SELECT Value FROM RgbValues WHERE Object=? AND Property=?".
Will it be better to prepare the sqlite3_stmt and reuse it? Or
ondemand:  prepare, use and close the sqlite3_stmt. Is the time-consuming of parsing a statement like above too high, so it will be better to reuse the sqlite3_stmt (so i will have thousends prepared)?

Thx
heribert

> heribert wrote:
>> The threads prepares their own sqlite3_stmt's with select statements
>> to the properties currently needed
> A single statement "SELECT Value FROM T WHERE Name = ?" might suffice.
>
>> Is their any limitation of sqlite3_stmt bound to a database?
> Only memory.
>
>> How much memory is used by a sqlite3_stmt?
> Not very much.  But preparing a statement is very fast; don't try to be
> too clever.
>
>
> Regards,
> Clemens
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick
Software Engineer
Scientific Games International GmbH
FN 157284 a, HG Wien
Klitschgasse 2-4, A-1130 Vienna, Austria
Tel: +43 1 80100 0
E-Mail: [hidden email]

This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.


_______________________________________________
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: [EXTERNAL] Re: sqlite3_stmt limitations

heribert
In reply to this post by Hick Gunter
Do i have to open a database connection for each threat? Like

rc = sqlite3_open("file::memory:?cache=shared", &db);


Am 20.09.2017 um 09:16 schrieb Hick Gunter:

> Make sure each thread has ist own private connection to the SQLite database (see https://sqlite.org/inmemorydb.html)
>
> Prepare the statement once in each reader thread and use the bind functions to set the constraint values
>
> The writer thread will need to prepare statements to populate the db too.
>
> -----Ursprüngliche Nachricht-----
> Von: sqlite-users [mailto:[hidden email]] Im Auftrag von heribert
> Gesendet: Mittwoch, 20. September 2017 09:09
> An: [hidden email]
> Betreff: [EXTERNAL] Re: [sqlite] sqlite3_stmt limitations
>
> @Clemens: You wrote
>
> "Not very much.  But preparing a statement is very fast; don't try to be too clever."
>
> What do you mean with "don't try to be too clever"? Is preparing for reuse not really necessary?
>
> The select will be like "SELECT Value FROM RgbValues WHERE Object=? AND Property=?".
> Will it be better to prepare the sqlite3_stmt and reuse it? Or
> ondemand:  prepare, use and close the sqlite3_stmt. Is the time-consuming of parsing a statement like above too high, so it will be better to reuse the sqlite3_stmt (so i will have thousends prepared)?
>
> Thx
> heribert
>> heribert wrote:
>>> The threads prepares their own sqlite3_stmt's with select statements
>>> to the properties currently needed
>> A single statement "SELECT Value FROM T WHERE Name = ?" might suffice.
>>
>>> Is their any limitation of sqlite3_stmt bound to a database?
>> Only memory.
>>
>>> How much memory is used by a sqlite3_stmt?
>> Not very much.  But preparing a statement is very fast; don't try to be
>> too clever.
>>
>>
>> Regards,
>> Clemens
>> _______________________________________________
>> sqlite-users mailing list
>> [hidden email]
>> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
>
> ___________________________________________
>   Gunter Hick
> Software Engineer
> Scientific Games International GmbH
> FN 157284 a, HG Wien
> Klitschgasse 2-4, A-1130 Vienna, Austria
> Tel: +43 1 80100 0
> E-Mail: [hidden email]
>
> This communication (including any attachments) is intended for the use of the intended recipient(s) only and may contain information that is confidential, privileged or legally protected. Any unauthorized use or dissemination of this communication is strictly prohibited. If you have received this communication in error, please immediately notify the sender by return e-mail message and delete all copies of the original communication. Thank you for your cooperation.
>
>
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
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: [EXTERNAL] Re: sqlite3_stmt limitations

Dominique Devienne
On Wed, Sep 20, 2017 at 1:41 PM, heribert <[hidden email]> wrote:

> Do i have to open a database connection for each threat? Like
>
> rc = sqlite3_open("file::memory:?cache=shared", &db);


I believe so, yes. --DD

PS: See also this thread:
http://sqlite.1065341.n5.nabble.com/Multiple-in-memory-database-table-query-td88861.html
_______________________________________________
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: [EXTERNAL] Re: sqlite3_stmt limitations

R Smith
On Wed, Sep 20, 2017 at 1:41 PM, heribert<[hidden email]>  wrote:

> Do i have to open a database connection for each threat?

Only if you work at the NSA.



(It's the answer everyone else wanted to post, but maturely refrained from doing... I was weak, sorry!)

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users