Prepared Statement Cache breaks parallel requests (from ticket)

Joe · 2014-10-13 09:30:11

Hello,
I am posting a problem description as suggested in ticket http://synopse.info/fossil/info/7362951 … 07b1e9aa58 - I am trying to solve a problem with SynDB, more specifically with TSQLVirtualTableExternal. The problem is not concurrent access from different threads (as there would exist two different connections, that is correct). The External connections do use prepared statement cache (SynDBSQLite3 and SynDBZEOS connections). If I do a SQL with multiple JOINs to the same table using the same field constraints with different values, the problem is easily visible. The only solution with current sources is to disable the prepared statement cache completely.

Is there something I am missing?

ab · 2014-10-13 10:38:40

Please take a look at my answer in the ticket.
SynDBZeos, for instance, should use one connection per thread, so there should be no issue about concurrency.

How are you running your queries?
Are you storing the statement in a variable, then reuse it? This is not how you should use it - but retrieve the statement from SQL before every execution.
If you are using the ORM, there should not be any issue if you use inlined parameters, since e.g. in mORMotSQLite3.pas the virtual table mechanism is always running the SQLite3 virtual process within a DB.Lock/DB.Unlock block.

Joe · 2014-10-13 11:12:18

I have read the answer. The problem is NOT in threading. Each thread has its own connection.

I run a SQL query directly to SQLite. The problem lies in the data fetch process: three instances of TSQLVirtualTableCursorExternal are created and initialized by method Search(): one for a master table A and two for a detail table B (using the same SQL for external data fetch). The joined data is not fetched correctly, because SQLite fetches the external records one-by-one and the second detail B conflicts with the first detail B. Both fetches of external detail data is supposed to return one record (JOIN by key). The problem in detail is that Cursor.Search() for first detail B prepares the statement and fetches first (the only one) record, then the second detail B is prepared by Cursor.Search() doing the same and effectively dumping data of first detail B. The column values are retrieved after all this, thus giving invalid data for the first detail B.

Last edited by Joe (2014-10-13 11:24:02)

ab · 2014-10-13 12:59:13

Makes better sense now.
So, in your use case, the same prepared statement is shared by two virtual cursors instances, so the failure is in mORMotDB cursor implementation?

How does your joined SQL query look like?
I still can't understand what are those two cursors for detail table B.*

Joe · 2014-10-13 14:16:38

Yes, that is it.

The query in question is:

select A.*,B.value,C.value from MAIN_TABLE as A 
left outer join DETAIL_TABLE as B on B.id=A.field1 
left outer join DETAIL_TABLE as C on C.id=A.field2 
where A.id=:id

Best Regards, Joe

ab · 2014-10-13 14:42:58

There should indeed be a problem in this case.

As a temporary workaround (given the time I will fix it), I suggest you switch to direct execution to the external engine, which would in all cases be much faster than running such a complex JOINed query through the virtual table mechanism of SQlite3. In fact, virtual cursors would need several database round trip, so a direct SQL execution on the external engine would definitively be faster.

In a first sight, I think your proposal fix is just correct.
Disabling the cache in this case should be a good idea.
See http://synopse.info/fossil/info/f844361743

But I would try something better, e.g. allowing several statements in the SynDB statement cache.
I've therefore enhanced TSQLDBConnection.NewStatementPrepared() so that up to 9 statements can be cached now for the same SQL - huge speed improvement in respect to simple cache disabling.
See http://synopse.info/fossil/info/f73b2d7a74

Joe · 2014-10-13 15:47:05

Thank you very much for the fix.

ab · 2014-10-13 16:27:54

You are welcome!

Please give some feedback, and if it is working as expected.
Also about performance information, especially if you managed to run the JOINed query directly to the external DB, or via the SQlite3 virtual tableS.

Joe · 2014-10-14 14:13:36

It is working perfectly now. I am running it through the SQLite3 virtual tables and the performance is good for my purposes. Thanks again for the improved fix.

mORMot Open Source

#1 2014-10-13 09:30:11

Prepared Statement Cache breaks parallel requests (from ticket)

#2 2014-10-13 10:38:40

Re: Prepared Statement Cache breaks parallel requests (from ticket)

#3 2014-10-13 11:12:18

Re: Prepared Statement Cache breaks parallel requests (from ticket)

#4 2014-10-13 12:59:13

Re: Prepared Statement Cache breaks parallel requests (from ticket)

#5 2014-10-13 14:16:38

Re: Prepared Statement Cache breaks parallel requests (from ticket)

#6 2014-10-13 14:42:58

Re: Prepared Statement Cache breaks parallel requests (from ticket)

#7 2014-10-13 15:47:05

Re: Prepared Statement Cache breaks parallel requests (from ticket)

#8 2014-10-13 16:27:54

Re: Prepared Statement Cache breaks parallel requests (from ticket)

#9 2014-10-14 14:13:36

Re: Prepared Statement Cache breaks parallel requests (from ticket)

Board footer