[sqlite] sqlite cannot find the row

2010-09-25 Thread Hemant Shah
Folks I have a C program that creates in-memory database with unique column. My code basically inserts row in database and if it gets duplicate row it selects the duplicate row from database and prints information. The problem is that when I select the row it says row not found. I simplified th

Re: [sqlite] Slow insertion for Unique Text Column

2010-09-25 Thread EQRecovery
--- On Sat, 9/25/10, Kees Nuyt wrote: > * Use large transactions (~ 50,000 is a good start for an > experiment) > * Offer the data to sqlite sorted by that unique column > * Enlarge cache_size. > * Enlarge page_size. > * pragma journal mode off. > * pragma synchronous off. > * Buy a faster disk. F

Re: [sqlite] Massive performance regression in 3.7.x

2010-09-25 Thread Richard Hipp
On Sat, Sep 25, 2010 at 3:05 PM, Max Vlasov wrote: > > Thanks, Richard, this is a perfect explanation and now it makes sense to > me. > > As for left joins, I suppose that the optimizer choses other approach for > them since it has a bias in estimating the sizes of tables when the left > join was

Re: [sqlite] Massive performance regression in 3.7.x

2010-09-25 Thread Max Vlasov
On Sat, Sep 25, 2010 at 10:34 PM, Richard Hipp wrote: > On Sat, Sep 25, 2010 at 10:54 AM, Max Vlasov wrote: > > > On Sat, Sep 25, 2010 at 6:35 PM, Richard Hipp wrote: > > > > > > > > You have a very unusual data distribution in your tables. SQLite does > > not > > > know this and so it chooses

Re: [sqlite] Massive performance regression in 3.7.x

2010-09-25 Thread Richard Hipp
On Sat, Sep 25, 2010 at 10:54 AM, Max Vlasov wrote: > On Sat, Sep 25, 2010 at 6:35 PM, Richard Hipp wrote: > > > > > You have a very unusual data distribution in your tables. SQLite does > not > > know this and so it chooses a query plan (in 3.7.1 and later) that > assumes > > a > > different a

Re: [sqlite] Slow insertion for Unique Text Column

2010-09-25 Thread Kees Nuyt
On Sat, 25 Sep 2010 10:35:18 -0700 (PDT), EQRecovery wrote: > I was pulling out my hair trying to figure out why > the insertion into a table was becoming extremely > slow as a table grew. > > I was creating a new table and populating it with > 100,000 rows of data (as a test case; I really > wan

[sqlite] Slow insertion for Unique Text Column

2010-09-25 Thread EQRecovery
I was pulling out my hair trying to figure out why the insertion into a table was becoming extremely slow as a table grew. I was creating a new table and populating it with 100,000 rows of data (as a test case; I really wanted to populate it with over a million rows). [Insertion A] When a Text

Re: [sqlite] Need help with self-join (I think)

2010-09-25 Thread Oliver Peters
Igor Tandetnik writes: [..] > > See how you like this one: > > select * > from (select distinct year from Test) as AllYears > join (select distinct name from Test) as AllNames > left join Test t on (t.year=AllYears.Year and t.name=AllNames.name) > left join Test2 t2 on (t.TestId

Re: [sqlite] Massive performance regression in 3.7.x

2010-09-25 Thread Max Vlasov
On Sat, Sep 25, 2010 at 6:35 PM, Richard Hipp wrote: > > You have a very unusual data distribution in your tables. SQLite does not > know this and so it chooses a query plan (in 3.7.1 and later) that assumes > a > different and more typical data distribution. The new query plan works > better f

Re: [sqlite] Massive performance regression in 3.7.x

2010-09-25 Thread Richard Hipp
On Fri, Sep 24, 2010 at 8:03 PM, Charles Samuels wrote: > > Greetings, > > With the sqlite database here: > http://www.derkarl.org/~charles/massive_performance_regression.bz2 > > There is a massive performance regression between

Re: [sqlite] sqlite.def file needed!

2010-09-25 Thread Igor Tandetnik
Rishi wrote: > I am trying to install Ruby on Rails on windows and am unable to do so > because the .def file is missing from sqlitedll-3_7_2.zip. It's happily present in the copy I've just downloaded from http://www.sqlite.org/sqlitedll-3_7_2.zip -- Igor Tandetnik

Re: [sqlite] Need help with self-join (I think)

2010-09-25 Thread Igor Tandetnik
Kristoffer Danielsson wrote: >> From: oliver@web.de >> a little mistake - here's the correction: >> >> SELECT DISTINCT t.Year, b.Name, >> (SELECT SomeValue FROM Test INNER JOIN Test2 WHERE Year = t.Year AND Name = >> b.Name) AS SomeValue >> FROM Test t >> CROSS JOIN (SELECT DISTINCT Name FROM

Re: [sqlite] FTS3 MATCH syntax

2010-09-25 Thread GHCS Software
Your suggestion seems to work fairly well. It might have some unexpected results, but I'll definitely give it a try. Thanks! From: Sam Roberts FTS3 tokenizes strings on whitespace (and other chars), so I think the best you can do would be something like given:john given:q. On Fri, Sep 24, 20

Re: [sqlite] EXTERNAL: Massive performance regression in 3.7.x

2010-09-25 Thread Black, Michael (IS)
Confirmed 3.6.23.1 sqlite> select count(*) from object_formats join page_table on page_table.version=3 and page_table.objectid = object_formats.objectid ; 12366 CPU Time: user 0.019997 sys 0.003000 3.7.0 CPU Time: user 32.269095 sys 0.019997 3.7.2 CPU Time: user 33.606891 sys 0.001000 Fossil r

Re: [sqlite] The database disk image is malformed on windows mobile system.

2010-09-25 Thread Simon Slavin
On 25 Sep 2010, at 7:14am, Zhonghuifeng163 wrote: > At 2010-09-25，"Simon Slavin" wrote: > >> On 25 Sep 2010, at 5:17am, Zhonghuifeng163 wrote: >> >>> At 2010-09-25，"Simon Slavin" wrote: >>> On 25 Sep 2010, at 3:43am, Zhonghuifeng163 wrote: > I use sqlite 3.6.20 on windows mobi

[sqlite] Massive performance regression in 3.7.x

2010-09-25 Thread Charles Samuels
Greetings, With the sqlite database here: http://www.derkarl.org/~charles/massive_performance_regression.bz2 There is a massive performance regression between 3.7.1 and 3.7.2, when compared to 3.6.23.1. The following query runs orders of magnitude slower than it did in the previous version:

[sqlite] sqlite.def file needed!

2010-09-25 Thread Rishi
Hello I am trying to install Ruby on Rails on windows and am unable to do so because the .def file is missing from sqlitedll-3_7_2.zip. Can someone please let me know how to get it? I get this error message :“The procedure entry point sqlite3_column_database_name could not be located in the dyna

Re: [sqlite] Query to compare two sqlite databases

2010-09-25 Thread John Reed
Hello, The following query works for me. select * from db2.table where not exists (select * from db1.table where db1.table.column1 = db2.table.column1); Thanks again for the assistance. --- On Fri, 9/24/10, Oliver Peters wrote: From: Oliver Peters Subject: Re: [sqlite] Query to compare t

Re: [sqlite] Need help with self-join (I think)

2010-09-25 Thread Kristoffer Danielsson
Well, let me explain :) My problem originates from a chart generator which needs all data to be explicitly set - including "none" values. Think of this simple example: 2001|B|123 2002|C|234 How would you GROUP and COMPARE A and B in a bar chart? Two columns in each category... 2001: B