CODEWITHSUNDEEP
javasqlitejdbc
Tschallacka
Tschallacka

Reputation: 28722

jdbc keep prepared statement in memory?

I'm using a prepared statement for my sqlite logging database.

My thread runs every 50ms to write things in the log buffer to the database.

Currently i'm making a new prepared statements batch on every thread run and closing them after all the data lines are written.

Now i'm wondering if its better to keep the prepared statement in memory and close it only when the thread closes/is interrupted?

The reason i'm doing this pre-optimisation is because I want this logging thread to be as least as intrusive on main app performance as possible and I can imagine allocating/parsing/verifying resources every 50 seconds may be more intensive than to keep the prepared statement in memory.

In the end I think i'll need about 20-30 prepared statements on my database for the various log types. So would it be better just to initialise these when needed as new prepared statements or should I just initialise them once and keep the prepared statements in memory?

Upvotes: 1

Views: 1276

Answers (2)

Coder_Wolfy
Coder_Wolfy

Reputation: 124

The simple answer for executing multiple Prepared Statement would be making a batch process as simple as adding all the statement to batch and executing all at once by executing batch. this will save processing by executing multiple queries at single instance. You are going ok by update after each data line, by this you will have better log. for every time execution of all this queries together try an while() loop. might help for entering log after every execution of data insertion.

Upvotes: 1

M Sach
M Sach

Reputation: 34424

Here are my two cents :-

If your purpose is just two save opening/closing prepared statement. I don't think you will gain much here comparison memory overhead. Costly operation is opening/closing connection not statement. And i believe you must be already using pooling for that

Also When you use prepared statement, As soon as DB gets this statement, it compiles it and caches it so that it can use the last compiled statement for successive call of same statement. So it becomes pre-compiled for successive calls.so from this perspective keeping in memory does not help here.

Upvotes: 3

Related Questions