Speed 'tweaking' AutoIt w/ SQLite

[TechCoder]

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?

ok on the thin wrap.

I do have a feature for working duplicates - in my php code.......  That is one of the things I haven't gotten to in AutoIt version yet - been slowed (no pun intended) on this particular thing for a couple days now.

Can't really say I have a plan of implementation for that yet as I haven't studied the docs to see what might can happen.  I did run across something several days ago about duplicates, but passed on it as I was doing other stuff.

What recommendations do you have?

[jchd]

Are you allowed to share your source with me with a significant data sample so that I have a look at it? If so, say so and I'll PM you a mail address: this forum isn't centered on SQL optimization.

[TechCoder]

Are you allowed to share your source with me with a significant data sample so that I have a look at it? If so, say so and I'll PM you a mail address: this forum isn't centered on SQL optimization.

actually, I was just putting a video together to show you (privately) the differences and was going to ask you for the same confidentiality.

PM away!

[mLipok]

Hey gentlemen, this thread was interesting.
Did you determine something interesting?

[jchd]

Definitely, but let TechCoder be the judge!

I transfered this to email/PMs since it has only remote relation with AutoIt per se.

[TechCoder]

"Interesting"? Perhaps the most interesting bit was the fact that jchd has more knowledge about sql in the tips of his little fingers that I have acquired in twenty-something years (well, maybe that isn't "interesting", but it certainly became very clear very quickly!)

I'd say there where too many 'take-aways' from the PMs, etc. to possibly list, though we (I can only use that term loosely as it was much more "under much appreciated, patient guidance - even when I didn't want to believe it, but did it anyway and found it was right.....") found where the slowdown occurs, I learned a LOT about how/where to clean up my code (both in this project and in general for all projects since) and even though I wound up spending a couple weeks cleaning and reworking things, it was well worth the time and experience.

Guess I hadn't really said THANK YOU (not publicly anyway), but I know if I have any further challenges with anything like this, jchd is very high on my list of 'go to' people.

As for what was found?  A portion of the slowness was my 'lazy' code.  Once I cleaned that up, I sped things up a bit (don't have the numbers now, but it was something like 3% faster).

The real significant time factor change was when I figured out the 'serious' slowdown was NOT SQLite and NOT AutoIt, but actually the INTERFACE between them (going through the .dll - massive time killer!)  

Once I learned that, the only answer to 'speed tweaking' everything was to do the massive rewrite to eliminate that from the program as much as possible (not easy for me as I've always done server-side code and running to the database with any sort of change has been my practice for years).

End result?  Moving everything from 'database reliant' to using arrays made the end product at least 'liveable' timewise.  Still nothing like the php version (which is constantly calling the database), but we got it down to around 45 seconds for the total process.  Also, to make the user presentation feel more 'speedy', I broke the processing in half and presented the user with the first report to look over, which happens 'quick', then we process the second half and give that report (the php version gives the reports on both portions at the end).  Makes it feel like 'something is happening' a lot better.

I believe I cut it down to something like three database calls instead of the dozen or so I was using for each run of the process. 

Constant testing of each feature/sort/etc. showed that it was possible to work with arrays for most things (my data requirements were 'on the edge' of really needing more than arrays would support, but I opted for speed over the 'edge' scenarios {ie., the customers that would kick into that area would likely be in a position to upgrade their system memory, etc.}) and where AutoIt doesn't really have quick functions (multiple sorting on cross-referenced data), I used the database, which excels in that.

I guess the biggest 'take away' was just learning what tool to pick up for the right job - using each in the way it works best, for the job it was designed.

[mLipok]

After the first reading sounds fascinating.
After the second will be instructive

But the spacing between the first and the second reading must be at least several days.
Then the knowledge perpetuates the best.

Many thanks for such a broad pronouncement.

mLipok

[Zedna]

There are some topics on this forum about general speed optimizations, for example:

'?do=embed' frameborder='0' data-embedContent>>

'?do=embed' frameborder='0' data-embedContent>>

'?do=embed' frameborder='0' data-embedContent>>

...

Edited April 14, 2014 by Zedna

[Iczer]

Cannot think better name for topic, so i try to hijack this 

i have this code:

$s_query_string = ""
For $i = 1 To $aResult[0][0]
    $s_query_string &= "UPDATE " & $mainTable & " SET Column_1="&$aResult[$i][1]&", Column_2='"&$aResult[$i][2]&"', Column_4='upd' " & _
                        "WHERE Column_1=0 AND Column_3 LIKE 'Mark' AND Column_0 LIKE '"&StringReplace($aResult[$i][0], "'", "''", 0, 1)&"';"
Next

_SQLite_Exec($BackUPDB, "BEGIN TRANSACTION;" & $s_query_string & "COMMIT;" )

is it possible to speed up this code about 10+ times?

Edited April 25, 2014 by Iczer

[jchd]

It all depends of the database schema. Without looking at it first there is no way to guide you. Also a rough idea of how many rows are concerned by the various conditions and how discriminant they are would help.

[Iczer]

database - is one table with 10 column, schema - "(column_0,column_1,column_2 ... )"

column_0 - 2 rows

column_1 - some thousands rows

column_3 - some thousands rows

maybe i should place column_0 on first place? change "LIKE" to "=" ?

[jchd]

LIKE is by default case INsensitive but = is not. Hence = is much faster if you can use it.

I don't know how to interpret the numbers you provide. If there are tipically 2 rows satisfying col_0 condition, there can't be more involved in the update.

How many rows does the table contain?

Try building a coumpound index on (col_0, col_1, col_3). At any rate declare column types.

Fine-tunning a DB is some white magic. If possible PM a .zip with your DB, your source code and instructions on how to trigger this update. Absolute discretion guaranteed.

EDIT: reversed false statement!

Edited April 26, 2014 by jchd

[Mat]

LIKE is by default case sensitive but = is not. Hence = is much faster if you can use it.

This seems counter-intuitive. How can a case insensitive match be faster than case sensitive when the latter has more possible letters to check against?

[Iczer]

i timed out process of data correction and get around 162 seconds for one thousand rows and i was need to update about 180 thousand rows in 423 thousand rows table.

why it update is so slow?

in ProcessExplorer i see AutoIt3.exe make about 60Gbytes I/O Reads to update 1000 rows

in contrast - rows deletion, data gathering and then insertion of 180 thousand rows was worth about 87 seconds

[jchd]

This seems counter-intuitive. How can a case insensitive match be faster than case sensitive when the latter has more possible letters to check against?

Of course you're right and I wrote the exact contrary to what I meant. Fixed.

[TechCoder]

Welcome, 'hijacker'   - I do hope you get a solution.

What I found was that a 'best practice' on using AutoIt and SQLite was to actually avoid as many calls to the database as possible (in other words, the best way to have fast code is to not use the database - extremely counter-intuitive and kills a lot of brain cells trying to get around it, IMHO....  )  

The slowdown (as I found in my testing) is in the calls to the .dll - not really anything that can be done short of a core code integration.

I wound up (under jghd's directing 'whip' with a work-around by creating some complicated arrays using Enum, etc. to help relieve the 'pain' of only having numerical arrays available (there is a UDF for naming the array slots, but it adds quite a bit of time too - nice to use, but not recommended if you have any speed issues in other places.)  And, I now go to the database once for a lookup (when the code starts, so it isn't really noticeable) and once when I write the 'massaged' data that needs sorting/selection (that I found no work-around for in AutoIt arrays) and then the "have to" dip of grabbing the complex sort, etc. that only the database can do.

As I said before, it made me 'think' more about my programming, which I do believe has made me a better programmer, so, overall, worth the effort, though it doesn't make for "get it done" programs.....

In your case, the query is complex enough that I'm not seeing a work-around that is feasible using AutoIt arrays - there isn't a great way to search and update arrays by matching multiple columns, etc. (that is what a database does very easily and well - I know...) and unless you can re-think your data layout and updates to match into arrays, you are destined to go through that painfully slow interface between AutoIt and SQLite.

With that, I turn this topic over to you and look forward to hearing of your successes.

[jchd]

Iczer,

Once again, each use case is different. The figures you cite are surprising and can most probably be cut down but doing so requires knowing more specifcs. Can you PM a download link so I can advise you?

BTW sometimes a different data schema can decrease particular operation dramatically.

Edited April 26, 2014 by jchd

[Iczer]

I think i found solution

at first i transformed update transaction to a "smaller" one:

$s_query_string = ""
For $i = 1 To $aResult[0][0]
    $s_query_string &= "UPDATE " & $mainTable & " SET Column_1="&$aResult[$i][1]&", Column_2='"&$aResult[$i][2]&"' " & _
                        "WHERE Column_0='"&StringReplace($aResult[$i][0], "'", "''", 0, 1)&"' AND Column_3='Mark';"
Next

_SQLite_Exec($BackUPDB, "BEGIN TRANSACTION;" & $s_query_string & "COMMIT;" )

but get 170 seconds to update 1000 rows - pretty much nothing changed

second - keeping in mind 60GB I/O reads to update 1000 rows - i changed sqlite cache size to double size of db or 128MB - what is greater:

_SQLite_GetTable($DB, "PRAGMA page_size;", $aResult, $iRows, $iColumns)
$DBCacheSize = _Max(2*FileGetSize($PathToDB)/$aResult[2],128*1024)
_SQLite_Exec($UPDB, "PRAGMA cache_size = " & $DBCacheSize & ";" )

and get 65 seconds to update 1000 rows while I/O reads was equal DB size - about 3 times better with zero extra/uncached reads - step into right direction

next i created index for 2 columns used in "WHERE" :

_SQLite_Exec($DB, "BEGIN TRANSACTION;CREATE INDEX IF NOT EXISTS indexN1 ON " & $mainTable & " (Column_0,Column_3);COMMIT;" )

and get blazing 0.021 second

[jchd]

All good.

 

BTW each individual SQLite statement is internally wrapped inside a transaction, so there is no need to transact them explicitely.

Next, you transact each iteration of your update loop: this is both useless and inefficient.

Prefer doing:

begin;

for loop

    update

next

commit