Jump to content

Recommended Posts

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?

Link to post
Share on other sites

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........

Link to post
Share on other sites

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.

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

Link to post
Share on other sites

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.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

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?

Link to post
Share on other sites

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

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).

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

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.......

Link to post
Share on other sites

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

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

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.

Link to post
Share on other sites

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.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

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)

Link to post
Share on other sites

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.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

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

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....

Link to post
Share on other sites

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.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

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......

Link to post
Share on other sites

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?

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to post
Share on other sites

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)

Link to post
Share on other sites

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.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

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
  • Recently Browsing   0 members

    No registered users viewing this page.

  • Similar Content

    • By Hermes
      Hi, I am struggling in setting the value of a textarea based on the value of clipboard (that contains a long web page source codes). If I use _WD_SetElementValue, it freezes after some time, or appears to be pressing tab and goes out of focus. I can also use send keys but i need the script to run in the background.
      Here is the full script:
      #Include "Chrome.au3" #Include "wd_core.au3" #Include "wd_helper.au3" #Include "WinHttp.au3" #include <MsgBoxConstants.au3> #include <WinAPIFiles.au3> #include <Array.au3> #include <AutoItConstants.au3> #include <WinAPIFiles.au3> #include <GDIPlus.au3> #include <Excel.au3> Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "http://demo.borland.com/testsite/stadyn_largepagewithimages.html") _WD_LoadWait($sSession) Global $sSource = _WD_GetSource($sSession) Local $Paste = ClipPut($sSource) Local $sData = ClipGet() Local $aArray = 0, _ $iOffset = 1 While 1 $aArray = StringRegExp($sData, '(?s)<p>.*</p>', $STR_REGEXPARRAYMATCH, $iOffset) If @error Then ExitLoop $iOffset = @extended For $i = 0 To UBound($aArray) - 1 Local $Paste = ClipPut($aArray[$i]) Local $sRegExData = ClipGet() ;MsgBox(0, "", "$sRegExData = " & $sRegExData) Next WEnd _WD_Navigate($sSession, "https://www.w3schools.com/tags/tryit.asp?filename=tryhtml5_textarea_placeholder") _WD_WaitElement($sSession, $_WD_LOCATOR_ByCSSSelector, "iframe#iframeResult") Local $sElement1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "iframe#iframeResult") _WD_FrameEnter($sSession, $sElement1) _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//html/body/textarea") $textarea = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//html/body/textarea") _WD_ElementAction($sSession, $textarea, 'click') ;WD SetElementValue(SsSession, Stextarea, $sRegExData) <-- I can do this but the focus goes out, or the browser freezes _WD_FrameLeave($sSession) sleep(2000) Send("^v") _WD_LoadWait($sSession) _WD_Shutdown() Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}' EndFunc ;==>SetupChrome Can someone help me please, or re-direct me to the right path? TIA!
    • By DJ143
      I have a autoit exe file which is used in upload/browse file functionality.  This has been integrated with selenium framework and I am invoking the autoit exe using Java process and runtime. 
      Now the issue is when I run the scripts and invoke the autoit exe in local it works perfectly.  But when I use selenium grid or jenkins to run the scripts in another windows server it is not working.
      Can anyone please suggest any solution for this?
    • By Hermes
      Hello, the script below will read column A from an excel file - and if a value matches in the browser, it will click the corresponding link and click on a specific button to paste the data, then writes "Completed" in Column B. It will continue to read from the excel file and do the same thing for all the remaining rows.
      #Include "Chrome.au3" #Include "wd_core.au3" #Include "wd_helper.au3" #Include "WinHttp.au3" #include <MsgBoxConstants.au3> #include <File.au3> #include <IE.au3> #include <Array.au3> #include <INet.au3> #include <AutoItConstants.au3> #include <WinAPIFiles.au3> #include <GDIPlus.au3> #include <Excel.au3> #Include "WinHttp.au3" #Include "_HtmlTable2Array.au3" Local $sDesiredCapabilities, $sSession SetupChrome() _WD_Startup() $sSession = _WD_CreateSession($sDesiredCapabilities) _WD_LoadWait($sSession) _WD_Navigate($sSession, "table1.html") _WD_LoadWait($sSession) _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']") Local $sElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, "//table[@class='main']") ;ConsoleWrite ("mat-table " & $sElement & @CRLF) Local $aArray1 = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//td[contains(@class,'data')]", $sElement, True) sleep(1000) For $i = 0 to UBound($aArray1) - 1 $aArray1[$i] = _WD_ElementAction($sSession, $aArray1[$i], 'text') Next ;_ArrayDisplay($aArray1) ;Email variables $SmtpServer = "" ; address for the smtp-server to use - REQUIRED $FromName = "Hermes" ; name from who the email was sent $FromAddress = "sender@gmail.com" ; address from where the mail should come $ToAddress = "recipient@gmail.com" ; destination address of the email - REQUIRED, use commas (,) to add more email addresses $Subject = "File not found" ; subject from the email - can be anything you want it to be $Body = "File not found!" ; the messagebody from the mail - can be left blank but then you get a blank mail $AttachFiles = "" ; the file(s) you want to attach seperated with a ; (Semicolon) - leave blank if not needed $CcAddress = "" ; address for cc - leave blank if not needed $BccAddress = "" ; address for bcc - leave blank if not needed $Importance = "High" ; Send message priority: "High", "Normal", "Low" $Username = "" ; username for the account used from where the mail gets sent - REQUIRED $Password = "" ; password for the account used from where the mail gets sent - REQUIRED $IPPort = 25 ; port used for sending the mail $ssl = 0 ; enables/disables secure socket layer sending - put to 1 if using httpS $tls = 0 ; enables/disables TLS when required Local $oAppl = _Excel_Open() Local $sWorkbook = "c:\test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters If FileExists($sWorkbook) Then ;Check if the file exist. Local $oAppl = _Excel_Open() Local $sWorkbook = "c:\test.xlsx" Local $oWorkbook = _Excel_BookOpen($oAppl, $sWorkbook) ;open excel and pass both parameters Local $aArray2 = _Excel_RangeRead($oWorkbook,Default,$oWorkbook.ActiveSheet.Usedrange.Columns("A:A")) Local $iIdx Local $Skipline = 0 ;0==> first line Do Local $temprf For $i = 0 To UBound($aArray2) - 1 $temprf &= $aArray2[$i] _WD_WaitElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]") Local $aElement = _WD_FindElement($sSession, $_WD_LOCATOR_ByXPath, ".//a[contains(@class,'edit') and contains(text(),'Edit')]", $sElement, True) $iIdx = _ArraySearch($aArray1, $aArray2[$i]) If @error Then ContinueLoop _WD_ElementAction($sSession, $aElement[$iIdx], 'click') If $i < $Skipline Then ContinueLoop $oRange = $oWorkbook.ActiveSheet.Range("B" & $i + 1 & ":XFD" & $i + 1) _Excel_RangeCopyPaste($oWorkbook.Activesheet, $oRange) ;Paste Local $oTest4 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "pastebutton") _WD_ElementAction($sSession, $oTest4, 'click') Sleep(1000) ;Save Button Local $save3 = _WD_FindElement($sSession, $_WD_LOCATOR_ByCSSSelector, "button.button") _WD_ElementAction($sSession, $save3, 'click') _Excel_RangeWrite($oWorkbook, $oWorkbook.Activesheet, "Completed", "B" & $i+1) sleep(1000) Next Until (Not @error) _Excel_Close($oWorkbook) Else _INetSmtpMailCom($SmtpServer, $FromName, $FromAddress, $ToAddress, $Subject, $Body, $CcAddress, $BccAddress, $Importance, $Username, $Password, $IPPort, $ssl, $tls) Exit EndIf _WD_LoadWait($sSession) ;Attaching files to emails Func _INetSmtpMailCom($s_SmtpServer, $s_FromName, $s_FromAddress, $s_ToAddress, $s_Subject = "", $as_Body = "", $s_CcAddress = "", $s_BccAddress = "", $s_Importance="Normal", $s_Username = "", $s_Password = "", $IPPort = 25, $ssl = 0, $tls = 0) Local $objEmail = ObjCreate("CDO.Message") $objEmail.From = '"' & $s_FromName & '" <' & $s_FromAddress & '>' $objEmail.To = $s_ToAddress Local $i_Error = 0 Local $i_Error_desciption = "" If $s_CcAddress <> "" Then $objEmail.Cc = $s_CcAddress If $s_BccAddress <> "" Then $objEmail.Bcc = $s_BccAddress $objEmail.Subject = $s_Subject If StringInStr($as_Body, "<") And StringInStr($as_Body, ">") Then $objEmail.HTMLBody = $as_Body Else $objEmail.Textbody = $as_Body & @CRLF EndIf $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserver") = $s_SmtpServer If Number($IPPort) = 0 then $IPPort = 25 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = $IPPort ;Authenticated SMTP If $s_Username <> "" Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendusername") = $s_Username $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendpassword") = $s_Password EndIf ; Set security params If $ssl Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True If $tls Then $objEmail.Configuration.Fields.Item ("http://schemas.microsoft.com/cdo/configuration/sendtls") = True ;Update settings $objEmail.Configuration.Fields.Update ; Set Email Importance Switch $s_Importance Case "High" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "High" Case "Normal" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Normal" Case "Low" $objEmail.Fields.Item ("urn:schemas:mailheader:Importance") = "Low" EndSwitch $objEmail.Fields.Update ; Sent the Message $objEmail.Send $objEmail="" EndFunc ;==>_INetSmtpMailCom Local $aDir = _FileListToArrayRec(@TempDir, "scoped_dir*;chrome_*", $FLTAR_FOLDERS, $FLTAR_NORECUR, $FLTAR_NOSORT, $FLTAR_FULLPATH) Sleep(2000) For $i = 1 To $aDir[0] DirRemove($aDir[$i], $DIR_REMOVE) Next _WD_LoadWait($sSession) _WD_Shutdown() Func SetupChrome() _WD_Option('Driver', 'chromedriver.exe') _WD_Option('Port', 9515) _WD_Option('DriverParams', '--log-path="' & @ScriptDir & '\chrome.log"') $sDesiredCapabilities = '{"capabilities": {"alwaysMatch": {"goog:chromeOptions": {"w3c": true, "args":["start-maximized","disable-infobars"]}}}}' EndFunc ;==>SetupChrome If the excel file doesn't exists in the folder, it will send an email to a specific recipient.
      What i am trying figure out now is if the excel crashes while the script/loop is running, I want to relaunch the excel file continue to the last row before the excel crashed. So if the value of column B is not marked as "completed", it should continue from that row
      Appreciate any help that I can get to achieve this.
      table1.html test.xlsx
    • By t0nZ
      Code to read a Spiceworks Database and export text data files in INI format.
      Focused on exporting data about PC inventory and useful to migrate to another inventory system.
      Tested with a SQLite DB from Spiceworks latest (and LAST on premise) version - (7.5.00107.30 ott 2019)
      It creates one  text file for every single machine.
       
      ;Spiceworks Db Exporter ; ;- NSC - t0nZ 2021 ;code to read a Spiceworks Database and export text data files in INI format. ;focused on exporting data about PC inventory. ;useful to migrate to another inventory system. ;tested with a SQLite DB from Spiceworks latest (and LAST on premise) version - (7.5.00107.30 ott 2019) ;It creates one text files for every single machine. ; --> please adapt paths to your environment. #include <SQLite.au3> #include <String.au3> #include <File.au3> dataINIfromDBspiceworks() #Region spiceworks immport Func dataINIfromDBspiceworks() ConsoleWrite("start import from db Spiceworks" & @CRLF) Local $dbspicepath = "C:\scambio" Local $dbspice = "spiceworks_prod.db" ; =====================>>>>> START SQL DLL _SQLite_Startup() ;======================<<<<<<<<<<<<<<<<<<< _SQLite_Open($dbspicepath & '\' & $dbspice) ;- Local $aCPdata Local $hQuery Local $recordcount = 0 Local $salvarec = 0 ;------------------------------------------------ Local $spiceQuery = "SELECT serial_number," & _ "server_name," & _ "manufacturer," & _ "(model || '-' || raw_model)," & _ "processor_type," & _ "raw_processor_type," & _ "processor_architecture," & _ "raw_processor_type," & _ "number_of_processors," & _ "'speed'," & _ "CAST (memory AS FLOAT) / 1073741824," & _ "current_user," & _ "domain," & _ "network_adapters.dns_domain," & _ "'logon'," & _ "operating_system," & _ "os_architecture," & _ "version," & _ "'lang'," & _ "('C:;Fixed;' || (CAST (disks.size AS FLOAT) / 1048576) || ';' || (CAST (disks.free_space AS FLOAT) / 1048576) )," & _ "devices.ip_address," & _ "'(0.0.0.0)'," & _ "'(0.0.0.0)'," & _ "'(0.0.0.0)'," & _ "'(0.0.0.0)'," & _ "network_adapters.gateway," & _ "(network_adapters.description ||' - '|| network_adapters.name)," & _ "devices.mac_address," & _ "devices.updated_on," & _ "user_tag " & _ "FROM devices " & _ "inner JOIN " & _ "network_adapters ON network_adapters.computer_id = devices.id " & _ "inner JOIN " & _ "disks ON disks.computer_id = devices.id " & _ "WHERE disks.name = 'C:' " & _ "ORDER BY devices.updated_on DESC;" _SQLite_Query(-1, $spiceQuery, $hQuery) While _SQLite_FetchData($hQuery, $aCPdata) = $SQLITE_OK writeINI($aCPdata) $recordcount += 1 If $recordcount = $salvarec + 10 Then $salvarec = $recordcount ConsoleWrite($recordcount & " processed records " & @CRLF) EndIf WEnd ;________________________________________________ _SQLite_Close() _SQLite_Shutdown() EndFunc ;==>dataINIfromDBspiceworks Func writeINI($aCPdata) Local $folderdataINI = "c:\scambio\ini" If Not FileExists($folderdataINI) Then DirCreate($folderdataINI) $aCPdata[28] = StringRegExpReplace($aCPdata[28], "[\D]", "") ; this "2021-04-17 02:34:16" to that "20210417023416 Local $cpini = $folderdataINI & "\" & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini" If FileExists($folderdataINI & "\" & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini") Then FileDelete($folderdataINI & "\" & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini") ; deleted previous files ! ConsoleWrite("deleted: " & "cp_" & $aCPdata[28] & "_" & $aCPdata[0] & "_.ini" & @CRLF) EndIf ;Section Unique :serial, computer name Local $aSectionUnique[2][2] = [["serial", $aCPdata[0]], ["computername", $aCPdata[1]]] IniWriteSection($cpini, "UNIQUE", $aSectionUnique, 0) ;Section Machine :manufacturer,model,cpuname,cpuid,cpuarc,cpuvendor,cpucores,cpuspeed,RAM,disk1capacity,disk1used Local $aSectionMachine[9][2] = [["manufacturer", $aCPdata[2]], ["model", $aCPdata[3]], ["cpuname", $aCPdata[4]], ["cpuid", $aCPdata[5]], ["cpuarc", $aCPdata[6]], ["cpuvendor", $aCPdata[7]], ["cpucores", $aCPdata[8]], ["cpuspeed", $aCPdata[9]], ["ram", $aCPdata[10]]] IniWriteSection($cpini, "MACHINE", $aSectionMachine, 0) ;Section User : username,domain,DNSdomain,logonServer Local $aSectionUser[4][2] = [["username", $aCPdata[11]], ["domain", $aCPdata[12]], ["DNSdomain", $aCPdata[13]], ["logonServer", $aCPdata[14]]] IniWriteSection($cpini, "USER", $aSectionUser, 0) ;Section OS : OSver, OSarch, OSbuild, OSlang Local $aSectionOS[4][2] = [["OSver", $aCPdata[15]], ["OSarch", $aCPdata[16]], ["OSbuild", $aCPdata[17]], ["OSlang", $aCPdata[18]]] IniWriteSection($cpini, "OS", $aSectionOS, 0) ;Section Disks: username,domain,DNSdomain,logonServer Local $aSectionDrives[1][2] = [["drives", $aCPdata[19]]] IniWriteSection($cpini, "DRIVES", $aSectionDrives, 0) ;Section network:localIP1,localIP2,localIP3,localIP4,publicIP,GW,adapter,mac Local $aSectionNetwork[8][2] = [["localIP1", $aCPdata[20]], ["localIP2", $aCPdata[21]], ["localIP3", $aCPdata[22]], ["localIP4", $aCPdata[23]], ["publicIP", $aCPdata[24]], ["GW", $aCPdata[25]], ["adapter", $aCPdata[26]], ["mac", $aCPdata[27]]] IniWriteSection($cpini, "NETWORK", $aSectionNetwork, 0) ;Section PLUS :date, groupid Local $aSectionPlus[2][2] = [["date", $aCPdata[28]], ["groupid", $aCPdata[29]]] IniWriteSection($cpini, "PLUS", $aSectionPlus, 0) EndFunc ;==>writeINI #EndRegion spiceworks import  
      Spiceworks on premise, now pretty abadonware, has a non crypted SQLite DB usually located in:
      c:\Program Files (x86)\Spiceworks\db\spiceworks_prod.db The query was the difficult part (at least for me), and I export in INI format because it was part of my effort to migrate from Spiceworks to a custom made system (Computer Plucker see this post) where I already parse .INI files in a custom MySQL and/or SQLite DB.
       
    • By adityaparakh
      Hello ,
      I am trying to use Websockets in AutoIt.
      It is to fetch live stock market prices , API is provided and documentation available for python language.
      The link for the code snippet is :
      https://symphonyfintech.com/xts-market-data-front-end-api-v2/#tag/Introduction
      https://symphonyfintech.com/xts-market-data-front-end-api-v2/#tag/Instruments/paths/~1instruments~1subscription/post
       
      https://github.com/symphonyfintech/xts-pythonclient-api-sdk
       
      Second Link is to subscribe to a list of ExchangeInstruments.
      Now I would like to get live stock ltp (LastTradedPrice) for a few stocks whose "ExchangeInstrumentID" I know.
      I am able to use the WinHttp object to perform actions using simple codes like below :
      I have the secretKey and appkey and can generate the needed token. And get the unique ExchangeInstrumentID.

      Below code is just for example of how I am using WinHttp. Unrelated to socket part.
      Global $InteractiveAPItoken = IniRead(@ScriptDir & "\Config.ini", "token", "InteractiveAPItoken", "NA") $baseurl = "https://brokerlink.com/interactive/" $functionurl = "orders" $oHTTP = ObjCreate("winhttp.winhttprequest.5.1") $oHTTP.Open("POST", $baseurl & $functionurl, False) $oHTTP.SetRequestHeader("Content-Type", "application/json;charset=UTF-8") $oHTTP.SetRequestHeader("authorization", $InteractiveAPItoken) $pD = '{ "exchangeSegment": "NSEFO", "exchangeInstrumentID": ' & $exchangeInstrumentID & ', "productType": "' & $producttype & '", "orderType": "MARKET", "orderSide": "' & $orderside & '", "timeInForce": "DAY", "disclosedQuantity": 0, "orderQuantity": ' & $qty & ', "limitPrice": 0, "stopPrice": 0, "orderUniqueIdentifier": "' & $orderidentifier & '"}' $oHTTP.Send($pD) $oReceived = $oHTTP.ResponseText $oStatusCode = $oHTTP.Status
          
          
      But am struggling to understand and use socket.
      Would be of great help if you can have a look at the link mentioned above and help with the code sample for AutoIt.
      To connect and listen to a socket.
      Thanks a lot
       
×
×
  • Create New...