Speed 'tweaking' AutoIt w/ SQLite

[TechCoder]

Continuation of topic started 

I have been testing various times and configurations after reading the tips given and things are a bit better, though now a new issue has come up - after installing the latest version of AutoIt (3.3.10.2) _SQLite_Startup() is taking a LOT of time to load.

Odd thing is, it is not consistent (I hate that....) - though times vary from 5 seconds (which is 'long', IMHO) to OVER 60 seconds!

ConsoleWrite("various states disabled/hidden " & Round(TimerDiff($timer) / 1000, 2) & ' sec' & @crlf)

    ; ************************************  read from / update the database
    FileInstall("sqlite3.dll", @ScriptDir & "", 1)
ConsoleWrite("SQL0 " & Round(TimerDiff($timer) / 1000, 2) & ' sec' & @crlf)
    _SQLite_Startup()
    If @error Then
        MsgBox(16, "SQLite Error", "SQLite3.dll Can't be Loaded!")
        Exit -1
    EndIf
ConsoleWrite("SQL1 " & Round(TimerDiff($timer) / 1000, 2) & ' sec' & @crlf)

Console output from one test (not 'typical', but certainly 'common' to be very long.....)

 

>"C:\Program Files (x86)\AutoIt3\SciTE\AutoIt3Wrapper\AutoIt3Wrapper.exe" /run /prod /ErrorStdOut /in "C:UsersSteveAll ProgramsKPT2.0consistency_tool.au3" /UserParams    

+>09:54:32 Starting AutoIt3Wrapper v.2.1.3.0 SciTE v.3.3.6.0 ;  Keyboard:00000409  OS:WIN_7/Service Pack 1  CPU:X64 OS:X64    Environment(Language:0409  Keyboard:00000409  OS:WIN_7/Service Pack 1  CPU:X64 OS:X64)

>Running AU3Check (3.3.10.2)  from:C:Program Files (x86)AutoIt3

+>09:54:32 AU3Check ended.rc:0

>Running:(3.3.10.2):C:Program Files (x86)AutoIt3autoit3.exe "C:UsersSteveAll ProgramsKPT2.0consistency_tool.au3"    

--> Press Ctrl+Alt+F5 to Restart or Ctrl+Break to Stop

setup done 0 sec

filemenu 0 sec

actionmenu 0 sec

helpmenu 0 sec

1st tab done 0.01 sec

2nd tab (parsing) done 0.06 sec

advanced tab done 0.09 sec

end of GUI 0.09 sec

default states set  0.26 sec

various states disabled/hidden 0.26 sec   

SQL0 0.26 sec      <<<====================  the only command here

SQL1 64.07 sec    <<<==================== is _SQLite_Startup()

SQL2 64.07 sec

SQL3 64.07 sec

disk based database open 64.07 sec

diskbased db DEFAULT set 64.11 sec

'new' settings complete 64.12 sec

ready for GUISetState 64.12 sec

GUISetState done 64.15 sec

+>09:55:41 AutoIt3.exe ended.rc:0

>Exit code: 0    Time: 69.868

 

 

Prior to loading 3.3.10.2 times were 'fast' (never noticed any lag enough to care to time it), though now when I start the program it is very noticably delayed.

I want to address various other tweaks and such as discussed by @jchd and certainly the differences and tweaks for working with disk based or memory based databases (in this project, I need one disk based and it may be that I go with memory based for the other, more temporary), though now, this is my #1 priority!  It doesn't matter if it is disk or memory based at this point, I'm just doing the Startup and with such  L O N G  times, it has my project stalled (I can't put something out that takes over a minute to start!).

BTW (not sure how important it might be), the #include list (which seems to grow constantly...) is now

 

#include <GUIConstantsEx.au3>

#include <GuiComboBox.au3>

#include <GuiEdit.au3>

#include <StaticConstants.au3>

#include <Array.au3>

#include <WindowsConstants.au3>

#include <SQLite.au3>

#include <SQLite.dll.au3>

#include <File.au3>

#include <Misc.au3>

#include "AssocArrays.au3"

#include <Misc.au3>

#include <IE.au3>

#include "_StringChooseCase.au3"

#include "Zip.au3"

#include "FileOperations/FileOperations_En/FileOperations.au3"

#include <MsgBoxConstants.au3>

I will be eliminating some of those, I'm sure as I tweak this program for speed (one of them being "AssocArrays" - I have found it to be (sadly, as I am used to named array elements...) causing some of the slowness in the program.  I have elimanted most of the AssocArray calls in the main loop and increased times about 20% (calling it nearly 40 times in the loop, with 30K+ files, it made a difference!)

Anyway, more on that stuff later - right now, I need to understand what to do about _SQLite_Startup()!

