Sqlite or txt is better for this particular function

[Champak]

I'm trying to decide if I should go with txt file or sqlite for a particular function. It's a function that will be called every 30 seconds, 1 line/row with 5-6 columns/cells/things that will be analyzed and written back every time it's called. I'm mostly looking for speed, which what I've seen people here say text file would be better when it's only one row, however, because things are going to be analyzed and compared I've seen people say sqlite would be better. Which way is suggested by the info provided.

Thanks.

[JLogan3o13]

As you mention, there are arguments for each. Why not write them both out and decide for yourself?

[BrewManNH]

Neither, there's not nearly enough information provided.

Also, text files don't have "columns" or "cells" they have text, you might have to split the text to get what you're looking for.

[Champak]

I decided to skip writing and testing it hoping someone might have specific insight in this to prevent me wasting time on one or the other.

[kylomas]

For any real advice, define this...

1 line/row with 5-6 columns/cells/things that will be analyzed

[Myicq]

I have done some tests myself in relation to some reading / lookup of a predefined limited set. Even an in-memory SQLite was significantly slower than dealing with arrays. I am not an expert but for my own use I would say SQLite is good if you need to sort/ select subsets, and arrays are better if you need to read/write in fixed places, example: get value 5, write value 6 etc.

[Champak]

@kylomas, if you were looking in a database, it would be 1 row with 6 columns, if you were looking in a txt file it would be 1 line with six things delimitated by commas or a pipe. 

I did this little test, I don't know if it's the best thing because I keep getting wildly different values from 40ms to 800ms...maybe due to specific resources acting up at the time on my computer? But more often than not the SQLite comes back in less than half the time than the txt. Again, I don't know if this is the best way to test it so let me know.

#include <SQLite.au3>
#include <SQLite.dll.au3>
Local $hQuery, $sMsg
Global $aRow[1]



$hTimer = TimerInit()
$testfile = @ScriptDir & "\TestTimeWrite.txt"

FileOpen($testfile)
;
$ThisLine = FileReadLine($testfile)
FileWriteLine($testfile, "Just This One")
FileClose($testfile)

MsgBox(0,TimerDiff($hTimer), $ThisLine)


;==================================================================================


$hTimer = TimerInit()
_SQLite_Startup()
$UNIVERSALVAR1 = @ScriptDir & "\DrivingGasLog.db"
Local $GasDriving_Log = _SQLite_Open($UNIVERSALVAR1)


_SQLite_QuerySingleRow(-1, "SELECT speed FROM Average_Speed_Calculator;", $aRow) ; Select single row and single field !
_SQLite_Exec($GasDriving_Log, "UPDATE Average_Speed_Calculator SET Speed = 300")


_SQLite_Shutdown()


MsgBox(0,TimerDiff($hTimer), $aRow[0])

[kylomas]

Champak,

Please describe what you want to do in detail, especially, data elements, thier use, whether they are persistent or not, etc. 

kylomas

[Champak]

I don't know what you mean by persistent or not, but...

Column 1 will be date

Column 2 will be incremental increase

Column 3 will be speed

Column 4 will be distance

Column 5 will have calculations to do with column 4

Now, on each cycle..every 30 sec, the date will be compared with the current date, if different it will print the contents in a different database. If the date is the same, it will calculate the column 2 against column 3, and then write the new settings in column 2 and 3. And do the same thing with 4 and 5.

I know that sqlite can do calculations by itself so that is a good thing over using txt files, however, I wonder if the sqlite calculations would take just as much time as extracting the info from a txt file and doing the calculations in autoit. And if so, then it all comes down to read and write times.

Let me know if you need anything else.

[kylomas]

Surely, either method would be well within your time frame. 

This

if different it will print the contents in a different database.

implies that you are already using SQLITE so stick with that. 

I hope I'm understanding you correctly.

kylomas

edit:

I wonder if the sqlite calculations would take just as much time as extracting the info from a txt file and doing the calculations in autoit

 

I suspect that the calcs/DB update would be considerably quicker.

Edited March 6, 2014 by kylomas

[Champak]

Right now my script consists of txt files and sqlite. I've transferred over two particular things to sqlite that I knew for sure would benefit from it. I'm looking into other things. This particular portion right now is txt file and I'm wondering if it would be better off as sqlite, mostly from my speed needs.

[kylomas]

Champak,

Given what you presented, yes, it would be better off as SQLite (assuming that you are not starting/stopping SQLite for each iteration).

Side note - if you are revisting application design you might want to spend some time looking at the data as a whole and not just mimicing file ops in
SQLite. 

[Champak]

No, I'm starting sqlite at the start of the app, and shutting it down upon exit.

What do you mean by this

Side note - if you are revisting application design you might want to spend some time looking at the data as a whole and not just mimicing file ops in

SQLite.

[kylomas]

You mentioned another DB/table, that's all.  I'm just advising that you look at your data as a whole and not as discreet pieces.  I can't get anymore specific than that because I don't know enough about your data.  Even if I did, it's not likely that my practical DB experience is any better than yours.

Good Luck,

kylomas

[Champak]

No problem, thanks.