MealMaster: Decoding text file format to database

[Fangbeast]

Hey Fang you have so many recipes to sort out you have no time to cook.
I will keep an eye out for your post.

LOL!!

There is currently 3.59 gigbytes of uncompressed recipes in various formats (and climbing) andf I have no room in my dropbox account after family photos have filled it.

Maybe google drive?

P.S. I remember last there that there was a mass download of my recipes so many of these you might already have. The ones I am still collecting are Living CookBook (FDX format) recipes.

[electrochrisso]

There is currently 3.59 gigbytes of uncompressed recipes in various formats (and climbing) andf I have no room in my dropbox account after family photos have filled it.

Gee thats a lot of recipes Fang, you must have every recipe ever made, what would be the size when they are compressed.

[Fangbeast]

There is currently 3.59 gigbytes of uncompressed recipes in various formats (and climbing) andf I have no room in my dropbox account after family photos have filled it.

Gee thats a lot of recipes Fang, you must have every recipe ever made, what would be the size when they are compressed.

882,793kb when compresses with 7zip maximum compression

P.S> I want to find lots more, no matter what the format and share as a lot of recipe sites shamefully grab everyone else's and then charge you to access them and it stinks. I've been to a lot of recipe sites where the copies (and the mistakes) still show you where they came from!

[Fangbeast]

3.9 gigabytes of recipes in various formats and growing!

I need help to design a better recipe program because I really am at the end of my skill set here.

Don't care about nice interface, that can come later (much later) but I need help with the underlying SQLITE technology because after 100,000 recipes, it's a slow as a wet weasel.

Can anyone help me design this please? I don't know what I am doing any more.

Tried normalisation but can't handle it by myself and to avoid a million duplicates, that's the way it has to be. Along with a separate pictures table.

I'll do all the work, just need examples of better database design.

[Starwolf20]

hi FangBeast. i may help
is it possible to have a look at your code ??

[Fangbeast]

hi FangBeast. i may help
is it possible to have a look at your code ??

All my source code is usually available on my web site but here is a link to the current infestation with an installed database of only 10,000 records at it takes forever for me to upload.

Don't laugh at my code, it works for the family:):):) (Just for the family you see). If I can make it faster, I will rewrite the entire interface and make it better.

https://drive.google.com/file/d/0B_tK5w ... sp=sharing

[Starwolf20]

Hi Fangbeast, it seems that you're hitting the exact same pb as i with my mp3 player (who have to deal with 30000 mp3).
I assume that your slowiness pb is related to the display of the recipes list in the ListIconGadget.
If so, it is not really related to the SQLITE design but rather at the populating of the ListInconGadget; what you already know as you limit the display at 500 items.

Just to confirm that, i have put a timer in procedure FindRecipes() and measure the results :
the initial display show the 500 first recipes in 3600 ms (and the 26 "dessert" recipes in 220 ms).

after commenting the 2 two lines
;AddGadgetItem(#Gadget_RecipeMuncher_Recipes, -1, KillQuote(Title.s) + Chr(10) + Favourite.s + Chr(10) + Deleted.s + Chr(10) + Record.s, ImageID(ImageNumber.i))
;SetGadgetItemData(#Gadget_RecipeMuncher_Recipes, ItemCounter.i, Val(Deleted.s))
it shows nothing of course but do the loop of the 500 first recipes in 33 ms (and the 26 "dessert" recipes in 9 ms).
which is pretty clear.

The solution i found was to use a thread for the process (you did that too) and a "Virtual" ListIconGagget to speed up the populating (works only in windows as you notice also and discard).
The only way left to limit the display.

There is nothing really wrong with your actual DB design but here my optimisation advices anyway :
1) the index on recipes table is useless as it contains almost all the columns of the table. it should index few important columns with the right order.
As the most costly query rely on searching the TITLE column, you should make a index only on that one.

2) you could use some SQL PRAGMA to tweak the DB (http://www.sqlite.org/pragma.html#pragma_page_size)
PRAGMA journal_mode = OFF
PRAGMA page_size = bytes;

3) Speed up the FindRecipes() NextDatabaseRow loop by skipping the unused columns (keep only the 4 used title,favourite,deleted,record) et modify all the "Select *" by "Select title,favourite,deleted,record) accordingly.