Ideas/suggestions?

[TechCoder]

Running the program several times to get some average startup - got this message

 

C:UsersSteveAppDataLocalTemp~ceqaejt.dll is either not designed to run on Windows or it contains an error.  Try installing the program again using the original installation media or contact your system administrator or the software vendor for support.

 

This was after getting return times (I didn't subtract the .26 seconds of other stuff, this is just the 'SQL1' line time)

6.37 5.91 5.9 5.9 6.01 5.92 5.91 39.08 5.94 6.04

 

I'm going to try re-installing AutoIt........

[BrewManNH]

Is the SQLite DLL in the scriptdir or @SystemDir prior to starting the script? If not, it will be downloaded when it's first started on a new computer.

[jchd]

What BrewManNH said is the culprit at 100%.

OTOH significant optimizations and new/changed functions for more extensive use cases are due to SQLite.au3. But don't hold your breath.

[TechCoder]

Is the SQLite DLL in the scriptdir or @SystemDir prior to starting the script? If not, it will be downloaded when it's first started on a new computer.

I've got dozens of versions (different sizes/dates) all over my computer, but after the upgrade, not one in the scriptdir or @SystemDir..........  (guess I had put it there before when I first started using SQLite - don't recall, but I am sure I had it in my scriptdir before - that is why I was loading it from there....)

I tried using the scripts in the new help file;

; SQLite.dll version must match 

returns a temp directory 

; no version reference so file must exist in @SystemDir, @WindowsDir, @ScriptDir or @WorkingDir

says I don't have the file

; open SQLite with a specific local file

says can't be loaded

; Force download from www.autoitscript.com

loads to a tmp directory

uncommenting the 
;~ #RequireAdmin ; needed if storing in @systemdir is wanted

gives the 'unknown publisher' error from MS - saying Yes again only loads a temp file (after a very long time...)

Any place for me to get a compatible, latest version file directly?

[TechCoder]

found it!

http://www.sqlite.org/2013/sqlite-dll-win32-x86-3080200.zip

I did have the older dll in the scriptdir, but the new AutoIt version check stopped it.

Installing this file now gets me back on track -

SQL0 0.29 sec

SQL1 0.29 sec

SQL2 0.29 sec

SQL3 0.29 sec

disk based database open 0.29 sec

diskbased db DEFAULT set 0.34 sec

'new' settings complete 0.34 sec

ready for GUISetState 0.35 sec

GUISetState done 0.39 sec

 

Yeah!  Now, I can get back to work (and back on the topic directly!)

Edited January 21, 2014 by TechCoder

[jchd]

For the record, an _SQLite_Open without parameter creates a new memory-based DB. Same if the filename is the special string ':memory:' (note the columns).

[TechCoder]

I'm now using _SQLite_Open() along with _SQLite_Open($DBName) to run the two databases.

I need one to retain settings the user needs, the other is the 'bulk' one that stores info from reading filenames, works the crunching of data, then outputs the results.  So far, I don't see a need to load the settings db into memory (it only contains about 50 tiny bits of data that is updated with every 'process this' run or when the user wants to save it and loading/saving is not noticeable at all - not sure if we could even measure a difference). 

However, using the tip to run things in memory for the bulk one PLUS the function from @AZJIO (see '?do=embed' frameborder='0' data-embedContent>>) has sped things up another 30% on the filename-to-memory-database side (what I call my 'input' side).

