Hello,
I have recently come to a very intersting problem on
SQLITE. We have SQLITE V2.8.5 on the embedded system.
I have two databases with the same schema. The
databases have 5 tables which have references to each
other. For eg. an id referred in one table is also
used in another table and so on. I need to show the
consolidated result of the queries to the user on the
LCD.
I thought of following approach:
Approcah1:
-----------
1) I open first database (DB) using
sqlite_Open()
2) I can Attach second database to the first
one using "ATTACH secDB
as SecondDB"
3) I can perform queries using UNION because
the tables can be
addressed using dot commands like FirstDB.songTable or
secondDB.songTable.
But when I did measurements, the queries take a
lot of time on UNION. For eg: if a query takes t1 on
FirstDB and t2 on SecondDB to get the same
result in an order, I expected slightly more than
(t1+t2). It is too high.
Approach2:
----------
1) I can copy the FirstDB to RAM
2) Then I can use attach command for secondDB
3) I can insert all the tables from SecondDB to
the FirstDB
4) Then the query will have to be only on
FirstDB for the same result.
This increases the startup time since the copying of
tables are to be done
once.
Approach3:
----------
Instead of using queries to copy tables as in
Approach3, can I combine the Databases at the binary
level? Has anyone tried this out??
I do not understand why Approach2 takes so much of
time even though both the DBs have same database
schema. Can something be changed in SQLITE to make it
faster?
Can some one throw more light on Approach3 and also
give ideas on how we could optimise Approach1 and
Approach2??
Regards,
- Rangan.
__________________________________
Do you Yahoo!?
Yahoo! Mail Address AutoComplete - You start. We finish.
http://promotions.yahoo.com/new_mail
