[sqlite] Search on Age, from DOB column
Hi,
I need to be able offer the user the ability to search for patients in the
database based on age. i.e. age 17 or age = 45 etc etc...
I only store the patient DOB in the database however, what is the SQL to achive
this? Can I subract todays date from the DOB and get the number of years within
an SQL string?
The patient table is similar to:-
Patients
{
INTEGER PrimaryKey;
TEXT Surname;
TEXT FirstName;
TIMESTAMP DOB;
...
...
...
}
Thanks in advance for your help.
Mark
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.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] Search on Age, from DOB column
Thanks to everybody that contirbuted to this thread. I have now implemented this functionality and it works well. 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. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQL query help (mutiple joins)
Hi,
Can anyone offer any help with the following SQL query?
I have a database that looks something like the following:-
PatientsTable { ID, Name, Sex, }
ExaminationsTable { ID, PatientID, }
TestTable { ID, ExamID, .}
ForcedSpiroTable { ID, TestID, EVC, IVC, IC ... }
RelaxedSpiroTable { ID, TestID, FVC, FEV1, PEF, ...}
Can someone help me out with the syntax for applying a search for all rows in
the PatientsTable that have a ForcedSpiroTable.EVC 2.0 and a
RelaxedSpiroTable.FVC 2.0?
Basically ExaminationsTable has a foreign key to PatientsTable, TestsTable has
a foreign key to ExaminationsTable and both ForcedSpiroTable and
RelaxedSpiroTable have a foreign key to the TestTable.
Your help would be gratefully received
Thanks in advance
Mark
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: [sqlite] SQL query help (mutiple joins)
Hi,
Thanks for your quick replies. I have tried this method but however I am
getting a row returned for each entry in ForcedSpiroTable or RelaxedSpiroTable
that matches the search criteria.
i.e. If a single patient say Joe Bloggs has 5 tests, all with EVC and FVC
greater than 2.0 then I get
Joe Bloggs
Joe Bloggs
Joe Bloggs
Joe Bloggs
Joe Bloggs
What I want is Joe Blogs just the once.
Does this make sense? What I need to do is find all patients that have an EVC
and FVC greater than 2.0.
Is there a way to do this? Am I missing something?
Thanks again
Mark
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 01 May 2007 15:31
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQL query help (mutiple joins)
Allan, Mark wrote:
I have a database that looks something like the following:-
PatientsTable { ID, Name, Sex, }
ExaminationsTable { ID, PatientID, }
TestTable { ID, ExamID, .}
ForcedSpiroTable { ID, TestID, EVC, IVC, IC ... }
RelaxedSpiroTable { ID, TestID, FVC, FEV1, PEF, ...}
Can someone help me out with the syntax for applying a
search for all rows in the PatientsTable that have a
ForcedSpiroTable.EVC 2.0 and a RelaxedSpiroTable.FVC 2.0?
Basically ExaminationsTable has a foreign key to
PatientsTable, TestsTable has a foreign key to
ExaminationsTable and both ForcedSpiroTable and
RelaxedSpiroTable have a foreign key to the TestTable.
Mark,
This should do the trick:
select p.*
from PatientsTable as p
join ExaminationsTable as e on e.PatientID=p.ID
join TestTable as t on t.ExamID=e.ID
join ForcedSpiroTable as f on f.TestID=t.ID
join RelaxedSpiroTable as r on r.TestID=t.ID
where f.EVC 2.0 and r.FVC 2.0;
HTH
Dennis Cote
--
---
To unsubscribe, send email to [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.
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
RE: [sqlite] SQL query help (mutiple joins)
Excellent, thanks for your help.
Indeed I was missing the DISTINCT keyword. The query does exactly what I need
it to now.
-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Behalf Of P
Kishor
Sent: 01 May 2007 15:50
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQL query help (mutiple joins)
On 5/1/07, Allan, Mark [EMAIL PROTECTED] wrote:
Hi,
Thanks for your quick replies. I have tried this method but
however I am getting a row returned for each entry in
ForcedSpiroTable or RelaxedSpiroTable that matches the search
criteria.
i.e. If a single patient say Joe Bloggs has 5 tests, all
with EVC and FVC greater than 2.0 then I get
Joe Bloggs
Joe Bloggs
Joe Bloggs
Joe Bloggs
Joe Bloggs
What I want is Joe Blogs just the once.
Does this make sense? What I need to do is find all
patients that have an EVC and FVC greater than 2.0.
Is there a way to do this? Am I missing something?
Yes, it makes sense.
Yes, there is a way to do this.
Yes, you are missing something.
You have to realize there is a difference between what the SQL returns
and what you expect/want to see. The SQL is being very truthful --
since your condition for Joe Bloggs is satisfied 5 times, Joe Bloggs
is returned 5 times. That is precisely what you want. Imagine it this
way --
If you had to create a table in a spreadsheet that would depict the
returned result, how many rows would you have for Joe Bloggs? Of
course, five.
However, you _want_ to display Joe Bloggs only once. Well, for that
you have to do something else.
If you want to stick to SQL, well, you can query the returned results
and SELECT DISTINCT on the patient's name. Or, if you have an
application, you can grab the entire result set, and reformat it using
your favorite programming language.
See, there is a difference between SQL's result and your expectation.
A SQL always returns a table -- a rectangular, rows x cols selection
where every cell is filled with something even if that something
might be null
Hope this helps.
-Original Message-
From: Dennis Cote [mailto:[EMAIL PROTECTED]
Sent: 01 May 2007 15:31
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] SQL query help (mutiple joins)
Allan, Mark wrote:
I have a database that looks something like the following:-
PatientsTable { ID, Name, Sex, }
ExaminationsTable { ID, PatientID, }
TestTable { ID, ExamID, .}
ForcedSpiroTable { ID, TestID, EVC, IVC, IC ... }
RelaxedSpiroTable { ID, TestID, FVC, FEV1, PEF, ...}
Can someone help me out with the syntax for applying a
search for all rows in the PatientsTable that have a
ForcedSpiroTable.EVC 2.0 and a RelaxedSpiroTable.FVC 2.0?
Basically ExaminationsTable has a foreign key to
PatientsTable, TestsTable has a foreign key to
ExaminationsTable and both ForcedSpiroTable and
RelaxedSpiroTable have a foreign key to the TestTable.
Mark,
This should do the trick:
select p.*
from PatientsTable as p
join ExaminationsTable as e on e.PatientID=p.ID
join TestTable as t on t.ExamID=e.ID
join ForcedSpiroTable as f on f.TestID=t.ID
join RelaxedSpiroTable as r on r.TestID=t.ID
where f.EVC 2.0 and r.FVC 2.0;
HTH
Dennis Cote
--
---
To unsubscribe, send email to [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.
--
---
To unsubscribe, send email to [EMAIL PROTECTED]
--
---
--
Puneet Kishor http://punkish.eidesis.org/
Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/
Open Source Geospatial Foundation http://www.osgeo.org/education/
-
collaborate, communicate, compete
RE: [sqlite] SQL query help (mutiple joins)
Allan, Mark wrote: What I want is Joe Blogs just the once. Mark, Then try adding distinct like this: select distinct p.* from PatientsTable as p join ExaminationsTable as e on e.PatientID=p.ID join TestTable as t on t.ExamID=e.ID join ForcedSpiroTable as f on f.TestID=t.ID join RelaxedSpiroTable as r on r.TestID=t.ID where f.EVC 2.0 and r.FVC 2.0; Ok, so here's another question, how would I get the count of patients where the EVC and FVC 2.0? 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. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQL query help (mutiple joins)
Ok, so here's another question, how would I get the count of patients where the EVC and FVC 2.0? Dont worry I have figured this out. I am doing:- select count (distinct p.PatientID) p.* from PatientsTable as p join ExaminationsTable as e on e.PatientID=p.ID join TestTable as t on t.ExamID=e.ID join ForcedSpiroTable as f on f.TestID=t.ID join RelaxedSpiroTable as r on r.TestID=t.ID where f.EVC 2.0 and r.FVC 2.0; 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. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Japanese-Korean characters
I think what he is trying to say is that you can store Japanese/Korean characters in your database using a UTF-8 or Unicode string. We currently use Sqlite and support Chinese, and plan to implement Japanese and Korean and have had no problems. We store the text as a UTF-8 encoded string. Provided you read the text out as UTF-8 encoded you should have no problems. http://en.wikipedia.org/wiki/UTF-8 -Original Message- From: Pavan [mailto:[EMAIL PROTECTED] Sent: 25 April 2007 13:16 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Japanese-Korean characters UTF-8 (or Unicode) rules! Thanks for the quick reply. But, I havent understood. Can you be bit more clear ? Best Regards, Pavan. On 4/25/07, Tian-Jian Barabbas [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Pavan 提到: Hi, Can we store/retrieve Japanese/korean characters in sqlite db ? Thanks, Pavan. UTF-8 (or Unicode) rules! Cheers, Mike -- --- To unsubscribe, send email to [EMAIL PROTECTED] -- --- -- ' Always finish stronger than you start * 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. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] journal - Unable to open the database file
Yes it would appear that the file is open read only. I cannot open the file in any other program to attempt to change the contents or truncate it. It appears that the file is locked, but when using Unlocker to try to unlock it, it states that there is no locking handle on the file. Try to delete the file through unlocker, explorer or DOS and it will fail saying It is being used by another process. I shutdown my pc and still get the same error. Spookily after a random amount of time the file will delete itself automatically. The only way to force its removal from the network is for me to ask a member of admin to do it for me. I cannot be sure it is Windows Desktop Search that causes this. But it is the most likely candidate. The file is locked by one of two members of staff here, both of which run Windows Desktop Search. Noone else uses it and noone else has ever been found guilty of having the file open by admin. However I have tested this on a PC running Vista (with the db on a local file system however), Vista has file indexing built in as standard. The test program ran all weekend without exhibiting this same problem on Vista. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 09 March 2007 16:36 To: sqlite-users@sqlite.org Subject: Re: [sqlite] journal - Unable to open the database file Allan, Mark [EMAIL PROTECTED] wrote: 3) Ask if anyone can offer any advise us as to what we can do to get around the problem of a journal file being locked and SQLite cannot delete it? Because at the moment if this situation occurs no one can write to that Db until the journal file has gone. Which is quite a severe problem... While Windows Desktop Search has the journal file open, is the journal file read only? Is it possible to change the content of the file or even truncate the file to zero length as long as the file is not deleted? -- D. Richard Hipp [EMAIL PROTECTED] -- --- To unsubscribe, send email to [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. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] journal - Unable to open the database file
I have been using SQLite as a replacement for MS Access for use in a PC application. It works well but we have been experiencing the following problem when the database file is located on a network drive. We are getting Unable to open the database file when trying to start a new transaction. Investigating this it appeared the reason this was happening was that the journal file is open by another process and cannot be deleted. SQLite cannot delete it and neither can I manually via explorer. After much investiagtion with out IT department it looks like the reason this was open is that Microsoft Windows Desktop Search (a file indexer program) has opened it. I am still unsure why it keeps it open and does not allow for anyone other than an admin to delete it. A file may stay in this state for hours before mysteriously disappearing of its own accord. If we configure Windows Desktop Search to ignore the folder, we do not get (or at least we have not yet got) this problem. Basically I would like to:- 1) Make this problem known to the community. 2) Ask if anyone has experienced anything like this? 3) Ask if anyone can offer any advise us as to what we can do to get around the problem of a journal file being locked and SQLite cannot delete it? Because at the moment if this situation occurs no one can write to that Db until the journal file has gone. Which is quite a severe problem... Thanks Mark 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: [sqlite] journal - Unable to open the database file
RB Smissaert, The database file is named thename.vdb. Is .vdb ok or wrong? Does it have to be .db3? Or is there just certian extensions I need to avoid? Dr Hipp, Currently all journals have been deleted by admin, so I am currently trying to create another one to find out exactly that information for you. -Original Message- From: RB Smissaert [mailto:[EMAIL PROTECTED] Sent: 09 March 2007 16:40 To: sqlite-users@sqlite.org Subject: RE: [sqlite] journal - Unable to open the database file If I remember well this is a problem if you have the extension .db for the database file and I think if you change that to something like .db3 then it won't happen. RBS -Original Message- From: Allan, Mark [mailto:[EMAIL PROTECTED] Sent: 09 March 2007 16:23 To: sqlite-users@sqlite.org Subject: [sqlite] journal - Unable to open the database file I have been using SQLite as a replacement for MS Access for use in a PC application. It works well but we have been experiencing the following problem when the database file is located on a network drive. We are getting Unable to open the database file when trying to start a new transaction. Investigating this it appeared the reason this was happening was that the journal file is open by another process and cannot be deleted. SQLite cannot delete it and neither can I manually via explorer. After much investiagtion with out IT department it looks like the reason this was open is that Microsoft Windows Desktop Search (a file indexer program) has opened it. I am still unsure why it keeps it open and does not allow for anyone other than an admin to delete it. A file may stay in this state for hours before mysteriously disappearing of its own accord. If we configure Windows Desktop Search to ignore the folder, we do not get (or at least we have not yet got) this problem. Basically I would like to:- 1) Make this problem known to the community. 2) Ask if anyone has experienced anything like this? 3) Ask if anyone can offer any advise us as to what we can do to get around the problem of a journal file being locked and SQLite cannot delete it? Because at the moment if this situation occurs no one can write to that Db until the journal file has gone. Which is quite a severe problem... Thanks Mark 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. -- --- To unsubscribe, send email to [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. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite db lock problem
Hi, A little while back I submitted a query as to whether SQLite would be a good alternative to using MS Access as an internal database for a PC application. I received many repiles for which I was grateful. Mostly people thought that SQLite was a far more superior option to Access, the main concern I was warned about however was that SQLite does not work particularly well in a multiuser environment. We will need (in some instances) for the database file to be located on a network drive and there is the requirement to support up to 10 concurrent users. The actual traffic in most cases will be very light and the likelyhood of writes actually taking place at the same time actually very slim. However we do need the database engine to be able to handle this. Basically my questions are thus:- 1) What are peoples experiences with SQLite under the scenario I have described above? 2) What are peoples opinions on whether SQLite is a good choice for this project? Any other alternatives? 3) How severe is the database locking problem? How easy is it to unlock a locked database? How often on average will it occur? It is worth noting that the project is a complete upgrade from an older version, the old version used access in the same environment as described above and we had no complaints of problems in multiuser usage. However we are finding access old technology and too slow and the 2Gb limit is mnow too small for some of our customers. Any help/suggestions will be gratefully received. Mark 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.
[sqlite] SQL query - TOP n
Hi, I have an SQL question. The following syntax is used in a MSDN example for retrieving a certain range of records from a table, so that data can be loaded into a DataGridView as a when is is needed for performance reasons:- Select Top n * From PATIENTS WHERE PATIENT_PK NOT IN (SELECT TOP n PATIENT_PK From PATIENTS Order By PATIENT_PK) Order By PATIENT_PK It would appear that the TOP syntax is not supported by SQLite (maybe just a Microsoft thing?). However is there an alternative? i.e. How can I achieve what the example SQL syntax above is doing? Can I do this with SQLite at all? Basically, when the above statement is run on access it takes 2-3 minutes on a database containing 10,000 records. We are now thinking of switching maybe to SQLite as we hope to get better storage efficiency and performance. However we will need to be able to execute a query similar to the one above. Thanks in advance for any help. Mark 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: [sqlite] SQL query - TOP n
Thanks Donald Griggs and Igor Tandetnik, I have indeed 'taken it to the limit' and it works ok, and I can't believe how quick it works. Thanks -Original Message- From: Griggs, Donald [mailto:[EMAIL PROTECTED] Sent: 16 February 2007 17:49 To: sqlite-users@sqlite.org Subject: RE: [sqlite] SQL query - TOP n Regarding: It would appear that the TOP syntax is not supported by SQLite (maybe just a Microsoft thing?). However is there an alternative? Take it to the LIMIT, Mark. See: http://sqlite.org/lang_select.html The LIMIT clause places an upper bound on the number of rows returned in the result. A negative LIMIT indicates no upper bound. The optional OFFSET following LIMIT specifies how many rows to skip at the beginning of the result set. In a compound query, the LIMIT clause may only appear on the final SELECT statement. The limit is applied to the entire query not to the individual SELECT statement to which it is attached. Note that if the OFFSET keyword is used in the LIMIT clause, then the limit is the first number and the offset is the second number. If a comma is used instead of the OFFSET keyword, then the offset is the first number and the limit is the second number. This seeming contradition is intentional - it maximizes compatibility with legacy SQL database systems. [opinions are mine, and not necessarily those of my company] -- --- To unsubscribe, send email to [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. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite on RAM
Believe so, in this case you would use an 'in memory' database. This can be opened by giving the filename :memory:. There is more information on the wesite and in the mail archive on this. Personally I have never used this feature, so cant really offer more help than that. -Original Message- From: Vivek R [mailto:[EMAIL PROTECTED] Sent: 10 October 2006 15:19 To: sqlite-users@sqlite.org Subject: [sqlite] SQLite on RAM Hi List, Is it possible to use SQLite on RAM (where there is no file system) i.e. directly on memory, (No Hard disk). I am planning to use it on RAM inside TV. How it will read and write on RAM. Thanks and Regards, Vivek R 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. - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] SQLite vs MS Access
Hi, After successfully using SQLite on an embedded device, we are now thinking of using SQLite in a PC application. This would be used in place of an MS Access database on a local/network disk. I believe that SQLite should be quicker for both transactions and queries than Access. The one draw back that comes to mind maybe portability (i.e. accessing data outside of the application), although the data would be portable across machines (PC, Mac, Unix, etc) should we ever need it to be in the future. Is there any webpage, or does anyone have any information comparing the benefits of the two. I can only find comparisons between MySQL and PostgreSQL. This information would aid us greatly in deciding whether to use SQLite or stick with Access. Any help/advice will be gratefully received. Mark 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: [sqlite] Using sqlite on Nintendo DS / PSP
I'm building a dictionary application that will run on Nintendo DS and PSP. I'm considering using sqlite to store the database which will will be read-only, and embedded on rom. Is it possible to get sqlite to read the database directly from such a pre-allocated memory area? Usage examples I've seen using :memory: all seem to create and populate the database on the fly. In this case, the database will already have been created. This may be possible if you code your own O/S interface layer. See the following link:- http://www.sqlite.org/arch.html And look at the following source files that come with SQLite:- os_win.c os_unix.c We are using SQLite on an embedded device with the DB stored on Flash (ROM). However we use a 3rd party filing system libray to read and write files to it as if it were a disk. We had to write our own interface layer however to call the filing system library funcs, we called it os_flash.c. If you could write a os_nintendo.c (or whatever) file that would be able to perform the reads from ROM then it should work. In this way you would not use an in memory database but a normal file based database. 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: [sqlite] sqlite too slow for me?
I believe that when using a transaction, i.e. Begin, Commit. SQLite will only perform the actual writing to file when Commit is made, therefore less disk I/O, or all I/O done at the end rather than after each insert and therefore better performance. See documentation at:- http://www.sqlite.org/speed.html P.S. I'm sure someone else will give a better explanation. -Original Message- From: Cesar David Rodas Maldonado [mailto:[EMAIL PROTECTED] Sent: 29 June 2006 16:12 To: sqlite-users@sqlite.org Subject: Re: [sqlite] sqlite too slow for me? I have a question for every body... SQLite was very slow for my inserts (like 5 inserts), with out sincronization but when i put BEGIN; before start with my inserts is was faster... like 1000 times more... :D why is that? On 6/28/06, Cesar David Rodas Maldonado [EMAIL PROTECTED] wrote: I dont know a lot about MySQL... but mysql is not so faster as you think... I have in my computer the mysql 5 is good, but sqlite is so faster too!!! but MySQL has a query cache so i think the cache make it seems more faster than sqlite... On 6/27/06, Péter Szabó [EMAIL PROTECTED] wrote: First, thank you all for the answers. UNIQUE(col1, col4, col5, col2), Adding this would surely make the query run faster -- provided that SQLite chooses the right index. But I also use the UNIQUE(col1, col4, col5) constraint to ensure the uniqueness of these three columns. So instead I should have both UNIQUE(col1, col4, col5), UNIQUE(col1, col4, col5, col2), but this would waste my disk space. I am wondering how can MySQL 4.1 be so fast compared to SQLite 3? MySQL answers my query in 0.02 seconds, and SQLite answers in more than 28 seconds. I guess that MySQL doesn't do any magic either ( i.e. it operates in O(Klog N) time, which appears to be just 0.02 seconds), but MySQL recognises that it should use the UNIQUE(col1, col4, col5) index, while SQLite poorly chooses some other index, maybe the PRIMARY KEY(col1, col2, col3, col4, col5), which is just wrong. Can someone confirm that the SQLite is using the wrong index? Is it possible to deterministically rewrite the query to force SQLite use the right index? 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.
[sqlite] disabling rollback journal
All, I have already posted some messages some months ago concerning disabling the creation of the journal file. It seems that currently there is no way to configure SQLite to do this. Journalling, it seems, is an integral part of the paging layer, is this correct? My question is thus, has anybody successfully disabled journalling of the database file? If nobody has done this could somebody suggest how difficult this would be for me to do myself. Or would this be considered as an option for a future release of SQLite? I ask as we find that journaling the database is just a performance and storage overhead for us. As I have stated before, our flash file system is 100% power-fail proof and thus the journalling of the database for us is not required. It just takes longer to save new records and the journal file uses up valuable space on our flash filesystem. I understand that journalling is important with most OS's to prevent possible database corruption but in our case this is not possible anyhow. If anybody can help me I will be most grateful. Mark 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: [sqlite] disabling rollback journal
Dr Hipp, Thanks for your reply. I have contacted the authors of our filesystem HCC Embedded. The filesystem we are using is called EFFS, some information can be found at http://www.hcc-embedded.com/site.php if necessary. Apparently, yes the flash filesystem implements fully atomic writes, indeed this was one of the features that made us choose this particular filesystem. I know from development of our product that files are only updated when the file is flushed, i.e. when sqlite3OsSync() is called. Any fwrites without a flush are lost on power loss, therefore no corruption occurs. Here is a reply from the author of EFFS:- Hi Mark - changes to the file are uilt in mirror chains - when the file is closed or flushed then the new file replaces the old - i.e. the file is updated atomically. So I think you do not need this roll back. We take the view that thisis how a failsafe file system should work - it is only meaningful to change at known states - it should not be necessary for the user to know that each write creates an update - a relibale application would be well nigh imposible to design in this environment. So we think it should work really nicely with your database and that you can disable this rollback. Would be glad if you could mention to your SQLite contact the beauty of our system (:-)) Regards Dave I think based on your information and that from HCC that we can lose the journalling of the database without any potential data loss. Again my question is how can this be done? And how difficult would this be? I think this would benefit us a great deal as our target hardware is not the quickest and any performance enhancements would help a great deal. I would like to state that I do not mean to criticise SQLite or is functionality, it works well for us and has saved us much time in development and test. However we would like to know if it can be tweaked to our specific needs a little. Thanks again. Mark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 07 June 2006 14:14 To: sqlite-users@sqlite.org Subject: Re: [sqlite] disabling rollback journal Allan, Mark [EMAIL PROTECTED] wrote: our flash file system is 100% power-fail proof and thus the journalling of the database for us is not required. Without journalling, you cannot have a ROLLBACK command. And the semantics of UPDATE become UPDATE OR FAIL instead of the default UPDATE OR ABORT. The difference is subtle, but important. Does your flash file system actually implement atomic writes of multiple changes changes distributed across a single file? Or does it just guarantee that each individual write() is atomic. I'm guessing the latter. But without the former, you can still quite easily get database corruption after a power failure if you have no rollback journal. Any single UPDATE or INSERT or DELETE command will often result in multiple write() operations to the database file. If some of those write()s complete and others do not, your database will end up in a corrupt state. Note that you do not have to take a power failure for corruption to happen. Suppose a program is writing to the database, and does 2 of the 5 writes required to make a change to the database, but then the program is kill because a different thread in the program hit a bug and segfaults, or because the program is sent a SIGKILL (or the equivalent). Without a rollback journal, the database is left in an corrupt state with no way to recover. In summary, unless you filesystem implements transactions that span multiple write() requests, you still need a rollback journal. -- 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: [sqlite] disabling rollback journal
I have been in contact with the developer of your flash filesystem and we are working on a solution now... That really is excellent news. Thanks for your interest and effort. I look forward to the solution. 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: [sqlite] C++ Arrays in sqlite
Jay is correct here, only use a blob if you do not want to search on that field. Your example may not be best suited to BLOB but that is for you to decide. We store medical test data as a blob. This is not much more complicated than an array of C structs. However we have a primary key field, a test type field, date field and patient foreign key field in the same table, which we will perform searches on. When we find a record we only want to read out the binary dump of the test data into RAM for our software to interpret and display/print in user readable form. i.e. CREATE TABLE Test ( TEST_ID integer PRIMARY KEY AUTOINCREMENT, PATIENT_ID varchar(15), TEST_TYPE integer, DATE timestamp, CONTENT blob } -Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: 15 May 2006 18:39 To: sqlite-users@sqlite.org Subject: Re: [sqlite] C++ Arrays in sqlite On 5/15/06, Kevin Piciulo [EMAIL PROTECTED] wrote: Both ideas are very helpful. I'll definately be visiting that sql site a lot. I'm not sure about BLOBs though, is it just like a catch all. Where if you can't save data as something better, just save it as a BLOB? As you can imagine a google search for BLOB gives me a general definition and not much more. Thanks again for the help. blobs are usually for storing data that's not going to be used in calculation or searching. It's difficult to formulate a query to search using the content of a blob. You might use a blob to store a picture of a product, but not to store the product name or identification number. You will likely search using that information. 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: [sqlite] C++ Arrays in sqlite
Indeed storing the array as a BLOB is the best way to store this. To store a blob you will need to use the following syntax I believe:- INSERT INTO table_name (NAME, NUMBERS) VALUES (the_name,?) And will need to bind the array to the SQL query after using sqlite3_bind_blob(). There is documentation on this on the Web site and in the API header file. At least this is what I have to do to store a blob. We use binary blobs to store a binary dump of test data which is basically only an array of data. Hope this helps ,but I'm sure someone else will post something more helpful than me. Mark -Original Message- From: Kevin Piciulo [mailto:[EMAIL PROTECTED] Sent: 15 May 2006 17:31 To: sqlite-users@sqlite.org Subject: [sqlite] C++ Arrays in sqlite Hey everyone, This may not be the place to ask (if not please direct me.) I'm relatively new to sql, and newer to sqlite. Through experimenting i've figured out how to add a string to a table, create a table, remove a string, some basic stuff like that. What I'd like to do is add an array to my table. For example: i have 2 columns, Name,and Numbers. Numbers needs to hold an array for each entry under Name. That is each name has it's own array. I tried to learn a little about BLOBs, but aside from the fact that they're Binary Large Objects I didn't find much. Is a BLOB what I'd use to store information in this way? I appreciate any help or direction you guys can give. Kevin 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: [sqlite] accurate progress indication
All, I have implemented the method suggested and it works well for me. I am running on an embedded device and the general performance we get using SQlite is not great. Additionally the database is not that large compared to some of the other users' needs. Our database can only ever be a mamixmum of 25MB. And in most cases it will never reach this size. On our PC simulator all commands execute quickly including our quick delete and indeed the progress bar shoots up to 100% in a second or two. The IO also completes quickly. On our target unit, it takes alot longer, deleting approx 800 records from one table and 2400 from another table will take approx 1min 30 secs. The bulk of this time is before the COMMIT, therefore before the IO stage I believe, the COMMIT only takes a few seconds. So in this case we would like a progress indicator to represent a reasonably accurate representation of progress so user will not think the unit has locked up. Currently the method specified by Dr Hipp seems to work well for us. Thanks for all your help and interest. Mark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 09 May 2006 17:44 To: sqlite-users@sqlite.org Subject: Re: [sqlite] accurate progress indication Dennis Cote [EMAIL PROTECTED] wrote: [EMAIL PROTECTED] wrote: Yes. To get reasonable performance you will need to enclose the SELECT and all the individual DELETEs within a BEGIN...COMMIT. Richard, Won't this quickly run through the select and delete commands, so that the progress bar quickly reaches 100%, and then sit there for some time while the I/O is done during the commit? Yes. So the answer to the original question becomes that there is not good way to give a progress bar on a big delete. -- 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: [sqlite] accurate progress indication
Jay, Yes there is a index on the date column. The delete operation is on date, specifically dates older than a date entered by the user (so he/she can clear out old records and keep newer ones). I don't think the time taken is slowed by the time SQLite takes to find the rows as I do a SELECT COUNT with the same query and this does not take as long as the DELETE. I don't know why it takes so long, I dont think it is the writing to the file as this should only happen on COMMIT and I can see in debug output that the COMMIT only takes a few seconds. It is before COMMIT that takes the time. Mark -Original Message- From: Jay Sprenkle [mailto:[EMAIL PROTECTED] Sent: 10 May 2006 14:10 To: sqlite-users@sqlite.org Subject: Re: [sqlite] accurate progress indication On 5/10/06, Allan, Mark [EMAIL PROTECTED] wrote: On our target unit, it takes alot longer, deleting approx 800 records from one table and 2400 from another table will take approx 1min 30 secs. The bulk of this time is before the COMMIT, therefore before the IO stage I believe, the COMMIT only takes a few seconds. So in this case we would like a progress indicator to represent a reasonably accurate representation of progress so user will not think the unit has locked up. Do you have an index on your tables that allows Sqlite to find the rows to delete quickly? 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: [sqlite] accurate progress indication
Jay,
Yes we debug on our target using a serial link to the PC and catching printf
statements in hyperterminal. From this we can deduce that it is the line of
code:-
sqlite3_exec( m_hDatabase, strSQL, NULL, NULL, NULL )
where strSQL is DELETE FROM EXAMINATIONS WHERE DATE datetime('2006-05-09
00:00:00')
that is taking the time.
We have not tried to place printf statements within SQLite to determine what is
taking the time internally to SQLite.
Note: the performance problem is not a new issue, only the ability to display a
progress bar was new. There is an existing thread on this archived at:-
http://www.mail-archive.com/sqlite-users@sqlite.org/msg10818.html
Thanks
Mark
-Original Message-
From: Jay Sprenkle [mailto:[EMAIL PROTECTED]
Sent: 10 May 2006 15:16
To: sqlite-users@sqlite.org
Subject: Re: [sqlite] accurate progress indication
On 5/10/06, Allan, Mark [EMAIL PROTECTED] wrote:
Jay,
Yes there is a index on the date column. The delete
operation is on date, specifically dates older than a date
entered by the user (so he/she can clear out old records and
keep newer ones). I don't think the time taken is slowed by
the time SQLite takes to find the rows as I do a SELECT COUNT
with the same query and this does not take as long as the
DELETE. I don't know why it takes so long, I dont think it is
the writing to the file as this should only happen on COMMIT
and I can see in debug output that the COMMIT only takes a
few seconds. It is before COMMIT that takes the time.
Is it possible to use a profiler tool on your embedded system?
One technique I've seen for debugging systems like that is to
make a null modem
cable (either serial or parallel) and hook the embedded
system to a terminal
or another computer. You can then put write statements into
your code to
write debugging info to the port for collection and analysis.
It's pretty
simple if you don't have any better tools. You can at least
see what part
of the code is taking so long to execute.
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.
[sqlite] accurate progress indication
Hi, We would like to be able to show a (reasonably) accurate progress bar when we are deleting a large number of records from our database. Is there a way in which this can be done. We are using SQLite version 3.2.7. I have had a look at the sqlite3_progress_handler() API function but it appears that the problem is knowing how many opcodes are required to complete the transaction before it is run. Has anyone tried something like this before? Any help will be gratefully received. Mark 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.
[sqlite] disabling journalling of the database - side affects?
Can anybody tell me if I will suffer any sideaffects if I disable the journalling of the database file with version 3.2.7? We have changed the SQlite code locally to omit the journalling of the database file by supplying 1 as the value for omitJournal to calls to the function sqlite3BtreeFactory. We have done this as we do not want the performance overhead of doubling the amount of writes we make as we are using an NOR flash filing system and this is not particularly quick. Can anybody help me? Thanks Mark 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: [sqlite] disabling journalling of the database - side affects?
I definately have a problem when I disable journalling on version 3.2.7 of SQLite. I dont see the same problem with 3.2.1 of SQlite. I disable the journalling of the database file as described in my earlier email. With version 3.2.7 this causes SQlite to not sync the database file after some updates (i.e. sqlite3OsSync() is not called). This means that if power is lost before the database file is explicitly closed we are losing those changes. I realise that the purpose of the journal file is to allow the database to return to its last valid state if power is lost during an update, however we believe that in our system we will not need this as our flash filing system is 100% power safe. i.e. it will return to the last flushed state of the file on power loss. So we believe that not only is the journalling of the database time consuming it is also unnecessary for us, which is a little annoying. If this is a bug in 3.2.7 of SQlite can it be fixed? If I cannot disable the journal file safely by the mechanism described in my previous email then can somebody please indicate how I can disable journalling of the database safely. Regards Mark -Original Message- From: Allan, Mark [mailto:[EMAIL PROTECTED] Sent: 01 November 2005 09:52 To: sqlite-users@sqlite.org Subject: [sqlite] disabling journalling of the database - side affects? Can anybody tell me if I will suffer any sideaffects if I disable the journalling of the database file with version 3.2.7? We have changed the SQlite code locally to omit the journalling of the database file by supplying 1 as the value for omitJournal to calls to the function sqlite3BtreeFactory. We have done this as we do not want the performance overhead of doubling the amount of writes we make as we are using an NOR flash filing system and this is not particularly quick. Can anybody help me? Thanks Mark 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. 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: [sqlite] disabling journalling of the database - side affects?
Thanks for your detailed reply. Would you mind answering my follow up queries? The ability to disable journaling is not a supported behaviour of SQLite. If you can get it to work, that's great. But if not, that is not considered a bug. Ok so the ability to not be able to disable journaling is not a bug. However would you consider implementing this as a new feature in a future revision of SQLite? I am unsure how many other users would find the disabling of the journal file a useful feature? I know here we would really appreciate this. We have seen information on the web indicating that turning off journaling would help performance, See section 3.3 at http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html. Although it does not state a mechanism for doing so. So maybe there are other users that would appeciate this as well as us? Indeed not journaling the file did help performance a great deal, but of course we have to prioritise data integrity over speed. We do believe however that journaling the database for us is of no benefit as our filing system is 100% fail safe and will return to the last flushed state of the file on power loss. Ideally we want to be able to use the latest versions of SQLite as they are released and as such don't want to stay with 3.2.1 especially as we may have been inadvertently benefiting from what was actually a bug anyhow. Regards Mark -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 01 November 2005 11:49 To: sqlite-users@sqlite.org Subject: Re: [sqlite] disabling journalling of the database - side affects? Allan, Mark [EMAIL PROTECTED] wrote: I definately have a problem when I disable journalling on version 3.2.7 of SQLite. I dont see the same problem with 3.2.1 of SQlite. I disable the journalling of the database file as described in my earlier email. With version 3.2.7 this causes SQlite to not sync the database file after some updates (i.e. sqlite3OsSync() is not called). This means that if power is lost before the database file is explicitly closed we are losing those changes. I realise that the purpose of the journal file is to allow the database to return to its last valid state if power is lost during an update, however we believe that in our system we will not need this as our flash filing system is 100% power safe. i.e. it will return to the last flushed state of the file on power loss. So we believe that not only is the journalling of the database time consuming it is also unnecessary for us, which is a little annoying. If this is a bug in 3.2.7 of SQlite can it be fixed? If I cannot disable the journal file safely by the mechanism described in my previous email then can somebody please indicate how I can disable journalling of the database safely. The ability to disable journalling is not a supported behavior of SQLite. If you can get it to work, that's great. But if not, that is not considered a bug. The omitJournal flag on sqlite3BtreeFactory() is used for transient tables that are never rolled back and which we do not care about if there is a program crash or power failure. The system should never call sqlite3OsSync() on such files because sqlite3OsSync() is an expensive operation (on most platforms) and for a transient file it does not accomplish anything useful. If version 3.2.1 was calling sqlite3OsSync() on unjournalled files, then that was a performance bug. I have no specific memory of fixing that problem in 3.2.7, but a lot of little problems were fixed in between those two releases, so it seems plausible that this was one of them. So my best guess is that if your journal-less use of SQLite worked with version 3.2.1 then that was due to a bug in 3.2.1 that has since been fixed - not a bug that has been introduced. -- 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: [sqlite] disabling journalling of the database - side affects?
Gerry, We are using a flash filesystem. The changes are made to a separate area of the flash, when the file is flushed then the descriptor blocks are updated to point to the new block of flash. If the power is lost before the file is flushed then the descriptor is not updated and therefore the file is still in the same state as it was prior to any of the writes. The file is only changed when it is flushed or closed. We believe based on this that we do not need to journal the database file. Indeed we had no problems with 3.2.1 albeit as we were benfiting from flushes that where not supposed to be happening. Regards Mark -Original Message- From: Gerry Snyder [mailto:[EMAIL PROTECTED] Sent: 01 November 2005 14:58 To: sqlite-users@sqlite.org Subject: Re: [sqlite] disabling journalling of the database - side affects? Allan, Mark wrote: We do believe however that journaling the database for us is of no benefit as our filing system is 100% fail safe and will return to the last flushed state of the file on power loss. Mark, I am probably in over my head, as usual, but how do you recover if power goes down during the write process for an update; that is, if some of an update is written to the file and some of it is not? I guess it must be a journalling fs. Double journalling would indeed be inefficient. Maybe it might be easier to turn off the fs journalling than that in sqlite? Gerry -- -- Gerry Snyder American Iris Society Director, Symposium Chair in warm, winterless Los Angeles -- USDA zone 9b, Sunset 18-19 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: [sqlite] Very Slow delete times on larger databases, please help!
Yes we found this out too. We never vacuum the file, it is acceptable for it to just get larger not smaller. We generate an indication on the current database capacity not from the file size but from the total number of pages in the database file minus the number of free pages in the database. So these times are not affected by vacuum. Thanks Mark -Original Message- From: Brett Wilson [mailto:[EMAIL PROTECTED] Sent: 26 October 2005 19:22 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Very Slow delete times on larger databases, please help! Vacuuming is just slow. I don't think there is much you can do except don't do it unless you really need it, and don't turn on autovacuum. Brett On 10/26/05, R S [EMAIL PROTECTED] wrote: In my case Delete happens reasonably OK but Vaccuuming takes incredibly long? On 10/21/05, Allan, Mark [EMAIL PROTECTED] wrote: Thanks to both Christian Smith and John Stanton for your posts. On Wed, 19 Oct 2005, Christian Smith wrote: 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? Our cache size is 75 pages of 8192 bytes = 600Kb. The maximum size of an EXAMINATIONS record is about 500 bytes, 450 bytes of this is a varchar field. In the test example the average size of an EXAMINATIONS record is 60 bytes as not much text is saved. The maximum size of a SPIRO_TEST record is about 5Kb, these will vary from test to test, but for the test example the size of each SPIRO_TEST record is fixed to approx 1Kb. Based on this I dont think that we should be thrashing the cache. I am however unsure how SQlite works here. 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. The maximum CPU speed of our ARM7 chip is 71Mhz. 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. I have tried permanently dropping the indexes on EXAM_TYPE and STATUS_FLAG and this gives some improvement in time. Indeed it does seem that the STATUS_FLAG index is worthless and in the initial version of the software we will have only 1 EXAM_TYPE (although this will increase for each module we release over the next
RE: [sqlite] Optimal page size
There doesn't appear to be any real documentation over what page size to use. I think it is more of a case of experimenting and determining which is best for your system/application. In the archive I found an article stating that for optimum performance on Win32 to match the page size with that of the disk cluster size. We are using SQLite on an embedded system with a Flash filesystem and found that SQLite performed best with 8K pages and we matched the sector size in Flash to 8K. (Where a sector size on our flash filesystem is equivalent to the cluster size on a FAT disk). We were able to effectivaley select the cluster size for our filesystem and found SQlite worked best with the larger page size although it does need to match the cluster size for optimum performance. Hope this helps. -Original Message- From: Zibetti Paolo [mailto:[EMAIL PROTECTED] Sent: 25 October 2005 11:40 To: sqlite-users@sqlite.org Subject: [sqlite] Optimal page size I could not find a document explaining how to find the optimal value for the page size parameter. Should I set the page size to match the allocation size (cluster size) of the file system ? Should I set it so that each page contains exactly a given number of records (i.e. no record is split between two pages) ? Thank you, bye 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: [sqlite] Very Slow delete times on larger databases, please help!
Thanks to both Christian Smith and John Stanton for your posts. On Wed, 19 Oct 2005, Christian Smith wrote: 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? Our cache size is 75 pages of 8192 bytes = 600Kb. The maximum size of an EXAMINATIONS record is about 500 bytes, 450 bytes of this is a varchar field. In the test example the average size of an EXAMINATIONS record is 60 bytes as not much text is saved. The maximum size of a SPIRO_TEST record is about 5Kb, these will vary from test to test, but for the test example the size of each SPIRO_TEST record is fixed to approx 1Kb. Based on this I dont think that we should be thrashing the cache. I am however unsure how SQlite works here. 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. The maximum CPU speed of our ARM7 chip is 71Mhz. 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. I have tried permanently dropping the indexes on EXAM_TYPE and STATUS_FLAG and this gives some improvement in time. Indeed it does seem that the STATUS_FLAG index is worthless and in the initial version of the software we will have only 1 EXAM_TYPE (although this will increase for each module we release over the next few months). I have also tried the suggested method of dropping the EXAM_PATIENT_ID_INDEX index on the examinations table before delete and rebuilding it on completion. I cannot delete the remaining indexes as they are used during the delete operation and this slows the whole operation down. The latest changes have reduced the time to delete the same number of records from 3:45 minutes to 2:53 minutes. Still a long time to wait but any time saving is welcome, especially as the test is for a 50% full scenario so at 99% we can expect it to take 6 minutes. Thanks again for your help. If there are any other ideas on how we can optimise this further then please let me know. Mark 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,
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: [sqlite] Very Slow delete times on larger databases, please help!
Have you been able to investigate this yet? Any ideas or recommendations? I sent you the analyzer output to [EMAIL PROTECTED] as it was too large to post on here. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: 12 October 2005 14:58 To: sqlite-users@sqlite.org Subject: Re: [sqlite] Very Slow delete times on larger databases, please help! Allan, Mark [EMAIL PROTECTED] wrote: We are experiencing incredibly slow delete times when deleting a large number of rows:- We are using SQLite on an embdedded platform with an ARM7 processor, 2Mb RAM and 32Mb NOR Flash memory 25Mb of which is allocated for storage for our database. What operating system? Can you send the output of sqlite3_analyzer run against your database file prior to doing the delete? Have you tried upgrading to a later version of SQLite? -- 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.
