Re: [sqlite] Performance tuning using PRAGMA, other methods
On 11/20/07, Scott Krig <[EMAIL PROTECTED]> wrote: > There are apparently no folks with the experience to answer the > questions as given? Those who have experience know better than to try to answer in an e-mail what 1000 mails in the mailing list are not enough. The wiki and documentation have more than enough information for the level of detail you want. Regards, ~Nuno Lucas > -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 empty_result_callbacks; > > > PRAGMA empty_result_callbacks = 0 | 1; > > > > > > PRAGMA encoding; > > > PRAGMA encoding = "UTF-8"; > > > PRAGMA encoding = "UTF-16"; > > > PRAGMA encoding = "UTF-16le"; > > > PRAGMA encoding = "UTF-16be"; > > > > > > PRAGMA full_column_names; > > > PRAGMA full_column_names = 0 | 1; > > > > > > PRAGMA fullfsync > > > PRAGMA fullfsync = 0 | 1; > > > > > > PRAGMA incremental_vacuum(N); > > > > > > PRAGMA legacy_file_format; > > > PRAGMA legacy_file_format = ON | OFF > > > > > > PRAGMA locking_mode; > > > PRAGMA locking_mode = NORMAL | EXCLUSIVE > > > PRAGMA main.locking_mode=EXCLUSIVE; > > > > > > PRAGMA page_size; > > > PRAGMA page_size = bytes; > > > > > > PRAGMA max_page_count; > > > PRAGMA max_page_count = N; > > > > > > PRAGMA read_uncommitted; > > > PRAGMA read_uncommitted = 0 | 1; > > > > > > PRAGMA short_column_names; > > > PRAGMA short_column_names = 0 | 1; >
Re: [sqlite] Performance tuning, and other (silly?) SQLitequestions.
> Ken <[EMAIL PROTECTED]> wrote: > > Can you do a test load to a memory database. What is the performance > (row/sec) of your data using an in memory database? I tried a RAMDisk on Windows. I haven't tried at home on Linux. I'm not sure if I tried the C API to RAMDisk database combination, only the Tcl API to RAMDisk database combination. Maybe Tcl was the bottleneck here. > Is that a burst rate or a sustained data rate? You need to achieve > about 70k rows per second to be able to maintain 66k rps. Well, at the moment it is mostly only bursts of data, but I have no control over the possibility that it will become even faster, or continuous, so I want need to push the envelope. > Maximum TPS is 60 on a 7200 rpm drive. A 15000 RPM drive should get > about 120, which is double the TPS. So maybe a faster drive would be > helpful. Quite possible. At the moment I'm not sure what HD is installed on the vehicle where the data is produced, but it can always be improved upon by throwing $$ at it ;-) Cheers, James. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Performance tuning, and other (silly?) SQLitequestions.
> James Steward <[EMAIL PROTECTED]> wrote: > > Dennis Cote <[EMAIL PROTECTED]> wrote: > > > You have said you tried both the TCL and C APIs, but you didn't say if > > you were using prepared insert statements in the C API. If not, that > > will save the overhead of parsing and code generation for each insert > > statement. The prepare, bind step, reset mechanism will give better > > perfomance. > > Is there an example you know of somewhere? I've been reading... http://www.sqlite.org/capi3ref.html#sqlite3_prepare_v2 Seems fairly straight forward. I'll try tomorrow (perhaps). Regards, James. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance tuning, and other (silly?) SQLitequestions.
Or you will need to get a faster disk drive! And more of them. Consider a raid 0 system using striping. High speed fiber connects will also be helpful. Can you do a test load to a memory database. What is the performance (row/sec) of your data using an in memory database? Is that a burst rate or a sustained data rate? You need to achieve about 70k rows per second to be able to maintain 66k rps. Maximum TPS is 60 on a 7200 rpm drive. A 15000 RPM drive should get about 120, which is double the TPS. So maybe a faster drive would be helpful. Ken [EMAIL PROTECTED] wrote: James Steward wrote: > > Michael Ruck wrote: > > > > I know that a natural join exists, but it is not automatic as > > it seems to be in MySQL. > > Thanks , and thanks to all who replied to my questions. > > I've been testing SQLite's speed, for inserting the type of data I > gather from the field. I've tried encapsulating multiple inserts > between begin and commit statements, fiddling the pragmas > and with both Tcl and C interfaces, even using a RAM disk to > store the database file. > > I can receive up to 2,000,000 records in about 30 seconds from > the field, and I can't seem to jam them into an SQLite database > any faster than about 100 seconds at best, on my system. > SQLite will do about 5 inserts/sec on my Linux workstation. Doing 2 million in 30 seconds is going to be pushing the envelope. You should probably consider a different solution. -- D. Richard Hipp - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
I found a solution - apparently the DB file was just hosed. There were actually duplicates stored somehow. I dumped the database to a flat file and modified the IDs by hand then imported the data into a new SQLite database. That seemed to do the trick. I'm not sure how the corruption happened but my users have been known to do things like store the live data file on a USB flash drive and use it so it's hard to tell! Thanks to all for the information and suggestions! On Nov 20, 2007 7:43 PM, Trey Mack <[EMAIL PROTECTED]> wrote: > INSERT INTO invoice_items > (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) > VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE > DIVIDED',1,800,450,'f','f','$8.00','$8.00') > > Does this work? > > INSERT INTO invoice_items > (invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) > VALUES (899,1001975,'HD0001 - ASH - YL','','HOUSE > DIVIDED',1,800,450,'f','f','$8.00','$8.00') > > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
INSERT INTO invoice_items (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE DIVIDED',1,800,450,'f','f','$8.00','$8.00') Does this work? INSERT INTO invoice_items (invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) VALUES (899,1001975,'HD0001 - ASH - YL','','HOUSE DIVIDED',1,800,450,'f','f','$8.00','$8.00') - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
I'm not sure what it means, but here it is : *** in database main *** On page 2580 at right child: 2nd reference to page 2677 On tree page 9 cell 15: 2nd reference to page 2678 On tree page 9 cell 15: Child page depth differs On tree page 9 cell 16: Child page depth differs Page 2681 is never used Page 2682 is never used Page 2683 is never used Page 2684 is never used Page 2685 is never used Page 2686 is never used Page 2687 is never used Page 2688 is never used Page 2689 is never used Page 2690 is never used Page 2691 is never used Page 2692 is never used Page 2693 is never used Page 2694 is never used Page 2695 is never used Page 2696 is never used Page 2697 is never used Page 2698 is never used Page 2699 is never used Page 2700 is never used Page 2701 is never used Page 2702 is never used Page 2703 is never used Page 2704 is never used Page 2705 is never used Page 2706 is never used Page 2707 is never used Page 2708 is never used Page 2709 is never used Page 2710 is never used Page 2711 is never used Page 2712 is never used Page 2713 is never used Page 2714 is never used Page 2715 is never used Page 2716 is never used Page 2717 is never used Page 2718 is never used Page 2719 is never used Page 2720 is never used Page 2721 is never used Page 2722 is never used Page 2723 is never used Page 2724 is never used Page 2725 is never used Page 2726 is never used Page 2727 is never used Page 2728 is never used Page 2729 is never used Page 2730 is never used Page 2731 is never used Page 2732 is never used Page 2733 is never used Page 2734 is never used Page 2735 is never used Page 2736 is never used Page 2737 is never used Page 2738 is never used Page 2739 is never used Page 2740 is never used Page 2741 is never used Page 2742 is never used Page 2743 is never used Page 2744 is never used Page 2745 is never used Page 2746 is never used Page 2747 is never used Page 2748 is never used Page 2749 is never used Page 2750 is never used Page 2751 is never used Page 2752 is never used Page 2753 is never used Page 2754 is never used Page 2755 is never used Page 2756 is never used Page 2757 is never used Page 2758 is never used Page 2759 is never used Page 2760 is never used Page 2761 is never used Page 2762 is never used Page 2763 is never used Page 2764 is never used Page 2765 is never used Page 2766 is never used Page 2767 is never used Page 2768 is never used Page 2769 is never used Page 2770 is never used Page 2771 is never used Page 2772 is never used Page 2773 is never used Page 2774 is never used Page 2775 is never used Page 2776 is never used On Nov 20, 2007 6:30 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > > Mitchell Vincent wrote: > > select max(item_id) from invoice_items; > > > > Produces "803" - but that is pretty clearly wrong when I look at the > > dataset a count() returns 15503 records in that table. > > > > min(item_id) is 1 (as expected). > > > > I can insert records if I specify a unique item_id. > > > > > > On Nov 20, 2007 5:50 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > > > >> Mitchell Vincent wrote: > >> > >>> Here we go. I am accessing the database through an ODBC connection > >>> using the most recent version from Christian's site.. > >>> > >>> The table schema : > >>> > >>> CREATE TABLE invoice_items ( > >>> item_id INTEGER PRIMARY KEY, > >>> invoice_id int4, > >>> product_id int4, > >>> product_name text , > >>> sku text , > >>> description text , > >>> quantity text, > >>> price int4, > >>> cost int4, > >>> taxable bool, > >>> format_price text, > >>> format_total_price text, > >>> taxable2 boolean) > >>> > >>> The query : > >>> > >>> INSERT INTO invoice_items > >>> (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) > >>> VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE > >>> DIVIDED',1,800,450,'f','f','$8.00','$8.00') > >>> > >>> The error : > >>> > >>> PRIMARY KEY must be unique (19) > >>> > >>> I can't post the database itself because it contains some sensitive > >>> information (as you might have guessed, it's a database of billing > >>> information). > >>> > >>> The INSERT query for the invoice_items table has "NULL" hard coded in > >>> for item_id, so I know it's not accidentally being set to an existing > >>> value. > >>> > >>> > >>> > >>> > >> Can you select the max and min item_id values form the database and > >> report them back here. > >> > >> Can you then change your code to insert a new dummy record with an > >> item_id that is set to one greater than the maximum value, rather than > >> your hard coded NULL value. I just want to see if you can insert a > >> properly formed record into your database at all. You should then delete > >> that record so it doesn't cause a problem in your database later. > >> > >> Dennis Cote > >> > >> > >> > >> - > >> To unsubscribe, send email to [EMAIL PROTECTED] > >>
Re: [sqlite] Re: Performance tuning, and other (silly?) SQLitequestions.
Dennis Cote <[EMAIL PROTECTED]> wrote: Hi Dennis, > You haven't said what your system is, but to record your data in real > time you will need to insert about 70K records per second. That is high, > > but not impossible for SQLite, so I wouldn't give up yet. I have had > SQLite doing 60K inserts per second on a standard 7200 RPM hard drive. That sounds good. > Do you actually need to insert records at this rate continuously, or > just for a short 30 second burst? If it is bursty, how much time do you > have between bursts? Usually there are bursts of data, and we can buffer it for a while, but at times, and to allow for system growth, I was hoping to match the system maximum continuous data rate. > You have said you tried both the TCL and C APIs, but you didn't say if > you were using prepared insert statements in the C API. If not, that > will save the overhead of parsing and code generation for each insert > statement. The prepare, bind step, reset mechanism will give better > perfomance. Is there an example you know of somewhere? > If you can do post processing on the data, then you could look at > storing the data into separate databases on separate high speed (i.e.15K > RPM) hard drives. This should give you the raw I/O speed you need to get > all the info to disk. The you can run a second program that merges the > separate databases into a single one. I will need to ponder this one. Thanks for the idea. > Do you need the ACID properties of SQLite, or can you simple repeat the > collection process if you have an OS crash or power fail while > collecting data? If not, then you can turn off the synchronous writing > with Pragma Synchronous=Off which should increase your write rate again. This is also a possibility. I shall investigate. Cheers, James. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
Mitchell Vincent wrote: select max(item_id) from invoice_items; Produces "803" - but that is pretty clearly wrong when I look at the dataset a count() returns 15503 records in that table. min(item_id) is 1 (as expected). I can insert records if I specify a unique item_id. On Nov 20, 2007 5:50 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: Mitchell Vincent wrote: Here we go. I am accessing the database through an ODBC connection using the most recent version from Christian's site.. The table schema : CREATE TABLE invoice_items ( item_id INTEGER PRIMARY KEY, invoice_id int4, product_id int4, product_name text , sku text , description text , quantity text, price int4, cost int4, taxable bool, format_price text, format_total_price text, taxable2 boolean) The query : INSERT INTO invoice_items (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE DIVIDED',1,800,450,'f','f','$8.00','$8.00') The error : PRIMARY KEY must be unique (19) I can't post the database itself because it contains some sensitive information (as you might have guessed, it's a database of billing information). The INSERT query for the invoice_items table has "NULL" hard coded in for item_id, so I know it's not accidentally being set to an existing value. Can you select the max and min item_id values form the database and report them back here. Can you then change your code to insert a new dummy record with an item_id that is set to one greater than the maximum value, rather than your hard coded NULL value. I just want to see if you can insert a properly formed record into your database at all. You should then delete that record so it doesn't cause a problem in your database later. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] - Mitchell, You should probably run an integrity check on your database to see if it has been corrupted somehow. Can you issue a PRAGMA INTEGRITY_CHECK command? Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Performance tuning, and other (silly?) SQLitequestions.
James Steward wrote: I can receive up to 2,000,000 records in about 30 seconds from the field, and I can't seem to jam them into an SQLite database any faster than about 100 seconds at best, on my system. So unless anyone can suggest some magic I have not thought of, I will have to abandon the SQL database dream, and write a less functional, data and application specific, database of my own. James, You haven't said what your system is, but to record your data in real time you will need to insert about 70K records per second. That is high, but not impossible for SQLite, so I wouldn't give up yet. I have had SQLite doing 60K inserts per second on a standard 7200 RPM hard drive. Do you actually need to insert records at this rate continuously, or just for a short 30 second burst? If it is bursty, how much time do you have between bursts? You have said you tried both the TCL and C APIs, but you didn't say if you were using prepared insert statements in the C API. If not, that will save the overhead of parsing and code generation for each insert statement. The prepare, bind step, reset mechanism will give better perfomance. If you can do post processing on the data, then you could look at storing the data into separate databases on separate high speed (i.e.15K RPM) hard drives. This should give you the raw I/O speed you need to get all the info to disk. The you can run a second program that merges the separate databases into a single one. Do you need the ACID properties of SQLite, or can you simple repeat the collection process if you have an OS crash or power fail while collecting data? If not, then you can turn off the synchronous writing with Pragma Synchronous=Off which should increase your write rate again. HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
No triggers at all in the database. It's pretty vanilla.. On Nov 20, 2007 6:04 PM, <[EMAIL PROTECTED]> wrote: > "Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > > > Here we go. I am accessing the database through an ODBC connection > > using the most recent version from Christian's site.. > > > > The table schema : > > > > CREATE TABLE invoice_items ( > > item_id INTEGER PRIMARY KEY, > > invoice_id int4, > > product_id int4, > > product_name text , > > sku text , > > description text , > > quantity text, > > price int4, > > cost int4, > > taxable bool, > > format_price text, > > format_total_price text, > > taxable2 boolean) > > > > The query : > > > > INSERT INTO invoice_items > > (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) > > VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE > > DIVIDED',1,800,450,'f','f','$8.00','$8.00') > > > > The error : > > > > PRIMARY KEY must be unique (19) > > > > I wonder if this error might be coming from a TRIGGER > and not from the INSERT statement itself? > > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
select max(item_id) from invoice_items; Produces "803" - but that is pretty clearly wrong when I look at the dataset a count() returns 15503 records in that table. min(item_id) is 1 (as expected). I can insert records if I specify a unique item_id. On Nov 20, 2007 5:50 PM, Dennis Cote <[EMAIL PROTECTED]> wrote: > > Mitchell Vincent wrote: > > Here we go. I am accessing the database through an ODBC connection > > using the most recent version from Christian's site.. > > > > The table schema : > > > > CREATE TABLE invoice_items ( > > item_id INTEGER PRIMARY KEY, > > invoice_id int4, > > product_id int4, > > product_name text , > > sku text , > > description text , > > quantity text, > > price int4, > > cost int4, > > taxable bool, > > format_price text, > > format_total_price text, > > taxable2 boolean) > > > > The query : > > > > INSERT INTO invoice_items > > (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) > > VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE > > DIVIDED',1,800,450,'f','f','$8.00','$8.00') > > > > The error : > > > > PRIMARY KEY must be unique (19) > > > > I can't post the database itself because it contains some sensitive > > information (as you might have guessed, it's a database of billing > > information). > > > > The INSERT query for the invoice_items table has "NULL" hard coded in > > for item_id, so I know it's not accidentally being set to an existing > > value. > > > > > > > Can you select the max and min item_id values form the database and > report them back here. > > Can you then change your code to insert a new dummy record with an > item_id that is set to one greater than the maximum value, rather than > your hard coded NULL value. I just want to see if you can insert a > properly formed record into your database at all. You should then delete > that record so it doesn't cause a problem in your database later. > > Dennis Cote > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
"Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > Here we go. I am accessing the database through an ODBC connection > using the most recent version from Christian's site.. > > The table schema : > > CREATE TABLE invoice_items ( > item_id INTEGER PRIMARY KEY, > invoice_id int4, > product_id int4, > product_name text , > sku text , > description text , > quantity text, > price int4, > cost int4, > taxable bool, > format_price text, > format_total_price text, > taxable2 boolean) > > The query : > > INSERT INTO invoice_items > (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) > VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE > DIVIDED',1,800,450,'f','f','$8.00','$8.00') > > The error : > > PRIMARY KEY must be unique (19) > I wonder if this error might be coming from a TRIGGER and not from the INSERT statement itself? -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Performance tuning, and other (silly?) SQLitequestions.
James Steward <[EMAIL PROTECTED]> wrote: > > Michael Ruck <[EMAIL PROTECTED]> wrote: > > > > I know that a natural join exists, but it is not automatic as > > it seems to be in MySQL. > > Thanks , and thanks to all who replied to my questions. > > I've been testing SQLite's speed, for inserting the type of data I > gather from the field. I've tried encapsulating multiple inserts > between begin and commit statements, fiddling the pragmas > and with both Tcl and C interfaces, even using a RAM disk to > store the database file. > > I can receive up to 2,000,000 records in about 30 seconds from > the field, and I can't seem to jam them into an SQLite database > any faster than about 100 seconds at best, on my system. > SQLite will do about 5 inserts/sec on my Linux workstation. Doing 2 million in 30 seconds is going to be pushing the envelope. You should probably consider a different solution. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Performance tuning using PRAGMA, other methods
Tom Brigg's response to your question 2 was an excellent response. Efficiently constructed queries will generally produce the best results. Also, indexes on fields contained in WHERE clauses will generally produce good results. Lee _ -Original Message- From: Scott Krig [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 3: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 empty_result_callbacks; > > PRAGMA empty_result_callbacks = 0 | 1; > > > > PRAGMA encoding; > > PRAGMA encoding = "UTF-8"; > > PRAGMA encoding = "UTF-16"; > > PRAGMA encoding = "UTF-16le"; > > PRAGMA encoding = "UTF-16be"; > > > > PRAGMA full_column_names; > > PRAGMA full_column_names = 0 | 1; > > > > PRAGMA fullfsync > > PRAGMA fullfsync = 0 | 1; > > > > PRAGMA incremental_vacuum(N); > > > > PRAGMA legacy_file_format; > > PRAGMA legacy_file_format = ON | OFF > > > > PRAGMA locking_mode; > > PRAGMA locking_mode = NORMAL | EXCLUSIVE > > PRAGMA main.locking_mode=EXCLUSIVE; > > > > PRAGMA page_size; > > PRAGMA page_size = bytes; > > > > PRAGMA max_page_count; > > PRAGMA max_page_count = N; > > > > PRAGMA read_uncommitted; > > PRAGMA read_uncommitted = 0 | 1; > > > > PRAGMA short_column_names; > > PRAGMA short_column_names = 0 | 1; > >
Re: [sqlite] Resetting a Primary Key
Mitchell Vincent wrote: Here we go. I am accessing the database through an ODBC connection using the most recent version from Christian's site.. The table schema : CREATE TABLE invoice_items ( item_id INTEGER PRIMARY KEY, invoice_id int4, product_id int4, product_name text , sku text , description text , quantity text, price int4, cost int4, taxable bool, format_price text, format_total_price text, taxable2 boolean) The query : INSERT INTO invoice_items (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE DIVIDED',1,800,450,'f','f','$8.00','$8.00') The error : PRIMARY KEY must be unique (19) I can't post the database itself because it contains some sensitive information (as you might have guessed, it's a database of billing information). The INSERT query for the invoice_items table has "NULL" hard coded in for item_id, so I know it's not accidentally being set to an existing value. Can you select the max and min item_id values form the database and report them back here. Can you then change your code to insert a new dummy record with an item_id that is set to one greater than the maximum value, rather than your hard coded NULL value. I just want to see if you can insert a properly formed record into your database at all. You should then delete that record so it doesn't cause a problem in your database later. Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Re: Performance tuning, and other (silly?) SQLitequestions.
> Michael Ruck <[EMAIL PROTECTED]> wrote: > > I know that a natural join exists, but it is not automatic as > it seems to be in MySQL. Thanks , and thanks to all who replied to my questions. I've been testing SQLite's speed, for inserting the type of data I gather from the field. I've tried encapsulating multiple inserts between begin and commit statements, fiddling the pragmas and with both Tcl and C interfaces, even using a RAM disk to store the database file. I can receive up to 2,000,000 records in about 30 seconds from the field, and I can't seem to jam them into an SQLite database any faster than about 100 seconds at best, on my system. So unless anyone can suggest some magic I have not thought of, I will have to abandon the SQL database dream, and write a less functional, data and application specific, database of my own. Cheers, James. - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
Here we go. I am accessing the database through an ODBC connection using the most recent version from Christian's site.. The table schema : CREATE TABLE invoice_items ( item_id INTEGER PRIMARY KEY, invoice_id int4, product_id int4, product_name text , sku text , description text , quantity text, price int4, cost int4, taxable bool, format_price text, format_total_price text, taxable2 boolean) The query : INSERT INTO invoice_items (item_id,invoice_id,product_id,product_name,sku,description,quantity,price,cost,taxable,taxable2,format_price,format_total_price) VALUES (NULL,899,1001975,'HD0001 - ASH - YL','','HOUSE DIVIDED',1,800,450,'f','f','$8.00','$8.00') The error : PRIMARY KEY must be unique (19) I can't post the database itself because it contains some sensitive information (as you might have guessed, it's a database of billing information). The INSERT query for the invoice_items table has "NULL" hard coded in for item_id, so I know it's not accidentally being set to an existing value. On Nov 20, 2007 4:44 PM, <[EMAIL PROTECTED]> wrote: > > "Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > > I have a primary key that auto increments and has apparently > > overlapped back on to itself. > > > > INSERT into mytable(id,name) values(NULL,'test'); > > > > ... is giving me "primary key must be unique" errors. > > > > How can I reset the sequence for a primary key? The table only has > > about 15000 records in it and I've never seen this happen before.. > > > > There are over 9e18 keys. So if you create 1 billion new > rows per second, continuously, it will take you 262 years to > exhaust all primary keys. Since SQLite has only been in > existance for 7 years, this is clearly impossible so you > must be doing something wrong. > > Perhaps if you provided more details we could help you. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Resetting a Primary Key
No doubt, it is obviously something that has been screwed up. Unfortunately I'm in "hot fix" mode right now and the investigation into why it happened will happen after I get this customer back up and running. I have a feeling it has something to do with the ODBC driver being used. I'll gather and post the schema of the table along with the query. The "null" value is hard-coded into the query string of the INSERT query so it is definitely not being set incorrectly by the software - I let SQLite handle the ID assignment. Is it possible to set the sequence that a primary key uses to track the next ID? If so, how can I do it? Thanks very much for the reply! On Nov 20, 2007 4:44 PM, <[EMAIL PROTECTED]> wrote: > > "Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > > I have a primary key that auto increments and has apparently > > overlapped back on to itself. > > > > INSERT into mytable(id,name) values(NULL,'test'); > > > > ... is giving me "primary key must be unique" errors. > > > > How can I reset the sequence for a primary key? The table only has > > about 15000 records in it and I've never seen this happen before.. > > > > There are over 9e18 keys. So if you create 1 billion new > rows per second, continuously, it will take you 262 years to > exhaust all primary keys. Since SQLite has only been in > existance for 7 years, this is clearly impossible so you > must be doing something wrong. > > Perhaps if you provided more details we could help you. > > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > > - > To unsubscribe, send email to [EMAIL PROTECTED] > - > > -- - Mitchell Vincent - K Software - Innovative Software Solutions - Visit our website and check out our great software! - http://www.ksoftware.net - To unsubscribe, send email to [EMAIL PROTECTED] -
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 empty_result_callbacks; > > PRAGMA empty_result_callbacks = 0 | 1; > > > > PRAGMA encoding; > > PRAGMA encoding = "UTF-8"; > > PRAGMA encoding = "UTF-16"; > > PRAGMA encoding = "UTF-16le"; > > PRAGMA encoding = "UTF-16be"; > > > > PRAGMA full_column_names; > > PRAGMA full_column_names = 0 | 1; > > > > PRAGMA fullfsync > > PRAGMA fullfsync = 0 | 1; > > > > PRAGMA incremental_vacuum(N); > > > > PRAGMA legacy_file_format; > > PRAGMA legacy_file_format = ON | OFF > > > > PRAGMA locking_mode; > > PRAGMA locking_mode = NORMAL | EXCLUSIVE > > PRAGMA main.locking_mode=EXCLUSIVE; > > > > PRAGMA page_size; > > PRAGMA page_size = bytes; > > > > PRAGMA max_page_count; > > PRAGMA max_page_count = N; > > > > PRAGMA read_uncommitted; > > PRAGMA read_uncommitted = 0 | 1; > > > > PRAGMA short_column_names; > > PRAGMA short_column_names = 0 | 1; > > > > PRAGMA synchronous; > > PRAGMA synchronous = FULL; (2) > > PRAGMA synchronous = NORMAL; (1) > > PRAGMA synchronous = OFF; (0) > > > > PRAGMA temp_store; > > PRAGMA temp_store = DEFAULT; (0) > > PRAGMA temp_store = FILE; (1) > > PRAGMA temp_store = MEMORY; (2) > > > > PRAGMA temp_store_directory; > > PRAGMA temp_store_directory = 'directory-name'; > > > > > > > > -- > -- > - > To unsubscr
Re: [sqlite] String manipulation with pure sqlite3?
I think you just need to implement an INSTR(x,y,z) where X is the input string.
Y is the search string, and z is the search starting location. Typically
negative numbers indicate the end of the string to search backwards.
So
Substr(t1.col, 0, instr(t1.col, '.', -1) ) || '(' t2.col ||')' ||
substr(t1.col, instr(t1.col,'.',-1) ) from t1, t2 where t1.id = t2.id
Should get you pretty close.
See load_extension to get an idea on how to build an extension function such as
the instr for your purposes.
hth
Alexander Skwar <[EMAIL PROTECTED]> wrote: Daniel Önnerby schrieb:
> This should be a simple task for any programming language to do once the
> results has been retrieved.
Yes, of course. But it would be nice, if that could be done
on SQL level.
> With the current expressions in SQLite I believe there is no way to do
> this unless you extend SQLite with your own "string_find_last" or
> "replace_last" function.
Thanks. That's what I figured out as well :) Thanks
a lot for confrming this!
Alexander Skwar
-
To unsubscribe, send email to [EMAIL PROTECTED]
-
Re: [sqlite] Resetting a Primary Key
"Mitchell Vincent" <[EMAIL PROTECTED]> wrote: > I have a primary key that auto increments and has apparently > overlapped back on to itself. > > INSERT into mytable(id,name) values(NULL,'test'); > > ... is giving me "primary key must be unique" errors. > > How can I reset the sequence for a primary key? The table only has > about 15000 records in it and I've never seen this happen before.. > There are over 9e18 keys. So if you create 1 billion new rows per second, continuously, it will take you 262 years to exhaust all primary keys. Since SQLite has only been in existance for 7 years, this is clearly impossible so you must be doing something wrong. Perhaps if you provided more details we could help you. -- D. Richard Hipp <[EMAIL PROTECTED]> - To unsubscribe, send email to [EMAIL PROTECTED] -
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 empty_result_callbacks; > > PRAGMA empty_result_callbacks = 0 | 1; > > > > PRAGMA encoding; > > PRAGMA encoding = "UTF-8"; > > PRAGMA encoding = "UTF-16"; > > PRAGMA encoding = "UTF-16le"; > > PRAGMA encoding = "UTF-16be"; > > > > PRAGMA full_column_names; > > PRAGMA full_column_names = 0 | 1; > > > > PRAGMA fullfsync > > PRAGMA fullfsync = 0 | 1; > > > > PRAGMA incremental_vacuum(N); > > > > PRAGMA legacy_file_format; > > PRAGMA legacy_file_format = ON | OFF > > > > PRAGMA locking_mode; > > PRAGMA locking_mode = NORMAL | EXCLUSIVE > > PRAGMA main.locking_mode=EXCLUSIVE; > > > > PRAGMA page_size; > > PRAGMA page_size = bytes; > > > > PRAGMA max_page_count; > > PRAGMA max_page_count = N; > > > > PRAGMA read_uncommitted; > > PRAGMA read_uncommitted = 0 | 1; > > > > PRAGMA short_column_names; > > PRAGMA short_column_names = 0 | 1; > > > > PRAGMA synchronous; > > PRAGMA synchronous = FULL; (2) > > PRAGMA synchronous = NORMAL; (1) > > PRAGMA synchronous = OFF; (0) > > > > PRAGMA temp_store; > > PRAGMA temp_store = DEFAULT; (0) > > PRAGMA temp_store = FILE; (1) > > PRAGMA temp_store = MEMORY; (2) > > > > PRAGMA temp_store_directory; > > PRAGMA temp_store_directory = 'directory-name'; > > > > > > > > -- > -- > - > To unsubscribe, send email to [EMAIL PROTECTED] > -- > -- > - > > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > ---
RE: [sqlite] Performance tuning using PRAGMA, other methods
Why not try benchmarking the pragmas yourself and posting your findings to the list? http://www.sqlite.org/pragma.html --- Scott Krig <[EMAIL PROTECTED]> wrote: > 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. Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now. http://mobile.yahoo.com/sports;_ylt=At9_qDKvtAbMuh1G1SQtBI7ntAcJ - To unsubscribe, send email to [EMAIL PROTECTED] -
RE: [sqlite] Resetting a Primary Key
Hi Mitchell, I don't know that resetting the primary key would be productive, since rollover of INTEGER PRIMARY KEY would not occur anywhere even remotely close to 15000. Are you sure you aren't somehow attempting an insert of a key that has already been used -- perhaps because of some race condition? -Original Message- From: Mitchell Vincent [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 20, 2007 4:01 PM To: sqlite-users@sqlite.org Subject: [sqlite] Resetting a Primary Key I have a primary key that auto increments and has apparently overlapped back on to itself. INSERT into mytable(id,name) values(NULL,'test'); .. is giving me "primary key must be unique" errors. How can I reset the sequence for a primary key? The table only has about 15000 records in it and I've never seen this happen before.. -- - Mitchell Vincent - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Need help reading 3.3.2 database files with 3.5.2...
Richard Klein <[EMAIL PROTECTED]> wrote: > > The previous statement is actually more general: SQLite > > version 3.x.y can read and write any database created by > > any prior version of SQLite. > > Even SQLite 2.w.z ? No. Any prior 3.x.x version of SQLite. - To unsubscribe, send email to [EMAIL PROTECTED] -
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 empty_result_callbacks; > PRAGMA empty_result_callbacks = 0 | 1; > > PRAGMA encoding; > PRAGMA encoding = "UTF-8"; > PRAGMA encoding = "UTF-16"; > PRAGMA encoding = "UTF-16le"; > PRAGMA encoding = "UTF-16be"; > > PRAGMA full_column_names; > PRAGMA full_column_names = 0 | 1; > > PRAGMA fullfsync > PRAGMA fullfsync = 0 | 1; > > PRAGMA incremental_vacuum(N); > > PRAGMA legacy_file_format; > PRAGMA legacy_file_format = ON | OFF > > PRAGMA locking_mode; > PRAGMA locking_mode = NORMAL | EXCLUSIVE > PRAGMA main.locking_mode=EXCLUSIVE; > > PRAGMA page_size; > PRAGMA page_size = bytes; > > PRAGMA max_page_count; > PRAGMA max_page_count = N; > > PRAGMA read_uncommitted; > PRAGMA read_uncommitted = 0 | 1; > > PRAGMA short_column_names; > PRAGMA short_column_names = 0 | 1; > > PRAGMA synchronous; > PRAGMA synchronous = FULL; (2) > PRAGMA synchronous = NORMAL; (1) > PRAGMA synchronous = OFF; (0) > > PRAGMA temp_store; > PRAGMA temp_store = DEFAULT; (0) > PRAGMA temp_store = FILE; (1) > PRAGMA temp_store = MEMORY; (2) > > PRAGMA temp_store_directory; > PRAGMA temp_store_directory = 'directory-name'; > > > - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Resetting a Primary Key
I have a primary key that auto increments and has apparently overlapped back on to itself. INSERT into mytable(id,name) values(NULL,'test'); .. is giving me "primary key must be unique" errors. How can I reset the sequence for a primary key? The table only has about 15000 records in it and I've never seen this happen before.. -- - Mitchell Vincent - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] Re: trouble with TRIGGERs
solved... case of the missing ; after the UPDATE and INSERT statements. On Nov 20, 2007 12:35 PM, P Kishor <[EMAIL PROTECTED]> wrote: > I have a table > > CREATE TABLE foo ( > foo_id INTEGER PRIMARY KEY, > foo_name TEXT, > foo_text TEXT > ); > > I have created a virtual table to do full text search > > CREATE VIRTUAL TABLE fts_foo ( > USING fts2(foo_text); > > I have populated this table > > INSERT INTO fts_foo (rowid, foo_text) > VALUES (foo.foo_id, foo.foo_text) > > for every row in foo. So far so good. Now, I want to create TRIGGERs > that update fts_foo every time foo.foo_text is updated or a new record > is inserted into foo. > > CREATE TRIGGER update_fts > AFTER UPDATE OF foo_text ON foo > BEGIN > UPDATE fts_foo > SET foo_text = new.foo_text > WHERE rowid = old.foo_id > END; > > CREATE TRIGGER insert_fts > AFTER INSERT ON foo > BEGIN > INSERT INTO fts_foo (rowid, foo_text) > VALUES (new.foo_id, new.foo_text) > END; > > But I am getting a syntax error in both cases. What am I doing wrong? > -- Puneet Kishor http://punkish.eidesis.org/ Nelson Institute for Environmental Studies http://www.nelson.wisc.edu/ Open Source Geospatial Foundation (OSGeo) http://www.osgeo.org/ Summer 2007 S&T Policy Fellow, The National Academies http://www.nas.edu/ - To unsubscribe, send email to [EMAIL PROTECTED] -
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 empty_result_callbacks; > PRAGMA empty_result_callbacks = 0 | 1; > > PRAGMA encoding; > PRAGMA encoding = "UTF-8"; > PRAGMA encoding = "UTF-16"; > PRAGMA encoding = "UTF-16le"; > PRAGMA encoding = "UTF-16be"; > > PRAGMA full_column_names; > PRAGMA full_column_names = 0 | 1; > > PRAGMA fullfsync > PRAGMA fullfsync = 0 | 1; > > PRAGMA incremental_vacuum(N); > > PRAGMA legacy_file_format; > PRAGMA legacy_file_format = ON | OFF > > PRAGMA locking_mode; > PRAGMA locking_mode = NORMAL | EXCLUSIVE > PRAGMA main.locking_mode=EXCLUSIVE; > > PRAGMA page_size; > PRAGMA page_size = bytes; > > PRAGMA max_page_count; > PRAGMA max_page_count = N; > > PRAGMA read_uncommitted; > PRAGMA read_uncommitted = 0 | 1; > > PRAGMA short_column_names; > PRAGMA short_column_names = 0 | 1; > > PRAGMA synchronous; > PRAGMA synchronous = FULL; (2) > PRAGMA synchronous = NORMAL; (1) > PRAGMA synchronous = OFF; (0) > > PRAGMA temp_store; > PRAGMA temp_store = DEFAULT; (0) > PRAGMA temp_store = FILE; (1) > PRAGMA temp_store = MEMORY; (2) > > PRAGMA temp_store_directory; > PRAGMA temp_store_directory = 'directory-name'; > > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] trouble with TRIGGERs
I have a table CREATE TABLE foo ( foo_id INTEGER PRIMARY KEY, foo_name TEXT, foo_text TEXT ); I have created a virtual table to do full text search CREATE VIRTUAL TABLE fts_foo ( USING fts2(foo_text); I have populated this table INSERT INTO fts_foo (rowid, foo_text) VALUES (foo.foo_id, foo.foo_text) for every row in foo. So far so good. Now, I want to create TRIGGERs that update fts_foo every time foo.foo_text is updated or a new record is inserted into foo. CREATE TRIGGER update_fts AFTER UPDATE OF foo_text ON foo BEGIN UPDATE fts_foo SET foo_text = new.foo_text WHERE rowid = old.foo_id END; CREATE TRIGGER insert_fts AFTER INSERT ON foo BEGIN INSERT INTO fts_foo (rowid, foo_text) VALUES (new.foo_id, new.foo_text) END; But I am getting a syntax error in both cases. What am I doing wrong? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] String manipulation with pure sqlite3?
Daniel Önnerby schrieb: This should be a simple task for any programming language to do once the results has been retrieved. Yes, of course. But it would be nice, if that could be done on SQL level. With the current expressions in SQLite I believe there is no way to do this unless you extend SQLite with your own "string_find_last" or "replace_last" function. Thanks. That's what I figured out as well :) Thanks a lot for confrming this! Alexander Skwar - To unsubscribe, send email to [EMAIL PROTECTED] -
AW: [sqlite] Re: Performance tuning, and other (silly?) SQLitequestions.
I know that a natural join exists, but it is not automatic as it seems to be in MySQL. > -Ursprüngliche Nachricht- > Von: Dennis Cote [mailto:[EMAIL PROTECTED] > Gesendet: Dienstag, 20. November 2007 18:32 > An: sqlite-users@sqlite.org > Betreff: Re: [sqlite] Re: Performance tuning, and other > (silly?) SQLitequestions. > > Michael Ruck wrote: > >> > >> Ah. I have been reading a PHP/MySQL book, that I thought > said a MySQL > >> server would see the common column names and automagically > join the 2. > >> Either I misremember what the book said (it's not with me > >> here), or this > >> is a feature of MySQL, not present in SQLite. Anyway, what > >> you suggest > >> works just fine. > >> > > > > SQLite does not implement this feature. Its not in the SQL > standard AFAIK. > > > > > > > > This feature *is* part of the SQL standard and is implemented > by SQLite. > It is called a NATURAL JOIN. > > select * from a_table natural join b_table; > > This will select all rows where all fields with the same name > in the two > tables are equal. See http://en.wikipedia.org/wiki/Join_(SQL) > for more > details. Note, only one column, with the same name as the matching > columns from the two tables, is generated in the result (and > this column > is not considered to be from either table in standard SQL). > > HTH > Dennis Cote > > > > > > -- > --- > To unsubscribe, send email to [EMAIL PROTECTED] > -- > --- > > - To unsubscribe, send email to [EMAIL PROTECTED] -
[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 empty_result_callbacks; PRAGMA empty_result_callbacks = 0 | 1; PRAGMA encoding; PRAGMA encoding = "UTF-8"; PRAGMA encoding = "UTF-16"; PRAGMA encoding = "UTF-16le"; PRAGMA encoding = "UTF-16be"; PRAGMA full_column_names; PRAGMA full_column_names = 0 | 1; PRAGMA fullfsync PRAGMA fullfsync = 0 | 1; PRAGMA incremental_vacuum(N); PRAGMA legacy_file_format; PRAGMA legacy_file_format = ON | OFF PRAGMA locking_mode; PRAGMA locking_mode = NORMAL | EXCLUSIVE PRAGMA main.locking_mode=EXCLUSIVE; PRAGMA page_size; PRAGMA page_size = bytes; PRAGMA max_page_count; PRAGMA max_page_count = N; PRAGMA read_uncommitted; PRAGMA read_uncommitted = 0 | 1; PRAGMA short_column_names; PRAGMA short_column_names = 0 | 1; PRAGMA synchronous; PRAGMA synchronous = FULL; (2) PRAGMA synchronous = NORMAL; (1) PRAGMA synchronous = OFF; (0) PRAGMA temp_store; PRAGMA temp_store = DEFAULT; (0) PRAGMA temp_store = FILE; (1) PRAGMA temp_store = MEMORY; (2) PRAGMA temp_store_directory; PRAGMA temp_store_directory = 'directory-name';
Re: [sqlite] Re: Performance tuning, and other (silly?) SQLitequestions.
Michael Ruck wrote: Ah. I have been reading a PHP/MySQL book, that I thought said a MySQL server would see the common column names and automagically join the 2. Either I misremember what the book said (it's not with me here), or this is a feature of MySQL, not present in SQLite. Anyway, what you suggest works just fine. SQLite does not implement this feature. Its not in the SQL standard AFAIK. This feature *is* part of the SQL standard and is implemented by SQLite. It is called a NATURAL JOIN. select * from a_table natural join b_table; This will select all rows where all fields with the same name in the two tables are equal. See http://en.wikipedia.org/wiki/Join_(SQL) for more details. Note, only one column, with the same name as the matching columns from the two tables, is generated in the result (and this column is not considered to be from either table in standard SQL). HTH Dennis Cote - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] String manipulation with pure sqlite3?
This should be a simple task for any programming language to do once the results has been retrieved. With the current expressions in SQLite I believe there is no way to do this unless you extend SQLite with your own "string_find_last" or "replace_last" function. Alexander Skwar wrote: Hello. Suppose I've got tables like this: sqlite> .schema t1 CREATE TABLE t1 (id integer primary key not null, name); sqlite> .schema t2 CREATE TABLE t2 (t1id integer, txt STRING NOT NULL); Filled with: sqlite> select * from t1; 1|foo.bar.boing 2|bumm.krach.klong.schepper 3|just.a.test.entry sqlite> select * from t2; 1|kurz 2|etwas laenger Now I'd like to have a SELECT statement, which would return: 1|foo.bar (kurz).boing 2|bumm.krach.klong (etwas laenger).schepper Ie., before the LAST ".", add what's in t2 but put it in brackets (). It is so, that there are more values in t1, then there are in t2. I only want to get those rows, which are listed in t2. sqlite> select * from t1, t2 where t2.t1id = t1.id; 1|foo.bar.boing|1|kurz 2|bumm.krach.klong.schepper|2|etwas laenger Is this doable in pure sqlite3, or would I need to "massage" the returned data in a programming language? Thanks, Alexander Skwar - To unsubscribe, send email to [EMAIL PROTECTED] - - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] String manipulation with pure sqlite3?
Hello. Suppose I've got tables like this: sqlite> .schema t1 CREATE TABLE t1 (id integer primary key not null, name); sqlite> .schema t2 CREATE TABLE t2 (t1id integer, txt STRING NOT NULL); Filled with: sqlite> select * from t1; 1|foo.bar.boing 2|bumm.krach.klong.schepper 3|just.a.test.entry sqlite> select * from t2; 1|kurz 2|etwas laenger Now I'd like to have a SELECT statement, which would return: 1|foo.bar (kurz).boing 2|bumm.krach.klong (etwas laenger).schepper Ie., before the LAST ".", add what's in t2 but put it in brackets (). It is so, that there are more values in t1, then there are in t2. I only want to get those rows, which are listed in t2. sqlite> select * from t1, t2 where t2.t1id = t1.id; 1|foo.bar.boing|1|kurz 2|bumm.krach.klong.schepper|2|etwas laenger Is this doable in pure sqlite3, or would I need to "massage" the returned data in a programming language? Thanks, Alexander Skwar - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Can't checkout from cvs
Hi, Yes that does seem to bypass the login prompt. It says "logging in to...". But still can't actually connect to server. windows has only recently been installed (winxp sp2). also get same problem with firewall turned off. Any other reason why I can't connect to server? Thank Rael - Be a better sports nut! Let your teams follow you with Yahoo Mobile. Try it now.
Re: [sqlite] Memory Usage
Once again you're missing the point. Of course you can get a malloc/free implementation that performs garbage collection, such as Boehm's conservative GC. But C garbage collection and malloc/free memory fragmentation are quite different things. You can still get heavily fragmented memory with a C garbage collector - or even with traditional malloc/free with an ideal free()ing scheme. Java and C garbage collectors are not directly comparable. Java is able to compact memory by copying it and adjusting the references transparently such that no unusable memory gaps are left. This is not possible in C due to its cavalier pointer casting and raw memory manipulation. Successful C garbage collectors instead opt for conservative GC leaving the memory at the same location. (I am not aware of any precise C garbage collector that works with multi-threaded code or runs in a comparable time to malloc/free.) Nevermind that typical garbage collectors (Java or C) need 3 times or more working memory as a typical malloc/free scheme in order to achieve decent timings. If memory is tight and performance is paramount, then garbage collection is not the answer. --- John Stanton <[EMAIL PROTECTED]> wrote: > You confused my point which is that your usual malloc/free definitely > does no garbage collection. That does not mean that a C language > program cannot perform garbage collection, just look at a Java run time > package for an example. > > If you never execute a free your dynamic memory is essentially contiguous. > > Joe Wilson wrote: > > --- John Stanton <[EMAIL PROTECTED]> wrote: > > > >>Malloc is a concept implemented in various ways, some more successful > >>than others but all of them hidden from the programmer. Free tries to > >>give back memory but as you can appreciate unless you use some garbage > >>collection scheme with backwards pointers fragmentation and > >>checkerboarding is very difficult to avoid. > > > > > > You seem to be confusing the topics of malloc/free memory fragmentation > > with C garbage collection - they are orthogonal concepts. > > > > C's free() has no power to free up any extra memory as active memory > > must stay where it was allocated until freed or else you'll have > > dangling pointers. A single call to free() typically releases only memory > > allocated by a single malloc() call. If, as result of the free, there > > happens to be block(s) of memory immediately before or after that > > released memory then that entire memory region can be coalesced and > > be made available as a larger block to future mallocs. > > > > If a C program employs perfect 1:1 malloc'ing to free'ing, i.e., has no > > memory leaks, then garbage collection is irrelevant to the topic of > > memory fragmentation. It's not like C can employ a copying garbage > > collector that moves memory blocks after free() without the knowledge > > or participation of the host program. The malloc() call is where > > fragmentation happens. Fragmentation in malloc depends on your allocation > > strategy: first-fit, best-fit, short-lived versus long-lived pools, > > per-allocation-size pools, statistical prediction, etc. Malloc must > > try to guess where an allocation must go to try to prevent future > > memory fragmentation. Never miss a thing. Make Yahoo your home page. http://www.yahoo.com/r/hs - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Where is sqlite3.h?
The sqlite3.h file is in sqlite-source-3_5_2.zip. That zip can be found in http://www.sqlite.org/download.html Good luck! I wanted to build a C app, copied the example C code from the Documentation on the web site, and downloaded the prebuilt binaries. There's no header in with the dll and def (for crummy windows). I tried building the source with MSYS/MinGW, but it didn't work out of the box. Any hints? - To unsubscribe, send email to [EMAIL PROTECTED] -
Re: [sqlite] Data encryption
hi everybody, the magic spell worked, but onky for me, not for my server: at the moment i have problems with my (free) dyndns account. my server (a little nslu2 in my living room) was online for some hours yesterday but disappeared again this morning ... until i solved my problem here are the temporary download links: http://www.greschenz.de/sqlite_crypt.zip http://www.greschenz.de/sqlite_compress.zip please inform me if its working or not. cu, gg To me it was a wonderful source of information and ideas, but this morning the URL was unable. May be if you recite certain magic spell, Günter can appear again... Cheers A.J.Millan - To unsubscribe, send email to [EMAIL PROTECTED] -
