Sqlite vs Metakit
Tony
and Metakit are good enough for my project. But is there any
performance test or comparison between them? I tried a little script to
test both of them, which looks like the following:
Sqlite:
while {[gets $fd line] >= 0} {
......
db1 eval {INSERT INTO t1 VALUES($i, $match, $x, $y)}
incr i
}
Metakit:
while {[gets $fd line] >= 0} {
......
mk::row append db.t1 no $i ch $match code1 $x code2 $y
incr i
}
Finally, I added 25236 records into these data files. I found Metakit
data file finished quickly, yet Sqlite data file was growing bigger and
bigger slowly after the script quit. Is Metakit better or I did
something wrong.
Thanks for any answers.
Tony
Torsten Reincke
> I think Sqlite and Metakit are good enough for my project.
> But is there any performance test or comparison between them?
It depends a bit on the version of sqlite you use. Since sqlite 3 there
is no significant difference in performance as far as I could see in my
tests.
> Is Metakit better or I did
> something wrong.
You cannot generally say which is better. I would base the decision on
what you need in terms of functionality. If you want sql and easy join
capabilities, you should go for sqlite, but if you only want storage of
large amounts of data and perhaps just simple and fast tabular access
then metakit might be the better choice. I have often started with
metakit first and then switched to sqlite in my projects, when I
realized I needed complex query capabilitites on the data. Note
however, there are extensions for metakit providing sql-like queries,
though slower then.
Torsten
Arjen Markus
flexible if you need to rearrange tables a lot - like combining columns
from different tables into one new table. It is also very suitable - by
design - for retrieving data in the order they went into the table
(quite useful for timeseries).
SQLite on the other hand is very suitable, as Torsten says, for
complicated queries. One thing to note is that inserting or updating
data into a SQLite database is best done via transactions: this means
that committing the data is concentrated in a few places, rather than
after each insertion/update. That makes quite a performance difference.
Regards,
Arjen
Torsten Reincke
> data into a SQLite database is best done via transactions:
> this means that committing the data is concentrated in a few
> places, rather than after each insertion/update.
> That makes quite a performance difference.
>
> Regards,
>
> Arjen
Oh yes, I forgot. Thanks Arjen for this hint. Tony, this will be much
faster:
Sqlite:
db eval begin
while {[gets $fd line] >= 0} {
......
db1 eval {INSERT INTO t1 VALUES($i, $match, $x, $y)}
incr i
}
db eval commit
Torsten
Ramon Ribó
Hello,
inserts in sqlite are quite fast into a transaction: from 20000 to
120000
insert per seconds (of course, depends on what you are inserting and on
what
computer).
But the nicest capability in sqlite are the selects (to query the
data). They
can be really, really fast, specially with an adequate use of indexes.
The thing to consider is that you need to learn a new language SQL.
But, as you
know, learning new things is never bad for the health.
Regards,
Ramon Ribó
En Fri, 28 Oct 2005 11:13:57 +0200, Torsten Reincke
<rei...@typoscriptics.de> escribió:
--
Compass Ing. y Sistemas Dr. Ramon Ribo
http://www.compassis.com ram...@compassis.com
c/ Tuset, 8 7-2 tel. +34 93 218 19 89
08006 Barcelona, Spain fax. +34 93 396 97 46
Tony
Thank you for so quick answers! Well, can I make a analogy that Sqlite
seems like Oracle (but lite and fast), and Metakit seems like DBase IV.
I have tried the way as Torsten showed. The result proves Sqlite can
perform as fast as Metakit. Thanks again!
Regards.
Tony
Tony
I know a little of Sql. I think it's not difficult to learn the new
syntax, but the difficulty is Sql operations needs skills for best
performance, isn't it?
Regards,
Tony.
Pa_McClamrock
> The thing to consider is that you need to learn a new language SQL.
> But, as you know, learning new things is never bad for the health.
Well, hardly ever--but wasting time, while trying and failing to learn
new things, can be bad for the health! Is there some good
documentation, tutorials, etc. (with SQLite or elsewhere) that makes it
possible to learn SQL very efficiently?
David McClamrock
David N. Welton
> The thing to consider is that you need to learn a new language SQL.
> But, as you
> know, learning new things is never bad for the health.
But once you learn SQL, you can use it for other databases as well...
say you decide you need a 'big' database - you can transfer most of your
knowledge to postgresql or oracle or whatever, modulo the peculiarities
of that implementation.
--
David N. Welton
- http://www.dedasys.com/davidw/
Linux, Open Source Consulting
- http://www.dedasys.com/
Torsten Reincke
> syntax, but the difficulty is Sql operations needs skills for best
> performance, isn't it?
There is a FAQ here:
http://web.utk.edu/~jplyon/sqlite/SQLite_optimization_FAQ.html
Although not written for Tcl, you will find the essentails there to
make your sqlite commands fast. Sometimes it's only the order of the
tables involved in a query, that make the difference ...
Torsten
Ramon Ribó
Tony,
Another advantage of sqlite is that it follows quite closely
the SQL standard. So, it should be very easy to get you introduced.
The main differences are that sqlite is more liberal in the column
type (you can insert a text in an integer column) than other databases.
Also, it does not support advanced features like foreign constraints
and so on.
In practice, most people consider the manifest typing as a good thing,
except in edge cases like sorting and integer-real confusion, and the
implemented SQL should be enough for most database users.
Related to the performance, you need to be an expert, capable of
understanding
the sqlite opcodes, only if you really need to take most of complex
queries.
For many uses scenarios, it is only necessary to add an index to the column
that you will use in the "where" or the "order by" clause.
Finally, there is an sqlite mailing list that is very useful for
understanding
the library in detail.
Ramon Ribó
--
Donal K. Fellows
> Sqlite:
> db eval begin
> while {[gets $fd line] >= 0} {
> ......
> db1 eval {INSERT INTO t1 VALUES($i, $match, $x, $y)}
> incr i
> }
> db eval commit
Even better is this:
db transaction {
while {[gets $fd line] >= 0} {
...
db eval {INSERT INTO t1 VALUES($i,$match,$x,$y)}
incr i
}
}
Why is that better? Simply because the code works without you needing to
understand whether you are currently in a transaction or pollute your
code with baroque [catch] statements everywhere. It's just more Tcl-ish!
Donal.