Re: [sqlite] how to speed up copy all the data in file db to memory db?
Thanks. I will try it. Do you have any idea for my question 2?? 2. How to release the unused page in the memory database? just like the vacuum in the file database. Thanks. On 12/24/06, Joe Wilson <[EMAIL PROTECTED]> wrote: This mailing list thread may be helpful: http://www.mail-archive.com/sqlite-users@sqlite.org/msg15902.html --- PY <[EMAIL PROTECTED]> wrote: > I am using a memory database in an embedded device with sqlite_3.3.5. > For performance consideration, all the database access are running in the > SDRAM. > I need to perform both CopyFileDBToMemDB and CopyMemDBToFileDB with high > performace and less memory used. > > My current solution is create schema then insert all data to the destination > table. But when I create the index after insert all the data, all the > CPU and Memory resource almost exhaust. > > > The problem is > 1. Could I perform a page by page copy to reach the synchronization purpose? > 2. How to release the unused page in the memory database? just like the > vacuum in the file database. > > >Would you please help to tell me how to improve that? > > Thanks for your great help. > > > Thanks, > VK > __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com - To unsubscribe, send email to [EMAIL PROTECTED] -
[sqlite] how to speed up copy all the data in file db to memory db?
Hi All, I am using a memory database in an embedded device with sqlite_3.3.5. For performance consideration, all the database access are running in the SDRAM. I need to perform both CopyFileDBToMemDB and CopyMemDBToFileDB with high performace and less memory used. My current solution is create schema then insert all data to the destination table. But when I create the index after insert all the data, all the CPU and Memory resource almost exhaust. The problem is 1. Could I perform a page by page copy to reach the synchronization purpose? 2. How to release the unused page in the memory database? just like the vacuum in the file database. Would you please help to tell me how to improve that? Thanks for your great help. Thanks, VK
Re: [sqlite] How to speed up the performance in LIMIT & OFFSET?
Dear Chandrashekar,
Sorry, I don't understand what is the precompiled query.
Would you please help me to descript that?
Thanks for your great help.
VK
On 9/19/06, Chandrashekar H S <[EMAIL PROTECTED]> wrote:
Hi
Are u using precompiled queries? If not try using it...
On 9/19/06, PY <[EMAIL PROTECTED]> wrote:
>
> Hi All,
> I have a problem about LIMIT & OFFSET profermance.
> Due to the limitation of memory, I could not get all of the query result
> at
> a time.
> In our soluction, we use the LIMIT and OFFSET to avoid the problem of
> memory
> issue.
>
> we observed the performance of LIMIT & OFFSET, it looks like a liner
grow
> of
> the response time. In our table, it only has 300~500 records.
>
>
>
> Here is the dummy script
> Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT);
> create index idx_foo on foo(x);
>
> insert into foo(x) values('text001');
> :
> :
> insert into foo(x) values('text300');
>
>
> Select distinct x from foo LIMIT 20 OFFSET 0; Select distinct x from foo
> LIMIT 20 OFFSET 20; Select distinct x from foo LIMIT 20 OFFSET 40;
Select
> distinct x from foo LIMIT 20 OFFSET 60;
> :
> :
> Select distinct x from foo LIMIT 20 OFFSET 280;
>
>
>
> Would you please help to tell me how to improve that?
>
> Thanks for your great help.
>
>
> Thanks,
> VK
>
>
Re: [sqlite] How to speed up the performance in LIMIT & OFFSET?
Hi all, Thanks for your suggestion. In fact, I use sqlite in a embedded device. So the memroy problem is really critical to us. For profermance improving, all of the database running in a in-memory database. For my case, is that will improve the query performance if I create the temp table of FOO? Furthermore, I need to sort the result, which following sql command has better performance? - Create Table tmp as select distinct x from foo; Select x from tmp where OID>0 and OID<=20 order by x; or - Create Table tmp as select distinct x from foo order by x; Select x from tmp where OID>0 and OID<=20; Thanks for your clearfy. VK.
[sqlite] Profermance of "Group BY" and "Distinct"
Hi all,
which one has the better performance between "GROUP BY" and "DISTINCT"?
I need to retrieve a distinct value of a specific column. Either "GROUP BY"
and "DISTINCT" could be finish that.
But I want to know which one has the better performance. Or I can use
another sql command to finish that with best performance.
Here is the dummy script
Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT);
create index idx_foo on foo(x);
insert into foo(x) values('text001');
:
:
insert into foo(x) values('text300');
SELECT Distinct x from foo;
or
SELECT x from foo group by x;
or
others.
Would you please help to tell me how to improve that?
Thanks for your great help.
Thanks,
VK
[sqlite] How to speed up the performance in LIMIT & OFFSET?
Hi All,
I have a problem about LIMIT & OFFSET profermance.
Due to the limitation of memory, I could not get all of the query result at
a time.
In our soluction, we use the LIMIT and OFFSET to avoid the problem of memory
issue.
we observed the performance of LIMIT & OFFSET, it looks like a liner grow of
the response time. In our table, it only has 300~500 records.
Here is the dummy script
Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT);
create index idx_foo on foo(x);
insert into foo(x) values('text001');
:
:
insert into foo(x) values('text300');
Select distinct x from foo LIMIT 20 OFFSET 0; Select distinct x from foo
LIMIT 20 OFFSET 20; Select distinct x from foo LIMIT 20 OFFSET 40; Select
distinct x from foo LIMIT 20 OFFSET 60;
:
:
Select distinct x from foo LIMIT 20 OFFSET 280;
Would you please help to tell me how to improve that?
Thanks for your great help.
Thanks,
VK
[sqlite] problem with sqlite3_exec method
Hello, I'm sorry but even after looking in the archives I haven't found any help on this subject : I'm testing a very simple C program that uses SQLite. The sqlite3_open method works fine and the database opens normaly, but then I get an error coming from the sqlite3_exec method : sqlite3_exec(db, "select * from tbl1" , callback, 0, ) The error is : SQL error: unsupported file format. The same SQL query works fine on the same database file with the command-line sqlite3 program. What am I missing here ? Thank you for your help...
[sqlite] unsupported file format
Hello, After compiling I'm testing the C example program provided in the quick start page, just to get into the C/C++ interface for SQLite. ./exprog testdb 'select * from tbl1' The database file opens normaly, but I get an error coming from the sqlite3_exec method : SQL error: unsupported file format. As if there was a problem with the table names or query. The same SQL query on the same database works fine with the command-line sqlite3 program. What am I misunderstanding ?
Re: [sqlite] How to realize the ROWID in a view?
Thanks for you reply.
I Just want to get a sequence number in a view, that is not the ID field in
the table foo.
Table foo is a sample of mine. In fact, my table is not only the id and x
field.
And the x field could be duplicate in the table foo.
Could you help me to finish that?
Thanks.
On 6/12/06, Christian Smith <[EMAIL PROTECTED]> wrote:
PY uttered:
> Hi All,
>
> I have a problem about the ROWID in a view. I want to simulate a ROWID
in a
> view just like the same purpose in a table.
>
> For Example:
>
> Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT);
> insert into foo(x) values('X');
> insert into foo(x) values('Y');
> insert into foo(x) values('X');
> insert into foo(x) values('Z');
>
> Create View v_foo AS
> SELECT distinct(x) FROM foo
> ORDER BY x desc;
>
>
> SELECT * from foo;
>
> id x
> -- --
> 1 X
> 2 Y
> 3 X
> 4 Z
>
>
> My expect result of "select * from v_foo;" is
>
> id x
> -- --
> 1 Z
> 2 Y
> 3 X
>
>
>
> Would you please help to tell me how to finish that?
> Thanks for your grest help.
Why is this your expected result? Distinct applies to the whole row,
which includes the id. The view to get id and x is:
Create View v_foo AS
select distinct id,x FROM foo
ORDER BY x desc;
This will give you:
sqlite> select * from v_foo;
4|Z
2|Y
3|X
1|X
Each row is indeed distinct.
As you view is defined, you don't get the id at all, and distinct does
indeed return a single instance of X, Y and Z. If you want the x to be
unique, make it the primary key or create a unique index on it:
Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT UNIQUE );
The insert of the second x=X row will now fail:
sqlite> insert into foo(x) values('X');
sqlite> insert into foo(x) values('Y');
sqlite> insert into foo(x) values('X');
SQL error: column x is not unique
sqlite> insert into foo(x) values('Z');
sqlite>
>
> Thanks,
> VK
>
Christian
--
/"\
\ /ASCII RIBBON CAMPAIGN - AGAINST HTML MAIL
X - AGAINST MS ATTACHMENTS
/ \
[sqlite] How to realize the ROWID in a view?
Hi All,
I have a problem about the ROWID in a view. I want to simulate a ROWID in a
view just like the same purpose in a table.
For Example:
Create Table foo(id INTEGER PRIMARY KEY AUTOINCREMENT, x TEXT);
insert into foo(x) values('X');
insert into foo(x) values('Y');
insert into foo(x) values('X');
insert into foo(x) values('Z');
Create View v_foo AS
SELECT distinct(x) FROM foo
ORDER BY x desc;
SELECT * from foo;
id x
-- --
1 X
2 Y
3 X
4 Z
My expect result of "select * from v_foo;" is
id x
-- --
1 Z
2 Y
3 X
Would you please help to tell me how to finish that?
Thanks for your grest help.
Thanks,
VK
Re: [sqlite] How to avoid the increment the id in sqlite_sequence table if the constraint occurs?
Thanks for your anwser.
On 5/19/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote:
PY <[EMAIL PROTECTED]> wrote:
> Hi All,
>
> I have a problem about the ROWID.
>
> I want to create a table with unique value, not only [id] but also
[name],
> and the table named [foo]
> CREATE TABLE foo (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT
UNIQUE);
>
> I don't want to select the table for avoiding the duplicate NAME
problem.
> So, I use the "IGNORE" when I insert into the table.
>
> INSERT OR IGNORE INTO foo (name) VALUES ('x');
> INSERT OR IGNORE INTO foo (name) VALUES ('x');
> INSERT OR IGNORE INTO foo (name) VALUES ('x');
> INSERT OR IGNORE INTO foo (name) VALUES ('x');
> INSERT OR IGNORE INTO foo (name) VALUES ('y');
>
> After that I found a phenomenon, when I select the table, the result
listed
> below.
>
> SELECT * FORM foo;
>
> id name
> -- --
> 1 x
> 5 y
>
>
>
> My expected value of y is 2 not 5.
>
>
> The squence of the id is important to us. How to resolve this problem?
> Thanks for your grest help.
>
Try using INSERT OR FAIL instead of of INSERT OR IGNORE.
--
D. Richard Hipp <[EMAIL PROTECTED]>
[sqlite] How to avoid the increment the id in sqlite_sequence table if the constraint occurs?
Hi All,
I have a problem about the ROWID.
I want to create a table with unique value, not only [id] but also [name],
and the table named [foo]
CREATE TABLE foo (id INTEGER PRIMARY KEY AUTOINCREMENT, name TEXT UNIQUE);
I don't want to select the table for avoiding the duplicate NAME problem.
So, I use the "IGNORE" when I insert into the table.
INSERT OR IGNORE INTO foo (name) VALUES ('x');
INSERT OR IGNORE INTO foo (name) VALUES ('x');
INSERT OR IGNORE INTO foo (name) VALUES ('x');
INSERT OR IGNORE INTO foo (name) VALUES ('x');
INSERT OR IGNORE INTO foo (name) VALUES ('y');
After that I found a phenomenon, when I select the table, the result listed
below.
SELECT * FORM foo;
id name
-- --
1 x
5 y
My expected value of y is 2 not 5.
The squence of the id is important to us. How to resolve this problem?
Thanks for your grest help.
Thanks,
VK
