Jump to content
Sign in to follow this  
Champak

Sqlite or txt is better for this particular function

Recommended Posts

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.

Share this post


Link to post
Share on other sites
JLogan3o13

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


√-1 2^3 ∑ π, and it was delicious!

Share this post


Link to post
Share on other sites
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.


If I posted any code, assume that code was written using the latest release version unless stated otherwise. Also, if it doesn't work on XP I can't help with that because I don't have access to XP, and I'm not going to.
Give a programmer the correct code and he can do his work for a day. Teach a programmer to debug and he can do his work for a lifetime - by Chirag Gude
How to ask questions the smart way!

I hereby grant any person the right to use any code I post, that I am the original author of, on the autoitscript.com forums, unless I've specifically stated otherwise in the code or the thread post. If you do use my code all I ask, as a courtesy, is to make note of where you got it from.

Back up and restore Windows user files _Array.au3 - Modified array functions that include support for 2D arrays.  -  ColorChooser - An add-on for SciTE that pops up a color dialog so you can select and paste a color code into a script.  -  Customizable Splashscreen GUI w/Progress Bar - Create a custom "splash screen" GUI with a progress bar and custom label.  -  _FileGetProperty - Retrieve the properties of a file  -  SciTE Toolbar - A toolbar demo for use with the SciTE editor  -  GUIRegisterMsg demo - Demo script to show how to use the Windows messages to interact with controls and your GUI.  -   Latin Square password generator

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
kylomas

For any real advice, define this...

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

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
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.


I am just a hobby programmer, and nothing great to publish right now.

Share this post


Link to post
Share on other sites
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])

Share this post


Link to post
Share on other sites
kylomas

Champak,

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

kylomas


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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 by kylomas

Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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. 


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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


Forum Rules         Procedure for posting code

"I like pigs.  Dogs look up to us.  Cats look down on us.  Pigs treat us as equals."

- Sir Winston Churchill

Share this post


Link to post
Share on other sites
Champak

No problem, thanks.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
Sign in to follow this  

×