Less easy (demand little more coding)
it should be efficient to split the recipes table to isolate the columns containing big text (mainly Ingredients & Instructions) as they slow down the reading of the table : SQLITE reads the table page after page and if a page contains more recipes, .............

NB : i use 2 freeware to view/modify/alter SQLITE DB : sqlitespy & sqliteman
Hope it helps.

[Fangbeast]

I assume that your slowiness pb is related to the display of the recipes list in the ListIconGadget.

Actually no. There is some code in the forum to turn off messaging in the ListIcon when you populate it (Tried it a while ago) but you can also LockWindowUpdate_() on the gadget to speed it up.

But when I got up to 150,000 records, I had to compromise by either limiting how many records were displaying during search, startup etc, or not showing anything in the list at all so I compromised at around 100 or so. But I found out that limiting the display to an arbitrary amount at startup was slow!!!

SORT BY Title ASC LIMIT 100

After startup, not so bad.

the initial display show the 500 first recipes in 3600 ms (and the 26 "dessert" recipes in 220 ms).

That's right, the initial display on startup was bad. Subsequent lists were not so bad.

The solution i found was to use a thread for the process (you did that too) and a "Virtual" ListIconGagget to speed up the populating (works only in windows as you notice also and discard).

Don't remember using a thread but it's been a while since I looked at it as I am not very familiar with them.

There is nothing really wrong with your actual DB design but here my optimisation advices anyway :1) the index on recipes table is useless as it contains almost all the columns of the table. it should index few important columns with the right order. As the most costly query rely on searching the TITLE column, you should make a index only on that one.

You are right of course. I just wanted to have the flexibility to search any field and mistakenly thought that indexing them all would help.

2) you could use some SQL PRAGMA to tweak the DB (http://www.sqlite.org/pragma.html#pragma_page_size)
PRAGMA journal_mode = OFF
PRAGMA page_size = bytes;

Thanks, I'll read up on those.

3) Speed up the FindRecipes() NextDatabaseRow loop by skipping the unused columns (keep only the 4 used title,favourite,deleted,record) et modify all the "Select *" by "Select title,favourite,deleted,record) accordingly.

I normally do that but made it easier for me to read if I kept all the statements that returned data looking the same as my old eyes make too many mistakes. But I generally do that with smaller programs.

it should be efficient to split the recipes table to isolate the columns containing big text (mainly Ingredients & Instructions) as they slow down the reading of the table : SQLITE reads the table page after page and if a page contains more recipes, .............

Yes, the process called 'normalisation'. There are three main types, 1nf, 2nf, 3nf but the process is CPU intensive and can result in huge joins. Complicated to update as well and I have no experience with it.

NB : i use 2 freeware to view/modify/alter SQLITE DB : sqlitespy & sqliteman
Hope it helps.

Seen them, I prefer SQLITEEXPERT personal, prefer it.

Now, are you any good at producing pretty interfaces? (I have a feeling I am pushing my luck here) :)

Thanks for your help and suggestions, much appreciated.

[Fangbeast]

I'm bored. Keep wanting to redo my recipe program but the interface bores me.

Can anyone who is less bored than me do a sketch of how something like this should look? (I'm not a graphic artist and everything I do is ugly)

Then I will rewrite the program again.

My addressbook and serial number keeper are okay.

[Fangbeast]

I am still looking for nice interface examples for a recipe manager. Does anyone have some suggestions and/or examples please?

Now that I've finished my other work, I really need to redo this ugly clunker of an interface.