Re: [sqlite] Backing up a SQlite database
Rich Shepard uttered: On Fri, 9 Feb 2007, Mikey C wrote: This might be a dumb question, but is taking a backup of a live database simply a matter of copying the file to a backup device/drive? Yes. It's a regular file to your OS. As a matter of fact, you can copy the file to another name and open that other name to see the same tables and data as with the original. That's how I keep a backup of the database I'm developing. No, no, no! Copying the file is not atomic, and a live database may be updated part way through the copy. Use the sqlite shell .dump command, which will implement the necessary locking: $ sqlite3 db.file .dump > backup.sql The backed up file is a SQL script that will restore the database. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite db lock problem
Allan, Mark uttered: 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? SQLite has been known to be problematic using NFS drives, mainly due to flaky NFS locking implementations. I believe SMB has better integrated locking in the protocol, and so may be better than NFS with regards to locking reliability. 2) What are peoples opinions on whether SQLite is a good choice for this project? Any other alternatives? If the locking issues above are non-issues under SMB, SQLite should be at least as good as Access, and likely much better. 3) How severe is the database locking problem? How easy is it to unlock a locked database? How often on average will it occur? Given that you will have writers rarely updating the database, you should have little contention on the database. Locks under SQLite are advisery OS locks. If a SQLite application crashes while holding a lock, the OS should automatically clear that lock when the application exits. A crashed machine may be more problematic, as the server holding the SQLite database will likely have to wait for the client connection to time out before releasing the lock. This is all OS level, though, and the same issues would apply to Access as well. So if you're currently happy with Access's locking, then you should be OK with SQLite. This assumes Access's locking is based on the OS's locking mechanisms. 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. Sounds like SQLite should fit your needs well. Any help/suggestions will be gratefully received. Mark Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Question about speed of CASE WHEN
RB Smissaert uttered: Looking at the fastest way to convert a field in a table and wonder if in general an update with a CASE WHEN construction or an update with a join to a lookup table is faster. My guess is that the CASE WHEN form will be faster for small number of possibilities. It compiles into a straight list of compare instructions. These conversions are of this type: UPDATE A3SQL77D_J SET ENTRY_TYPE = (case when ENTRY_TYPE = 9 then 'Issue when ENTRY_TYPE = 2 then 'Note' when ENTRY_TYPE = 1 then 'Encounter' when ENTRY_TYPE = 8 then 'Authorisation' when ENTRY_TYPE = 11 then 'Prescription' when ENTRY_TYPE = 5 then 'Treatment' when ENTRY_TYPE = 3 then 'Problem' when ENTRY_TYPE = 13 then 'Discontinuation' when ENTRY_TYPE = 6 then 'Reminder' when ENTRY_TYPE = 14 then 'Adverse reaction' when ENTRY_TYPE = -1 then 'Unknown' when ENTRY_TYPE = 4 then 'Sub-problem' when ENTRY_TYPE = 7 then 'Battery' when ENTRY_TYPE = 10 then 'Return-Script' else ENTRY_TYPE end) So, an integer value to be converted to a limited number of strings. I could figure this out with some testing, but maybe there are some general rules that apply to this. Testing is probably the best way. However, the difference in speed may not be significant given the increased maintainability of the table lookup based solution. Also, would the order of the CASE WHEN options make a difference, speed-wise, so would it be faster to have the WHEN options in decreasing order of frequency? The sequence of code generated compares the cases in the order written. So the common cases should go first. RBS Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Strange performance behavior
Hubertus uttered:
Dear list,
sorry to just come up with another performance question. I build a yet small
database with one table. It has about 650.000 rows, 75 columns and
has at the moment about 650 Mb. It runs on a Intel Pentium M with 2 GHz. The
Laptop runs Suse 10.2 and does basicly nothing but this database.
sqlite3 -version is 3.3.8
This is the schema:
CREATE TABLE 'data'('nr' INTEGER PRIMARY KEY,
'year' INTEGER,
'month' INTEGER,
'day' INTEGER,
'sec' REAL,
'campId' TEXT,
'flightNr' INTEGER,
'1' REAL,
...
'71' REAL
);
CREATE INDEX sec on data(year,month,day,sec);
What a nasty schema! What exactly do the '1'...'71' fields represent? Are
they all used in each row? If not, you might be better off putting the
data in a seperate table and joining the data.
I experience a big variability of time a query needs:
~database> time sqlite3 data.db 'select "14" from data where campId="polstar98" and
"14">-;' >/dev/null
real0m3.115s
user0m1.748s
sys 0m1.368s
~/database> time sqlite3 data.db 'select "14" from data where campId="polstar98" and
"14">-;' >/dev/null
real0m3.139s
user0m1.756s
sys 0m1.380s
~/database> time sqlite3 data.db 'select "42" from data where campId="stream94" and
"14">-;' >/dev/null
real0m50.227s
user0m4.692s
sys 0m3.028s
I've tried it with and without index and at different times. Most of the
time such simple queries take about 35 sec. Why is that so and what can
be done? I can live with 3 sec of response but not up to one minute and
the database is still not complete. Would pytables with hdf5 be an
alternative? Knowing that this is probably not the right place to ask...
The rows are probably quite big (each real value is 8 bytes), and would
not fit in a single low level SQLite BTree cell, but instead overflow
using an overflow page per row. As well as being inefficient for access of
columns in the overflow page, it is also massively space inefficient, as
the overflow page is not shared and most of it's space is probably wasted.
Tips, suggestions, recommendation are gratefuly appreciated!
If you can't change the schema, your best bet is to increase the page size
of the database, which will hopefully allow you to keep entire rows
together without using overflow pages. Create a new database, and use:
PRAGMA page_size=4096;
then import your existing data from your old database. Something like:
$ rm new.db
$ sqilte3 new.db
sqlite> PRAGMA page_size=4096;
sqlite> ATTACH 'old.db' AS old;
sqlite> CREATE TABLE data AS SELECT * FROM old.data;
Thanks in advance
Hubertus
Christian
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Auto Vaccum and fragmentation?
Ludvig Strigeus uttered: Assuming I have an autovacuum database that primarily stores 32k blobs. If I add/remove lots of rows, will this lead to excessive fragmentation of the overflow chains, or does Sqlite do anything to try to unfragment the pages belonging to a single row? I believe auto-vacuum will simply shuffle data so as to compact free space, rather than ordering table and overflow data into contiguous regions. Hence, you'll still have fragmentation. Thanks, Ludvig -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] How to restrict the peak heap usage during multiple inserts and updates?
Kalyani Tummala uttered: I am planning to use sqlite as a database for storing and retrieving media data of about 5-10k records in a device whose main memory is extremely small. A sequence of insert statements increasing the heap usage to nearly 70K(almost saturating point) which is crashing my application. I want to restrict this to 30K. I tried closing database and reopen after some inserts but of no use. I have observed that, when I open the database with about 1K to 2K records in it, inserts and updates take more heap and also gradually increase than a a database with less than 1k records in it. When updating the database, SQLite will keep a bitmap representing modified pages in memory, so as to manage the rollback journal. Therefore, making your minimum pages size smaller will now require more bits to track all the potentially modified pages in the database file. Instead, using the stock SQLite parameters, increase the page size and reduce the number of buffers. Increasing the page size will reduce the number of pages being tracked, as well as increasing the number of rows in each page. But, depending on how big your database is, this may not be a significant amount of memory. How big is a typical database? My objective is to reduce the peak heap usage during inserts, updates and also deletes with little or no performance degradation. You'll get a certain amount of slowdown when reducing the number of available buffers, as you'll be spilling dirty buffers to disk more often. Please suggest me if I can do anything to do so. You could try profiling memory usage before randomly changing parameters. In the source, perhaps on a test machine rather than the target platform, replace sqliteMalloc with a macro to log memory allocation, along with source file and line number information, something like what is done now with memory debugging turned on (see src/malloc.c and src/sqliteInt.h). Thank you in advance Kalyani -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 6:51 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates? Since you are only using part of Sqlite have you considered using a much smaller footprint storage system which only implements the functions you are using? Kalyani Tummala wrote: Hi joe, Thanks for your response. In order to reduce the footprint size, I have bypassed parser completely and using byte codes directly as my schema and queries are almost compile time fixed. Hence I am not using sqlite3_prepare(). The following is the schema and inserts I am using. CREATE TABLE OBJECT( PUOIINTEGER PRIMARY KEY, Storage_Id INTEGER, Object_Format INTEGER, Protection_Status INTEGER, Object_Size INTEGER, Parent_Object INTEGER, Non_Consumable INTEGER, Object_file_nameTEXT, NameTEXT, File_Path TEXT ); CREATE TABLE AUDIO( PUOIINTEGER PRIMARY KEY, Use_Count INTEGER, Audio_Bit_Rate INTEGER, Sample_Rate INTEGER, Audio_Codec_TypeINTEGER, Number_of_Channels INTEGER, Track INTEGER, Artist TEXT, Title TEXT, Genre TEXT, Album_Name TEXT, File_Path TEXT ); INSERT INTO OBJECT VALUES ( 7, 65537, 12297, 0, 475805, 6, 0, 'ANJANEYASTOTRAM.mp3', NULL, 'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\ANJANEYASTOTRAM.mp3' ); INSERT INTO AUDIO VALUES ( 7, 6, 144100, 0, 0, 0, 6, NULL, NULL, NULL, NULL, 'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\ANJANEYASTOTRAM.mp3' ); INSERT INTO OBJECT VALUES ( 8, 65537, 12297, 0, 387406, 6, 0, 'BHADRAM.mp3', NULL, 'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\BHADRAM.mp3' ); INSERT INTO AUDIO VALUES ( 8, 6, 144100, 0, 0, 0, 6, NULL, NULL, NULL, NULL, 'C:\\MTPSim\\Store0\\Music\\Artist\\Album\\BHADRAM.mp3' ); Warm regards Kalyani -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 29, 2007 9:42 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] How to restrict the peak heap usage during multiple inserts and updates? I am working at porting sqlite ( ver 3.3.8 ) on an embedded device with extremely low main memory. I tried running select queries on the tables( with about 2k records each having about 5 strings) and they do well within 20kB of runtime heap usage. But, when I try new insertions, the heap usage grows tremendously (about 70 kB at peak). Perhaps preparing the statements (sqlite3_prepare) might decrease RAM use somewhat. Can you post an example of your schema and these insert statements? Choose the right car based on your needs. Check out Yahoo! Autos ne
Re: [sqlite] Does Attach improve Concurrency ?
[EMAIL PROTECTED] uttered: When you have a connection with multiple attached databases and the connection acquires an exclusive lock, does it always lock all attached databases or does it keep track of which databases require the lock? 1st process: C:\Documents and Settings\Administrator\Pulpit>sqlite3.exe a.db SQLite version 3.3.17 Enter ".help" for instructions sqlite> attach 'b.db' as b; sqlite> begin exclusive; 2nd: C:\Documents and Settings\Administrator\Pulpit>sqlite3.exe b.db SQLite version 3.3.17 Enter ".help" for instructions sqlite> create table tab(col); SQL error: database is locked So it locks all attached databases. Not by default. BEGIN EXCLUSIVE is not the default transaction locking mode. By default, BEGIN will not lock anything until needed, in which case you can have different sessions locking different attached databases in different ways. SQLite will not block readers until it gets an EXCLUSIVE lock, which is usually upon committal of a transaction, or the spillage of pages from a full page cache. But that will only be on the database that is being updated. BEGIN EXCLUSIVE is explicit and applied to all attached databases. But upgrading an attached database to an EXCLUSIVE lock does not upgrade locks on other attached databases. So, by default, using multiple attached databases may increase concurrency, but watch for deadlock conditions. Does using separate databases and attaching them improve concurrency (by providing finer-grained locking)? Yes it does. You can open a connection to a memory database, store each table in a separate database and attach them if needed. I'v already changed sqlite to do it transparently. It still has a lot of bugs, but it has already proven to work. But if you don't mind attaching databases manually originall sqlite will work perfectly. As stated above, not needed if you avoid using BEGIN EXCLUSIVE. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Does Attach improve Concurrency ?
Eduardo Morras uttered: At 19:32 01/06/2007, you wrote: When you have a connection with multiple attached databases and the connection acquires an exclusive lock, does it always lock all attached databases or does it keep track of which databases require the lock? Does using separate databases and attaching them improve concurrency (by providing finer-grained locking)? It locks all attached databases. No, it does not improve concurrency but i can improve speed if database files are on more than one phisical disk. The sqlite bottleneck is i/o access on most cases. Tip for performance on Linux and ext3. Mount your database filesystem with "data=journal" option to write data to the journal before being written in place. This reduces latency drastically, as the journal is contiguous and written at the full IO rate of the device without seeks. An example of the effect it has on a test I did can be found here: http://www.osnews.com/permalink.php?news_id=16522&comment_id=184137 Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Proper way to transfer a live sqlite database
Rich Rattanni uttered: The databases will be in flux, and I didnt necessairly want to suspend the application that is performs reads and writes into the database. A simple copy worries me because it seems like messing with SQLITE on the file level is dangerous since you circumvent all the protection mechanisms that provide fault tolerance. I didnt want to have to worry about if the database has a journal file that needs copied, or any other situation like that. I figured using the SQLITE API to do the copy would award me some protection against corruption. You're right to be cautious. Never copy an in use database if that database could possibly be updated. If you open the database, and obtain a SQLite read lock on it, you can be sure it is not going to be modified, and can be safely copied at the OS level. Best way of doing this is to execute a 'BEGIN IMMEDIATE', copying the database file, then executing a 'ROLLBACK' to end the transaction. To limit the time the database is locked, I suggest copying the file to a local filesystem first, then transferring across the network after the lock is released. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Journal File Optimization
Andre du Plessis uttered: How can one optimize the creation of the journal file. The problem is this, for our system which is an event based one each message needs to be insterted and committed to the database (guaranteed), this results in a commit per insert, this was obviously unacceptably slow and according to the lists by design you need to do more bulk transactions, and with some efford I modified it with a combination of bulk and temporary tables to do quite a few at a time, but the problem remains that committing is terribly slow. What sort of latency is acceptable for commits? How many event sources are there? How do you track commits against the event source? If you require guaranteed single inserts, a client/server database may be better performing, as writes can be better optimised in a redo/undo journal used by client/server systems, rather than the undo journal used by SQLite. I'm thinking the journal file, the fact that it is created written and deleted each time which is slowing it down, Is there not a way to create and set up a permanent journal file pre-allocated to a certain size, so sqlite does not have to go through the OS each time to create and delete this file? If you require multi-process access to the database, then Dan's suggestion may not work, and you'll have to optimise the sync case. You can do this, as suggested elsewhere by turning of synchronous updates. This leaves you at the mercy of the OS to guard against crashes. Depending on your OS, you may be able to optimise OS sync. Solaris ZFS is a tree based FS, a bit like WAFL by NetApp. Synchronous writes are aggregated and written to minimize seeks. I've not done any benchmarking on ZFS, so YMMV (time to power up the Solaris Express partition, me thinks.) Linux ext3 can write data to the same journal that FS meta-data is written to, which can greatly enhance single insert speeds due to the journal being written at disk IO speed without seeks. Tests I've done indicate a doubling of performance over regular ordered data writing that is the default for ext3. Finally, on NetBSD (where LFS is still actively developed) you may see performance improvements using LFS, for similar reasons to the ext3 case above. I've not, however, tried that recently, so again YMMV. Of course, if you're not running Solaris, Linux or NetBSD, you may be stuck as not many other OS/FS support such optimisations. Along the same lines of this question, is there a way to fix the initial size of the DB, ie set it to pre-allocate 700mb for instance so that no growing of the db file is needed until the space is exceeded, may also speed things up. Probably won't improve speed that much, especially as you approach your working database size. Avoid vacuuming your database, so that free pages are recycled and the database size will stabilise. If your dataset is likely to constantly grow without bounds, then SQLite may not be your optimal choice in the long run, and a client/server database may provide better performance over the long term. You might also try increasing your page size, up to the maximum of 32768, so that new page allocations are required less. Thank you very much in advance. -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Recovery After Crash
Asif Lodhi uttered: Hi Kees, Thanks for replying. On 6/17/07, Kees Nuyt <[EMAIL PROTECTED]> wrote: >... thankful if you experts would give me an "accurate" and fair >picture of the crash-recovery aspects of SQLite - without any hype. I'm not sure if you would qualify this as hype, but sqlite is used in many end-user products, ranging from operating systems .. Basically, I intend to use sqlite's data capacity as well - I mean 2^41 bytes - for reasonably sized databases. Well, not as much as 2^41 but somewhere around 2^32 to 2^36 bytes. I would like to know if the "crash-recovery" feature will still work and the high-performance mentioned will be valid even if I have this kind of a data volume. And yes, I am talking about highly normalized database schemas with number of tables exceeding 80. Please reply assuming I tend to come up optimized db & query designs - keeping in view general rules for database/query optimizations. SQLite is not optimised for large datasets. Data recovery will work, as advertised, in the general case including large datasets, but the memory footprint of the library increases as the size of the database grows. Consider using larger pages than the default 1024 bytes to limit the number of pages SQLite must track. Other than that, the performance should degrade predictably with increasing datasets, given that SQLite uses the same BTree(+) based algorithms used by most database engines. -- Thanks again and best regards, Asif - To unsubscribe, send email to [EMAIL PROTECTED] - -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Lemon Parser - Modular & Extensible ?
Uma Krishnan uttered: Hello: Is lemon parser modular and extensible? Extensible to do what? It generates parsers, and is self contained. It does a single job, and does it well. What more could you ask for? Thanks Uma Asif Lodhi <[EMAIL PROTECTED]> wrote: Hi Everybody, I have just joined this mailing list as Sqlite looks like a good software solution to my needs. What I need right now is RE-assurance of "crash-recovery" that is mentioned on your front page. So, I would be thankful if you experts would give me an "accurate" and fair picture of the crash-recovery aspects of SQLite - without any hype. -- Best regards, Asif - To unsubscribe, send email to [EMAIL PROTECTED] - -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Lemon Parser - Modular & Extensible ?
Uma Krishnan uttered: Hey, There's no need to be offensive. I did not mean to be critical. Far from it, it does a great a job (far more than I'm capable of producing). What I was trying to find out was, if it is possible for a .y files to be broken such that it can be built on top on other .y files. Sorry if I came across as offensive. That was not the intention. I was just a little confused about the question. I think lemon can only handle single input files. But you can can include C source into your output C file using the %include directive. Check out the documentation at, if you haven't already done so: http://www.hwaci.com/sw/lemon/lemon.html Now, what may draw some critical analysis is top posting and hijacking an existing thread for a new topic... [snip] Not sure if this is the right group. But could not find a lemon parser user group. This is the best group to ask. While not tied to SQLite, it appears to be maintained as part of SQLite (but I may be wrong.) Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Cache invalidation after insert statements.
pompomJuice uttered: I suspected something like this, as it makes sense. I have multiple binaries/different connections ( and I cannot make them share a connection ) using this one lookup table and depending on which connection checks first, it will update the table. What is your working set like? Are all processes on the same machine? Sounds like you might benefit from increasing the amount of RAM on this machine. You may be thrashing the OS cache, as your lookup process hogs memory for it's own cache, pushing out old pages from the OS filesystem cache. If RAM upgrade is not feasible, then try reducing the cache of the lookup process, so that the OS cache isn't forced out of memory so easily. Then, when the lookup process has a cache miss, it's missed page is more likely to be in the OS memory cache, and copied to the lookup process at memory copy speed. As you may have guessed, choosing the correct cache size for the lookup process may involve several tuning iterations. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Converting from 2.8.x to 3.x?
Gilles Ganault uttered: Hello As we move from a 2.8.x file-based solution to a 3.x c/s solution, we'll have to convert databases from one format to the other. What's the easiest way to do this? sqlite olddb .dump | sqlite3 newdb Thank you G. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Milliseconds
John Stanton uttered:
The Sqlite date/time routimes have a resolution to seconds, not milliseconds.
If you want milliseconds from SQL implement your own user defined functions
which give you milliseconds. You would access the time functions using the
API of the underlying OS.
You might choose to implement your underlying storage as a 64 bit integer.
If you use the julianday representation, the integer component is the
number of days since "noon in Greenwich on November 24, 4714 B.C", with
the fractional part being the fraction of that day. Hence, the resolution
is determined by the fractional component of the real number. Now, in the
UK, I get the following:
sqlite> select julianday('now');
2454295.1407767
The integer component consumes probably 21 bits of the available 52 bits
mantissa of an IEEE-754 64-bit real. That leaves 31 bits for the fractions
of a day, giving a resolution of 1/24855 of a second:
2^31/(60*60*24) = 24855.134814814814814814814814815
Plenty enough for milli-second resolution.
Probably not very good for embedded applications if an FPU is not
available.
Christian
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Get the data from previous months: please, help me to optimize the query...
Joe Wilson uttered: CREATE TABLE 'Months' ( IDMonth INTEGER PRIMARY KEY NOT NULL, MonthRef INTEGER ); (where MonthRef is the date of the first day of the month - created in the code) Using what epoc? CustomerData -- CREATE TABLE 'CustomerData' ( IDCustomerData INTEGER PRIMARY KEY NOT NULL, IDMonth INTEGER, NdgSingolo TEXT NOT NULL DEFAULT '0', NdgCliente TEXT NOT NULL DEFAULT '0', FatturatoNdg REAL DEFAULT 0 , FatturatoGruppo REAL DEFAULT 0 , MargineIntermediazioneLordo REAL DEFAULT 0 , MargineInteresse REAL DEFAULT 0 , MargineServizi REAL DEFAULT 0 , RaccoltaDirettaSM REAL DEFAULT 0, RaccoltaIndirettaSM REAL DEFAULT 0 , ImpieghiSM REAL DEFAULT 0 , RaccoltaDirettaSP REAL DEFAULT 0 ); (where IDMonth is the foreign key to the Months table). CustomerData contains the data of a single Customer (NdgSingolo), for the selected month ID. What I need to do is to get "some" data in a record from the previous year, and from the end of the previous year. For instance, if the current month is March 2007, then I need the data of March 2006, and of December 2006. To accomplish this, I created these two views: _VCustDataMonths -- CREATE VIEW _VCustDataMonths AS SELECT * FROM CustomerData A LEFT OUTER JOIN Months B ON A.IDMonth = B.IDMonth; _VCustomerData_1 -- CREATE VIEW _VCustomerData_1 AS SELECT AC.*, M1.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_m1, AP.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_ap, M1.MargineInteresseAS MargineInteresse_m1, AP.MargineInteresseAS MargineInteresse_ap, FROM _VCustDataMonths AC LEFT OUTER JOIN _VCustDataMonths M1 ON AC.NdgSingolo = M1.NdgSingolo AND AC.NdgCliente = M1.NdgCliente AND M1.MonthRef = date( AC.MonthRef, '-1 year' ) LEFT OUTER JOIN _VCustDataMonths AP ON AC.NdgSingolo = AP.NdgSingolo AND AC.NdgCliente = AP.NdgCliente AND AP.MonthRef = date( AC.MonthRef, 'start of year', '-1 month' ); Now, the query _VCustomerData_1 (that is the one that I need) takes *145,23 seconds* to run!! (with about 4000 records in the CustomerData table). This is really too much... I have indexes in the Months and CustomerData tables for the fields NdgSingolo and NdgCliente... How could I increase the performance of this query to get reasonable results?? Much faster - add 3 new fields in CustomerData which you can populate via SQLite's trigger mechanism, or an explicit UPDATE prior to your SELECT: MonthRef-- populate from Months table MonthRef2 -- date(Months.MonthRef, '-1 year') MonthRef3 -- date(Months.MonthRef, 'start of year', '-1 month') This way you can avoid several joins with the Months table and avoid the use of the slow view. This is leaving you open to data errors. Better to use a single IDMonth and calculate the join values at run time. Even better, avoid the MonthRef table completely, and use the first day of the month directly. My take: CREATE TABLE 'CustomerData' ( IDCustomerData INTEGER PRIMARY KEY NOT NULL, IDMonth INTEGER, NdgSingolo TEXT NOT NULL DEFAULT '0', NdgCliente TEXT NOT NULL DEFAULT '0', FatturatoNdg REAL DEFAULT 0 , FatturatoGruppo REAL DEFAULT 0 , MargineIntermediazioneLordo REAL DEFAULT 0 , MargineInteresse REAL DEFAULT 0 , MargineServizi REAL DEFAULT 0 , RaccoltaDirettaSM REAL DEFAULT 0, RaccoltaIndirettaSM REAL DEFAULT 0 , ImpieghiSM REAL DEFAULT 0 , RaccoltaDirettaSP REAL DEFAULT 0 ); CREATE INDEX CustomerDataByMonth ON CustomerData(IDMonth,NdgCliente,NdgSingolo); DROP VIEW IF EXISTS _VCustomerData_1; CREATE VIEW _VCustomerData_1 AS SELECT AC.*, M1.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_m1, AP.MargineIntermediazioneLordo AS MargineIntermediazioneLordo_ap, M1.MargineInteresseAS MargineInteresse_m1, AP.MargineInteresseAS MargineInteresse_ap FROM CustomerData AC LEFT OUTER JOIN CustomerData M1 ON AC.NdgSingolo = M1.NdgSingolo AND AC.NdgCliente = M1.NdgCliente AND M1.IDMonth = date(AC.IDMonth,'-1 year') LEFT OUTER JOIN CustomerData AP ON AC.NdgSingolo = AP.NdgSingolo AND AC.NdgCliente = AP.NdgCliente AND AP.IDMonth = date(AC.IDMonth,'start of year', '-1 month'); Now you have the same speed as Joe's solution (similar query plan): sqlite> explain query plan select * from _VCustomerData_1 ; 0|0|TABLE CustomerData AS AC 1|1|TABLE CustomerData AS M1 WITH INDEX Cust
Re: [sqlite] Milliseconds
Scott Baker uttered:
Christian Smith wrote:
If you use the julianday representation, the integer component is the
number of days since "noon in Greenwich on November 24, 4714 B.C", with
the fractional part being the fraction of that day. Hence, the
resolution is determined by the fractional component of the real number.
Now, in the UK, I get the following:
sqlite> select julianday('now');
2454295.1407767
The integer component consumes probably 21 bits of the available 52 bits
mantissa of an IEEE-754 64-bit real. That leaves 31 bits for the
fractions of a day, giving a resolution of 1/24855 of a second:
2^31/(60*60*24) = 24855.134814814814814814814814815
Plenty enough for milli-second resolution.
Probably not very good for embedded applications if an FPU is not
available.
I'm a little confused by the math... help me work this out.
sqlite> SELECT julianday('now');
2454295.20404931
That gives me days since the Julian epoch. If I multiply by 86400 I
should get seconds since the Julian epoch.
sqlite> SELECT julianday('now') * 86400;
212051105903.613
That leaves me three decimal points of precision for seconds. So
that's thousandths of a second? Where do you get 24000ths of a second?
The floating point representation used by SQLite maps to the IEEE 754
64-bit representation, which has 1 bit for sign, 11 bits for for the
exponent, leaving 52 bits (effectively 53 bits including the implied
leading 1 binary digit) for the precision.
Given that, the 2454295.20404931 uses 21 bits for the integral part of the
number (before the floating point) including the implied initial 1 digit.
That leaves 52-21 bits of precision, or 31 bits for the fraction of a day.
So, you have 1/2^31 days resolution, or 86400/2^31 seconds resolution.
That is 1/24855.134814814814814814814814815 second resolution.
Christian
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] In-Memory Database: Delete rows on a Table increases the memory usage.
Lokesh Babu uttered:
Hello Folks,
When I perform the DELETE operation on a Table using In-Memory Database
(":memory:"), the memory usage increases.
I tried using PRAGMA auto_vacuum=1; /* result - nothing works */
Even I tried using VACUUM table_name; /* this too isn't work */
if I perform DROP the table operation, memory usage works. Deleting the rows
doesn't work.
Anybody please help me in resolving this.
Memory usage goes up because SQLite must temporarily store copies of the
old pages that store the deleted rows, in order to allow a future rollback
if required.
Once a commit is done, the old pages are free'd, but by that time the
memory footprint has already increased. Not many libc implementations
release heap memory back to the OS once it's allocated.
Thanks in advance,
Lokee
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] In-Memory Database: Delete rows on a Table increases the memory usage.
Lokesh Babu uttered:
Hi Smith,
Is there any way where I can free the old pages and without using rollback
feature.
Because I'm much concerned about memory usage.
As soon as I delete some records, It should free up the memory.
Use a libc that has a malloc implementation that releases excess memory
back to the operating system. I don't know of specific instances of libc
that do this, so I can't help further, sorry.
Thanks
On 8/1/07, Christian Smith <[EMAIL PROTECTED]> wrote:
Lokesh Babu uttered:
Hello Folks,
When I perform the DELETE operation on a Table using In-Memory Database
(":memory:"), the memory usage increases.
I tried using PRAGMA auto_vacuum=1; /* result - nothing works */
Even I tried using VACUUM table_name; /* this too isn't work */
if I perform DROP the table operation, memory usage works. Deleting the
rows
doesn't work.
Anybody please help me in resolving this.
Memory usage goes up because SQLite must temporarily store copies of the
old pages that store the deleted rows, in order to allow a future rollback
if required.
Once a commit is done, the old pages are free'd, but by that time the
memory footprint has already increased. Not many libc implementations
release heap memory back to the OS once it's allocated.
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] In-Memory Database: Delete rows on a Table increases the memory usage.
Scott Derrick uttered:
are you saying this is a memory leak?
sqlite never gives back the unused memory?
No, libc never gives back the memory.
It is not leaked because the malloc implementation keeps a reference to
all the free'd heap memory in tracking it for future requests.
Christian Smith wrote:
Lokesh Babu uttered:
Hello Folks,
When I perform the DELETE operation on a Table using In-Memory Database
(":memory:"), the memory usage increases.
I tried using PRAGMA auto_vacuum=1; /* result - nothing works */
Even I tried using VACUUM table_name; /* this too isn't work */
if I perform DROP the table operation, memory usage works. Deleting the
rows
doesn't work.
Anybody please help me in resolving this.
Memory usage goes up because SQLite must temporarily store copies of the
old pages that store the deleted rows, in order to allow a future rollback
if required.
Once a commit is done, the old pages are free'd, but by that time the
memory footprint has already increased. Not many libc implementations
release heap memory back to the OS once it's allocated.
Thanks in advance,
Lokee
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] [SQLite improve productivity]
A common issue of high latency transactions. SQLite has a high
per-transaction overhead, which can be amortized across multiple INSERTs
or UPDATEs to improve the average INSERT rate. You are doing a single
INSERT per transaction, so wrap multiple INSERTs inside a single "BEGIN"
... "END" transaction.
See:
http://www.sqlite.org/cvstrac/wiki?p=PerformanceConsiderations
Christian
Igor Mironchick uttered:
Hi.
How can I improve productivity of this code:
a_messenger_t::data_buff_t --> std::deque< some_struct >
//
char * errors = 0;
for( a_messenger_t::data_buff_t::const_iterator it = msg.m_buff.begin(),
last = msg.m_buff.end(); it != last; ++it )
{
// Converting long to std::string...
std::string sec( itos( it->m_time.sec() ) );
std::string usec( itos( it->m_time.usec() ) );
// Columns in 'data' table defined like (INTEGER, INTEGER, INTEGER,
TEXT)
char * sql = sqlite3_mprintf( "INSERT INTO data VALUES( '%q', '%q',
'%q', '%q' )",
m_sources_map[ it->m_source ].m_sid.c_str(),
sec.c_str(), usec.c_str(), it->m_value.c_str() );
// m_db --> sqlite3*
int ret = sqlite3_exec( m_db, sql, 0, 0, &errors );
if( ret != SQLITE_OK )
{
ACE_DEBUG( ( LM_INFO, ACE_TEXT( "[%D] %M -- %C\n" ), errors ) );
sqlite3_free( errors );
}
sqlite3_free( sql );
}
Any idea? This method are so slow - about 1 kB per second new data in my DB.
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] sqlite as server queries
Edwin Eyan Moragas uttered: hi group, i have several small questions for the group any experiences or thoughts shared would be greatly appreciated. 1) anybody used sqlite as a sql server? i'm thinking of say using the embedded sqlite in PHP5 or similar. 2) anybody ever implemented something like a single process of sqlite doing queries for a lot of networked clients? A few people have implemented such a solution. It loses one of the benefits of SQLite, however, in that SQLite is no longer admin free. 3) how big has your sqlite database grown? have you had any trouble managing the db? any bad experiences as to stability of the db file? Stability of the file? In what sense? Compatibility? Or resistence to corruption? Or size, perhaps? i am asking all of these is because i'm seriously considering sqlite to be used as my sql server for a project. how i'll implement it looks something like this: components of app: embedded web server sqlite some scripting language there will only be one sqlite process which will be forked when the web server is launched. queries to sqlite will be sent to the sqlite process via sockets. You can do this, but you may find it easier to embed SQLite right into your app, using whatever wrapper language binding your app is written in. That is how it's designed to be used. It'll also be faster that way as well, and easier to manage. i can see that the queries will be sequential. no problems there. i'm not worried with speed at the moment. i just want to know if this has been done before and i'd like to solicit wisdom from the group. I have thoughts on wrapping SQLite with FreeTDS, in order to provide networked access to legacy apps that expect a TDS server to talk to. But that is more for legacy reasons. You don't have this legacy burden by the sounds of it, so just embed SQLite. thank you. ./e Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] [SQLite improve productivity]
Igor Mironchick uttered: Thx, very helpfull reply. One more question: is it need to do "END" after "BEGIN" or enought "COMMIT"? You can use "COMMIT". Probably should do, as it is more descriptive about what is happening. Check the docs for transaction commands: http://www.sqlite.org/lang_transaction.html Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] sqlite as server queries
Edwin Eyan Moragas uttered: On 8/6/07, Christian Smith <[EMAIL PROTECTED]> wrote: 2) anybody ever implemented something like a single process of sqlite doing queries for a lot of networked clients? A few people have implemented such a solution. It loses one of the benefits of SQLite, however, in that SQLite is no longer admin free. how so? Because now you need to manage port numbers, multiple processes on potentially multiple machines. Not massive overhead, but still not as easy as starting or stopping your one process. 3) how big has your sqlite database grown? have you had any trouble managing the db? any bad experiences as to stability of the db file? Stability of the file? In what sense? Compatibility? Or resistence to corruption? Or size, perhaps? resistance to corruption in particular. thinking about it, this may be an OS issue but given that the OS is ok, how does sqlite handle it? SQLite uses a rollback journal along with timely OS level syncs to ensure the database is always in a consistant or recoverable state. SQLite can survive OS or hardware failure so long as the filesystem remains intact. thank you for the response. ./e Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] version 3.5.0 - Segv
[EMAIL PROTECTED] uttered: [EMAIL PROTECTED] wrote: Ken <[EMAIL PROTECTED]> wrote: Recompiled with: gcc -DSQLITE_THREADSAFE -I. -I../src ^^^ Should be -DSQLITE_THREADSAFE=1 The =1 is important in this case. This problem will likely come up again. To try and work around it, I have added a new (experimental) API to the latest version in CVS. Call sqlite3_threadsafe() To get back a boolean to indicate whether or not your build is threadsafe. May I suggest adding a call to this routine at the beginning of sqlitetest_thrd35.c and printing and error message and aborting if the library is not threadsafe? Is it not worth simply making the library threadsafe by default? There is basically no platform supported today that doesn't have some form of thread abstraction, the overhead of mutex locking is probably negligible, and if someone wants an absolutely no holds barred fastest single threaded implementation, then they can provide their own platform abstraction with no-op mutexes. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Difference between OR and IN [http://www.sqlite.org/php2004/page-052.html]
Once you get your first row back (corresponding to (a==1), simply halt
there and sqlite3_finalize() or sqlite3_reset the statement. You control
the execution and how many rows you want back.
RaghavendraK 70574 uttered:
Hi,
Ok.
Is there any way to tell the VDBE to stop execution moment it gets a record
from the IN list rather than continue to query for all the parameters?
I mean can it work like the C if clause
a = 1;
b = 100;
if( a == 1 or b == 10)
{
}
in the above case a is evauated but not b. Is this possible in SQL or SQLite?
Pls suggest.
regards
ragha
**
This email and its attachments contain confidential information from HUAWEI,
which is intended only for the person or entity whose address is listed above.
Any use of the information contained herein in any way (including, but not
limited to, total or partial disclosure, reproduction, or dissemination) by
persons other than the intended recipient(s) is prohibited. If you receive this
e-mail in error, please notify the sender by phone or email immediately and
delete it!
*
- Original Message -
From: Trevor Talbot <[EMAIL PROTECTED]>
Date: Sunday, September 2, 2007 1:03 am
Subject: Re: [sqlite] Difference between OR and IN
[http://www.sqlite.org/php2004/page-052.html]
On 9/1/07, RaghavendraK 70574 <[EMAIL PROTECTED]> wrote:
I want to know why
prepareStatement: select * from xxx where IN (?);
stmt.bind("abc,xyz,123"); is not supported for multiple
values.
It's not supported because it doesn't make sense. The parametric
binding mechanism is for single values; it's not a macro-like text
replacement system. With your syntax, how do I bind a set of
integers? Strings? Blobs?
One common use for parametric binding (besides convenience) is to
avoid SQL injection attacks. The example you posted doesn't do that;
you have to manually escape each individual value to make sure it's
valid syntax for the IN() group in text form. Why even use parameters
in that case? It's the same amount of work whether you build the
entire SQL statement or not.
All common databases I'm aware of work exactly the same way.
---
--
To unsubscribe, send email to [EMAIL PROTECTED]
---
--
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] multiple connections
Joe Wilson uttered: --- "D. Richard Hipp" <[EMAIL PROTECTED]> wrote: In 3.5, cache can be shared between all threads, but shared cache is still disabled by default. You have to invoke sqlite3_enable_shared_cache() to turn it on. I put a comment in the documentation that we might turn shared cache on by default in future releases. But until I better understand the backwards compatibility issues, I think it is probably better to leave it off for now. There's no quicker way to see if there's a problem than enabling it in a release by default. ;-) As we saw when 3.3.0 was release with a non-backword compatible change in storing booleans... Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] SQLite version 3.6.14
On Wed, May 06, 2009 at 10:36:50PM -0400, D. Richard Hipp wrote: > > A new optional extension is included that implements an asynchronous I/ > O backend for SQLite on either windows or unix. The asynchronous I/O > backend processes all writes using a background thread. This gives > the appearance of faster response time at the cost of durability and > additional memory usage. See http://www.sqlite.org/asyncvfs.html for > additional information. What are the benefits of using async I/O over "PRAGMA synchronous = OFF"? If AIO is used for the rollback journal as well, you've lost your ACID properties already, so you may as well just use "PRAGMA synchronous = OFF" anyway and keep the code simpler. Where I might be able to see the benefit of this background thread is if the background thread grouped all pending write requests into a single writev (or win32 equiv), which would reduce the system call count, but this may be offset by all the extra memory buffer copying that is occurring when copying a write request to the write queue. We now have 2 buffer copies when writing a buffer (once to the AIO queue, plus the copy to the OS.) Are there any benchmarks numbers that indicate AIO is better than the async PRAGMA? Cheers, Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] SQLite version 3.6.14
On Fri, May 08, 2009 at 05:32:45PM +0700, Dan wrote: > > On May 8, 2009, at 5:21 PM, Christian Smith wrote: > > > On Wed, May 06, 2009 at 10:36:50PM -0400, D. Richard Hipp wrote: > >> > >> A new optional extension is included that implements an > >> asynchronous I/ > >> O backend for SQLite on either windows or unix. The asynchronous I/O > >> backend processes all writes using a background thread. This gives > >> the appearance of faster response time at the cost of durability and > >> additional memory usage. See http://www.sqlite.org/asyncvfs.html for > >> additional information. > > > > > > What are the benefits of using async I/O over "PRAGMA synchronous = > > OFF"? > > If AIO is used for the rollback journal as well, you've lost your ACID > > properties already, so you may as well just use "PRAGMA synchronous > > = OFF" > > anyway and keep the code simpler. > > That's not the case. You lose the Durability property, in that a COMMIT > statement may return before a transaction is stored on the persistent > media, > but transactions are still Atomic, Consistent and Isolated. > > When using the "PRAGMA synchronous=off" your database might be corrupted > by a power failure or OS crash. When using asynchronous IO this should > not > be possible (assuming the hardware is not being untruthful - just as > when > using regular "PRAGMA synchronous=full" mode without the async IO VFS). Ah, the bulb has lit. Because the writes and syncs are processed by the single queue in order, journal writes are guaranteed to be synced and consistent before main in-place updates to the db file. Might be worth mentioning this in the documentation, as this is not clear without examining the source. In that case, I like it :) Is this something that might be made the default in the future, with the addition of some synchronization between foreground and background threads on the xSync messages to emulate the existing "PRAGMA synchronous=full" behaviour? > > Dan. Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Slow Transaction Speed?
On Wed, May 27, 2009 at 08:05:00AM -0400, pyt...@bdurham.com wrote: > Dr. Hipp, > > > Your OS and filesystem configuration have a big impact too. I've notice, > > for example, that transactions are really slow on RieserFS on Linux > > compared to Ext3. > > In your experience, which Linux file system(s) provide the high > performance platform for SQLite? I can't speak for DRH, but I have found that ext3 with the option of "data=journal" gives a massive improvement speed wise than default ext3 options, mainly because the journal is contiguous and ext3 can avoid seeks while still ensuring data is written safely to disk. This is a big win for rotating platter disks. I did an informal benchmark of various filesystem types on Linux (note this is 2 1/2 years ago) as part of an OSNews thread here: http://www.osnews.com/permalink?184137 I'd be interested in how ext4 compares to ext3. Perhaps an evening project beckons. > > Which Linux file systems do you recommend avoiding for SQLite use? Anything with FAT in the name... Plus, avoid NFS due to possible locking issus. > > Thank you, > Malcolm ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] very large SQLite tables
On Wed, Jun 24, 2009 at 02:21:09PM -0500, Matthew O'Keefe wrote: > > > We are using SQLite for indexing a huge number (i.e., 100 million to 1 > billion) of key pairs > that are represented by an 88-byte key. We are using a single table with a > very large number of rows (one for each data chunk), and two columns. > > The table has two columns. One is of type ³text² and the other is type > ³integer². > > > > The table is created with: > > > > CREATE TABLE chunks > > ( > > name text primary key, > > pid integer not null > ); > > As expected, as the > table grows, the underlying B-tree implementation for SQLite means that the > number of > disks accesses to (a) find, and (b) add a chunk, grows larger and larger. > We¹ve tested up > to 20 million chunks represented in the table: as expected performance > exponentially > decreases as the number of table entries grows. > > We wanted to post to the mailing list to see if there are any obvious, > first-order things > we can try to improve performance for such a large table. Bit late to the game... Try increasing your page size. The larger page size will result in greater fan out of the btree, resulting in a shallower tree and less IO requests. Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] using lemon to create a c++ parser class
Wilson, Ron uttered: It has been a very long time since I have tinkered with lex/yacc but my current project requires a parser. I'm thinking of learning lemon. Frankly, the sqlite code base is far more complex than what I will implement. Is anyone willing to share a lemon parse.y code example for something less complex than SQL? There are tutorials on the net that might be worth looking at, for example: http://freshmeat.net/articles/view/1270/ Also, i'm looking for advice on using lemon to make a c++ parser class instead of a global c parser function. Is it as simple as declaring the following? %name MyParserClass::Parse I'm pretty sure I can create the right c++ preamble with %include. Also, is there a cheap way to make lemon output a .cpp file besides renaming the output file? Feel free to tell me I'm on a foolish quest if I am. The C++ quest might be unnecassary, but I wouldn't say foolish. There is no problem linking C and C++ code. I'd say just leave the Parse function as a C function. You might even be able to make it static, thus limiting it's scope, and wrapping that static function in a class, but why bother? RW Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance.....
On Wed, Mar 05, 2008 at 10:21:58AM -0500, [EMAIL PROTECTED] wrote: > > > I'm in the process of architecting the software for an embedded Linux system > that functions as a remote and local user interface to a control system. > There > will be a lot of analog (fast) data arriving via SPI bus and I'm thinking of > using SQLite to store this data in a well organized and easy to access > manner. > > My main concern is performance. Has anyone had any similar application > experience they could comment on? I want to be able to insert data arriving > on the SPI bus and then query the data to update a GUI at a very high rate > (less than 250Ms). This is not real time so 250Ms is desirable but does not > have to be guaranteed. > > Any thoughts or experience would be appreciated... We'd need more details for definitive answers, such as whether you're using disk or FLASH based storage, your data and schema format. Some things to consider: - Batch inserts. Given your 250ms update requirement, you could perhaps batch data 4 times a second. That'll give you a very high insert rate. - If using disk based storage, using ext3 with "data=journal" mount option The journal can be written and sync'ed very quickly. - Experiment with indexing. Indexes will slow insertions, but improve querying. - If the above is still too slow, and you're happy risking the database in the event of a system crash, then you can turn off synchronous updates. HTH, Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Performance
On Wed, Mar 05, 2008 at 09:02:17PM -0500, James Kimble wrote: > > > One thing I can highly recommend on embedded systems, especially flash > > based ones, is turn pragma synchronous to off. Having sqlite write every > > record modification to the flash, is a stunningly expensive process, > > even when it's all encapsulated in a large transaction. Let linux handle > > the synchronisation and write caching for you. A lot less robust, but, > > the benefits more than outweighed the cost. If you need guaranteed write > > to disk, then perform the synch yourself. > > > Cost vs benefit and all that guff. > > > That's sounds like good advice. I'll do that. > > Working with flash in this way is going to be a challenge. With limited > number of writes in a lifetime (this device needs to last approx 20 > years...) I will have to make some major design decisions around how > I handle the writes. How important is the persisent data? Is it kept for audit, statistical analysis, what? Basically, can you afford to lose it, or at least a subset of it? If so , then I'd say maintain the data in an in-memory database, and write out the data to disk (using safe synchronous writes) at whatever intervals you desire. I say use safe synchronous writes, as recovery may be an issue if you don't write safely. Not what you need on an embedded system where user interaction may be required. Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Where is the database file created?
On Sat, Mar 22, 2008 at 06:44:45PM +0900, Fred Janon wrote: > Hi, > > I read the documentation, features and faq and could not find anything that > specifies where (which directory) the database file is stored. I launched > sqlite3.exe on windows without a database name, using the '.databases' > command, I get: > > sqlite> .databases > seq name file > --- --- > -- > 0main > sqlite> > > and since it doesn't show a file, I presume that sqlite does actually > support in-memory temporary databases? Where is is documented? SQLite supports file and/or in-memory databases. The file is wherever you tell it to be. Start the sqlite3.exe command with an (unused) filename and you'll see that file created when you do any writes to this new database. You might want to start here: http://sqlite.org/quickstart.html > > Thanks > > Fred ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Major memory leak
On Fri, Mar 21, 2008 at 10:41:10AM -0400, Rob Richardson wrote: > My SQLite library is built from the single translation unit > sqlite.c/sqlite.h. That file contains the version number 3.3.17. > > I do not have valgrind, but circumstantial evidence that this is a > SQLite problem is strong. When stepping through my code, I see that > my application's memory jumps by over 2.5 megabytes when the > sqlite3_step() method is called when using either the sorted query or > the query using max(). The unsorted query doesn't show any memory > jump. Also, the difference in memory consumption before this part of > the code is executed and after it is left is the same size as the jump > in memory when sqlite3_step() is called. When doing a sorted query, the result set is formed in a temporary database somewhere defined by the environment. In your case, it sounds like the temporary database is memory based. Once the result set is done with, SQLite may return the memory to the OS using free, but that will show under the process's virtual memory footprint. You can tell SQLite to use a disk based temporary database using: http://sqlite.org/pragma.html#pragma_temp_store Using this, your memory usage will probably be more stable. However, this certainly isn't a memory leak. > > RobR > Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why is SQLite so slow across a network (afp Mac OS X)?
On Tue, May 13, 2008 at 11:15:51PM -0600, Peter K. Stys wrote: > Hi Folks: > I'm new to this list and just came across a major issue so I thought I'd > post here: I'm using SQLite (from REALbasic which uses the SQLite DB engine > for SQL support) to store image files, and while R/W to local files is > excellent, the same operations on files residing on a remote volume mounted > via afp or smb (all Mac OS X) suffer a 20-fold performance hit. > > Why is this, and is there a workaround? > > To be clear, while the remote files are on a server, this is still single > user access to a single file at a time, just remotely. > > Any input greatly appreciated! A local disk is on a >1Gb/s connection, probably at the end of a wire <0.5m long. The remote volume will have that, but also a <1Gb/s connection, on top of a >10m length of cabling to implement the network. Laws of physics, such as the speed of light, limit the turn-around of synchronous writes across a network. Your hard disk has probably an order of magnitude quicker synchronous write than your network share. Try playing with synchronous writes turned off. The reduced synchronous requirements may allow you to make more optimum use of the network file protocols, which operate best asynchronously. Try, in order: PRAGMA synchronous = NORMAL; PRAGMA synchronous = OFF; And measure performance of each. But SQLite is simply not designed to work efficiently across a network based file system, so manage your expectations. > > Peter. > Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Why is SQLite so slow across a network (afp Mac OS X)?
On Fri, May 23, 2008 at 12:55:47PM -0600, Peter K. Stys wrote: > On Fri, May 23, 2008 at 4:31 AM, Christian Smith < > [EMAIL PROTECTED]> wrote: > > > On Tue, May 13, 2008 at 11:15:51PM -0600, Peter K. Stys wrote: > > > Hi Folks: > > > I'm new to this list and just came across a major issue so I thought I'd > > > post here: I'm using SQLite (from REALbasic which uses the SQLite DB > > engine > > > for SQL support) to store image files, and while R/W to local files is > > > excellent, the same operations on files residing on a remote volume > > mounted > > > via afp or smb (all Mac OS X) suffer a 20-fold performance hit. > > > > > > > Try playing with synchronous writes turned off. The reduced synchronous > > requirements may allow you to make more optimum use of the network file > > protocols, which operate best asynchronously. > > > > Try, in order: > > PRAGMA synchronous = NORMAL; > > PRAGMA synchronous = OFF; > > > > And measure performance of each. But SQLite is simply not designed to work > > efficiently across a network based file system, so manage your > > expectations. > > > > BTW, those PRAGMAs made little difference. I resorted to caching the remote > file to the local drive via a fast OS-level file copy then doing the SQL > R/W, then copying back to the remote in a bkgnd thread. A programming > headache to keep everything in sync, but very acceptable performance. Actually, you might want to try using a larger page size. SQLite uses, by default, 1KB pages. Increasing that to 16KB or perhaps larger will not only reduce the overhead of BLOBs, but also increase performance significantly, as each page will be going across the network one by one. Christian ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] Strange Behaviour on Solaris 8 on Sparc
On Wed, 5 Apr 2006, Phuah Yee Keat wrote: >Hi, > >I am currently running some tests to decide whether to use sqlite, and >bump into some strange behavior. I compiled sqlite 3.3.4 from source and >installed it on a solaris 8 on sparc without any updates. I run the same >scripts (which insert 1000 entries without BEGIN/END block), on the same >machine, but in different directories, getting totally different results: > >### ># In the "db1" directory: >### >[EMAIL PROTECTED]:~/db1$ time cat /export/home/ykphuah/test.sql | sqlite3 >test.db > ... Note, you are not testing the SQLite time here, but the time taken to run "cat /export/home/ykphuah/test.sql". What you actually need is: $ cat /export/home/ykphuah/test.sql | time sqlite3 test.db As to what is causing the slowdown, check your system log. You may have hardware problems. Whatever the problem, it is almost certainly a non-SQLite problem. >I am really puzzled as to why there's so much difference in the time to >execute the same script on the same box just in different directories, >thinking that it might be placement of the directories in the physical >disc itself causing the fsync calls to differ so much? > >Is there any way where I can provide more information to help you guys >help me? System utilities. Check prstat (top like util), dmesg (for kernel messages), vmstat (IO stats) and truss (syscall trace). > >Thanks in advance. > >Cheers, >Phuah Yee Keat > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] www.sqlite.org Server setup
On Fri, 7 Apr 2006, Dan Kennedy wrote: > >> The same virtual server hosts multiple websites. Besides >> SQLite it hosts: >> >>http://www.cvstrac.org/ >>http://canvas3d.tcl.tk/ >>http://tkhtml.tcl.tk/ > >http://3dcanvas.tcl.tk :) Mmmm, triangles:) > > >__ >Do You Yahoo!? >Tired of spam? Yahoo! Mail has the best spam protection around >http://mail.yahoo.com > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] MMAP
On Sun, 16 Apr 2006, John Stanton wrote: >I wonder if members can help me with some advice. I have a program >which is a multi-threaded application server with Sqlite embedded which >runs on Unix and Windows. For an i/o buffer per thread I have the idea >of using a mmap'd file so that it can be transferred using >sendfile/TransmitFile with minimum overhead and no buffer shadowing. >The program design is such that there is a pool of inactive threads >waiting for a connection, and that pool could be quite large. Each one >would have a mmap'd file with an open fd involved. Is the SQLite database file what you want to send down the socket? Or are you just building up a buffer of your regular session protocol data to send? If the former, then you'll have problems with synchronisation unless you stop all database processing while sending the file. You'll also have to wait until the file is static (locked) before MMAP'ing it, so you'll know how much to MMAP. There will be setup cost associated with MMAP the file. MMAP is probably not optimised to be done on demand. Instead, it is designed for up front persistant mappings of known size, like libraries and memory mapped devices. If the latter, assuming you don't need the session data to be persistent, then what's wrong with using just an anonymous memory buffer from malloc? The buffer is anonymous and so will only ever be swapped, so no file IO at all unless you have memory pressure. sendfile will gain you nothing if you don't require persistent data. > >Does anyone know the performance impact of having a considerable number >of mmap'd files attached to a single process in Unix/Linux and Windows? > Has anyone tried such a strategy? My guess is that the impact is not >great, but I have no evidence in support. The OS has to track each MMAP segment, which is usually a linear linked list sorted by virtual address. As most processes don't have that many segments mapped (10's of segments would be considered a lot) the O(n) linear search is not considered much of a burden. If this increases to the 100's or 1000's of segments, the kernel will spend correspondingly more time in VM management. Such linear searches can also have a detrimental effect on the CPU caching. All in all, my guess is that the complications would not be worth any performance gains you may achieve. It sounds like a micro-optimisation (sendfile itself is really only useful for static content.) >JS > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Is it safe to read or write a table while being indexed?
On Sat, 15 Apr 2006, Tito Ciuro wrote: >Hello, > >I was wondering whether it is safe to read or write a table while >being indexed. Here's a scenario: for batch imports, it's sometimes >better to DROP the indexes, do the INSERTs and then recreate the >relevant indexes. Indexing may take a little bit of time, so I was >wondering if: > >- Other processes/threads could read or write the data (even though >the indexes are not yet available) > >- Is it safe, or even possible? SQLite will make possible whatever is safe. Just be prepared to handle SQLITE_BUSY and SQLite will take care of the rest. > >- Does SQLite acquire an EXCLUSIVE lock when indexing? If not, should >I wrap the CREATE INDEX statements within a BEGIN EXCLUSIVE >transaction, to be safe? > >- If I'm not mistaken, an EXCLUSIVE lock does not stop other readers >from accessing the database. What would happen if a process/thread >reads data being indexed? If the indexing process uses a large SQLite cache (using the cache_size PRAGMA) then the indexer can perform some of the indexing without promoting the lock to EXCLUSIVE, and allow concurrent access with readers. This is using a RESERVED lock. However, the EXCLUSIVE lock will be required once the cache is filled with dirty pages as pages will be required to be spilled to the database file (and hence the rollback journal etc will be created.) This will occur once all current readers have finished. When an EXCLUSIVE lock is required, no new readers are allowed. > >Thanks in advance, > >-- Tito > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] MMAP
On Wed, 19 Apr 2006, John Stanton wrote: >Chris, Thanks for the very pertinent comments on segment linking. > >I am not sending an Sqlite database. It is the output from my program, >principally assembled HTML/PDF/PostScript and similar. > >I want to avoid buffer shadowing on the content my program creates. If >I use a malloc'd buffer it gets copied several times before arriving at >the network interface. If I use the zero copy mechanism with a sendfile >or TransmitFile on an open file descriptor/handle in conjunction with a >modern network adapter then the transfer is optimal. Compared to the general work in generating the buffer, is the time to send it to the socket actually significant? Compared to the throughput of your network, is the bandwidth of a memory->memory copy going to be a bottleneck? sendfile is an optimisation for web servers to serve static content. The benefits of server dynamic content would be suspect at best. I can't find any pages recommending it's usage for dynamic content. > >It has always been my experience that if you get the underlying >mechanisms used by your application to be optimal, then you have no >scaling problems and no walls to hit in the future. As an analogy, you could hand code the most efficient data swapping algorithm possible, but it won't make a bubble sort more efficient. Get the solution working simply, then profile before putting in a hack like this, especially if run on a platform where sendfile is not available or implemented using read()/write(). Of course, I'd be interested in whether using sendfile() for dynamic content is indeed useful in increasing performance. > >JS > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Direct use of SQLite btree functions / performance
On Wed, 19 Apr 2006, Joe Wilson wrote: >--- [EMAIL PROTECTED] wrote: >> Joe Wilson <[EMAIL PROTECTED]> wrote: >> > >> > If you read the blob all in at once, true. >> > But doesn't sqlite3BtreeData() allows you to read a partial chunk of >> > data from the blob at an arbitrary offset? This could be advantageous >> > if you are dealing with multi-megabyte blobs and do not require/want >> > the entire blob in memory at once. >> > >> >> sqlite3BtreeData() actually reads a prefix of the data. >> Because of the way large blobs are stored (as a linked list >> of disk pages) you have to start reading at the beginning >> and read everything up to the point of interest. > >Random access for blobs would be ideal, but even a sequential block >fetching mechanism for blobs would be a useful SQLite API addition. You could simply partition the blob yourself, and use a compound key to access parts of a BLOB at random: CREATE TABLE blocks ( id integer, offset integer, block blob, primary key (id,offset) ); Assuming you store, say, 16K in each block, then access to any one 16k block then becomes a single index lookup and 17 page reads. Much more efficient than storing the whole BLOB in one. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] MMAP
At the risk of this turning into another argument between us... On Thu, 20 Apr 2006, John Stanton wrote: >Q How do programs get to be slow and bloated? >A One small inefficiency at a time. > >It is just as inefficient to send dynamically created content through a >series of buffers as it is to send a static file, and just as unecessary >when the machine and OS has a mechanism embedded to avoid it. > >If you half the overhead of a program which hits its limit at 1,000 >users, you get the potential to service 2,000 or the ability to add >features and still service the 1,000. Assuming you spend x% of your time in function Y, then halving the overhead of function Y will yield x/2% of run time. The lower x is, the lower the savings. Some overhead is just too small to be worth worrying about. In your case, remember that you'll also have to create the file before use (synchronous IO) and resize it to the correct size before sending it down the pipe (sendfile sends the whole file) which will involve more synchronous IO. You'll also have to re-mmap your file each time you truncate it and re-write it, as mmap mappings are undefined for truncated files etc. Invalidating mappings could also result in cross CPU interrupts to flush TLB entries in SMP machines, which affects not only your process but also processes on the CPU that got interrupted. If the cross CPU interrupts haven't put you off yet, then the required synchronous IO sure as hell should have. Now, if you dynamic content is likely to be reused, then that is a different matter. Write to a file as a cache entry, and sendfile the cache entry. That I can see working. >JS > Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] upgrading versions of database files across version of SQLite
On Thu, 20 Apr 2006, Steve Bland wrote: > We have been using SQLite ( v2.8.6 ) for a while now and as part of a > new release were thinking of moving ahead to a more current version. > > But that is where the issues began. I did search the archives, but found > noting of any real use on this. The same with the faq page and the > changes page. Naturally I could have missed something but... > > If I try to load one of our existing files with the command line tool, I > get the response "Error: file is encrypted or is not a database". If I > do a read command on the same file, I get "Incomplete SQL: ** This file > contains an SQLite 2.1 database **" and then there is incomplete and > rather odd SQL data. The 2.1 database message actually overwrites the > SQL on the screen. The file format changed with SQLite 3.x. This was to allow certain improvements (such as blobs, 64bit integers etc) that wearn't possible with the SQLite 2.1 based format. > > If I load these files with v2.8.17 all is fine. Is there a compatibility > problem? Is there an import tool? You can export data from v2.8.17 using .dump, and read in the SQL commands from the dump to a 3.x database. However, the SQLite 3 API is also different, so unless you're using a wrapper, it might just be easier to stay with 2.8 unless you want some of the new functionality from 3.x. > > I know this is probably covered in either the email digests or somewhere > on the site, but I could not find any information. http://www.sqlite.org/version3.html > > > Steve > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Using sqlite3_open or sqlite3_open16?
On Thu, 20 Apr 2006, DBTools Software wrote: >Hi, > >I have an application that needs to open a database in the users's personal >folder. I noticed that in some circunstances the sqlite3_open fail as the >filename is in UTF16 format. I don't know that in advance so I could open >the db with sqlite3_open16. > >The question is: > >Is it safe to always use the UTF16 functions independent of the >localization? What I mean is can I use the functions sqlite3_xxx16 instead >of sqlite3_xxx for all cases? Your data should always be in UTF-16 to use sqlite3_xxx16, else you'll incur the cost of transforming your date to/from UTF-8. If most of your data is UTF-8 or ASCII, use the regular UTF-8 functions. If your filename is UTF-16, just convert it to UTF-8 (it's a relatively simple, unambiguous transformation) and use sqlite3_open. > >Thanks, > >COS > > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Rule of thumb for estimating index size
On Fri, 21 Apr 2006, John Newby wrote: >A little off subject I know, but what is the purpose of an index? Just like in a book. If you want to search for particular terms in a book, you look up the page number in the index. In a database, fields are indexed, so you can see which rows contain particular field values without searching the whole table. For example, consider the following rows: rowid | field1 | field2 --++--- 0 | foo| bar 1 | foo| foo 2 | bar| foo 3 | bar| bar If the field1 is indexed, then your index will look like key | rowid --+-- foo | 0 foo | 1 bar | 2 bar | 3 If we want all rows where field1 = 'foo', then we can use the index to locate the only two rows that satisfy the condition (0 and 1 in this case). If we want to find all rows where field2 = 'foo', then we have to search the whole of the table. Indexes come into their own when tables have thousands or millions of rows with largely disparate field values. Finding a particular row in these cases can be as quick as a single index lookop followed by a single row lookup, instead of a multi-million row table scan. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Rule of thumb for estimating index size
On Fri, 21 Apr 2006, Rusty Conover wrote: >Hi, > >Is there an easy way to get an estimate on the space used by an >existing index? If not what is a good guess on how to estimate the >size? > >My guess would be (assuming text fields are being indexed): > >[total length of all index keys] + [number of rows]*8 (for the offset >location) > >Is that close? I realize disregards all space used by page allocation >overhead. The page overhead will be pretty constant per index entry. One underestimate from above is the effect of overflow pages and internal fragmentation. If the key doesn't fit in the btree node, overflow pages are used to store the rest of the key, and the pages are used in their entirety, and not shared with other entries. Thus, if your keys are quite long, the internal fragmentation must be taken into account in the total key length. If 1/4 of your rows cause overflow, the the extra overhead can be approximated as: pagesize/2 * [num of rows]/4 This assumes that the last overflow page is on average half full. > >Thanks, > >Rusty >-- >Rusty Conover >InfoGears Inc. > > > > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] SQLite on limited Memory
On Mon, 24 Apr 2006, Tony Fenleish wrote: >I've been developing an app on a linux embedded device with 128 M of RAM, >although the available RAM for queries is less than that. Now that the >databases are getting a little larger (120 MB), I'm having problems with >some queries that have large results. I watch my memory drop completely, >and then my sqlite client is killed. Now, as I understand it, the entire >database is not opened into memory, just the amount of data that is needed. > >According to documentation, 256 Bytes of memory is needed for every 1 MB of >data. Does anyone have insights as to the memory limitations of SQLite? > IIRC, the 256 bytes of memory per 1MB of data is to track dirty pages for rollback. The memory footprint of SQLite is controlled primarily by: - Page cache. Defaults to up to 2000 pages. At 1K pages, that's up to 2MB of cached page data. This number can be reduced as needed. - Temporary tables in memory or disk based tables. If memory based temporary store is used, then any temporary tables used for sorting, for example, will be memory resident. Temporary tables, however, can be moved to temporary disk files to compete with the page cache just like the main database. This is set using "PRAGMA temp_store=". See http://www.sqlite.org/pragma.html for details. Otherwise, avoid loading the full result set into memory as you process it. Ie. Avoid sqlite3_get_table(). If you are sorting large queries, switch to using disk based temporary store. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] create unique index quickly
On Sat, 20 May 2006, Brannon King wrote: John Stanton wrote: You don't seem to need a data manipulation system like Sqlite, more a form of high volume storage. Do you really need elaborate SQL, journalling, ROLLBACK and assured disk storage? Di you consider some form of hashed storage, perhaps linear hashing, to build a compact and high performance associative array for your sparsely keyed data. Do you really need the overhead of B-trees is you are just storing a sparse array? JS I don't need journaling or rollback. I'd love a way to shut them off. But elaborate SQL, that sure is handy. I'm not just storing, I'm viewing stored, compressed data. I definitely need some way of querying a sparse matrix data that is larger than my DRAM. Sqlite sure seems like the quickest route to a workable product for that to happen. It has all the streaming/caching built in. Because of that, I assume it is faster than random file access. It supports complex data queries and indexes, both things I would need anyway. In the world of programming, I think many will agree you should get a working product, then make it faster. I'm just trying to get the most speed out of the easiest tool. If I need to rewrite the file storage for the next version, we can consider the cost to benefit for that separately. Is that 800MB/s sustained? Is it constant, 24/7? Or is it bursty data? If it's bursty, you could buffer the inserts in an append only log. Insertion to the SQL(ite) database can be done asynchronously. Writing to unstructured, append only log files can be done at pretty much disk I/O speed. If it's not bursty, but sustained, then I fear SQLite or any other database will not be able to match that rate. You'll probably run out of CPU before running out of disk IO. Christian PS. On a side note, is it wise still buying SGI kit?
Re: [sqlite] Extra functions - New Project?
On Wed, 24 May 2006, Mikey C wrote: I would rather add these functions directly to the core SQLite DLL in C in and compile them directly into the code (using a conditional). They then register this function by adding it to the array of existing functions: ... This seems to work (I've tried it). HOWEVER, it means altering func.c and I was looking for how to add these functions in a separate C file without having to alter any existing code? Anyone any ideas how best to extend the codebase of SQLite with minimal alteration to existing code? You best bet is to open a ticket in CVSTrac: http://www.sqlite.org/cvstrac/tktnew Attach a patch to the ticket that implements your new functions. Send your declaration of dedication of the code to the public domain to the list, and hope DRH includes the patch in the next release. Cheers, Mike Christian
Re: [sqlite] In the year 4461763
On Tue, 23 May 2006, Chris Werner wrote: Hello, I am just curious about SQLite's date and time manipulation functions. I am using the sqlite3 command line interface on the above described platform. When I select a datetime for 2^47: sqlite> SELECT datetime(140737488355328, 'unixepoch'); 4461763-06-20 05:22:07 Adding a second to the time [2^47 +1] seems to add 2 seconds to the return?? sqlite> SELECT datetime(140737488355329, 'unixepoch'); 4461763-06-20 05:22:09 I suspect an overflow of some sort, but I cannot phrase it concisely. Datetime in SQLite is represented as a 64 bit floating point value. The units are seconds since the unix epoch. What you're seeing is the limited precision (48 bits I believe) of 64 bit floating point numbers. Not a problem for real world values, but a problem if high sub-second precision or long distant dates are needed. Date time seems to return reasonable values up until 185327782012799 [you tell me], after which the return format is not a valid date. Again, I suspect an overflow of some sort, can anyone explain? sqlite> SELECT datetime(185327782012799, 'unixepoch'); 5874773-08-15 23:59:58 sqlite> SELECT datetime(185327782012800, 'unixepoch'); -5884205--1-00 00:00:00 Not sure about this one. Check out computeYMD() in date.c. It has some pretty funky calculations to work out the year from the time. I won't pretend to pretend what all the figures are for, but there is probably some 32 bit integer overflow that messes up the calculations. Potential bug note: I notice all the variables in computeYMD() and computeJD() use integer intermediate values, yet all the intermediate values might be best off being held in real value variables due to the use of floating point arithmatic. Is there a real threat of wrong dates coming from this? I can understand the use of integers from a performance POV, and some small embedded processors have no FPU. Perhaps there could be a compile time flag to choose between ints and doubles for these intermediate results? Not a critical item, nor important enough to be reported as a bug, Just curious, Christian Werner
Re: [sqlite] Re: DLLs containing user-defined SQL functions
On Wed, 7 Jun 2006, Igor Tandetnik wrote: [EMAIL PROTECTED] wrote: "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: On Windows, the loader works in a very different way. Basically, export/import connections are established at link time, not at load time. The loader does not perform a symbol search over all the DLLs, the import tables in the executable image (emitted by the linker) tell it exactly where to look. The disadvantages to the windows approach are obvious. Before I add this characteristic to the ever-growing list of reasons why I hate windows and especially hate programming for windows, I should be fair and ask if there are any advantages to the windows way of doing things that I have overlooked. From http://www.iecc.com/linker/linker10.html : "The runtime performance costs of dynamic linking are substantial compared to those of static linking, since a large part of the linking process has to be redone every time a program runs. Every dynamically linked symbol used in a program has to be looked up in a symbol table and resolved. (Windows DLLs mitigate this cost somewhat, as we describe below.)" Wow. That philosophy has diminishing returns as machines get faster. What foresight! This is one of the most painful aspects of Windows programming (among many) but can be somewhat mitigated by doing away with .def files: http://msdn2.microsoft.com/en-us/library/3y1sfaz2.aspx Basically, wrap the above in a macro, something like: #ifdef WIN32 #define EXPORT __declspec( dllexport ) #else #define EXPORT #endif Then declare functions as EXPORT int foo( int bar ); On UNIX and other sane environments, it does nothing as nothing is required. On Windows, an export symbol (or whatever it is) is created without the need for a .def file. Don't know if it'll work on .exe's, mind. You may have to dllimport the required function from the .exe to the dll. I don't know for sure. Igor Tandetnik Christian
Re: [sqlite] Re: DLLs containing user-defined SQL functions
On Wed, 7 Jun 2006, Igor Tandetnik wrote: Dennis Jenkins <[EMAIL PROTECTED]> wrote: [EMAIL PROTECTED] wrote: "Igor Tandetnik" <[EMAIL PROTECTED]> wrote: On Windows, the loader works in a very different way. Basically, export/import connections are established at link time, not at load time. The loader does not perform a symbol search over all the DLLs, the import tables in the executable image (emitted by the linker) tell it exactly where to look. That explanation does not seem entirely accurate (especially the second sentence). If that were true, it owuld not be possible to release an updated DLL with re-arranged entry points and expect the caller EXE or DLL to link to it properly; yet that works. "Exactly where to look" was perhaps too strong a stateent. What I mean is the import table lists the name (just the file name, without path) of the DLL and the name (or ordinal number) of the exported function. The loader still needs to search the path for the DLL file, and search the DLL's export table for the symbol name. What it does not have to do is search all the DLLs in the system and all the symbols in them to resolve dependencies, as it appears to be the case under Unix-like scheme. I don't really know how Unix loader works sufficiently well to engage in intelligent discussion. Under Unix, run time linking is basically exactly that, a run time link. The exe is loaded, and unresolved symbols are searched for in the required libraries. The list of required libraries is maintained in the exe, so the linker doesn't have to search all the system libraries. Shared libraries may also depend on further shared libraries, symbols being resolved on the way, until no more symbols are left (or no more libraries are left or cannot be found, when an unresolved symbol error is thrown.) The main difference appears that Unix just says "Symbol foo is unresolved, look in libx, liby and libz" whereas Windows says "Symbol foo from liby is unresolved." The Unix way is more flexible in that things like LD_PRELOAD can override symbols from libraries. Very useful. Igor Tandetnik Christian
RE: [sqlite] Problems with multiple threads?
On Wed, 7 Jun 2006, Jiri Hajek wrote: However, right after fixing this, I found another problem. It certainly can be my fault, but I don't see how could it be: If I don't use transactions, multiple threads seem to proceed well, but then right after I add BEGIN and COMMIT to some place, all threads lock eventually. I debugged it and found that _all_ threads accessing SQLite are in a loop waiting for an action to proceed and all of them keep getting SQLITE_BUSY result. I wonder, can it be somehow my fault, or is it some kind of a dead-lock in SQLite? If one transaction already has a read lock, and another transaction has a reserved lock (trying to get a write lock), neither thread can get a write lock. One of the transactions must abort. Such a sequence might be (in order): Transaction 1: BEGIN; SELECT ... Transaction 2: BEGIN; DELETE ... (SQLITE_BUSY) T1 : UPDATE ... (SQLITE_BUSY) Both transactions are now deadlocked. It would be nice if SQLite told us this. However, SQLite detects the reserved lock and returns SQLITE_BUSY, telling niether transaction much other than to try again. If a reserved lock is detected when trying to promote an existing read lock, this is a deadlock situation and should perhaps return an error code of SQLITE_DEADLOCK instead? Christian
Re: [sqlite] how to recover a corrupted database?
On Thu, 8 Jun 2006, [EMAIL PROTECTED] wrote:
I also occasionally back up the database using subversion ("svn
commit"), while the app that uses it is still running. My belief is
that subversion only reads a file to commit it, and doesn't write to it,
but it's possible that is wrong.
Subversion only reads the file, but it doesn't do the atomically! This
backup strategy has a race condition that could result in the backup of a
corrupted file. DON'T use this method.
Instead, take a stable snapshot using the .dump command from the SQLite
shell, and save the resulting text file as your backup:
$ sqlite3 dbfile .dump | gzip > dbfile.backup.gz
To restore the backup, feed the contents of the backup into the SQLite
shell. The backup is simply a sequence of SQL commands to restore the
data, and compresses pretty well:
$ zcat dbfile.backup.gz | sqlite3 new.dbfile
Thanks to the backup, I only lost about a day's worth of data, and much
of that was recoverable from other sources. It sounds like corruption
is fairly rare, so for now I'll just bolster my backup & recovery
procedures and stick with it.
If you're finding corruption a problem, you might want to check the
reliability of the machine. As Jay suggested, memtest86 is a good bet. You
might also want to test on a backup machine to see of the problem can be
replicated. If the problem is common enough, perhaps take an hourly backup
using the above method, keeping perhaps the last 24 hours worth of
backups.
Thanks,
- Joe
Re: [sqlite] disabling large file support
On Thu, 8 Jun 2006, Doug Shelton wrote: How does one disable large file support? As mentioned in comments, I've added -DSQLITE_DISABLE_LFS to the Makefile, but continue to get errors indicating lack of kernel support for large files. The following lines are the end of my compile (so you can see make options) and the behavior of the resulting sqlite3. ./libtool --mode=link gcc -g -O2 -DOS_BEOS=1 -DSQLITE_DISABLE_LFS -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG -DTHREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_CURSOR -DHAVE_READLINE=0 -lroot -lbe \ -o sqlite3 ./src/shell.c libsqlite3.la \ -lreadline -lreadline gcc -g -O2 -DOS_BEOS=1 -DSQLITE_DISABLE_LFS -DHAVE_USLEEP=1 -I. -I./src -DNDEBUG -DTHREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=-1 -DSQLITE_OMIT_CURSOR -DHAVE_READLINE=0 -o sqlite3 ./src/shell.c ./.libs/libsqlite3.a -lroot -lbe -lreadline $ sqlite3 test.db SQLite version 3.3.5 Enter ".help" for instructions sqlite> .databases Error: kernel lacks large file support sqlite> .exit $ This is in continuing work to support sqlite3 under BeOS. Any assistance would be greatly appreciated. Make sure you compile os_unix.c with -DSQLITE_DISABLE_LFS. I assume you're using os_unix.c or a derivative as a base. You may have an old stale version without the -DSQLITE_DISABLE_LFS. Out of interest, what porting is requied for BeOS? I though it had a basically POSIX like API already by default? Christian
Re: [sqlite] SQLite 3.3.6 - possibly memory leak
Sasa Zeman uttered:
I working with my own SQLite wrapper for Delphi, with the statically linked
SQLite 3.3.6.
File variant works fine:
SQLite3_Open('Test.sqb',db);
SQLite3_Exec(db,'DROP TABLE TEST',NIL,NIL,ErrMsg);
...
However memory variant rise a memory leak report:
SQLite3_Open(':memory:',db);
SQLite3_Exec(db,'DROP TABLE TEST',NIL,NIL,ErrMsg);
...
I'm aware that above is a nonsence script. However, scripts with created
table which is later deleted twice by mistake, can also produce mentioned
problem. Please confirme memory leak existanace.
Speculating, as I'm not familiar with your Delphi wrapper, but are you
sqlite3_free()'ing the error message string when reporting any errors?
Sasa
Christian
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
Re: [sqlite] MacOS X build failure
Kon Lovett uttered: Hi, The following occurs building the CVS head w/ gcc 4.0.1 on MacOS 10.4.6: ./libtool --mode=compile gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../sqlite/src -DNDEBUG -DSQLITE_ALLOW_XTHREAD_CONNECT=1 -I/usr/local/include -DTHREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=1 -DSQLITE_OMIT_CURSOR -c ../sqlite/src/loadext.c gcc -g -O2 -DOS_UNIX=1 -DHAVE_USLEEP=1 -I. -I../sqlite/src -DNDEBUG -DSQLITE_ALLOW_XTHREAD_CONNECT=1 -I/usr/local/include -DTHREADSAFE=1 -DSQLITE_THREAD_OVERRIDE_LOCK=1 -DSQLITE_OMIT_CURSOR -c ../sqlite/src/loadext.c -fno-common -DPIC -o .libs/loadext.o ../sqlite/src/loadext.c: In function 'sqlite3CloseExtensions': ../sqlite/src/loadext.c:285: error: 'SQLITE_LIBRARY_TYPE' undeclared (first use in this function) ../sqlite/src/loadext.c:285: error: (Each undeclared identifier is reported only once ../sqlite/src/loadext.c:285: error: for each function it appears in.) ../sqlite/src/loadext.c:285: error: parse error before ')' token Probably 'HAVE_DLOPEN' not defined but don't know why. I have the same issue on Linux. I think it's because HAVE_DLOPEN is not defined, and DRH doesn't use or maintain the autoconf based build, but uses his own Makefile (possibly Makefile.linux-gcc based). configure.ac will need to be updated to check for dlopen and set the defines accordingly. As autoconf is horrid, bagsy not me to do it:) Use you own Makefile until the build system is fixed or remove loadext.c from your build. Thank you in advance, Kon -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] How to realize the ROWID in a view?
PY uttered:
Hi All,
I have a problem about the ROWID in a view. I want to simulate a ROWID in a
view just like the same purpose in a table.
For Example:
Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT);
insert into foo(x) values('X');
insert into foo(x) values('Y');
insert into foo(x) values('X');
insert into foo(x) values('Z');
Create View v_foo AS
SELECT distinct(x) FROM foo
ORDER BY x desc;
SELECT * from foo;
id x
-- --
1 X
2 Y
3 X
4 Z
My expect result of "select * from v_foo;" is
id x
-- --
1 Z
2 Y
3 X
Would you please help to tell me how to finish that?
Thanks for your grest help.
Why is this your expected result? Distinct applies to the whole row,
which includes the id. The view to get id and x is:
Create View v_foo AS
select distinct id,x FROM foo
ORDER BY x desc;
This will give you:
sqlite> select * from v_foo;
4|Z
2|Y
3|X
1|X
Each row is indeed distinct.
As you view is defined, you don't get the id at all, and distinct does
indeed return a single instance of X, Y and Z. If you want the x to be
unique, make it the primary key or create a unique index on it:
Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT UNIQUE );
The insert of the second x=X row will now fail:
sqlite> insert into foo(x) values('X');
sqlite> insert into foo(x) values('Y');
sqlite> insert into foo(x) values('X');
SQL error: column x is not unique
sqlite> insert into foo(x) values('Z');
sqlite>
Thanks,
VK
Christian
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
Re: [sqlite] Avoiding Fragmentation of SQLite database file ???
John Stanton uttered: Jay Sprenkle wrote: On 6/14/06, RohitPatel <[EMAIL PROTECTED]> wrote: Any solution to that (which does not force end-user of app to manage sqlite file fragments or to defragment disk) ? A scheduled task or cron job is trivial to implement and does not add any extra work for the end user. Making Sqlite smart enough to manipulate the operating systems storage management is NOT trivial and would need to be different for every operating system. We have a saying that applies: "There is no free lunch" If the disk is kept is a tidy state with regularly scheduled defragmentation then the files which grow do not get gross fragmentation. If there were a way to stop file fragmentation without requiring any management effort there would be no need for defrag programs. With current file systems we need defraggers. A fact of life like death and taxes. I've never, ever defragged a UNIX box in my life. Keeping the amount of free space reasonable goes a long way to helping an OS avoid fragmentation. I only have the issue on Windows when I'm running out of disk space on a device. The other pain on Windows is the lack of swap partition support, which would be a massive performance benefit when memory is overcommitted. A fragmented swap file is a definite performance killer. I don't know if cylinder groups help particularly for UNIX. I guess it must do as even a fragmented file will have it's fragments close together. There is a ext2 defragger, but it doesn't work with ext3 and hasn't been updated since 2002. I can't wait to play with ZFS on Solaris. Doesn't look fragmentation prone. And with point in time writeable snapshots, it should even be safe to take a snapshot copy of a directory containing a SQLite database and SQLite will just do the right thing upon using the snapshot (the journal will be snapshot atomically with the DB file.) Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] SQLite Vs VistaDB - Comparison ???
Bogus�aw Brandys uttered: [EMAIL PROTECTED] wrote: Mikey C <[EMAIL PROTECTED]> wrote: Please implement table and row level locking. :-) People commonly believe that doing so must be easy. I certainly get a lot of requests for it from people who think they know how. But in fact, row-level locking is extraordinarily difficult. To my knowledge, nobody has yet come up with a way to do it unless you: * Add a server process to coordinate access. * Accept that the database might be corrupted if an application crashes while writing. * Make writing to the database very, very slow. * Double the size of the database file. If you think you know a way to implement row-level locking that does not impose one of the above limitations, then please tell me and I will look into the matter. I'm not an expert but let me propose such solution: Multi- Generational Architecture like Interbase/Firebird can cope with all except the last point (double size of database file) - however in the last case periodical vaccum could shrink database. There is also no need to have server process - each instance of sqlite library could be a server. Shared lock manager could be required or simply each instance of sqlite library could have separate one. Shared lock manager is tricky idea (maybe it could be for example dumb manager in each sqlite library with shared memory pool and if one instance terminate another one could detect it and play that role?) In fact that is as I fairy know how it's implemented in Firebird Classic Server (where each server process has separate lock manager I suppose) This classic server processes are spawn by xinetd deamon. I see sqlite in very similar manner : sqlite library is attached to each spawned process which uses it. In order to communicate with the other lock managers, all instances of the SQLite library would have to be on the same box. If you want MVCC without process communication (as not all processes would be on the same box) you'd need each row update to be synchronous and synced, which would be slower than what we have now. The locking protocol could maybe be changed to allow locking at the table level, but such a change would be incompatible with the current locking protocol. And how do you manage multiple rollback journals for multiple writers? A sort of table level locking is already possible anyway using attached databases. I can't see this being a feasible project. Regards Boguslaw Brandys Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] How to port SQLite to a uc/os-II OS with customized file system?
Ҷ�� uttered: Hi,all I'm trying to bulid a database engine based on uc/os-II RTOS with my own customized file system(similar with FAT16, but not exactly the same). I find that SQLite is a good choice. I have read the SQLite source code for several days, but I still have no idea where I should begin with. SQLite is a db engine totally based on disk file. So I guess most of my future work should be to wrap my self-defined file system to have the same interface as needed in os_win.c. Is it correct? Could anyone give me some advice? Implement the interface defined by "struct IoMethod" in os.h. Use the existing os_*.c as templates, yes. If you can provide a largely posix like interface to your file system, you could use os_unix.c largely unchanged. You might want to strip out the nightmare locking code, though:) Another question: Because my project will run in an embedded environment, so I have to take care of the RAM consumption. I have went through the mail list, but not found the description of minimum RAM usage. Could anyone tell me how much RAM is needed to run SQLite in an embedded environment? That depends. SQLite can have functionality conditionally compiled out, reducing it's size. The front page (http://www.sqlite.org/) proclaims: "Small code footprint: less than 250KiB fully configured or less than 150KiB with optional features omitted." YMMV. Your best bet is to choose the features you don't need, compile up your target library, and measure the code size yourself. Thanks in advance! Best regards, Sarah Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Delete performance vs. Insert performance
Insun Kang uttered: Hi. I tested big deletes performance and big insert performance on a Windows CE device in various cache size configurations. ( 1MB, 100KB, 50KB ) Insert 3000 records performs within 23sec, 43sec and 61sec, with respect to each cache size configuration. However, delete 1000 records among 3000 records performs within about 0.4secs for all cache size configurations. Why does delete operation outperform insert operation? and how come the delete operation is independent of cache size? I think the updating indices costs are almost same in both insert and delete operations. Can anybody explain the reason? Delete without constraints is implemented using a DROP of the table followed by recreation of the table. Thus, all pages used by the table are simply marked as unused and added to the freelist. Adding to the free list will touch each page at most once, and thus caching adds no benefit (and has no loss for a smaller cache.) Inserting may touch each page multiple times, for such operations as rebalancing the tree. Therefore, a larger cache will be beneficial on inserts. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Re: Opening the database file for read on Windows XP
Igor Tandetnik uttered: Ran <[EMAIL PROTECTED]> wrote: Thanks for your reply. I know that I should lock the file before copying it, and the "BEGIN IMMEDIATE" is indeed a nice trick. However, I think I didn't explain my problem clearly. I would like to copy that file _without_ using the sqlite library (so using the windows API only). When I try to do that with: CreateFile(db_file, GENERIC_READ, 0, NULL, OPEN_EXISTING, FILE_ATTRIBUTE_NORMAL, NULL); I get error 0x20 - "the process cannot access the file becuase it is beging used by other process". You want to enable sharing. Pass FILE_SHARE_READ | FILE_SHARE_WRITE as the third parameter. Surely not FILE_SHARE_WRITE! You don't want other processes writing the database while you're copying it. Igor Tandetnik -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Delete performance vs. Insert performance
Jay Sprenkle uttered: On 6/21/06, Christian Smith <[EMAIL PROTECTED]> Adding to the free list will touch each page at most once, and thus caching adds no benefit (and has no loss for a smaller cache.) Inserting may touch each page multiple times, for such operations as rebalancing the tree. Therefore, a larger cache will be beneficial on inserts. Does delete t rebalance the trees? or does it leave it until it's needed by an insert? It should rebalance the tree if deletions cause the tree to become unbalanced. If a node becomes empty, the tree is indeed unbalanced. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Re: Re: Opening the database file for read on Windows XP
Igor Tandetnik uttered:
Christian Smith
<[EMAIL PROTECTED]> wrote:
Igor Tandetnik uttered:
You want to enable sharing. Pass FILE_SHARE_READ | FILE_SHARE_WRITE
as the third parameter.
Surely not FILE_SHARE_WRITE! You don't want other processes writing
the database while you're copying it.
The file is already opened by another process for read/write, you must
specify FILE_SHARE_WRITE otherwise you won't be able to open it. You have to
impose a locking mechanism separate from that provided by the OS. Hence BEGIN
IMMEDIATE command which guarantees that no writes will occur via SQLite.
But the OP didn't want to use SQLite in the copying program (for whatever
reason). Without SQLite to arbitrate locking, using
FILE_SHARE_WRITE won't help any as the file can still be updated
regardless while we're copying. If the file is already open with SQLite,
then we're stuck with it I suppose.
The OP's best bet, then, is to lock the file an a way compatible with
SQLite. The easiest way to do this is to use the Win95 compatible LockFile
similar to the function getReadLock in the os_win.c source. Use the
following code to read lock the file in a SQLite compatible way:
#define PENDING_BYTE 0x4000 /* First byte past the 1GB boundary */
#define SHARED_FIRST (PENDING_BYTE+2)
#define SHARED_SIZE 510
static int getReadLock( HANDLE fhandle )
{
int lk = random();
int sharedLockByte = (lk & 0x7fff)%(SHARED_SIZE - 1);
return res = LockFile( fhandle, SHARED_FIRST+sharedLockByte, 0, 1, 0);
}
Note, this function will fail (return 0) if the file is already locked for
writing, as the entire region from SHARED_FIRST to
SHARED_FIRST+SHARED_SIZE is locked. The function will also fail on NT if
the SQLite library already has a read lock on the file. If you want a more
complete function that is more capable on NT, look at the getReadLock() in
os_win.c.
If closing the handle does not clear the lock, you'll need to record the
sharedLockByte value and unlock the file first. MSDN is unclear whether
this is the case (no surprises there!)
Igor Tandetnik
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
Re: [sqlite] How to port SQLite to a uc/os-II OS with customized file system?
Sarah uttered: Thank you for your advice. If I skip the functionality of file locking, that is to say, in my project, I do not need to use the functionality of file locking, then which approach will be easier for me to wrap my self-defined file system to a set of standard interfaces？ One is to wrap it to fit os_win.c; The other one is to wrap it to fit os_unix.c; Being a UNIX guy, I'd recommend UNIX. If your background is Windows, you may find it easier to implement a Win32 like API. Basically, without locking, the two should be broadly similar. - Original Message - From: "Christian Smith" <[EMAIL PROTECTED]> To: Sent: Wednesday, June 21, 2006 7:04 PM Subject: Re: [sqlite] How to port SQLite to a uc/os-II OS with customized file system? Ҷ uttered: Hi,all I'm trying to bulid a database engine based on uc/os-II RTOS with my own customized file system(similar with FAT16, but not exactly the same). I find that SQLite is a good choice. I have read the SQLite source code for several days, but I still have no idea where I should begin with. SQLite is a db engine totally based on disk file. So I guess most of my future work should be to wrap my self-defined file system to have the same interface as needed in os_win.c. Is it correct? Could anyone give me some advice? Implement the interface defined by "struct IoMethod" in os.h. Use the existing os_*.c as templates, yes. If you can provide a largely posix like interface to your file system, you could use os_unix.c largely unchanged. You might want to strip out the nightmare locking code, though:) Another question: Because my project will run in an embedded environment, so I have to take care of the RAM consumption. I have went through the mail list, but not found the description of minimum RAM usage. Could anyone tell me how much RAM is needed to run SQLite in an embedded environment? That depends. SQLite can have functionality conditionally compiled out, reducing it's size. The front page (http://www.sqlite.org/) proclaims: "Small code footprint: less than 250KiB fully configured or less than 150KiB with optional features omitted." YMMV. Your best bet is to choose the features you don't need, compile up your target library, and measure the code size yourself. Thanks in advance! Best regards, Sarah Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] sqlite3_free()
[EMAIL PROTECTED] uttered: Two SQLite APIs, sqlite3_exec() and sqlite3_mprintf(), return strings in memory obtained from a malloc-like memory allocator. The documentation has always said that you need to use sqlite3_free() in order to free those strings. But, as it happens, it has until now worked to call plain old free(). But that might change. In the latest code in CVS, if you disregard the documentation and use free() in place of sqlite3_free(), it will likely lead to a segfault. It might still work depending on how you compile. But a segfault is the more likely outcome. So correct code should continue to work fine. But broken code that happened to work before might now really break. My own personal opinion on these coding style issues is if the API requires special handling of cleanup, then the API should do the cleanup. Returning an allocated string that requires special cleanup results in a potentially generic operation now being special cased by the API client. While it's too late to change now, this puts the client in the unenviable position of needed to copy the string anyway if the string is required elsewhere in the client that may not be aware of the special SQLite API requirements. I'm hoping that this change will not have too much adverse impact. If you think this change might cause excessive hardship, please let me know (before the next release!) and we will consider using (suboptimal) alternatives that allow the older broken code to continue functioning. If I do not hear a sufficiently large outcry, the new code will appear in the next release. How is free() sub-optimal? IMHO, malloc/free is not something an API should be trying to optimise other than internally and opaquely to the API client. You want to block allocate buffers? Fine, do it in SQLite, but exporting this to the API is the implementation showing through. If the client wants to do memory checking, then the developer should link against instrumented malloc/free like valgrind or ElectricFence. As to the actual change, I guess this is trying to optimise the realloc case in the future, perhaps? Is this truly a bottleneck? Otherwise, the current CVS implementation doesn't add anything. If this seems like a rant, I'm sorry. I just hate the practice of overriding malloc/free because it makes API specific a generic case. Memory allocation is something the original C standard library got mostly right. -- D. Richard Hipp <[EMAIL PROTECTED]> Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] sqlite3_free()
[EMAIL PROTECTED] uttered: Christian Smith <[EMAIL PROTECTED]> wrote: My own personal opinion on these coding style issues is if the API requires special handling of cleanup, then the API should do the cleanup. Returning an allocated string that requires special cleanup results in a potentially generic operation now being special cased by the API client. If all the world was Unix, this would work great. But sadly, it is not. We also have to support windows. See http://www.sqlite.org/cvstrac/tktview?tn=444 The sqlite_freemem() API is an old SQLite version 2 API that was added to work around the fact that memory allocated using malloc() in a DLL cannot be passed to free() in the main program. Yes, of course, Windows sticks it's oar in again. Going back to the previous DLL discussion, this alone is surely confirmation of why the Windows DLL system sucks. My previous rant was really that, just a rant. Given the previous interface, you must maintain compatibility, and breaking the old use of free() should be acceptable. My own code is not affected, as I already used sqlite_freemem (stuck with 2.x for the moment.) For reference (well, for my reference at least) I believe that returned memory should be considered static to the database connection, with subsequent invocations overwriting the previous contents. That way, all management would be internal to the API, and if the client wants a copy, he should copy it before the next invocation. This is especially true of such things as error strings. -- D. Richard Hipp <[EMAIL PROTECTED]> Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Hardcopy docs?
Bernie Cosell uttered: On 26 Jun 2006 at 14:14, [EMAIL PROTECTED] wrote: "Bernie Cosell" <[EMAIL PROTECTED]> wrote: I'll confess that I am an old-fashioned "hardcopy" kind of guy... are the sqlite3 docs available in any sort of reasonably-printable format? http://www.apress.com/book/bookDisplay.html?bID=10130 Hmmm... Since I don't really need 450+ pages of info, but just something akin to a crib sheet of the SQL that sqlite supports (which I'd guess would end up at about 20 pages if it weren't so difficultly-embedded in html files), the option of a fifty dollar book isn't really very useful to me. [BTW: no knock on Mike or on the worth of the book. But: I'm not a beginner [either with Perl, SQL or SQLite] nor do I care hardly at all about SQLite's internals... I just need to know the SQL SQLite implements, and so virtually all of Mike's book is really not going to be of any interest or use to me... I'm just the wrong audience for it]. Try this perhaps? http://www.tdb.uu.se/~jan/html2ps.html Just tried (in www directory): $ tclsh lang.tcl | html2ps > lang.ps Mostly useable output. A few formatting issues, but will do as a reference. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] sqlite3_free()
Dennis Cote uttered: Christian Smith wrote: Yes, of course, Windows sticks it's oar in again. Going back to the previous DLL discussion, this alone is surely confirmation of why the Windows DLL system sucks. This really has nothing to do with the Windows DLL system. It is simply the case that the main application and the SQLite library may be compiled with different compilers that use different runtime libraries and therefore implement different memory heaps. You can't in general expect memory that was allocated from one heap by one runtime library (used by SQLite) to be correctly released to another heap maintained by a another runtime library used by the application. Under *nix it is more common, but not required, for applications to link to one common runtime library. Under UNIX it is more common because UNIX provides a runtime system by default. Windows programs all ship with their own runtime due to sloppy engineering on MS's part. It harks back to the days when each DLL had it's own local data segment under Win16. Implementation details from 20 years ago biting us in the bum even when the Win32 API doesn't have segments! For reference (well, for my reference at least) I believe that returned memory should be considered static to the database connection, with subsequent invocations overwriting the previous contents. That way, all management would be internal to the API, and if the client wants a copy, he should copy it before the next invocation. This is especially true of such things as error strings. Ack! No! This leads to non-reentrant code. This is the kind of problem that the standard asctime() API has. It is much better for the caller to provide the memory buffer, or have the library dynamically allocate the buffer and pass it back to the caller. In this case you never have to worry about some other thread calling the function before your thread has completed its copy. Static is probably the wrong word. The string is local to the database connection, which shouldn't be used by more than one thread without proper synchronisation. Anyway, it's not difficult to provide thread local storage. HP-UX's netdb.h functions (gethostbyname etc.) are fully re-entrant despite returning 'static' data, for example. Other UNIXs got hamstrung with various getXbyY_r implementations, with horrible semantics. Dennis Cote Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] sqlite3_free()
Andrew Piskorski uttered: On Tue, Jun 27, 2006 at 04:14:37PM +0100, Christian Smith wrote: Anyway, it's not difficult to provide thread local storage. HP-UX's netdb.h functions (gethostbyname etc.) are fully re-entrant despite returning 'static' data, for example. Other UNIXs got hamstrung with various getXbyY_r implementations, with horrible semantics. Well yes, the *_r functions are often pretty ugly to use. But they work great if what you want to do is build your own thread local storage version on top! I've always assumed there's some good reason for the existence and use of *_r functions rather than equivalent thread local storage versions, although I've never been sure just what it is. Mainly because the _r functions were hacked by lazy types who couldn't be bothered to use TLS (or TLS wasn't available). The _r functions weren't particularly well thought out, leaving the client to allocate the storage (arguably good) without telling the client how big the storage has to actually be (definitely bad). It is this type of implementation issue that should be completely hidden from the client, hence my preferred use of TLS for 'static' buffers managed by the API. Grr, I'm definitely sounding like I'm ranting now:) Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Huge performance difference between SQLite on Windows XP and Windows 2000
Lukáš Neumann uttered: Hello, I am using SQLite DLL version 3.2.1 to access a single file database. I use sqlite3_exec() to call this simple query: BEGIN; INSERT INTO Messages (IDMessage, Body) VALUES (1054, 'Test'); COMMIT; When the application runs under Windows XP, the query takes unnoticable amout of time (10ms or so), but when I run the very same application under clean installation of Windows 2000, the query may take 150ms or more. The database size is same on both systems. How many times did you reproduce the test? Is this test representitive of your actual work patterns? You don't want to benchmark individual INSERTS, then draw conclusions on the relative performance of two similar systems based on the miniscule results. Note, also, that WinXP is a later version of Windows than Win2000. It may contain performance enhancements that earn't present in the earlier version, though I guess the overall performance should be roughly similar. Could you suggest any way how I can improve the performance under Windows 2000? Thanks in advance Lukas Neumann Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] sqlite3_free_table question
Dennis Cote uttered: Your call to sqlite3_free_table is correct. You free the error message by calling sqlite3_free(tresult.err_msg). If either pointer returned by sqlite3_get_table() is NULL, then no memory was allocated, so there is no need to free it, however I believe it should be safe to call the free routines with a NULL pointer. From the discussion the other day, I looked at the implementation of sqlite3_free, and the sqlite3GenericFree that implements it asserts that the pointer passed in is not NULL. So it is not safe to pass in a NULL pointer. It should be safe, if sqlite3_free and co are mimicking the behaviour of the libc free. The current CVS implementation should therefore check for NULL and do nothing in this case. HTH Dennis Cote Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Problem with compiling under HP-UX
Henrik Goldman uttered: Hi, I have a new HP-UX maching running latest official OS B.11.23 and has gcc 4.1.1. The problem is that when I try to configure I get an error: bash-3.00# ./configure CFLAGS="-O2 -lp64" --enable-threadsafe checking build system type... ia64-hp-hpux11.23 checking host system type... ia64-hp-hpux11.23 checking for gcc... /usr/local/bin/gcc checking for C compiler default output file name... configure: error: C compiler cannot create executables See `config.log' for more details. Have you checked config.log for details? bash-3.00# gcc -v Using built-in specs. Target: ia64-hp-hpux11.23 Configured with: ../gcc/configure Thread model: single gcc version 4.1.1 Does any of you have an idea how to proceed? Try compiling a simple "Hello World" application using the compiler. If the application won't run, then it's a compiler problem (I suspect this is the issue.) Thanks in advance. -- Henrik Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
RE: [sqlite] endian-specific code in pure c source release?
Pat Wibbeler uttered: A quick search through the sqlite source release led me to believe that the source may be coded around endian issues. I guess what I'm looking for is affirmation or denial of my cursory reading. If the source is endian dependent, how does sqlite configure and/or make determine the endian nature of the platform on which it is building? What does it change? If I know these things, I suspect that I can build a project that either runs configure as part of the process, or does the same things that make/configure do to make endian-ness correct. If these aren't documented somewhere, I can reverse engineer configure/make, but I was hoping that someone here might have the answers. SQLite database files are stored using network byte order, which is big endian. The C code takes care of translating the on disk network byte order data to host format data. In btree.c, this is handled by the following functions: get2byte get4byte put2byte put4byte In pager.c, this is handled by: read32bits write32bits put32bits retrieve32bits Variable length integers are handled in util.c by: sqlite3GetVarint sqlite3GetVarint32 sqlite3PutVarint The remaining code in SQLite deals with integers in host byte order, and is thus endian-independent. Thanks again! Pat -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, July 03, 2006 10:04 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] endian-specific code in pure c source release? Sqlite data is endian agnostic, but the executables are, like any executables, dependent upon the endian nature of the host processor. Just compile the Sqlite library for each platform and share the data. On platforms other than Windows use configure, otherwise use the prepared windows source. If you make any extensions to Sqlite, such as your own functions, they will be platform independent. By using the regular Sqlite source distribution you will be able to upgrade easily, and not have your application rev-locked. Pat Wibbeler wrote: For a couple of reasons: * I'd like to use xcode to build a universal binary. If I run ./configure, I imagine that any endian specific code that is fixed using configure will be set to whatever platform I run configure on (i386 or ppc). * I already have the packaged source for a windows build using visual studio and I'd like to use the same sources if possible to avoid confusion. Pat -Original Message- From: John Stanton [mailto:[EMAIL PROTECTED] Sent: Monday, July 03, 2006 9:24 AM To: sqlite-users@sqlite.org Subject: Re: [sqlite] endian-specific code in pure c source release? Why not use the regular source and run configure? Pat Wibbeler wrote: I'd like to build an xcode project for sqlite. One straightforward approach is to take the sqlite-source-3_3_6.zip "pure c" source release and build the xcode project from that. Is there any endian specific code in that source release that might trip me up on power pc processors? I ask this because I know that this release is "provided as a service to MS-Windows users who lack the build support infrastructure of Unix." Thanks! Pat -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Using sqlite on Nintendo DS / PSP
ahochan uttered: 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. Not sure how NDS or PSP access ROM (isn't the PSP disc based only anyway?) but you could write a file abstraction on top of the ROM address range, and write an os_ninds.c file to acccess the ROM image. Check out the os_* source files in SQLite. Your job should be eased by not requiring any locking code. It would be difficult to use a ROM :memory: database without being very familiar with the SQLite internals. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Compressing the DBs?
[EMAIL PROTECTED] uttered: Mikey C <[EMAIL PROTECTED]> wrote: Not sure what you mean there DRH, but I set compression on one of my database files on NTFS and file size shrunk from 1,289,216 bytes to 696,320 bytes. And of course the whole compression / decompression process is completely transparent to SQLite and if you decide that compression is a bad thing, you just uncheck the box on that file and you are back to where you started. After turning compression on, try making lots of updates to the database. Does the database stay the same size? Is there a significant I/O performance hit? I'm guessing that the answer in both cases will be "yes". Please let me know. Compression on NTFS and co is done at a cluster group level. If the cluster group does not compress, it is stored as is. I think NTFS works with 16 cluster groups, which would be 64k chunks I think. My guess is that bigger page sizes will benefit compressing filesystems, as similar keys will be close to each other. Match the page size to the group size, so 64k in the case of NTFS. The performance hit should be negligable if any, especially given modern processors' vast performance advantage over disk IO. As has been said, the amount of data being read/written should be lower, so performance may marginally improve. But seek latency should be similar in both cases, so performance is probably largely the same. On a full/fragmented filesystem, writing less data may also reduce the number of seeks required. But a full and fragmented filesystem will have other performance issues anyway. -- D. Richard Hipp <[EMAIL PROTECTED]> Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Compressing the DBs?
Gussimulator uttered: I've been using SQLite for a very short period of time and so far Its doing a great job for my application (single user, quite a big amount of data though). Now, since theres a lot of repetitive data, I thought that compressing the database would be a good idea, since, we all know.. One of the first principles of data compression is getting rid of repetitive data, so... I was wondering if this is possible with SQLite or it would be quite a pain to implement a compression scheme by myself?.. I have worked with many compression libraries before so that wouldnt be an issue, the issue however, would be to implement any of the libraries into SQLite... So, before I waste my time with something that perhaps has been done already, I decided to ask here. Is there any compression scheme I can use? or I'm doomed to implement this by myself? (any tips?). For desktop or server use, storage is cheap. Unless you're constrained by limited space because of embedded FLASH storage, you're better off just taking the hit, IMO. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Fwd: sqlite3_busy_timeout() on NetBSD
[EMAIL PROTECTED] uttered: =?ISO-8859-1?Q?Tobias_Rundstr=F6m?= <[EMAIL PROTECTED]> wrote: Is there something wrong with sqlite3_bus_timeout on NetBSD? I've not had any problems with sqlite3_busy_timeout on Linux. And I do not have NetBSD handy for testing. Not sure what the problem might be. I've just tried using NetBSD/sparc, and it all appears to work fine. This is with current CVS (NetBSD and SQLite) and using .timeout from the shell. -- D. Richard Hipp <[EMAIL PROTECTED]> Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] callback function parameters in UTF-16?
Tzu-Chien Chiu uttered: OK. But what I don't understand is: is this (lack of sqlite3_exec16) by design? The sqlite3_exec function is a deprecated interface used by earlier SQLite releases. New applications should use, for performance reasons if nothing else, sqlite3_compile/sqlite3_step/sqlite3_finalize or related 16 bit equivalents. I presume sqlite3_exec16 was not written because new applications would not be written using the interface. There have been no calls for such an interface until now, and so it was obviously low in demand. Existing applications ported from v2 to v3 use just the UTF-8 interface only as that better maps to the v2 interface. Yet another question on the library design. I've seen some projects which provide a same interface for all supported encodings, e.g. #ifdef _UNICODE #define sqlite3_open sqlite3_open_utf16 #else #define sqlite3_open sqlite3_open_ansi #endif #ifdef _UNICODE #define strcpy _strcpy_w #else #define strcpy _strcpy_a #endif I'd like to know the design rationales on why sqlite chose to make available the interfaces with different encodings simultaneously, and have the users _explicit_ choose which to use. Data migration, perhaps? Or the developer would prefer to use UTF-8 enoding but use the UNICODE wide version of the Windows APIs? 2006/7/10, [EMAIL PROTECTED] <[EMAIL PROTECTED]>: "Tzu-Chien Chiu" <[EMAIL PROTECTED]> wrote: > > Is it true: the only way the fetch the values of the fields in UTF-16 > is to write another version of sqlite3_exec() wrapping sqlite3_*16() > functions? > You do not have to write a new sqlite3_exec16() from scratch. You can make a copy of sqlite3_exec() and with a few simple edits turn it into sqlite3_exec16(). -- D. Richard Hipp <[EMAIL PROTECTED]> -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
[sqlite] Curious join issue
Hello JOIN expoerts:) I have the following schema: CREATE TABLE instances ( instanceid integer primary key, type, instance); CREATE INDEX instances_type_instance ON instances(type,instance); CREATE TABLE instance_fields ( instanceid references instances(instanceid), field, subscript default NULL, value, primary key (instanceid, field, subscript) ); The idea is that instances catalogs instances of an arbitrary type, and instance_fields records the data of each instance as name value pairs. instance_fields.subscript is for arrays as field values (unused ATM). Now, suppose I have an instance type of 'event', which contains the following fields: count first last severity summary node source Then the following view will select all the 'event' instances, formtted into a nice table view: create view events_instance as select i.instance as event, count.value AS count, first.value AS first, last.value AS last, severity.value AS severity, summary.value AS summary, node.value AS node, source.value AS source FROM instances as i inner join instance_fields as count using(instanceid) inner join instance_fields as first using (instanceid) inner join instance_fields as last using (instanceid) inner join instance_fields as severity using (instanceid) inner join instance_fields as summary using (instanceid) inner join instance_fields as node using (instanceid) inner join instance_fields as source using (instanceid) WHERE i.type = 'event' AND first.field = 'first' AND count.field = 'count' AND last.field = 'last' AND severity.field = 'severity' AND summary.field = 'summary' AND node.field = 'node' AND source.field = 'source'; The problem is that the first join is not being done using the primary key index (on count in this case). The query plan is below: 0|0|TABLE instances AS i WITH INDEX instances_type_instance 1|1|TABLE instance_fields AS count 2|2|TABLE instance_fields AS first WITH INDEX sqlite_autoindex_instance_fields_1 3|3|TABLE instance_fields AS last WITH INDEX sqlite_autoindex_instance_fields_1 4|4|TABLE instance_fields AS severity WITH INDEX sqlite_autoindex_instance_fields_1 5|5|TABLE instance_fields AS summary WITH INDEX sqlite_autoindex_instance_fields_1 6|6|TABLE instance_fields AS node WITH INDEX sqlite_autoindex_instance_fields_1 7|7|TABLE instance_fields AS source WITH INDEX sqlite_autoindex_instance_fields_1 I'd expect count to be opened using sqlite_autoindex_instance_fields_1, which refers to the (instanceid, field, subscript) primary key index, as the instanceid is available from 'instances AS i' Changing the order of the joins doesn't help. It's always the first join which goes wrong. I've tried it with and without data in the tables, before and after 'VACUUM ANALYZE'. Anyone know what's wrong? Cheers, Christian PS. This is using less than week old CVS HEAD version. -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Curious join issue
Solved the problem. For reference, field instanceid in the instance_fields needs to be declared as an integer, thus: CREATE TABLE instance_fields ( instanceid integer references instances(instanceid), field, subscript default NULL, value, primary key (instanceid, field, subscript) ); DRH, Is this expected behaviour? I'd have thought the manifest typing would have seen to that, but it appears that join fields need to be the same type. Christian Christian Smith uttered: Hello JOIN expoerts:) I have the following schema: CREATE TABLE instances ( instanceid integer primary key, type, instance); CREATE INDEX instances_type_instance ON instances(type,instance); CREATE TABLE instance_fields ( instanceid references instances(instanceid), field, subscript default NULL, value, primary key (instanceid, field, subscript) ); The idea is that instances catalogs instances of an arbitrary type, and instance_fields records the data of each instance as name value pairs. instance_fields.subscript is for arrays as field values (unused ATM). Now, suppose I have an instance type of 'event', which contains the following fields: count first last severity summary node source Then the following view will select all the 'event' instances, formtted into a nice table view: create view events_instance as select i.instance as event, count.value AS count, first.value AS first, last.value AS last, severity.value AS severity, summary.value AS summary, node.value AS node, source.value AS source FROM instances as i inner join instance_fields as count using(instanceid) inner join instance_fields as first using (instanceid) inner join instance_fields as last using (instanceid) inner join instance_fields as severity using (instanceid) inner join instance_fields as summary using (instanceid) inner join instance_fields as node using (instanceid) inner join instance_fields as source using (instanceid) WHERE i.type = 'event' AND first.field = 'first' AND count.field = 'count' AND last.field = 'last' AND severity.field = 'severity' AND summary.field = 'summary' AND node.field = 'node' AND source.field = 'source'; The problem is that the first join is not being done using the primary key index (on count in this case). The query plan is below: 0|0|TABLE instances AS i WITH INDEX instances_type_instance 1|1|TABLE instance_fields AS count 2|2|TABLE instance_fields AS first WITH INDEX sqlite_autoindex_instance_fields_1 3|3|TABLE instance_fields AS last WITH INDEX sqlite_autoindex_instance_fields_1 4|4|TABLE instance_fields AS severity WITH INDEX sqlite_autoindex_instance_fields_1 5|5|TABLE instance_fields AS summary WITH INDEX sqlite_autoindex_instance_fields_1 6|6|TABLE instance_fields AS node WITH INDEX sqlite_autoindex_instance_fields_1 7|7|TABLE instance_fields AS source WITH INDEX sqlite_autoindex_instance_fields_1 I'd expect count to be opened using sqlite_autoindex_instance_fields_1, which refers to the (instanceid, field, subscript) primary key index, as the instanceid is available from 'instances AS i' Changing the order of the joins doesn't help. It's always the first join which goes wrong. I've tried it with and without data in the tables, before and after 'VACUUM ANALYZE'. Anyone know what's wrong? Cheers, Christian PS. This is using less than week old CVS HEAD version. -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Curious join issue
[EMAIL PROTECTED] uttered: Christian Smith <[EMAIL PROTECTED]> wrote: select FROM instances as i inner join instance_fields as count using(instanceid) inner join instance_fields as first using (instanceid) inner join instance_fields as last using (instanceid) inner join instance_fields as severity using (instanceid) inner join instance_fields as summary using (instanceid) inner join instance_fields as node using (instanceid) inner join instance_fields as source using (instanceid) I believe that USING is broken when there are more than two tables in the join. Try an ON clause instead and see if that doesn't work better. No, that didn't work either until I put the explicit integer type fix in for instanceid in instance_fields. Now the fix is in place, the join with USING works perfectly. Worth creating a ticket? -- D. Richard Hipp <[EMAIL PROTECTED]> Christian PS. An old SPARCclassic is an ideal machine to guage performance differences between schemas and queries:) Before schema change: 100.70 seconds to select from a view After schema change: 11.75 seconds to select from a compatible view. Awesome! -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Curious join issue
[EMAIL PROTECTED] uttered: "Brandon, Nicholas (UK)" <[EMAIL PROTECTED]> wrote: I don't want to hijack this thread (not too much anyway) but this got me thinking about JOINs since I have a database that uses a similar concept (one table holds a number of key-value pairs for another). As I understand it, an INNER JOIN is equivalent in pseudo-code to: For (every record in table A) for (every record in table B) for (...) where another for loop is added with each additional JOIN. Using Christian's example, would SQLite use the pseudo-code represented above or does it use some intelligence that all the JOINs are from the same table and hence the pseudo-code is: for (every record in table "instances") for (every record in table "instance_fields") It does a nested loop. There is no optimization for when the same table is joined multiple times. How often does that happy, really? Not very often, certainly not worth optimizing for. I will only use the view for generating a transient table cache for viewing like objects and reporting. I'm writing an general purpose object storage library, hence the need for arbitrary fields. But the view is not needed when querying or updating single objects, only when generating a view of all similar objects in a table form. For reference, using a SPARCclassic, I get essentially a 4x speed hit using the view against selecting data from the equivalent cache table. Acceptable trade off IMO. -- D. Richard Hipp <[EMAIL PROTECTED]> Thanks, Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] problems compiling with arm-linux-gcc
Keiichi McGuire uttered:
This is a bit confusing, especially since if I compile it using gcc it will
compile w/o any problems. What would this "incompatibility" mean and what
would a solution be to make it compatible and be found by the compiler?
Chances are that the libsqlite.a you're trying to link against is a host
library, ie. x86 binary.
Compile SQLite from source, using the cross compiler.
On 7/11/06, John Stanton <[EMAIL PROTECTED]> wrote:
Now youhave a clean compile,but no sqlite library. It is looking
forlibsqlite.so or libsqlite.a. Seems to be finding an libsqlite.a which
is incompatible with yhe object you compiled.
Keiichi McGuire wrote:
> still no luck:(
> and yea I'm still a bit new to C. I have a few years experience in C
> during
> school, but mainly used for microcontrollers, so I've done a very little
> amount of system applications.
>
> anyways, with the pointer added to the errMsg, as well as the -lsqlite
> switch, i get the following:
>
> [EMAIL PROTECTED]:~/dev/c$ arm-linux-gcc test.c -o test6
> /tmp/ccSgrZe2.o(.text+0x30): In function `main':
> : undefined reference to `sqlite_open'
> collect2: ld returned 1 exit status
> [EMAIL PROTECTED]:~/dev/c$ arm-linux-gcc test.c -o test6 -lsqlite
>
/usr/local/arm/3.3.2/lib/gcc-lib/arm-linux/3.3.2/../../../../arm-linux/bin/ld:
>
> s kipping incompatible
> /usr/local/arm/3.3.2/lib/gcc-lib/arm-linux/3.3.2/libsqlite. so when
> searching for -lsqlite
>
/usr/local/arm/3.3.2/lib/gcc-lib/arm-linux/3.3.2/../../../../arm-linux/bin/ld:
>
> s kipping incompatible
> /usr/local/arm/3.3.2/lib/gcc-lib/arm-linux/3.3.2/libsqlite. a when
> searching
> for -lsqlite
>
/usr/local/arm/3.3.2/lib/gcc-lib/arm-linux/3.3.2/../../../../arm-linux/bin/ld:
>
> c annot find -lsqlite
> collect2: ld returned 1 exit status
>
> this is making my brain hurt! :(
>
>
>
> On 7/11/06, John Stanton <[EMAIL PROTECTED]> wrote:
>
>>
>> Your program should look more like this -
>>
>> sqlite *db;
>> char *errmsg = NULL;/*Ptr to possible error message.*/
>>
>> int main() {
>>printf("Hello!\n");
>>db = sqlite_open("sqlitetest", 0660, &errmsg);
>>
>>/*At this point if you get an error errmsg will not be null
>>and will point to the error message. It will have malloc'd
>>memory for that message so you need to "free" it or suffer
>>a memory leak.*/
>>if (errmsg != NUL) {
>> ...
>>}
>> .
>> }
>>
>> I guess you are new to C. It will get easier. The first ten years are
>> the hardest.
>>
>> Keiichi McGuire wrote:
>> > Hi John,
>> > I still get an error when i use the -lsqlite switch. When i take that
>> > out it
>> > says:
>> > incompatible types in assignment
>> > passing arg 3 of 'sqlite_open' from incompatible pointer type
>> >
>> > also I wanted to use v.3, but i'm working on a system where the disk
>> space
>> > is limited to 64megs, and i actually got the v2 from an ipkg package,
>> and
>> > there was an sqlite3 package available, but it was not compatible
with
>> php
>> > that is on this system.
>> >
>> > -Keiichi
>> >
>> > On 7/11/06, John Stanton <[EMAIL PROTECTED]> wrote:
>> >
>> >>
>> >> Keiichi McGuire wrote:
>> >> > Hi everyone,
>> >> > I'm still a bit new to sqlite and also to crosscompiling programs.
>> >> > I've been having problems with being able to compile this test c
>> >> program
>> >> > that should just connect to the database.
>> >> >
>> >> > My program looks like this:
>> >> >
>> >> > #include
>> >> > #include"sqlite.h>
>> >> >
>> >> > int main()
>> >> > {
>> >> >
>> >> > printf("Hello!\n");
>> >> > sqlite *db;
>> >> > char errMsg;
>> >> > errMsg = NULL;
>> >> >
>> >> > db = sqlite_open("sqlitetest",0660,&errMsg);
>> >> >
>> >> > return 0;
>> >> > }
>> >> >
>> >> >
>> >> > and I get the following error:
>> >> >
>> >> > [EMAIL PROTECTED] dev]# arm-linux-gcc test.c -o test4 -L
>> >> > /home/kmcgui/sqlite-arm/.lib
>> >> >
>> >> > s/ -lsqlite tesc: In function `main':
>> >> > test.c:9: warning: assignment makes integer from pointer without a
>> cast
>> >> > test.c:11: warning: pabssing arg 3 of qlite_open' from
incompatible
>> >> pointer
>> >> > type
>> >> >
>> >>
>>
/usr/local/arm/3.3.2/lib/gcc-lib/arm-linux/3l.3.2/../../../.m-linux/biin/ld:
>>
>> >>
>> >> >
>> >> > skipping mpatible /home/kmcgui/sqlite-arm/.flibs//libsqliteso when
>> >> > senarching for
>> >> >
>> >>
>>
-ls/local/armi/3.3.2/lib/gcc-e/usr/local/arm/3.3.2/lib/gcc-lib/arm-linux/3.3.2/../../../../arm-linux/bin/ld:
>>
>> >>
>> >> >
>> >> > skipping incompatible
>> >> > /usr/local/arm/3.3.2/lib/gcc-lib/arm-linux/3.3.2/libsqlite.a when
>> >> > searlching
>> >> > for -lsqle
>> >> >
>> >>
>>
/usr/local/arm/3.3.2/lib/gcc-lib/arm-linux/3.3.2/../../../../arm-linux/bin/ld:
>>
>> >>
>> >> >
>> >> > cannot find -lsqlite
>> >> > collect2: ld rxeturned 1 exit s
>> >> >
>> >> > thank you in advance!!!
>> >> >
>> >> >
>> >> > -Keiichi
>> >> >
>> >> Try declaring errMsg as a character array rather than a character,
>> >>
>> >> char errMsg[256];
>> >>
>> >>
Re: [sqlite] SQL error: database disk image is malformed
Bull219 uttered: Dear all, I am developping a freeware which uses SQLite. One of my beta testers informed me about an issue he had: with his DB, following the query which is sent to the DB, I have the error in the subject of this email. I did some testing, and when I succeeded in reproducing the error just by doing a VACUUM. I also reproducing the error by doing a "select * FROM table". What is strange is that if I do a select with a specific WHERE condition so that few rows are returned, no error is raised. You'll get the error doing a VACUUM because VACUUM iterates over all the records in the database, as does the "select * FROM table" iterate over all the records in that table. The constrained select query obviously is not hitting the region of corruption. Could you please help me as I need to have a stable DB before releasing the final version of my freeware? That you can reproduce the problem indicates this is unlikely to be a hardware issue. Can you reproduce the test case reliably and consistently? Things to check: - Multiple processes accessing the database file. SQLite will arbitrate access using a well defined locking protocol. If a process does not respect this protocol, or locking is defective (NFS perhaps) then corruption can result from two processes updating the file. - Check for crashes and/or removal of the rollback journal. - Check that your program is not corrupting memory. This would more likely manifest itself in a SIGSEGV, but you're best checking. Things to tell us to offer better advice: - SQLite version - OS platform (Windows, Linux, UNIX etc.) - Filesystem in use (native, NFS SMB etc.) - Language used to access SQLite, plus info on the language SQLite wrapper. - How to reproduce the corruption. Thank you very much in advance, Best regards, Bull -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] filter and sort in CREATE VIEW or CREATE TABLE ?
Inline. Jens Miltner uttered: Hi all, I need to create a temporary table holding ID column values from a number of tables, so that I have a unique way to access the data for display purposes: say I have 2 related tables [snip schema] When displaying a filtered subset of the persons with all their addresses, I'd like to create a temporary table holding the person.id and address.id columns for each row being displayed: CREATE TEMP TABLE display AS SELECT person.id as person_id, address.id as address_id FROM person LEFT JOIN address ON address.person_id=person.id WHERE person.name LIKE '%foo%' ORDER BY person.name, address.type ; What purpose would this table serve? It only has ids in it, so is not any more useful for searching than the base tables, unless you're going to be using it as a cache and processing the result multiple times. To complicate things, I have enumeration tables that are used in the display (let's say for the address type), which can also be filtered for, e.g. CREATE TABLE address_type_enum ( key INTEGER, value TEXT ); INSERT INTO address_type_enum (1, 'Home'); INSERT INTO address_type_enum (2, 'Work'); so that the real query to create the temp table might look like this: CREATE TEMP TABLE display AS SELECT person.id as person_id, address.id as address_id FROM person LEFT JOIN address ON address.person_id=person.id WHERE person.name LIKE '%foo%' AND (SELECT value FROM address_type_enum WHERE key=address.type) LIKE 'work' ORDER BY person.name, (SELECT value FROM address_type_enum WHERE key=address.type) COLLATE NOCASE ; This is horrendously complex for what you're trying to achieve. Why not just join on the address type? To get work addresses, something like: SELECT person.id AS person_id, address.id AS address_id FROM person LEFT JOIN address ON address.person_id=person.id LEFT JOIN address_type_enum ON key=address.type WHERE address_type_enum.value LIKE 'work'; Here, I have two subqueries fetching the same value, so I figured it would be helpful to use an intermediate view: [snip intermediate view stuff] Now, finally, here's my question: Is there a difference in performance whether I do the filtering and ordering in the CREATE VIEW statement or in the CREATE TABLE AS SELECT statement? I'd only create a temporary table if the data is going to be reused multiple times. Else, I'd create a view. You can always create a temporary table from the view, as you've shown below, but it is not necassary unless you want to cache the data. Would this one perform better: CREATE TEMP VIEW display_view AS SELECT person.id as person_id, address.id as address_id, person.name as person_name, SELECT value FROM address_type_enum WHERE key=address.type) as address_type FROM person LEFT JOIN address ON address.person_id=person.id WHERE person_name LIKE '%foo%' AND address_type LIKE 'work' ORDER BY person_name, address_type COLLATE NOCASE ; CREATE TEMP TABLE display AS SELECT person.id as person_id, address.id as address_id FROM display_view ; This would be a better bet. Define views to create your views by whatever criteria you see fit. Store in a temproary table for caching purposes. Any ideas? Thanks, P.S.: These are not real queries, they're just examples of much larger and more complex queries I have in the real application. Also, the tables may contain a few hundred thousand records each, so don't be mistaken by the apparently straight forward table layout given... -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] How do you find out the names of the fields within a table?
John Newby uttered: Hi, how can I find out the names of the fields within a given table? I've tried "pragma table_info(test);" but this brings back too much info, I just require the names as I'll be storing them in an array within my application. Then just cherry pick the information you require. You get a result set from the pragma, and the column name is the second field. Not difficult. Many thanks John -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] using max value
Keiichi McGuire uttered: I'm a bit stuck on this one problem I have this simple table that has two fields: id and value. What I want to do is to get the largest id (the last entry) and then add an arbitrary number to the value that's in the same entry as that one, and put the result in the next entry. to make it sound less confusing, these series of queries should be able to do a fibbonacci sequence. I was trying this query and it's not working: insert into test(value) values((select value from test where id=max(id)); what the heck am I doing wrong? You can't use aggregate functions in WHERE clauses. Also, you can't use a select as the value in an insert. You can insert from the results of an insert. The best I could come up with was: insert into test (value) select max(id)+value from test; This sets the value of the new row to the sum of the previous max(id) and it's associated value. But you must have an initial value in the first row. Thanks! Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] How do you find out the names of the fields within a table?
John Newby uttered: Hi Martin, I'm not sure, I don't use VB that often, I just need to use it for my Uni project at the moment. With all due respect to your University, but VB sucks as a teaching language IMO. Doesn't your Uni have better development tools? Any professor that advocates VB is not worthy of the title. Many thanks John. On 13/07/06, Martin Jenkins <[EMAIL PROTECTED]> wrote: John Newby wrote: > Yeah I can get the names, but I need to put them in an array, and to put > them in an array I need to know the size of the array to store them in, > so I > need to get a count first, then store this number as the size of the array > before I store the values into the array. Are you sure there no dynamic container objects in VB that support an "append" method? Lists? If not (and I find that hard to believe) you could hack around it by appending the names to a string, then parsing the string and then dimensioning your array, or you could build a linked list but ... surely VB has more intelligent containers than statically sized arrays? Martin -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] using max value
Jay Sprenkle uttered: On 7/13/06, Christian Smith <[EMAIL PROTECTED]> wrote: You can't use aggregate functions in WHERE clauses. Also, you can't use a select as the value in an insert. You can insert from the results of an insert. uh...It seems to work: The OP was trying to use a SELECT result set within an insert statement of the form: INSERT INTO test VALUES () which obviously doesn't work. Also, your example below doesn't use an aggregate as a WHERE term expression, but uses a sub-select containing an aggregate. Not the same thing. SQLite version 3.0.8 Enter ".help" for instructions sqlite> CREATE TABLE test ...> ( ...> IdINTEGER PRIMARY KEY, ...> value INTEGER ...> ); sqlite> sqlite> INSERT INTO test(value) VALUES(42); sqlite> INSERT INTO test(value) VALUES(43); sqlite> INSERT INTO test(value) VALUES(44); sqlite> INSERT INTO test(value) VALUES(45); sqlite> sqlite> select * from test; 1|42 2|43 3|44 4|45 sqlite> select value + 10 ...> from test ...> where id = (select max(id) from test); 55 sqlite> insert into test( value ) ...> select value + 10 ...> from test ...> where id = (select max(id) from test); sqlite> sqlite> select * from test; 1|42 2|43 3|44 4|45 5|55 sqlite> -- SqliteImporter and SqliteReplicator: Command line utilities for Sqlite http://www.reddawn.net/~jsprenkl/Sqlite Cthulhu Bucks! http://www.cthulhubucks.com -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] How do you find out the names of the fields within a table?
John Newby uttered: Hi Christian, Nope VB is the best they can come up with, and it's only version 2002, they can't even get the latest edition so I have to work with an outdated wrapper aswell. The standards of education these days. Still, it's better than the F77 my uni shoved down our throats only 10 years ago. I was reading your reply regarding the Max(id) thread, I was wondering if I could use this for my query. The pragma table_info(test) command returns 6 columns, the second being the name column which is what I want. The first being cid column which numbers the fields, if I could somehow select the max from the cid column and then add 1 that wuold give me the total fields in my table and I could use this in my array. Do you know of a way I can do a select on the information brought back with the pragma command? Don't think you can. Why not just run the pragma twice, once to count the number of columns, allocate the array, then again to fill the array. It's not a heavyweight operation. Many thanks John On 13/07/06, Christian Smith <[EMAIL PROTECTED]> wrote: John Newby uttered: > Hi Martin, I'm not sure, I don't use VB that often, I just need to use it > for my Uni project at the moment. With all due respect to your University, but VB sucks as a teaching language IMO. Doesn't your Uni have better development tools? Any professor that advocates VB is not worthy of the title. > > Many thanks > > John. > > On 13/07/06, Martin Jenkins <[EMAIL PROTECTED]> wrote: >> >> John Newby wrote: >> > Yeah I can get the names, but I need to put them in an array, and to put >> > them in an array I need to know the size of the array to store them in, >> > so I >> > need to get a count first, then store this number as the size of the >> array >> > before I store the values into the array. >> >> Are you sure there no dynamic container objects in VB that support an >> "append" method? Lists? >> >> If not (and I find that hard to believe) you could hack around it by >> appending the names to a string, then parsing the string and then >> dimensioning your array, or you could build a linked list but ... surely >> VB has more intelligent containers than statically sized arrays? >> >> Martin >> > -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \ -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] How to port the SQLite
Vivek R uttered: Hi , I am New bee to the group and SQLite. Can anyone explain me How to port the SQLite to DVD or Consumer products or any other Embedded Systems. Where I can look for it ? What and all things to be considered while porting. Which is the best version to port to consumer product which runs VxWorks. VxWorks comes with a POSIX compliant API, IIRC, so it should be just a case of using the existing UNIX based port. Have you tried compiling a default release? Thanks and Regards, Vivek R -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] This great!
Cesar David Rodas Maldonado uttered: I am wondering if it will have a better performance if i split every index and table into different files, i know that i will loose the LITE concept, but i am wondering if it will have a better performance... Not unless each individual file is on a different hard disk. The bottleneck for a hard disk is the head actuator and spindle speed. The OS will already optimise data IO to make best use of the harddisk, whether that be one one or many files. Your laptop will only have a single hard disk. You'll get no more performance splitting the file. Non-LITE databases that use table spaces for improved performance only improve performance when each tablespace is on a different device. Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
Re: [sqlite] Order of columns within a CREATE TABLE statement
w b uttered: Hi all, Just had a quick question with regards to the order of the columns within a create table statement I have a few tables that use the BLOB type for storing various lengths of binary data and I was wondering if its better (more efficient) to always declare columns of this type last within the create table statement or not. I'm not searching on these columns but they do have the chance of being updated with more or less binary data than was originally in them, so wondered if there is any penalty difference for having them at the end or in the middle of of a list of other columns within the table. Or does it not really matter given that any column can handle any data type ? Put longer and not searched for columns at the end of the column list. SQLite will put a minimum of 4 rows in a single page, which for 1K pages, results in something like <240 bytes per row of room after meta information has been used. For rows bigger than this, SQLite builds an overflow list of pages, into which the rest of the data is written. This overflow list is slow to traverse, so it is better to have indexed and/or commonly used columns in the first couple of hundred bytes of the row to avoid having to traverse the overflow pages. Thanks Wayne Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
RE: [sqlite] SQLiteSpy vs. SQLite.dll speed comparison
michael cuthbertson uttered: Brannon: Thank you for your thoughts. To be clear, the 'optimize for speed' setting in MY release is actually slower than MY debug version - I know nothing about Ralf's settings. That issue is separate from SQLiteSpy - I didn't mean to conflate them. And the issue is not which version of VS I'm using. If I simply put a 'return 0' in my callback, the time is within 2%. Thus, I am timing the dll only and I am using the pre-compiled version, not a VS 6 compiled version. Ralf is compiling his own version, in Borland, and gets 2.5 times my speed. Therefore, I believe the speed difference lies in the differences between the two compiled versions. The precompiled version from sqlite.org is compiled using Mingw32, which is a Win32 targeted version of gcc. Being cross platform, gcc can not be as aggressive in optimisations as VC or Borland, which are mostly targeted at Intel based processors and have large, paid development teams wringing every last drop of performance out of them. At a guess, as SQLite code is heavy in branching, the Borland compiler is making better use of your processor pipeline by reordering instructions, especially if you have a P4. Michael Christian -- /"\ \ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL X - AGAINST MS ATTACHMENTS / \
