Re: [sqlite] benchmarking UTF8 vs UTF16 encoded databases
On Nov 22, 2007 1:04 PM, Daniel Önnerby <[EMAIL PROTECTED]> wrote: > In the future I am using UTF8 encoded databases since the conversion of > strings is a small thing for the system. The advantages of using UTF8 > are many: > 1. Faster in most cases > 2. Smaller databases (30% smaller in benchmark test database) > 3. Less memory usage OR more information will fit in memory. Well of course it comes at no surprise that if your database is primarily US-ASCII text, UTF-8 will be better. Smaller sizes mean smaller comparisons and more packed b-trees. UTF-16 is only good if you have a lot of text that would be encoded with >= 2 UTF-8 code units. -- Cory Nelson
Re: [sqlite] Sqlite version for libc 2.1.3
On 11/22/07, Tara_Nair <[EMAIL PROTECTED]> wrote: > I just recently started to use Sqlite3 version 3.5.2 on an ARM-LINUX > based embedded platform. > Sqlite3-v3.5.2. has a dependency on libc, ld and libpthread versions 2.2.3. Er.. it should depend on whatever versions you link it against when you cross-compile for your platform. How/where did you get the version of SQLite you're trying to use now? - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Sqlite version for libc 2.1.3
Hello all, I just recently started to use Sqlite3 version 3.5.2 on an ARM-LINUX based embedded platform. Sqlite3-v3.5.2. has a dependency on libc, ld and libpthread versions 2.2.3. My sorrow is that the rest of my application and the libraries on my ramdisk are of version 2.1.3. It doesn't seem to be a simple task to update the ramdisk and possibly other software to be compatible with the latest versions of these libraries. I was wondering if anybody knew if there exists an older version of sqlite3 which uses 2.1.3 versions of libc,ld and libpthread. Would be very grateful, if anyone could help with a version number and where I could download it from. Thanks in advance. Tara. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] wrong version number when compiled
Hi all: I've just downloaded, compiled and installed SQLite3 3.5.2 on my mac. Everything is working fine but the version number. I know OS X 10.4 comes with SQLite built-in on /usr/bin. I installed 3.5.2 on /usr/local and updated my PATH so that is all right. Look at what I get: xabita:/ xabriel$ which sqlite3 /usr/local/bin/sqlite3 xabita:/ xabriel$ sqlite3 -version 3.1.3 As mentioned previously my PATH is all right: xabita:/ xabriel$ echo $PATH /usr/local/bin:/usr/local/sbin:/usr/local/mysql/bin:/bin:/sbin:/usr/bin:/usr/sbin Something weird is that 3.1.3 is the version that my Mac has built-in: xabita:/ xabriel$ /usr/bin/sqlite3 -version 3.1.3 So, do somebody see what I am missing? thanks, regards, -- Xabriel J. Collazo-Mojica B.S. in Computer Engineering Student University of Puerto Rico at Mayagüez http://xabrielc.googlepages.com
[sqlite] Reported Error with SQLITE_MEMORY_SIZE
3.5.2 from the ZIP file,
Compiled under Visual Studio 2005.
Static Library
SQLITE_OMIT_AUTHORIZATION
SQLITE_OMIT_AUTOVACUUM
SQLITE_OMIT_BLOB_LITERAL
SQLITE_OMIT_DATETIME_FUNCS
_CRT_SECURE_NO_DEPRECATE
SQLITE_MEMORY_SIZE=100
SQLITE_CORE
A couple weeks ago I reported a crash when I used the
SQLITE_MEMORY_SIZE=100 compile time setting. I've now gotten a
chance to look at it further. The reply on the list was it's working
fine.
I have several small DB's that I vacuum during startup. In most cases
the vacuum works perfectly OK with this setting and there are no
crashes. There is one DB though that, during the vacuum,
It gets to here
int sqlite3BtreeRollbackStmt(Btree *p){
int rc = SQLITE_OK;
BtShared *pBt = p->pBt;
sqlite3BtreeEnter(p);
if( pBt->inStmt && !pBt->readOnly ){
rc = sqlite3PagerStmtRollback(pBt->pPager);
assert( countWriteCursors(pBt)==0 );
pBt->inStmt = 0;
}
sqlite3BtreeLeave(p);
return rc;
}
If I look at the pPager, I see fd.methods is NULL but, jfd.methods and
stfd.methods aren't NULL.
** Ticket #1171: The statement journal might contain page content that is
** different from the page content at the start of the transaction.
** This occurs when a page is changed prior to the start of a statement
** then changed again within the statement. When rolling back such a
** statement we must not write to the original database unless we know
** for certain that original page contents are synced into the main rollback
** journal. Otherwise, a power loss might leave modified data in the
** database file without an entry in the rollback journal that can
** restore the database to its original form. Two conditions must be
** met before writing to the database files. (1) the database must be
** locked. (2) we know that the original page content is fully synced
** in the main journal either because the page is not in cache or else
** the page is marked as needSync==0.
*/
pPg = pager_lookup(pPager, pgno);
PAGERTRACE4("PLAYBACK %d page %d hash(%08x)\n",
PAGERID(pPager), pgno, pager_datahash(pPager->pageSize, aData));
if( pPager->state>=PAGER_EXCLUSIVE && (pPg==0 || pPg->needSync==0) ){
i64 offset = (pgno-1)*(i64)pPager->pageSize;
rc = sqlite3OsWrite(pPager->fd, aData, pPager->pageSize, offset);
if( pPg ){
makeClean(pPg);
}
}
Here it attempts to write to the OS using pPager->fd and since
fd.methods is null, there's a crash.
I don't know the connection between the memory setting and the
crash. The other DB's don't rollback during the vacuum which
probably explains why they don't crash. I'm going to make a backup
copy of the DB and then try to manually vacuum the file and see if
the crashing goes away.
I'm sitting in a hotel so, my connectivity is spotty. Sorry if any
replies to the list are delayed till Saturday evening.
C
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
[sqlite] benchmarking UTF8 vs UTF16 encoded databases
When I started using SQLite I found it natural to use the sqlite3_open16 and use UTF16 encoding on strings since my applications always use wchar_t when handeling strings. I never questioned this until now when I decided to do some benchmark, and I found it interesting enough to share with you. In my benchmark I used a database with several tables and indexes and the table I decided to benchmark contains 10 columns and 14000 rows with different types. It's a well normalized database that is used in a real life application. The benchmark is made on 2 different databases that are identical except for the fact that one is UTF8 encoded and the other is UTF16 encoded. I always get the 2 columns using sqlite3_column_text16 - so when getting the string from the UTF8 database - a conversion is made, but the output strings from both databases are always the same. The benchmark is looped 10 times for better average results. Benchmark 1: Selecting 2 columns from the table without any WHERE or ORDER BY UTF8.db0.38s UTF16.db 0.33s As expected the UTF16 encoded database is a little bit faster since no conversion is made. The difference is: 15% slower using UTF8 encoding. Benchmark 2: Selecting 2 columns from the table without and WHERE, but with ORDER BY on a text-column without any index (slow) UTF8.db 4.34s UTF16.db11.19s Well, this is a slow query. Sorting a UTF8 encoded string is obviously a lot faster than sorting a UTF16 encoded string. The conversion done by sqlite3_column_text16 is not noticeable in this benchmark. Difference: 66% faster using UTF8 encoding. Benchmark 3: Selecting 2 columns from the table without any WHERE, but with ORDER BY on text-column WITH index. UTF8.db 0.58s UTF16.db 0.63s Interesting. I guess the conversion done by sqlite3_column_text16 is not noticeable compared to the extra disk/mem IO for the extra data using UTF16. Difference: 8% faster using UTF8 encoding. In the future I am using UTF8 encoded databases since the conversion of strings is a small thing for the system. The advantages of using UTF8 are many: 1. Faster in most cases 2. Smaller databases (30% smaller in benchmark test database) 3. Less memory usage OR more information will fit in memory. I forgot to tell you that the benchmark is made on windows XP. The conversion done in sqlite3_column_text16 may be a lot slower/faster on any other platform. Best regards Daniel - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite Announcements
At 2:47 PM + 11/22/07, Alberto Simões wrote: Is it just me, or SQLite announcements for new releases are not being sent to this list? At least I didn't see 3.5.0, 3.5.1 and 3.5.2 announcements. I am aware of them just because complain about them :) I saw the 3.5.0 announcement on this list, but no announcements for 3.5.1 nor 3.5.2, which was part of the reason I previously stated I hadn't realized that 3.5.x had come out of alpha. -- Darren Duncan - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQL error: database disk image is malformed - SQLITE3.5.1
-Original Message- From: Dennis Cote [mailto:[EMAIL PROTECTED] Sent: quinta-feira, 22 de novembro de 2007 12:58 To: sqlite-users@sqlite.org Subject: Re: [sqlite] SQL error: database disk image is malformed - SQLITE3.5.1 > > > There is a good description of the things that can cause database > corruption at http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption > which may give you some ideas for things to check. > Your program can check the database on startup by executing a "pragma > integrity_check;" command just like you did from the command line. > HTH Dennis Cote Thanks for the help. Joaquim - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] integrity_check needs too much RAM on WinCE
Dennis Cote schrieb: > Thomas Damme wrote: >> We set "page_size" and "cache_size" both at 8KB because it had the best >> performance on the device. >> > Do you have 64 MB of ram to dedicate to sqlite for its cache? That's > what you are telling it by setting the cache to 8K pages of 8K each. No we don't. But selects, updates and inserts work as expected. Also for a long time. That's why we choosed this configuration. Though there are not many during regular usage. Anyway, thanks for the reminder. I read that during the development but it seemed to have no effect(beside the increased performance). We are experimenting with these values again right now. Regards, -- Thomas Damme - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] integrity_check needs too much RAM on WinCE
Thomas Damme wrote: We set "page_size" and "cache_size" both at 8KB because it had the best performance on the device. Do you have 64 MB of ram to dedicate to sqlite for its cache? That's what you are telling it by setting the cache to 8K pages of 8K each. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] integrity_check needs too much RAM on WinCE
Oh, maybe I should also tell you this: We set "page_size" and "cache_size" both at 8KB because it had the best performance on the device. Thomas Damme schrieb: > Hello Folks, > > we are running a SQLite-Database(version 3.3.5) on a > WinCE-device(version 4.2). The database is about 90 MB and resides on a > USB-Stick. The database itself contains of about 500 records with BLOBs > from 1KB to 100 KB. > > When we are running an "PRAGMA integrity_check" the device stops and > says "Not enough Program-Memory" in a system-dialogue. So I assume, > SQLite ate up all dynamic RAM for the check and cannot complete. > > Approx. how much RAM is needed for such a check? Can I prevent this? Is > this influenced by the BLOB-size? > > Thanks a lot. -- Thomas Damme - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] integrity_check needs too much RAM on WinCE
Hello Folks, we are running a SQLite-Database(version 3.3.5) on a WinCE-device(version 4.2). The database is about 90 MB and resides on a USB-Stick. The database itself contains of about 500 records with BLOBs from 1KB to 100 KB. When we are running an "PRAGMA integrity_check" the device stops and says "Not enough Program-Memory" in a system-dialogue. So I assume, SQLite ate up all dynamic RAM for the check and cannot complete. Approx. how much RAM is needed for such a check? Can I prevent this? Is this influenced by the BLOB-size? Thanks a lot. -- Thomas Damme - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQL error: database disk image is malformed - SQLITE 3.5.1
Salles, Joaquim Campos wrote: Now running the command "PRAGMA integrity_check": sqlite> PRAGMA integrity_check; *** in database main *** Main freelist: 138691677 of 4 pages missing from overflow list starting at 2972 On tree page 322 cell 0: 3 of 4 pages missing from overflow list starting at 297 1 Page 2936 is never used Page 2941 is never used Page 2963 is never used Page 2966 is never used Page 2968 is never used Page 2970 is never used sqlite> Running the VACUUM command the sqlite terminate abnormally (in windows). Any one has some idea what I have to check in my program to avoid corruption in database? What I can do, in my program, in startup, to check if the database is ok? There is a good description of the things that can cause database corruption at http://www.sqlite.org/cvstrac/wiki?p=DatabaseCorruption which may give you some ideas for things to check. Your program can check the database on startup by executing a "pragma integrity_check;" command just like you did from the command line. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite Announcements
Hi Is it just me, or SQLite announcements for new releases are not being sent to this list? At least I didn't see 3.5.0, 3.5.1 and 3.5.2 announcements. I am aware of them just because complain about them :) Cheers ambs -- Alberto Simões - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLITE_THREAD_OVERRIDE_LOCK
Hi, I am not clear on when this flag must be enabled. SQLITE_THREAD_OVERRIDE_LOCK What are the implications of enabling this. Thanks, Kiran *** This e-mail and attachments contain confidential information from HUAWEI, which is intended only for the person or entity whose address is listed above. Any use of the information contained herein in any way (including, but not limited to, total or partial disclosure, reproduction, or dissemination) by persons other than the intended recipient's) is prohibited. If you receive this e-mail in error, please notify the sender by phone or email immediately and delete it! ***
[sqlite] SQL error: database disk image is malformed - SQLITE 3.5.1
I'm using SQLITE 3.5.1 - in Linux, Had Red 7.1, with a single thread application - and 2 different programs accessing the same database. I am getting the following message: SQL error: database disk image is malformed. I copy the database to windows and run with sqlite and also get the same message. Running the following select command I get the same message: Select * from IDMsg; SQL error: database disk image is malformed. Now running the command "PRAGMA integrity_check": sqlite> PRAGMA integrity_check; *** in database main *** Main freelist: 138691677 of 4 pages missing from overflow list starting at 2972 On tree page 322 cell 0: 3 of 4 pages missing from overflow list starting at 297 1 Page 2936 is never used Page 2941 is never used Page 2963 is never used Page 2966 is never used Page 2968 is never used Page 2970 is never used sqlite> Running the VACUUM command the sqlite terminate abnormally (in windows). Any one has some idea what I have to check in my program to avoid corruption in database? What I can do, in my program, in startup, to check if the database is ok? Thanks for the help, Joaquim - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] error
Thanks On Nov 22, 2007 6:35 PM, Dennis Povshedny <[EMAIL PROTECTED]> wrote: > Hi! > > Place the limit clause at the end. > > select * from databaseentry where Sub="BarsandPubs" order by Button_Name > limit 5; > > Regards, > Dennis > > > Xeepe Phone Solution Team > http://en.xeepe.com > mailto:[EMAIL PROTECTED] > sip:[EMAIL PROTECTED] > > > -Original Message- > From: nishit sharma [mailto:[EMAIL PROTECTED] > Sent: Thursday, November 22, 2007 3:51 PM > To: sqlite-users@sqlite.org > Subject: [sqlite] error > > > After making command i m getting this error > > select * from databaseentry where Sub="BarsandPubs" limit 5 order by > Button_Name; SQL error: near "order": syntax error can anybody help > > regards > Nishit > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > > - > > No virus found in this incoming message. > Checked by AVG Free Edition. > Version: 7.5.503 / Virus Database: 269.16.3/1144 - Release Date: > 21.11.2007 16:28 > > > No virus found in this outgoing message. > Checked by AVG Free Edition. > Version: 7.5.503 / Virus Database: 269.16.3/1144 - Release Date: > 21.11.2007 16:28 > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] error
Hi! Place the limit clause at the end. select * from databaseentry where Sub="BarsandPubs" order by Button_Name limit 5; Regards, Dennis Xeepe Phone Solution Team http://en.xeepe.com mailto:[EMAIL PROTECTED] sip:[EMAIL PROTECTED] -Original Message- From: nishit sharma [mailto:[EMAIL PROTECTED] Sent: Thursday, November 22, 2007 3:51 PM To: sqlite-users@sqlite.org Subject: [sqlite] error After making command i m getting this error select * from databaseentry where Sub="BarsandPubs" limit 5 order by Button_Name; SQL error: near "order": syntax error can anybody help regards Nishit - To unsubscribe, send email to [EMAIL PROTECTED] - No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.3/1144 - Release Date: 21.11.2007 16:28 No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.5.503 / Virus Database: 269.16.3/1144 - Release Date: 21.11.2007 16:28 - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] error
After making command i m getting this error select * from databaseentry where Sub="BarsandPubs" limit 5 order by Button_Name; SQL error: near "order": syntax error can anybody help regards Nishit - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
Hi Vincent, On 11/21/07, Mitchell Vincent <[EMAIL PROTECTED]> wrote: > I have a primary key that auto increments and has apparently > overlapped back on to itself. > > INSERT into mytable(id,name) values(NULL,'test'); > > .. is giving me "primary key must be unique" errors. > > How can I reset the sequence for a primary key? The table only has > about 15000 records in it and I've never seen this happen before.. Though I haven't used sqlite but apparently you must have stored NULL once into the table and were storing it a second time which led to this error because this violated the primary key constraint. You could have COUNTed NULLs instead on ItemID using different values for other relevant columns to use GROUP BY to check the data yourself and see what was wrong. -- Asif - To unsubscribe, send email to [EMAIL PROTECTED] -
