I read about optimization for page_size, and I'll try to use different
size to check if I got speed up with page size 4096 instead of 1024.
Anyway, did you make some tests about this speed up? which operation are
faster with 4096 page size: Select, insert or delete?
Il 22/09/2010 20.19, Max
I have an SQLite DB of about 9GB with about 2.500.000 records.
I can't understand why the select COUNT(*) from log statement is
extremely slow, it takes me about 9-10 minutes!
I try with:
select COUNT(1) from logs
select COUNT(DateTime) from logs
same result. Have you idea of why it's so slow?
On Sep 24, 2010, at 3:13 PM, Michele Pradella wrote:
I have an SQLite DB of about 9GB with about 2.500.000 records.
I can't understand why the select COUNT(*) from log statement is
extremely slow, it takes me about 9-10 minutes!
In SQLite, count() is obliged to traverse the entire table
Hello Michele,
sqlite does not remember the number of records in a table. Therefore,
counting them requires to scan the full table, which explains the slow
perfornamce.
This topic has been discussed previously in this list. See
Quoth Michele Pradella michele.prade...@selea.com, on 2010-09-24 10:13:59
+0200:
I have an SQLite DB of about 9GB with about 2.500.000 records.
I can't understand why the select COUNT(*) from log statement is
extremely slow, it takes me about 9-10 minutes!
I try with:
select COUNT(1) from
ok, thank you for the advices, I'll try to use a TRIGGER.
The DB already has an index.
Anyway if I have to count something like this:
select COUNT(*) from logs WHERE DateTime=yesterday
I can't do it with a TRIGGER
Il 24/09/2010 10.29, Martin Engelschalk ha scritto:
Hello Michele,
sqlite
Am 24.09.2010 10:38, schrieb Michele Pradella:
ok, thank you for the advices, I'll try to use a TRIGGER.
The DB already has an index.
Anyway if I have to count something like this:
select COUNT(*) from logs WHERE DateTime=yesterday
I can't do it with a TRIGGER
No, but in this case an
I can get a big speed up of COUNT if I first do a VIEW of what I have
to count and than make select COUNT on the view.
Without VIEW: 9 Minutes
With VIEW: 8 Seconds!
Il 24/09/2010 10.58, Martin Engelschalk ha scritto:
Am 24.09.2010 10:38, schrieb Michele Pradella:
ok, thank you for the
Hi,
I require some help in getting the fix for some memory corruption issues,
occurring while using the sqlite3 library. It would be very helpful, if you can
guide to fix the below issues or atleast provide some info on this, to minimize
the memory corruption, occurring mostly while
Hi,
I require some help in getting the fix for some memory corruption issues,
occurring while using the sqlite3 library. It would be very helpful, if you can
guide to fix the below issues or atleast provide some info on this, to minimize
the memory corruption, occurring mostly while
What's the chance that 2 people with the same surname would have
the same problem in the same week...
I believe I just solved the same problem you are asking about yesterday
thanks to a query from Richard:
CREATE TABLE Message (message_id INTEGER PRIMARY KEY AUTOINCREMENT,
Subject TEXT);
On Friday, September 24, 2010 1:03 PM, Josh Gibbs wrote:
What's the chance that 2 people with the same surname would have
the same problem in the same week...
[ ... ]
I can only think its something in the name!
I did actually see your thread moments after creating my own. I'd done a
This is an auto-replied message. I am currently unavailable. I will be back at
work on Monday 27th September. For urgent questions please contact Michael Brey
(michael.b...@oracle.com)
___
sqlite-users mailing list
sqlite-users@sqlite.org
Consider this SQLite session:
sqlite create table A (id integer primary key);
sqlite create table B (id integer primary key, name text);
sqlite select * from A as X order by (select name from B where B.id = A.id);
Error: no such column: A.id
sqlite select * from A as X order by (select name from
Quoth Igor Tandetnik itandet...@mvps.org, on 2010-09-24 08:16:07 -0400:
sqlite create table A (id integer primary key);
sqlite create table B (id integer primary key, name text);
sqlite select * from A as X order by (select name from B where B.id = A.id);
Error: no such column: A.id
sqlite
Apparently you are calling sqlite3_free twice on the same statement
pointer. Try to add assigning to NULL after freeing and checking for
NULL before freeing.
Pavel
On Thu, Sep 23, 2010 at 10:57 AM, Borra, Kishore Babu
kishorebabu.bo...@adc.com wrote:
Hi,
I require some help in getting the
On Sep 23, 2010, at 9:57 PM, Borra, Kishore Babu wrote:
Hi,
I require some help in getting the fix for some memory corruption
issues, occurring while using the sqlite3 library. It would be very
helpful, if you can guide to fix the below issues or atleast provide
some info on this, to
On Fri, Sep 24, 2010 at 9:50 AM, Pavel Ivanov paiva...@gmail.com wrote:
Apparently you are calling sqlite3_free twice on the same statement
pointer. Try to add assigning to NULL after freeing and checking for
NULL before freeing.
The second step (checking for NULL before freeing) is
Hello,
I compare an application every few days which has sqlite as it's client
database.
I look at the content and check whether documents have made it into the
application after it has been built. I also check the metadata in the sqlite
client database for changes.
So, I am constantly
On Fri, 24 Sep 2010, John Reed wrote:
I compare an application every few days which has sqlite as it's client
database. I look at the content and check whether documents have made it
into the application after it has been built. I also check the metadata in
the sqlite client database for
On 24-09-10 21:06, Rich Shepard wrote:
On Fri, 24 Sep 2010, John Reed wrote:
I compare an application every few days which has sqlite as it's client
database. I look at the content and check whether documents have made it
into the application after it has been built. I also check the
On Fri, 24 Sep 2010, luuk34 wrote:
you mean something like:
select id1, id2 from callprog a where id1 not in (select b.id1 FROM
callprog b where b.id1=a.id1 );
Yeah; much better.
But what is there is more than 1 column? it will grow in complexity when
you have a lot of columns.
Hello,
Thanks for your response.
I'm almost there but I keep getting sql syntax error (near col1) with the last
part of the query
where(not exists in db2);
Here's where I'm at:
select 'db1', db1.table1.col1, 'db2', db2.table2.col1 from db1.table1,
db2.table2 WHERE (NOT EXISTS col1 IN db2);
Am 24.09.2010 20:41, schrieb John Reed:
Hello,
I compare an application every few days which has sqlite as it's client
database.
I look at the content and check whether documents have made it into the
application after it has been built. I also check the metadata in the sqlite
client
Oliver Peters oliver@... writes:
sry
the result is what is not in table01
I meant:
the result is what is in table01 but NOT in table02
Oliver
___
sqlite-users mailing list
sqlite-users@sqlite.org
Hi,
I have an interesting SQL problem where I want certain rows to always be
represented. It feels like a self-join, but I'm not sure. Please help!
Create a database as follows:
CREATE TABLE Test (TestID
I'm just started with generating a table using FTS3 and understand
most of the query formats, but am stuck on one minor point of syntax. My
FTS3 query will make reference to a specific table, e.g.:
MATCH 'surname:smith'
The problem is that I can't figure out how to specify it if the
On Sat, Sep 25, 2010 at 01:01:36AM +0200, Kristoffer Danielsson wrote:
CREATE TABLE Test (TestID INTEGER PRIMARY KEY, Year INT NOT NULL, Name TEXT
NOT NULL);
INSERT INTO Test (Year, Name) VALUES (2007, 'A');
INSERT INTO Test (Year, Name) VALUES (2007, 'B');
INSERT INTO Test (Year, Name)
Hi,
Thanks for your reply. You're right, it's an outer-join I'm looking for.
Unfortunately, your suggestion does not do the trick.
You didn't have a Test1 row for (2007, 'C'), so why would you get 2007|C|NULL?
Because, given a certain algorithm, generating statistics will become a lot
Kristoffer Danielsson kristoffer.daniels...@... writes:
SELECT DISTINCT t.Year, b.Name,
CASE
WHEN (SELECT TestID FROM Test WHERE Year = t.Year AND Name = b.Name) IS NULL
THEN NULL
ELSE (SELECT SomeValue FROM Test2)
END AS SomeValue
FROM Test t
CROSS JOIN (SELECT DISTINCT Name FROM Test) b
;
Neat! Many thanks :)
Putting this logic together with my original query will be an interesting
challenge, hehe. Do you believe this is the best solution?
Chris
To: sqlite-users@sqlite.org
From: oliver@web.de
Date: Fri, 24 Sep 2010 23:47:59 +
Subject: Re: [sqlite] Need help
sry,
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 Test) b
;
(results don't differ - in this case!)
Oliver
Ah, this one's easier to follow. What do you mean by in this case? What was
the condition that made it possible to remove the rest?
Again, thanks!
To: sqlite-users@sqlite.org
From: oliver@web.de
Date: Sat, 25 Sep 2010 00:05:11 +
Subject: Re: [sqlite] Need help with self-join (I
Kristoffer Danielsson kristoffer.daniels...@... writes:
Ah, this one's easier to follow. What do you mean by in this case? What was
the condition that made it
the point was not to remove something but to take into consideration that there
has to be a JOIN on the TestIDs (in this case
Yeah. USING is good to have :)
And yes, 123 might very well be a random number. I should have made that clear!
To: sqlite-users@sqlite.org
From: oliver@web.de
Date: Sat, 25 Sep 2010 00:19:44 +
Subject: Re: [sqlite] Need help with self-join (I think)
Kristoffer Danielsson
Am 25.09.2010 01:47, schrieb Kristoffer Danielsson:
[...]
Because, given a certain algorithm, generating statistics will become a lot
easier if each value combination is represented in the returned row set.
really? NULL means there are no values present or there are unknown
values -
I'm not an authority, but I've been using FTS3.
FTS3 tokenizes strings on whitespace (and other chars), so I think the
best you can do would be something
like given:john given:q.
It doesn't work really well out of the box for substring matching.
On Fri, Sep 24, 2010 at 4:16 PM, GHCS Software
On Fri, Sep 24, 2010 at 7:16 PM, GHCS Software g...@ghcssoftware.comwrote:
I'm just started with generating a table using FTS3 and understand
most of the query formats, but am stuck on one minor point of syntax. My
FTS3 query will make reference to a specific table, e.g.:
MATCH
I use sqlite 3.6.20 on windows mobile system,but the sqlite often return error
message The database disk image is malformed, then the sqlite database file
can not be used! I also use sqlite 3.6.20 on other operate system on mobile
phone, and do not have this problem.
On 25 Sep 2010, at 3:43am, Zhonghuifeng163 wrote:
I use sqlite 3.6.20 on windows mobile system,but the sqlite often return
error message The database disk image is malformed, then the sqlite
database file can not be used! I also use sqlite 3.6.20 on other operate
system on mobile phone,
At 2010-09-25,Simon Slavin slav...@bigfraud.org wrote:
On 25 Sep 2010, at 3:43am, Zhonghuifeng163 wrote:
I use sqlite 3.6.20 on windows mobile system,but the sqlite often return
error message The database disk image is malformed, then the sqlite
database file can not be used! I also
41 matches
Mail list logo