How to speed up create index on temp database?

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

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 10000 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
Reply | Threaded
Open this post in threaded view
|

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

Jay Sprenkle
On 11/10/05, Huang Tao <[hidden email]> 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 10000 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?
Reply | Threaded
Open this post in threaded view
|

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

roger-15
In reply to this post by Huang Tao



>
> 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



Reply | Threaded
Open this post in threaded view
|

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

Huang Tao
In reply to this post by Jay Sprenkle
Jay Sprenkle wrote:

>On 11/10/05, Huang Tao <[hidden email]> 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 10000 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.