RE: [sqlite] Performance tuning using PRAGMA, other methods
I believe the optimizations that would help the most is the time spent on "commits." Thanks, Brett -Original Message- From: Scott Krig [mailto:[EMAIL PROTECTED] Sent: Thursday, November 29, 2007 10:57 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods BTW, several PRAGMAS actually increase performance in my embedded app case - maybe 15-30% depending upon transaction activity and the way I structure transaction commits. Specific PRAGMAS that helped include: // // Synchronous OFF (0) // rc = sqlite3_exec(*dbHandle, "PRAGMA synchronous = 0;", 0, 0, 0); // // Keep temporary storage in MEMORY (2) instead of a file // rc = sqlite3_exec(*dbHandle, "PRAGMA temp_store = 2;", 0, 0, 0); // // Allow reads from uncommitted memory containing DB tables/records // rc = sqlite3_exec(*dbHandle, "PRAGMA read_uncommitted = 1;", 0, 0, 0); // // Exclusive access to DB to avoid lock/unlock for each transaction // rc = sqlite3_exec(*dbHandle, "PRAGMA locking_mode = exclusive;", 0, 0, 0); -Original Message- From: Scott Krig [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 2:03 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods There are apparently no folks with the experience to answer the questions as given? -Original Message- From: Tom Briggs [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 1:41 PM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods re: Q1 - Maybe (hopefully) someone will prove me wrong, but I think that your question is too broad to be answerable (unless you're actually attempting to assemble a collection of optimal values in all possible situations for all existing pragmas... ;shrug) re: Q2 - At the risk of sounding crass, tuning queries is, has been and always will be the best way to optimize the performance of any database. I've done a lot of tuning of SQLite and a half dozen other databases, and query design is always what has the most impact. Pragmas, #defines, API usage, etc. are always a distant second in the race for performance gains. -T > -Original Message- > From: Scott Krig [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 20, 2007 4:24 PM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods > > To the point, the questions are: > > > Q1)) PRAGMA: Does anyone have experience and good results optimizing > sqlite performance using PRAGMA's? If so, which ones, how were they > used, and what was the performance increase? > > Q2)) Other techniques: Any success stories on sqlite optimization > methods of any type would be appreciated. > > > > > > -Original Message- > From: Tom Briggs [mailto:[EMAIL PROTECTED] > Sent: Tuesday, November 20, 2007 10:40 AM > To: sqlite-users@sqlite.org > Subject: RE: [sqlite] Performance tuning using PRAGMA, other methods > > >Which pragmas will be most effective (and what values you > should use > for each) depends on what you're trying to do with the database. > Synchronous is important if you're writing frequently, for > example, but > won't matter much in a read-only setting. Appropriate values for the > page_size and cache_size pragmas vary depending on whether > the database > is write-mostly or read-mostly and also depending on whether > you want to > optimize for reading or writing. > >So in short, the answer is, it depends. Depends on what you're > trying to tune for, that is. > >-T > > > -Original Message- > > From: Scott Krig [mailto:[EMAIL PROTECTED] > > Sent: Tuesday, November 20, 2007 1:13 PM > > To: sqlite-users@sqlite.org > > Subject: [sqlite] Performance tuning using PRAGMA, other methods > > > > > > What are the 'biggest bang for the buck' sqlite optimization > > techniques > > to apply to a working system to tune performance? > > > > Q1)) PRAGMA: Does anyone have experience and good results optimizing > > sqlite performance using PRAGMA's? If so, which ones, how were they > > used, and what was the performance increase? > > Q2)) Other techniques: Any success stories on sqlite optimization > > methods of any type would be appreciated. > > > > Thanks. > > > > > > Scott > > -=- > > > > > > > > Here is a list of the PRAGMA examples from the sqlite documentation: > > > > > > > > PRAGMA auto_vacuum; > > PRAGMA auto_vacuum = 0 | none | 1 | full | 2 | incremental; > > > > PRAGMA cache_size; > > PRAGMA cache_size = Number-of-pages; > > > > PRAGMA case_sensitive_like; > > PRAGMA case_sensitive_like = 0 | 1; > > > > PRAGMA count_changes; > > PRAGMA count_changes = 0 | 1; > > > > PRAGMA default_cache_size; > > PRAGMA default_cache_size = Number-of-pages; > > > > PRAGMA default_synchronous; > > > > PRAGMA
RE: [sqlite] Amalgamation questions
Well I basically did the following, but not sure it's optimal: 1) Took 3.3.17 amalgamation 2) Took shell.c from 3.3.17 full distribution, and made a target that just uses sqlite3.c and shell.c to get the command line tool 3) Took whatever I had in my old makefile for compile flags and added them for compiling sqlite3.c in my project An extra step was needed: 1) Apparently 3.3.17 needs libdl, where 3.3.8 seemingly didn't, so I had to make some adjustments. Not that it's all that hard to figure the above out, but just wanted some perspective on the "recommended" approach, since I couldn't find any docs or list emails regarding building the shell using the amalgamation, and things might change so that what I'm doing now may no longer "work." Thanks, Brett -Original Message- From: Brett Keating [mailto:[EMAIL PROTECTED] Sent: Saturday, June 09, 2007 9:43 AM To: sqlite-users@sqlite.org Subject: [sqlite] Amalgamation questions Hi, I am interested in upgrading my sqlite3, and the amalgamation looks like an interesting option. However I have some questions. 1) The page says with 3.3.18, the amalgamation will be available for direct download. Is 3.3.18 not available yet? It's not on the download page. If it's not I will generate it from 3.3.17... just want to make sure I have to do that :) 2) With the old way of configuring, generating and building the sqlite library, I have a makefile that builds both a linkable library as well as an installable command-line tool. With the amalgamation, I no longer need to build the linkable library because I can directly drop it into my project... but then what is the recommended way to also build the command-line tool and install it, under this paradigm? 3) Is there a set of compile-time options that I'd have to include in my project makefile (which would now include the amalgamation of sqlite), that would otherwise have been in sqlite's own makefile under the old paradigm? Thanks! Brett - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Amalgamation questions
Hi, I am interested in upgrading my sqlite3, and the amalgamation looks like an interesting option. However I have some questions. 1) The page says with 3.3.18, the amalgamation will be available for direct download. Is 3.3.18 not available yet? It's not on the download page. If it's not I will generate it from 3.3.17... just want to make sure I have to do that :) 2) With the old way of configuring, generating and building the sqlite library, I have a makefile that builds both a linkable library as well as an installable command-line tool. With the amalgamation, I no longer need to build the linkable library because I can directly drop it into my project... but then what is the recommended way to also build the command-line tool and install it, under this paradigm? 3) Is there a set of compile-time options that I'd have to include in my project makefile (which would now include the amalgamation of sqlite), that would otherwise have been in sqlite's own makefile under the old paradigm? Thanks! Brett
RE: [sqlite] bizarre query problem
Actually this wasn't the issue after all... Indices have nothing to do with it. The genre was being inserted from two different sources. It is a UTF-16 string, and in one case it was being inserted with a null terminator, and in another case it was not. Since I used "sqlite3_bind_text16" and specified a length that included the null terminator, it was stored in the database with that null terminator. Unfortunately when I do this, the string that I get back from the database is of length -1 compared to what I inserted. So for example if genre is "Rock" and I inserted "Rock0" where 0 is null terminator, I get "Rock" back and not "Rock0." Note below that POP is reported as 3 characters long, but was inserted as 4 with a null terminator. Interestingly enough, sqlite3 will give me two copies of POP when I ask for unique genres, if I insert a value as "POP" and another value as "POP0." So in a sense this was merely user error but also an interesting idiosyncracy of the sqlite3 database. Thanks, Brett -Original Message- From: Joe Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 23, 2007 11:41 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] bizarre query problem --- Brett Keating <[EMAIL PROTECTED]> wrote: > Msica independiente|text|19|0056_People Get Ready1_test1.wma > POP|text|3|0057_The Mighty Ship1_test1.wma 0058_The Mighty > POP|text|3|Quinn1_test1.wma > > Anyway, it turns out the problem was caused by creating an index on > the genre field. If I don't create an index, it works normally for > both OSes. Clearly there's a problem. Can you try testing with the latest version of sqlite? Assuming it is not already fixed, consider making a small test case and filing a ticket: http://www.sqlite.org/cvstrac/tktnew Get the free Yahoo! toolbar and rest assured with the added security of spyware protection. http://new.toolbar.yahoo.com/toolbar/features/norton/index.php - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] bizarre query problem
It said that the hex function was not found so I skipped that. Msica independiente|text|19|0056_People Get Ready1_test1.wma POP|text|3|0057_The Mighty Ship1_test1.wma POP|text|3|0058_The Mighty Quinn1_test1.wma Anyway, it turns out the problem was caused by creating an index on the genre field. If I don't create an index, it works normally for both OSes. Thanks, Brett -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 22, 2007 9:07 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] bizarre query problem Joe Wilson <[EMAIL PROTECTED]> wrote: > Just for kicks, what happens on both platforms when you issue: > > select genre, length(genre), hex(genre), filename > from objects where media_type=1; > Make that: select genre, typeof(genre), length(genre), hex(genre), filename from objects where media_type=1; -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] bizarre query problem
Hi, I have a bizarre problem. Here is an example of something I tried in sqlite3 3.3.8: sqlite> select genre,filename from objects where media_type=1; query abbreviated... Msica independiente|0056_People Get Ready1_test1.wma POP|0057_The Mighty Ship1_test1.wma POP|0058_The Mighty Quinn1_test1.wma query abbreviated... sqlite> select genre,filename from objects where genre='POP'; sqlite> So basically, no results are returned from the second query although clearly there are items in the list with genre='POP'. This problem only happens on Linux. On Windows, the query returns the results as expected... Which makes it yet more bizarre. The exact same query run "in code" using the C interface, rather than on the command line interface, also behaves similarly... Works on Windows, not on Linux. In both cases I am careful to put the entire query into UTF-16, as the strings are stored as UTF-16 for internationalization purposes. I have a couple questions: 1) Would having an index on the genre column cause any potential issues here? 2) Is there a potential issue in string handling between linux/windows that I should be aware of? 3) Are there any build-time configuration differences I may have inadvertently introduced that may cause behavior like this? The Linux version runs on an ARM-11. The Windows version runs on a typical Dell PC in a visual studio environment. Thanks, Brett - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Effect of blobs on performance
Thanks I think this answers my question well! Brett -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, February 21, 2007 1:41 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Effect of blobs on performance "Brett Keating" <[EMAIL PROTECTED]> wrote: > Hi, > > I'm curious about what the effect of having a blob in the database may > be on performance. I have two design options: 1) put a small image > file > (15-30kbyte) into the database as a blob, and 2) store the image in a > separate file on disk and hold the filename in the database. My table > has around 20 rows in it, about half are strings/smaller blobs and > half are integers. > > Option number one, for various reasons, is far more elegant and simple > in terms of its impact on the rest of the code. However, I am > concerned that holding such large amounts of data per record might > impact performance. I could be worried about nothing though, which is > why I'm writing to this list :). > When I was designing the SQLite file format, I made the assumption that BLOBs would be used infrequently and would not be very big. The file format design is not optimized for storing BLOBs. Indeed, BLOBs are stored as a singly-linked list of database pages. It is hard to imagine a more inefficient design. Much to my surprise, people begin putting multi-megabyte BLOBs in SQLite databases and reporting that performance really was not an issue. I have lately taken up this practice myself and routinely uses SQLite database with BLOBs that are over 10MiB is size. And it all seems to work pretty well here on my Linux workstation. I have no explanation for why it works so well, but it does so I'm not going to complain. If your images are only 30KiB, you should have no problems. Here's a hint though - make the BLOB columns the last column in your tables. Or even store the BLOBs in a separate table which only has two columns: an integer primary key and the blob itself, and then access the BLOB content using a join if you need to. If you put various small integer fields after the BLOB, then SQLite has to scan through the entire BLOB content (following the linked list of disk pages) to get to the integer fields at the end, and that definitely can slow you down. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Effect of blobs on performance
Thanks for the quick reply. I was lysdexic, I meant to say 20 columns. Probably would never exceed 20,000 rows, most likely would hover around 2-4K rows in a typical situation. If it has no effect on performance, I'd rather hold it in the database because I do like the idea of having a "neat package" so to speak. For instance when a record is deleted, I'd rather not worry about having to check to see if it holds an image file and then go delete that file... And if I delete many items at once with one statement, I'd have to break it up into individual deletes and check each one in order to delete my corresponding image file on disk... Otherwise I'll get these orphaned image files all over the place on my disk that correspond to deleted records. I'm fairly sure disk space requirements will be nearly identical in each case... Just worried about query/update performance. Thanks, Brett -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of P Kishor Sent: Wednesday, February 21, 2007 1:38 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Effect of blobs on performance On 2/21/07, Brett Keating <[EMAIL PROTECTED]> wrote: > Hi, > > I'm curious about what the effect of having a blob in the database may > be on performance. I have two design options: 1) put a small image > file > (15-30kbyte) into the database as a blob, and 2) store the image in a > separate file on disk and hold the filename in the database. My table > has around 20 rows in it, about half are strings/smaller blobs and > half are integers. > > Option number one, for various reasons, is far more elegant and simple > in terms of its impact on the rest of the code. However, I am > concerned that holding such large amounts of data per record might > impact performance. I could be worried about nothing though, which is > why I'm writing to this list :). > unless you haven't told us the complete story, you are worried about nothing probably. You have only 20 rows, fer crying out loud, you could probably build an application with chalk and slate and it would be fast enough ;-) that said, there is much recurring discussion on this on another list that I am on (folks wanting to store remote sensing imagery into PostGres). Most concur that it is a ridiculous idea... just store the metadata including the image path in the db, and then let the filesystem do the heavy lifting. Usually one can't do much db level analysis on blobs anyway, so storing them in the db becomes pointless other than the fact that it is a nice neat single bundle. Well, wonder if you could utilize the concept of packages the way they are on OS X. >From the outside, a package looks like a file... you can grab it, copy it, move it. Actually it is a folder in which you can have your core db as well as all the images. -- Puneet Kishor http://punkish.eidesis.org/ Nelson Inst. for Env. Studies, UW-Madison http://www.nelson.wisc.edu/ Open Source Geospatial Foundation http://www.osgeo.org/education/ - collaborate, communicate, compete = - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] SQLite last inserted id
Can you use the API call sqlite3_last_insert_rowid ? -Original Message- From: Weston Weems [mailto:[EMAIL PROTECTED] Sent: Monday, January 22, 2007 3:58 PM To: sqlite-users@sqlite.org Subject: [sqlite] SQLite last inserted id What I'd ultimately like to do, and what I used to do with a sproc, is the following Pass a string to a sproc, it'll select from table where field = string value, and if no records insert it and grab last inserted id. That way with one call, I can ensure one string in db, and always get the an id back. Is there a way i can do this with sqlite 3 latest? Looks like there is SOME sort of concept of this with views (xp_proc) with parameters etc, but I cant really find any documentation etc. Anyone have any advice for a sqlite newb? Thanks in advance. - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Restricting integer primary key values
Hi Wayne, What I've decided to do is manage it myself. I have a trigger on deletes to the main table, which stores the deleted primary key in another table. If the other table is empty, I query for the max key in the main table and increment it by one. If the other table is not empty, I take a value from that table and use it, then delete that value from that table. The values of the primary keys don't matter to me so long as they are within my range, so I'm just trying to re-use any "holes" in the sequence just in case I ever run out (which I imagine won't happen in my lifetime but hey you never know). Thanks for the replies, Brett -Original Message- From: w b [mailto:[EMAIL PROTECTED] Sent: Monday, January 15, 2007 11:42 AM To: sqlite-users@sqlite.org Subject: RE: [sqlite] Restricting integer primary key values Hi Brett, I dont think that there is a way within Sqlite to automagically do what you are looking for. I know that within Oracle they have the ability for you to define a sequence which allows lower and upper bounds to be defined as well as if the sequence can loop, which sounds like what you could be looking for. However typically even within Oracle you then use the value obtained from the sequence value into your insert statement. This however is not automatic and would still require you to check the bounds conditions, since a declared sequence can be used for anything you really like and is not tied to a specific primary key within a specific table. In the case of sqlite you would probably need to create a C function to do that your self. Dr H, I was wondering if it would be worth considering exposing the ability for a user to define a function that could override the auto increment abilities of the primary key, or would that open up a can of worms ? Wayne Brett Keating <[EMAIL PROTECTED]> wrote: Hi, This does indeed work, but only to prevent primary keys from being inserted into the database if they fall out of range. What I was hoping was that the with this command, the sqlite3 would always automatically choose primary keys that fell within this range. However with this command, the sqlite3 will eventually automatically choose a primary key outside of this range (I tested with a range of 100 to 1000), and fail to insert the new item based on the check clause. I tried to repeat the statement hoping that a new primary key would be generated and eventually the insertion would succeed, but this ended up causing an infinite loop (I think the key once chosen by the automated algorithm remains the same for repeated attempts). Are there any alternatives? I want automatic key generation that is restricted to a range, and I don't want failures unless all possible values are taken. I will continue trying other things in the meantime. Thanks, Brett -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, January 14, 2007 6:09 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Restricting integer primary key values "Brett Keating" wrote: > Hi, > > I don't want to spam the list but basically, if "id INTEGER PRIMARY > KEY CHECK (id > 0 and id < 0x)" or something along those lines > will work, please let me know. It's not clear if sqlite3 supports > checks on primary keys from what I can tell. > SQLite does not support hexadecimal constants. Use 4294967295 instead of 0x and it should work. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Restricting integer primary key values
Hi, This does indeed work, but only to prevent primary keys from being inserted into the database if they fall out of range. What I was hoping was that the with this command, the sqlite3 would always automatically choose primary keys that fell within this range. However with this command, the sqlite3 will eventually automatically choose a primary key outside of this range (I tested with a range of 100 to 1000), and fail to insert the new item based on the check clause. I tried to repeat the statement hoping that a new primary key would be generated and eventually the insertion would succeed, but this ended up causing an infinite loop (I think the key once chosen by the automated algorithm remains the same for repeated attempts). Are there any alternatives? I want automatic key generation that is restricted to a range, and I don't want failures unless all possible values are taken. I will continue trying other things in the meantime. Thanks, Brett -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Sunday, January 14, 2007 6:09 PM To: sqlite-users@sqlite.org Subject: Re: [sqlite] Restricting integer primary key values "Brett Keating" <[EMAIL PROTECTED]> wrote: > Hi, > > I don't want to spam the list but basically, if "id INTEGER PRIMARY > KEY CHECK (id > 0 and id < 0x)" or something along those lines > will work, please let me know. It's not clear if sqlite3 supports > checks on primary keys from what I can tell. > SQLite does not support hexadecimal constants. Use 4294967295 instead of 0x and it should work. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Restricting integer primary key values
Hi, I don't want to spam the list but basically, if "id INTEGER PRIMARY KEY CHECK (id > 0 and id < 0x)" or something along those lines will work, please let me know. It's not clear if sqlite3 supports checks on primary keys from what I can tell. Thanks, Brett -Original Message----- From: Brett Keating [mailto:[EMAIL PROTECTED] Sent: Sunday, January 14, 2007 5:14 PM To: sqlite-users@sqlite.org Subject: [sqlite] Restricting integer primary key values Hi, I'm interested in the ability to restrict the values my integer primary key is allowed to take. Specifically, I would like to restrict the value to be between (but not including) 0x and 0x. In other words, a 32 bit number where all zeroes and all ones is not allowed. Is this possible in sqlite3? Thanks, Brett - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Restricting integer primary key values
Hi, I'm interested in the ability to restrict the values my integer primary key is allowed to take. Specifically, I would like to restrict the value to be between (but not including) 0x and 0x. In other words, a 32 bit number where all zeroes and all ones is not allowed. Is this possible in sqlite3? Thanks, Brett
