Re: [sqlite] How to speed up create index on temp database?

[黄涛]

Jay Sprenkle wrote:


On 11/10/05, Huang Tao <[EMAIL PROTECTED]> wrote:
 


Hello:

I run sqlite in embedded system which use nand flash. So I have to
reduce write count. Save index in master database will cause much
write. I try to dynamic create index on temp database. But the speed
is not very well.
example:
create table employee (id int primary key not null, name);
insert 1 record to table;
create index temp.idx_name on employee (name);
create index will use 24.519761 sec

with gprof, I see most time was used in sqlite3BtreeInsert, which
called 10001 times.
Anyone can give me suggestion to improve the speed?
   



Can you move your temp tables to in an in-memory(RAM) database?
 


You may not understand my question, the _table_ must be in NAND Flash.
and the most time is spend in create index.
In my test case, all table and index are in RAM(table in tmpfs, which is 
ram base file system in Linux, index in temp database, which is ram base 
in SQLite)
To create a big btree, may be just need so much time, but I hope I can 
speed it up. I believe I can, but I need some hint.

RE: [sqlite] How to speed up create index on temp database?

[roger]

> 
> Can you move your temp tables to in an in-memory(RAM) database?

In the package I posted earlier (the uSQLiteServer), the example
database supplied with the system shows the use of memory tables used
in conjunction with disk based tables, and how you can set up the
memory tables from data in the disk database(you can also use similar
techniques to update the disks from the memory.

Note that when using the uSQLiteServer all users see the memory based
tables, which is not the case in a normal 'shared' SQLite scenario.
However, the techniques are the same if you have a single user (of
course if you had distributed users you could use uSQLite server ;-) )

See http://users.iol.it/irwin

Re: [sqlite] How to speed up create index on temp database?

[Jay Sprenkle]

On 11/10/05, Huang Tao <[EMAIL PROTECTED]> wrote:
> Hello:
>
> I run sqlite in embedded system which use nand flash. So I have to
> reduce write count. Save index in master database will cause much
> write. I try to dynamic create index on temp database. But the speed
> is not very well.
> example:
> create table employee (id int primary key not null, name);
> insert 1 record to table;
> create index temp.idx_name on employee (name);
> create index will use 24.519761 sec
>
> with gprof, I see most time was used in sqlite3BtreeInsert, which
> called 10001 times.
> Anyone can give me suggestion to improve the speed?

Can you move your temp tables to in an in-memory(RAM) database?

[sqlite] How to speed up create index on temp database?

[Huang Tao]

Hello:

I run sqlite in embedded system which use nand flash. So I have to
reduce write count. Save index in master database will cause much
write. I try to dynamic create index on temp database. But the speed
is not very well.
example:
create table employee (id int primary key not null, name);
insert 1 record to table;
create index temp.idx_name on employee (name);
create index will use 24.519761 sec

with gprof, I see most time was used in sqlite3BtreeInsert, which
called 10001 times.
Anyone can give me suggestion to improve the speed?

CPU: 96MHz mips32
Memory: 16M
File System:  tmpfs (so all run in memory)
SQLite: 3.2.7