So yes, another step back in the Krecipes usability. SQLite is nice, easy to install, easy to code... but in two (or 3) words: "it's crap". Slower than a cow trying to climb up a ladder.
It's convenient when the lists are small and no strange queries are done, but give it a bit of search work, and it's useless. MySQL could do a query over 3000 recipes in 0.12s and took SQLite 1 minute to complete the same thing.... I can't say that's useful.
Somebody will say that having 3000 recipes is not usual... Well it is by other recipe tool users. Those 3000 recipes were imported just from a single file.
I'm now thinking of using embedded MySQL, but hey, distros like Mdk forget about this tiny library that should be included by default. Why???
If I can't solve this stuff... well, krecipes has its days counted. Well, at least for moms that do not know about passwords and usernames.
Syndicate Blogs
Valentina database
You can look on Valentina Database.
http://www.paradigmasoft.com.
Note Testimonials page: http://paradigmasoft.com/en/testimonials
People say very impressive things about its speed
comparing to MySQL, Access, SqlLite...
joins
Sql can join 2 tables in 2 ways:
1) select bla from a, b where a.id = b.id and ....
2) select bla from a, (select id from b where seq=1) b where a.id = b.id and...
The second one is faster when the 2 tables get bigger.
This is because in the first case the 2 whole tables are loaded into memory and then joined, after which the irrelevant rows are removed.
The second first removed lots of rows from table B before it joins. Hence a smaller amount of data to move around.
DBs like oracle (and probably MySQL) will make that optimization step in their preprocess step. I'm not sure sqllite will.
You might want to try and see if that makes a difference.
Thanks!
I'll check that.
This comment is obsolete now :)
Well, I fixed the stuff at last. Some indexing here, less JOIN's and reducing the number of queries helped to have a SQLite system almost as fast as MySQL's one.
The time lag of reloading 3000 recipes and clasifying into categories doesn't let you time to blink the eye (maybe half a second?), including the redisplaying on KListView. I think that's pretty acceptable.
Still, I feel that the MySQL code is faster in this case. Try it yourselves. Download Krecipes, and see both SQLite and MySQL behaviour. Both are running the same code and in the same conditions, except for API interfaces that are different.
Oh, obviously, import quite a few recipes to test speed
SQLite = very fast
You must be doing something wrong, since i use sqlite all day long.. with databases of 10's of thousands of records.. It always takes a few milliseconds to fetch the results.. Even without transactions..
SQLite, can put samples
I have a sample database that I can send you if you want. And I can't be doing anything wrong when I try to do the same query by hand in the sqlite "client".
It's been tested and verified by other people too. The same structure as another MySQL database. It's quick when I have 1 or 2 results thanks to an index (otherwise it was very slow). But not when I have 2000 records as output.
Oh, and I wish I have done something wrong. I wouldn't be coding a workaround right now.
Send me the DB
ralsina - at - kde.org
I'll take a look in the weekend.
That's strange...
For example, try doing a transaction around your queries.
Also, it is kinda slow if what you need are fulltext searches. If that is the case, you have to be careful about indexing, and trade space (use more) by speed (faster).
Also, make sure you are not using the debug version.
In my experience sqlite is quite fast (I wrote qsqlite, qsqlite.sf.net, a Qt driver for sqlite).
For example, http://www.sqlite.org/speed.html
You may also consider just using Qt's database classes, and let the user choose the backend.
>For example, try doing a tra
>For example, try doing a transaction around your queries.
I'll look at that.
>Also, it is kinda slow if what you need are fulltext searches.
In this case it's just crosstable ID comparisons, and yes, I have almost no text searches except when completely necessary.
>You may also consider just using Qt’s database classes, and let the >user choose the backend.
I'm using those classes, but unfortunately they're not complete enough do do everything without going down to the direct DB calls.
I'm actually using qsqlite for SQLite (very nice interface indeed) and QSQl for MySQL.
Thanks for the suggestions