Hi
I've encountered a big problem which I failed to solve and I'm writing here
to get some advice.
But let's begin from the start. I use WAL journal mode in most of my
applications including WinCE 4/20 based devices. I know that it's not
supported, but using simple trick it actually works with C++ based
applications, because I get 36%+ performance when writing data. This is how
I modified winShmSystemLock function:
static int winShmSystemLock(
winShmNode *pFile, /* Apply locks to this open shared-memory segment */
int lockType, /* _SHM_UNLCK, _SHM_RDLCK, or _SHM_WRLCK */
int ofst, /* Offset to first byte to be locked/unlocked */
int nByte /* Number of bytes to lock or unlock */
){
// PATCH START: _WIN32_CE/Lock/Unlock file: Grzegorz Russek
int rc = 0; /* Result code form Lock/UnlockFileEx() */
#if SQLITE_OS_WINCE == 0
// PATCH END: _WIN32_CE/Lock/Unlock file: dr4cul4
OVERLAPPED ovlp;
DWORD dwFlags;
// PATCH START: _WIN32_CE/Lock/Unlock file: dr4cul4
//int rc = 0; /* Result code form Lock/UnlockFileEx() */
// PATCH END: _WIN32_CE/Lock/Unlock file:
dr4cul4
/* Access to the winShmNode object is serialized by the caller */
assert( sqlite3_mutex_held(pFile->mutex) || pFile->nRef==0 );
/* Initialize the locking parameters */
dwFlags = LOCKFILE_FAIL_IMMEDIATELY;
if( lockType == _SHM_WRLCK ) dwFlags |= LOCKFILE_EXCLUSIVE_LOCK;
memset(&ovlp, 0, sizeof(OVERLAPPED));
ovlp.Offset = ofst;
/* Release/Acquire the system-level lock */
if( lockType==_SHM_UNLCK ){
rc = UnlockFileEx(pFile->hFile.h, 0, nByte, 0, &ovlp);
}else{
rc = LockFileEx(pFile->hFile.h, dwFlags, 0, nByte, 0, &ovlp);
}
if( rc!= 0 ){
rc = SQLITE_OK;
}else{
pFile->lastErrno = GetLastError();
rc = SQLITE_BUSY;
}
OSTRACE(("SHM-LOCK %d %s %s 0x%08lx\n",
pFile->hFile.h,
rc==SQLITE_OK ? "ok" : "failed",
lockType==_SHM_UNLCK ? "UnlockFileEx" : "LockFileEx",
pFile->lastErrno));
// PATCH START: _WIN32_CE/Lock/Unlock file: dr4cul4
#endif
// PATCH END: _WIN32_CE/Lock/Unlock file: dr4cul4
return rc;
}
As you can see I just ignore locking and it works like a charm, but recently
I was writing new project in Compact Framework so I used System.Data.SQLite
provider. It was working quite well but we got a performance issue. Problem
is that we need to insert about 27000 rows into one table which is also
quite wide (about 25 columns, mostly text). Those rows come to application
from a web service which we can't replace with something else.
I modified compilation flags and added above modification and it doesn't
work... actually crashes. I wrote the same inserting code in C++ to check if
it's possible with that version of library and it works. So I decided to
write here because I cant find a reason for this behavior. SQLite throws
error in one of the first statements (randomly sometimes first sometimes
third). I pinpointed location in code:
Error occurs in winShmMap function. System function CreateFileMapping
returns NULL and GetLastError() returns 6 (Invalid Handle) which results
in SQLITE_IOERR error. This happens on 3.7.0.1 and 3.7.6.3, but I suppose
issue will also happen on all other versions.
To specify my needs:
1. Is there a way to overcome this obstacle and enable WAL journal mode
using >NET Compact Framework provider (target platform is still WinCE 4.20,
so I can't switch to WinCE 5)
2. Is there a way to insert 27000 rows under 3 minutes (200MHz Motorolla
device - daemon of slowness, both CPU and Flash)
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
