Re: [sqlite] threading and win32
SQLite 3.2.6. I will do as both you and Chris say. Thanks. :) Wilson
Re: [sqlite] threading and win32
On 10/19/05, Wilson Yeung <[EMAIL PROTECTED]> wrote: > > I wasn't doing a very good job paying attention to the FAQ about > threading and SQLite when I wrote a bunch of code on win32. > > I'm sharing sqlite3 database handles between threads, although I am > ensuring that no two threads are executing against the sqlite3 > database handle at the same time. > > The threads are in fact interleaving, and the sqlite3 database handle > is indeed being shared between the two threads, and everything is > working as I'd like it to work... > > Is the warning about threads in the FAQ specifically apply to Linux > systems? Or can we pretty much say that my code is going to > eventually break on win32 and I'm lucky that it hasn't already? > > Wilson > What version of sqlite are you using? Newer versions are supposed to complain about being used in two different threads. As Chris said, you should probably follow the documentation on this point, as the behavior of threads is such that errors can often be hidden, until under stress. John
Re: [sqlite] threading and win32
I'm using threads and SQLite on a Win32 system I can tell you that things may work 100% for ages, then one day, the moons align and the threads will fall over each others feet It may happen later, rather than sooner, and may not even happen in your lifetime, but it'll happen :) > I wasn't doing a very good job paying attention to the FAQ about > threading and SQLite when I wrote a bunch of code on win32. > > I'm sharing sqlite3 database handles between threads, although I am > ensuring that no two threads are executing against the sqlite3 > database handle at the same time. > > The threads are in fact interleaving, and the sqlite3 database handle > is indeed being shared between the two threads, and everything is > working as I'd like it to work... > > Is the warning about threads in the FAQ specifically apply to Linux > systems? Or can we pretty much say that my code is going to > eventually break on win32 and I'm lucky that it hasn't already? > > Wilson
[sqlite] threading and win32
I wasn't doing a very good job paying attention to the FAQ about threading and SQLite when I wrote a bunch of code on win32. I'm sharing sqlite3 database handles between threads, although I am ensuring that no two threads are executing against the sqlite3 database handle at the same time. The threads are in fact interleaving, and the sqlite3 database handle is indeed being shared between the two threads, and everything is working as I'd like it to work... Is the warning about threads in the FAQ specifically apply to Linux systems? Or can we pretty much say that my code is going to eventually break on win32 and I'm lucky that it hasn't already? Wilson
RE: [sqlite] Very Slow delete times on larger databases, please help!
On Wed, 19 Oct 2005, Allan, Mark wrote: >We are able to compile our application for both target and host. When >compiled for host the application runs on Win32 and will >create/read/write to a database file on the host PC. > >The performance of the deletions on Win32 will not take the 11 minutes I >specified, this is only a problem for our target. The same operation on >the PC will take only 3-4 seconds. Please note that since my first email >we have tried increasing the page size of SQLite and have increased the >page size from 1024 bytes to 8192 bytes. This has decreased the time to >process the same delete operation from 11 minutes to 3.75 minutes on our >target hardware. Both host and target versions of the software use the >same configuration a page size of 8192 bytes and a cache of 75 pages = >600k. > >The only real differences are 1) the hardware, 2) the filing system. We >would expect a difference in performance as the PC is much faster than >our target hardware and the write speed to NOR flash is comparatively >slow but not as drastic a drop-off as we see. 3-4 seconds -> 3-4 minutes. >From the VDBE output you originally posted, you are doing a fair amount of work for each deleted row: - Index search to find the next row from EXAMINATIONS to delete - Removing the row from 3 indexes on EXAMINATIONS - (trigger) Remove related row in SPIRO_TEST from 2 indexes on SPIRO_TEST - (trigger) Remove related row in SPIRO_TEST - Remove the row from EXAMINATIONS Check your cache size. If the above work is causing the 75 page entry cache to thrash, you're likely to hit worst case performance as the thrashing pages may be being accessed in a cyclical fashion. Not sure how like it is that your page cache is not big enough. How big is a row of data, typically? Also, a 60x slowdown is not to be unexpected. The PC version, while probably having the same SQLite page cache size in the SQLite app itself, will most likely be reading and writing to the OSes cache at memory to memory copy speed most of the time, with synchronous writes only done when needed. The embedded platform you're using probably writes straight to FLASH, which is necassarily a synchronous operation if your OS doesn't have a cache between your app and the FLASH FS. While flash writes are low latency, they are also low bandwidth, and won't be within an order of magnitude of performance when compared to a desktop PC write to OS filesystem cache. Finally, you give no indication on the actual CPU speed of the embedded platform. It's quite reasonable to assume a development PC could be an order of magnitude faster on sheer integer throughput. I'm amazed how slow my 50MHz microSPARC based SPARCclassic is. Such a platform would not be much, if at all, slower than a modern embedded platform, and has the benefit of gobs of RAM, but still runs the same code two orders of magnitude slower at least than my Athlon XP 1700 based desktop. You have to keep your performance expectations realistic. You are, afterall, running a complete, ACID transaction, SQL relational database. > >It may be useful for us to know what SQLite is doing during deletion so >that we can try and optimise our code and/or any configuration of SQLite, >our filesystem code or the hardware to try and get this figure down. Can >anyone give me a reasonably detailed description of what is happening >during delete. The documentation on the website has not helped us >diagnose where our problem lies. Others have indicated that dropping indexes might help when deleting or inserting records. However, have you tried simply not having indexes at all? Would that cause unacceptable slowdown? Perhaps, for the demo query from the original post, just keep the DATE index on EXAMINATIONS, and use full table scans for queries based on EXAM_TYPE and STATUS_FLAG. Truth is, given the small number of EXAM_TYPE and STATUS_FLAG values (I presume), you're as well just doing table scans when looking for specific exam types and statuses. Indexes only really help when you have a large variation in values with few collisions. Doing this will leave a single index update in addition to the actual row removals, which should improve performance. > >Best Regards > >Mark > Christian > > >-Original Message- >From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] >Sent: 18 October 2005 19:06 >To: sqlite-users@sqlite.org >Subject: Re: [sqlite] Very Slow delete times on larger databases, please >help! > > >"Allan, Mark" <[EMAIL PROTECTED]> wrote: >> Have you been able to investigate this yet? > >I have investigated and I found nothing wrong. I am unable >to reproduce the problem. >-- >D. Richard Hipp <[EMAIL PROTECTED]> > > > > >DISCLAIMER: >This information and any attachments contained in this email message is >intended only for the use of the individual or entity to which it is addressed >and may contain information that is privileged, confidential, and exempt from >disclosure under applicable law. If the rea
[sqlite] sqlite3_open16 with unicode string
Hi all, I have a little problem when trying to open database that contain Unicode caracters: Here is a small sample: wstring lPath = L"c:\\\x4ECA\x6708 database.db"; DWORD lReturn = sqlite3_open16((void *)lPath.c_str (), &this->cdb); This works correctly, but the problem is that the database name created is not correct: I get something like: [今月 database.db] instead of [今月database.db] I guess that I have a problem a string encoding, but I can not find any solution. I have also tried to use sqlite3_open with encoding the name UTF8, but I get exactly the same result. Can anyone help? Thanks a lot Benoît Gantaume R&D Manager. DMAILER
Re: [sqlite] Very Slow delete times on larger databases, please help!
This may or may not help, depending upon your schema. We find in general that insertions and deletions are the major overhead in index maintenance and there is a point where it is cheaper to drop the indices you are not using and rebuild them rather than to involve the high overhead of repeated B-Tree rebalancing etc. The drop and rebuild has the added benefit of ending up with an optimally organized index free from fragmentation. The cost of insertions and deletions in an index is not linear with index size. I haven't looked at the Sqlite B-Tree algorithms, so this I can only suggest this as an experiment. If Sqlite uses some form of B-Tree optimization, the overhead of insertions and deletions is greater and the drop and rebuild more likely to be an improvement. JS Allan, Mark wrote: We are able to compile our application for both target and host. When compiled for host the application runs on Win32 and will create/read/write to a database file on the host PC. The performance of the deletions on Win32 will not take the 11 minutes I specified, this is only a problem for our target. The same operation on the PC will take only 3-4 seconds. Please note that since my first email we have tried increasing the page size of SQLite and have increased the page size from 1024 bytes to 8192 bytes. This has decreased the time to process the same delete operation from 11 minutes to 3.75 minutes on our target hardware. Both host and target versions of the software use the same configuration a page size of 8192 bytes and a cache of 75 pages = 600k. The only real differences are 1) the hardware, 2) the filing system. We would expect a difference in performance as the PC is much faster than our target hardware and the write speed to NOR flash is comparatively slow but not as drastic a drop-off as we see. 3-4 seconds -> 3-4 minutes. It may be useful for us to know what SQLite is doing during deletion so that we can try and optimise our code and/or any configuration of SQLite, our filesystem code or the hardware to try and get this figure down. Can anyone give me a reasonably detailed description of what is happening during delete. The documentation on the website has not helped us diagnose where our problem lies. Best Regards Mark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 18 October 2005 19:06 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Very Slow delete times on larger databases, please help! "Allan, Mark" <[EMAIL PROTECTED]> wrote: Have you been able to investigate this yet? I have investigated and I found nothing wrong. I am unable to reproduce the problem. -- D. Richard Hipp <[EMAIL PROTECTED]> DISCLAIMER: This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.
Re: AW: [sqlite] and , or
Martin Engelschalk wrote: Hi all, The problem seems to be that sqlite makes a difference between an empty string and a null value. Therefore, your query has to check both. In Oracle (and problaby others) the datatype "Varchar2" can be used to treat an empty string as a null value. Does anyone know if there is a way to do this in sqlite? Martin, When you create the table you can tell sqlite to use an empty string as the default value if nothing is entered. Check the column-constraint description in the CREATE TABLE writeup on the syntax web page. Gerry -- -- Gerry Snyder American Iris Society Director, Symposium Chair in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19
Re: AW: [sqlite] and , or
Hi all, The problem seems to be that sqlite makes a difference between an empty string and a null value. Therefore, your query has to check both. In Oracle (and problaby others) the datatype "Varchar2" can be used to treat an empty string as a null value. Does anyone know if there is a way to do this in sqlite? Martin Eggert, Henri schrieb: Sorry , I was not clear enough. Considere the following : create table t1 ( a integer , b text , c text ) ; insert into t1 ( a , b , c ) values ( 1 , 'a' , 'b' ) ; insert into t1 ( a , b , c ) values ( 2 , 'c' , '' ) ; insert into t1 ( a , b , c ) values ( 3 , '' , 'd' ) ; insert into t1 ( a , b , c ) values ( 4 , '' , '' ) ; insert into t1 ( a , b ) values ( 5 , 'e' ) ; insert into t1 ( a , c ) values ( 6 , 'f' ) ; insert into t1 ( a ) values ( 7 ) ; select a from t1 where ( b ='' and c = '' ) This returns 4 ( b and c have explicitely been set to empty string ) But not 7 ( b and c not set but also empty ). What I need is a select statement which returns both. Regards , Henri By the way : Sqlite is a great piece of software. Thank you drh. -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 18. Oktober 2005 20:05 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] and , or "Eggert, Henri" <[EMAIL PROTECTED]> wrote: Hi sqlite-users Works fine : select Id from Data where ( ( Text = '' ) or ( Comming = '' ) ) But NOT: select Id from Data where ( ( Text = '' ) and ( Comming = '' ) ) Both work fine when I try them. Why do you think the second one is not working? -- D. Richard Hipp <[EMAIL PROTECTED]>
RE: [sqlite] Very Slow delete times on larger databases, please help!
We are able to compile our application for both target and host. When compiled for host the application runs on Win32 and will create/read/write to a database file on the host PC. The performance of the deletions on Win32 will not take the 11 minutes I specified, this is only a problem for our target. The same operation on the PC will take only 3-4 seconds. Please note that since my first email we have tried increasing the page size of SQLite and have increased the page size from 1024 bytes to 8192 bytes. This has decreased the time to process the same delete operation from 11 minutes to 3.75 minutes on our target hardware. Both host and target versions of the software use the same configuration a page size of 8192 bytes and a cache of 75 pages = 600k. The only real differences are 1) the hardware, 2) the filing system. We would expect a difference in performance as the PC is much faster than our target hardware and the write speed to NOR flash is comparatively slow but not as drastic a drop-off as we see. 3-4 seconds -> 3-4 minutes. It may be useful for us to know what SQLite is doing during deletion so that we can try and optimise our code and/or any configuration of SQLite, our filesystem code or the hardware to try and get this figure down. Can anyone give me a reasonably detailed description of what is happening during delete. The documentation on the website has not helped us diagnose where our problem lies. Best Regards Mark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 18 October 2005 19:06 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Very Slow delete times on larger databases, please help! "Allan, Mark" <[EMAIL PROTECTED]> wrote: > Have you been able to investigate this yet? I have investigated and I found nothing wrong. I am unable to reproduce the problem. -- D. Richard Hipp <[EMAIL PROTECTED]> DISCLAIMER: This information and any attachments contained in this email message is intended only for the use of the individual or entity to which it is addressed and may contain information that is privileged, confidential, and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient, or the employee or agent responsible for delivering the message to the intended recipient, you are hereby notified that any dissemination, distribution, forwarding, or copying of this communication is strictly prohibited. If you have received this communication in error, please notify the sender immediately by return email, and delete the original message immediately.
Re: AW: [sqlite] and , or
On Wed, 19 Oct 2005 09:59:49 +0200, "Eggert, Henri" <[EMAIL PROTECTED]> wrote: > What I need is a select statement which returns both. try select Id from Data where (coalesce(text,'') = '') and (coalesce(comming,'') = ''); klint. +---+-+ : Klint Gore: "Non rhyming: : EMail : [EMAIL PROTECTED] : slang - the: : Snail : A.B.R.I.: possibilities : : Mail University of New England : are useless" : : Armidale NSW 2351 Australia : L.J.J. : : Fax : +61 2 6772 5376 : : +---+-+
AW: [sqlite] and , or
Sorry , I was not clear enough. Considere the following : create table t1 ( a integer , b text , c text ) ; insert into t1 ( a , b , c ) values ( 1 , 'a' , 'b' ) ; insert into t1 ( a , b , c ) values ( 2 , 'c' , '' ) ; insert into t1 ( a , b , c ) values ( 3 , '' , 'd' ) ; insert into t1 ( a , b , c ) values ( 4 , '' , '' ) ; insert into t1 ( a , b ) values ( 5 , 'e' ) ; insert into t1 ( a , c ) values ( 6 , 'f' ) ; insert into t1 ( a ) values ( 7 ) ; select a from t1 where ( b ='' and c = '' ) This returns 4 ( b and c have explicitely been set to empty string ) But not 7 ( b and c not set but also empty ). What I need is a select statement which returns both. Regards , Henri By the way : Sqlite is a great piece of software. Thank you drh. -Ursprüngliche Nachricht- Von: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Gesendet: Dienstag, 18. Oktober 2005 20:05 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] and , or "Eggert, Henri" <[EMAIL PROTECTED]> wrote: > Hi sqlite-users > > Works fine : select Id from Data where ( ( Text = '' ) or ( Comming = '' > ) ) > But NOT: select Id from Data where ( ( Text = '' ) and ( Comming = > '' ) ) > Both work fine when I try them. Why do you think the second one is not working? -- D. Richard Hipp <[EMAIL PROTECTED]>
