Hello, I wonder if anyone could shed some light on this. I am using sqlite in my client programs to access databases over the network. I know sqlite isn't recommended for this usage model, but I am happy that I have implemented it such that only 1 client ever has write access, and am also happy with the performance (speed) over the network in normal situations.
However, I've noticed that when 2 clients have attached to the same database (where the database is on a remote machine, *not local*) then I get a significant slowdown when querying that database - the sqlite3_step function takes longer to complete. If only 1 client is attached to the database, the query performs perfectly fast, so it's definitely not just network latency. Can anyone explain what sqlite is doing and if there is anything I can do to avoid this issue? Note I am querying a table which contains blobs of data. I think there is a slowdown on querying tables without blobs in it, but it is certainly very obviously slower on the table with blobs. You can reproduce this using the sqlite3 cmd line program (v3.5.6) and creating 2 databases where the 2nd database has a table something like: create table ImageData(ID integer primary key, nRows integer, nCols integer, imageMtx blob, palette blob, nGridRows integer, nGridCols integer, gridMtx blob); and add some data to the table First db connection open test1.db with sqlite3.exe (test1.db on remote machine) attach test2.db as currentAnalysis select nGridRows, nGridCols, gridMtx from currentAnalysis.ImageData ---------- data is returned instantaneously Second db connection now open test1.db with another instance of sqlite3.exe (test1.db on remote machine) attach test2.db as currentAnalysis now go back to the first db connection, repeat the query and notice the results returned after 6 or so seconds if you detach the database from the second db connection, and run the query again on the first db connection, it's still slow if you now detach and reattach the test2.db database in the first db connection and run the query again, it's fast again. Apologies for the very long description - I hope I've explained the situation clearly enough. I may legitimately have 2 clients reading the same database at the same time, and it's very frustrating for my client program to slow down in this situation. Could anyone explain if there is a reason sqlite is doing this and if I can avoid it? many thanks, Serena. _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
