You are not logged in.
- Topics: Active | Unanswered
#1 2012-11-30 07:44:03
- tamm
- Member
- Registered: 2012-11-29
- Posts: 2
queries slow down with more than 1 client, read only, SynSQlite3
I'm using SynSQlite3 to access a ~6MB database on a fileserver (NAS).
Permormance goes down when more than 1 client accesses the file, even though
the database is not changed. Therefore,
I could make a local copy to a temporary file, and open this db at
program start. Is there a better solution?
I understand that using ":Memory:", I have to fill the db first.
Setting the fileattribute of the db to writeprotected does not change the situation.
Is there something about "mode=" in the URI style, I could do?
Offline
#2 2012-11-30 08:56:10
Re: queries slow down with more than 1 client, read only, SynSQlite3
So you are using a SQLite3 remote database file as a shared database?
This is NOT to be done at all.
See point "6.0 How To Corrupt Your Database Files"' in http://www.sqlite.org/lockingv3.html and more generally read all the page.
If you want fast shared access to SQLite3, use out mORMot RESTful access.
If you do not need the whole ORM part of mORMot, check out the "13 - StandAlone JSON SQL server" sample included in the framework.
My latest advice is to use SynDB classes, i.e. SynDBSQLite3 and not the low-level SynSQLite3 unit, unless you need explicitly SQlite3 features.
Thanks to SynDB classes, your code may switch to another database very easily, with no speed penalty.
If you only want an in-memory copy of a remote SQLite3 file, you can use the backup features of SQLite3.
See http://www.sqlite.org/backup.html
But a local copy of the file will be fast, and easier to implement.
Online
#3 2012-12-05 16:15:49
- tamm
- Member
- Registered: 2012-11-29
- Posts: 2
Re: queries slow down with more than 1 client, read only, SynSQlite3
Thank you very much,
I did not know how to change the page count of a newly created :Memory: db.
I found a different solution, similar to your decryption:
(overwriting WINRead) in SynSQLite3
Copying everything to a MemoryStream, and WinRead gets the data from this stream:
(it works read only and only for one db, filename in SpeiFnam)
********************************************
var SpeiHandle:THandle;
SpeiStream:TMemoryStream;
SpeiFnam:String;
function GetFileSize(hFile: THandle; lpFileSizeHigh: Pointer): DWORD; stdcall;
var siz:Int64;
begin
siz:=SpeiStream.Size;
Result:= Int64Rec(siz).Lo;
PCardinal(lpFileSizeHigh)^:= Int64Rec(siz).Hi;
end;
function LockFile(hFile: THandle; dwFileOffsetLow, dwFileOffsetHigh: DWORD;
nNumberOfBytesToLockLow, nNumberOfBytesToLockHigh: DWORD): BOOL; stdcall;
begin
Result:=True;
end;
function LockFileEx(hFile: THandle; dwFlags, dwReserved: DWORD;
nNumberOfBytesToLockLow, nNumberOfBytesToLockHigh: DWORD;
const lpOverlapped: TOverlapped): BOOL; stdcall;
begin
Result:=True;
end;
function UnlockFile(hFile: THandle; dwFileOffsetLow, dwFileOffsetHigh: DWORD;
nNumberOfBytesToUnlockLow, nNumberOfBytesToUnlockHigh: DWORD): BOOL;stdcall;
begin
Result:=True;
end;
function CloseHandle(hObject: THandle): BOOL; stdcall;
begin
Result:=True;
end;
function WinRead(var F: TSQLFile; buf: PByte; buflen: integer; off: Int64): integer; {$ifndef USEFASTCALL}cdecl;{$endif}
// Read data from a file into a buffer. Return SQLITE_OK on success
// or some other error code on failure
var offset: Int64Rec;
aSQLEncryptTable: PByteArray;
i: integer;
begin
//SynSQLite3Log.Add.Log(sllCustom2,'WinRead % off=% len=%',[F.h,off,buflen]);
if SpeiHandle<>F.h then
begin
SpeiStream:=TMemoryStream.Create;
SpeiStream.LoadFromFile( SpeiFnam);
SpeiHandle:=F.h;
Windows.CloseHandle(F.h);
end;
offset.Lo := Int64Rec(off).Lo;
offset.Hi := Int64Rec(off).Hi and $7fffffff; // offset must be positive (u64)
SpeiStream.Position:=off;
cardinal(Result):= SpeiStream.Read(buf^,buflen);
if Result=0 then
if buflen>0 then
begin
result := SQLITE_FULL;
exit;
end;
...
Offline