~20 seconds on 3K+ files - down from ~30 seconds before (it is also possible AutoIt upgrade helped - no exact data on which thing was best - I'll try to keep better results records for the output and 'overall' tweaks) - I'll do some runs on the 31K+ list later today.   Still no comparison to the ~27 seconds processing the same data through not only the 'input' side, but also the 'output' side (i.e., all the way through to the report stage) using the same flow of logic in php + MySQL (I totally understand that its apples and oranges systems, though user experience/perception isn't different), but getting better all the time.......

[jchd]

Are you inserting all filenames in one transaction, or do you cut the job in smaller chunks?

[TechCoder]

Are you inserting all filenames in one transaction, or do you cut the job in smaller chunks?

basically, 

1. loop through the files in the chosen directory(ies)

2. parse them according to the chosen rules (using StringRegExp, _PathSplit, StringRight, StringLeft,etc.)

3. verify they should be on the list to process (i.e., kick out various exceptions that might have made it through StringRegExp - perhaps by user selected option, my own 'bounce' rules, etc.)

4. take all the 'bits' (full filename as well as all the broken down pieces) and save to the database

I believe I can get some speed by not keeping all these bits floating around and just break it apart again later (though I wonder if it will be anything really significant and worth the effort - it is only perhaps 200 characters per filename...)

All this code is simply using the same logic/etc. from the php code - I took the comments from that code and ported the functions into AutoIt (as a new AutoIt user, doing the best I could with what I could find to make that section work...), so I'm sure there are many 'better ways' to get things faster - and certainly several things to change in the flow to be better code overall.

Directly on the speed testing;

I just tried using BEGIN/COMMIT with the memory db and the results are so similar I can't judge one is better than the other (both around 20 seconds for the input side).  As I'm only using the disk based db for quick dips and single commands, there is no sense (that I can think of) to use that - and, it is quite fast anyway.

So, to directly answer your question (I believe) - I have tested it both ways ("all filenames in one transaction" with BEGIN/COMMIT and 'chunks' as one-at-a-time saves) and it seems no different.

[jchd]

Ah yes, it won't make you gain anything significant for a memory DB. Since a memory DB is not shareable (well, not easily) among processes using transactions is at best rethorical.

Sorry I somehow forgot details of the thread where I replied. I'm doing some other work on several processes biting into a single DB.

[TechCoder]

More data..................

Looking ONLY in the grab-filename-put-into-database loop on the INPUT side

LEGEND:

End of Setup    (shown after some small parsing after _PathSplit and one _ArrayBinarySearch)

Start Exception Processing (did a StringRegExp just before)

End Exception Processing  (all my exceptions checks are done)

 

Start Add To Active List  (if the file passes, this starts the db dump)

End Add To Active List (end of db write)

OR

Start Add to Reverse Lookup 

End Add To Reverse Lookup 

 

Starting the ProgressSet (once in awhile we update the progress meter)

Time to process this file (ProgressSet is done and total time of processing is here)

 

NOW, TO THE DATA.............

 

A file that doesn't match original Exceptions (fastest processed file type)

 

End of Setup 0.114230944627442

Start Exception Processing 0.157959978117635

End Exception Processing 0.580971444941131

Starting the ProgressSet 0.601943532431325

Time to process this file 0.622023190666618

I'll further break down the Exception processing section to see if I can find anything that is 'wasteful'.......

 

A file that doesn't match Exceptions though hits ProgressSet counter (next fastest processed file type)

 

End of Setup 0.106645295960776

Start Exception Processing 0.149928114823518

End Exception Processing 0.589449522862699

Starting the ProgressSet 0.610421610352893

Time to process this file 1.40780714960773

You can see from this that calling ProgressSet does eat up some time (though we have to call it because the users want it...

 

A file that matches 'first run' things but not the main StringPregExp (goes to the 'Reverse Lookup' table in the database)

 

End of Setup 0.153944046470576

Start Add to Reverse Lookup 0.190087431294103

End Add To Reverse Lookup 3.56882459035266

Starting the ProgressSet 3.59782854113697

Time to process this file 3.62281656027423

Yes, we found the time bandit............  

The only thing in this section is the sql build-it string and the _SQLite_Exec() - this is to the MEMORY database

One thing 'of note' is that I am using _SQLite_Escape() on all the data pieces - I'm sure that is eating up time here.  This is, I believe, a good habit to get into (I used a similar function in the php that helped to protect against database injection - this is the only function I could find similar in AutoIt/SQLite, though, with this particular data/situation, I believe I could get away with just 's around it).  I'll do some tests changing _SQLite_Escape($databit) to "'" & $databit & "'" and see what that does.

 

 

a file that matches our StringPregExp and passes all the Exception checking

 

End of Setup 0.140111393019597

Start Exception Processing 0.214629235803905

End Exception Processing 0.76927401772543

Start Add To Active List 0.78801503207837

End Add To Active List 4.92933298945059

Starting the ProgressSet 4.97350823756824

Time to process this file 5.0038508322349

 

and, finally, the LONGEST time = a 'good' file that also hits the ProgressSet

 

End of Setup 0.130294671215676

Start Exception Processing 0.178932065607829

End Exception Processing 2.35467458905864

Start Add To Active List 2.38055503745079

End Add To Active List 6.02166639745051

Starting the ProgressSet 6.05200899211717

Time to process this file 6.85564153615633

 

Conclusion:

There are three locations to check further for 'waste';

- Exception processing 

- any/all function calls in/around the sql build (primarily _SQLite_Escape, though in the 'Active' write, there are also some calls to AssocArray, yet looking at the times above, they are not 'horrible', yet do exist...)

- look at calling ProgressSet as little as possible (maybe half as often as I'm calling it now)

[jchd]

Can you post the complete schema of your DB?

Sidenotr: SQLite DBs are awfully easy to manage with SQLite Expert (freeware version is fine). Post what appears under the DB DDL tab.

[TechCoder]

Can you post the complete schema of your DB?

Sidenotr: SQLite DBs are awfully easy to manage with SQLite Expert (freeware version is fine). Post what appears under the DB DDL tab.

$sql = "CREATE TABLE '" & $CT_active_tool & "crunch' (full_filename text NOT NULL PRIMARY KEY, file_name text NOT NULL, file_ext text NULL, dir_name text NULL, filename text NULL, DiscID text NULL, Artist text NULL, Track integer DEFAULT '0', Title text NULL, Custom text NULL, output_filename text NULL, output_folder text NULL, output_DiscID text NULL, output_Track integer NULL, output_Title text NULL, output_Artist text NULL, output_Custom text NULL);"

 

(SQLite Expert can't open memory databases - at least, I couldn't figure out a way to do it....... )

[TechCoder]

changing the progress bar update time did not significantly change the overall processing (hardly noticed - did not print out a timer or anything as it is just minimal)

Though, for those looking for a method to update the ProgressSet and not have a 'runaway' look (showing every file count does slow the time, but it is pretty irritating to watch the flying numbers.....)
 

Local $point = $counter / Round($size[1] / ($size[1] / ($size[1] / 100)))
If $point = Round($point) Then
    ProgressSet($point, "Parced " & $counter & " of " & $size[1] & " files in " & Int($dif / 60000) & " min., " & Round((($dif / 60000) - Int($dif / 60000)) * 60, 1) & " sec.", "Processing database input records")
ElseIf $point>= 100 Then
    ProgressSet($point, "Parced " & $size[1] & " of " & $size[1] & " files in " & Int($dif / 60000) & " min., " & Round((($dif / 60000) - Int($dif / 60000)) * 60, 1) & " sec.", "Processing database input records")
EndIf

where (in this case);

$counter is counting the 'each' you want to look for (files)

$size comes from DirGetSize

$dif = TimerDiff() from a timer you set at the start of where you want to check time

a bit shorter code and (somewhat) simpler than I found at '?do=embed' frameborder='0' data-embedContent>> though I'm sure the math could be done in a less complex way (seems I've stayed away from creating mathmatical formulas for time - not one of the things I do often....), but this works, which is what is important....

[jchd]

Expert just can't open a memory DB in another process. That's why backing up (to disk) is a good thing for diagnosis or experiment.

I was suspecting you were using too many indices but that doesn't seem to be the case.

Indeed, the need to invoke DllCalls repeatedly is a bottleneck but we have little possibility to escape that. As a comparison, decently written C programs can achieve 100K medium-size inserts per second but rotating magnetic disks bring a limit of 12 transactions per second.

I'm afraid you can't speed things much more.

[TechCoder]

Speed difference by replacing _SQLite_Escape with "'" on each side (not recommended for 'unknown' data!)

'Typical' run (as described above)

processed in 0 min., 23.8 sec.

processed in 0 min., 24.1 sec.

processed in 0 min., 23.6 sec.

processed in 0 min., 24.2 sec.

After changing 5 elements

processed in 0 min., 22.1 sec.

processed in 0 min., 24.4 sec.

processed in 0 min., 23.2 sec.

processed in 0 min., 24.4 sec.

I must say I was surprised - I thought there would be at least a tiny measurable difference, but the numbers don't show it......

[jchd]

This function is just a thin wrap. Could be a macro if we had them.

Do you have to handle/guard against duplicates? If so, how do you do it?

[TechCoder]

I'm afraid you can't speed things much more.

Well, that is good to know, in one way, though sad in another, since my php program will do twice the process (input and output functions) for 30K records in the time I'm doing 3000 here.......  But, there are benefits of having the program here (GUI look, simpler to create the user interface) and security issues with the other method, so I guess I'll move off trying to make this faster and get back to feature implementation - I thought (and was hoping) it was just my newness to the program.  

Processing taking < 1 minute each round is 'acceptable', so for really small needs, it works just fine.  The input side on my 31K records takes ~5 minutes (there are a lot of unmatched records, which reflects on my Exception tracking, which I will look into) and I wonder about clients with super-large numbers of files (from the calculations shown before, 

 

50 K rows of 30 column with average 200 characters. 50 K * 30 * 200 * 2 = 600 Mb

and taking that 'average' number to what I think is my 'biggie size' client, 

250 K rows of 30 column with average 200 characters. 250 K * 30 * 200 * 2 = 3 G - which takes it out of the memory size of most PCs and I have to revert back to the disk based db anyway (have been thinking about an algorithm to look at available memory size, number of files, etc. and figure out if we should take the memory or disk based route - shouldn't be too hard to come up with, if that becomes the best answer)

[jchd]

Then forget the memory DB. x64 processes are limited to 3G in the best case.

Nonetheless, the huge difference in speed between the two platforms makes me wonder what is going wrong.