[Solved] Fast Real-Time File Read Write

[Ascer]

Dear community,

I would like to ask what is the fast possible way to get or set specific data file.

• file type doesn't matter.
• file should contains around 100,000 multiple row tables.
• time to get or set info should be close to 0ms to avoid lagging main script.

Example how should works such as script.

• Received task to find table with user name: "Tom"
• Read file for user table and return it.
• Manage user table: add, remove, replace values.
• Save edited table to file.

I already tested and this can't be used.

• FileRead FileWrite FileReadToArray ==> 10000ms
• Microsoft.XMLDOM ==> 100ms
• Msxml2.DOMDocument.6.0 ==> 25ms

If you have an idea what could help please post simple example.

Solution:

• Use XML file to store users data
• Transfer it from XML to dictionary
• Manage add/edit/remove inside dictionary
• Save results to file XML every some time.

Warring! This is not real-time read/write this is memory reading if you need other solution read whole thread ppls post there other solutions.

expandcollapse popup

;----------------------------------------------------------
; Config for script.
;----------------------------------------------------------
Global $TIME = 0
Global $FILE = "users.xml"                  ; Name of file with users.
Global $PATH = @ScriptDir & "\" & $FILE     ; Path where we create users file xml
Global $TOTAL = 100000                      ; Amount of users to create.

ObjEvent("AutoIt.Error", "error")           ; Register COM event handler.

;----------------------------------------------------------
; Create satabase file.
;----------------------------------------------------------
setTime("Creating database xml with " & $TOTAL & " users..")
createXMLDatabase($PATH, $TOTAL)
print("Database has been created.")
getTimeDiff()

;----------------------------------------------------------
; Transfer satabase from xml file to dictionary.
;----------------------------------------------------------
setTime("Transfering Database from xml file to dictionary it take some time don't close script..")
Dim $base = transferXMLToDictionary($FILE)
Dim $xmlbase = $base[0]
Dim $database = $base[1]
print("Transfer has end.")
getTimeDiff()

;----------------------------------------------------------
; Search database for user.
;----------------------------------------------------------
setTime("Serching dictionary for user nr " & $TOTAL & "..")
Dim $user = getDatabaseUser("User" & $TOTAL, $database)
getTimeDiff()
If $user <> -1 Then
    print("User has been found.")
    For $i = 0 To UBound($user) - 1
        print($user[$i])
    Next
Else
    print("User not found.")
EndIf


;----------------------------------------------------------
; Change user name for Ascer.
;----------------------------------------------------------
setTime("Change user name for Ascer..")
$user[0] = "Ascer"
$database.item("User" & $TOTAL) = $user
getTimeDiff()
print("Changed user name for Ascer")

;----------------------------------------------------------
; Save database to file.
;----------------------------------------------------------
setTime("Save database to file xml..")
saveDatabaseToXML($path, $xmlbase, $database)
print("Saved database do file.")
getTimeDiff()

#Region 1.1, Functions.

Func saveDatabaseToXML($path, ByRef $xmlbase, ByRef $database)
    Local $xml, $row
    $xml &= '<?xml version="1.0" encoding="UTF-8" standalone="no"?>' & @CRLF
    $xml &= '<users>' & @CRLF
    For $user in $database.keys
        $row = $database.item($user)
        $xml &= @TAB & '<user name="' & $row[0] & '" passwd="' & $row[1] & '" range="' & $row[2] & '" group="' & $row[3] & '"/>' & @CRLF
    Next
    $xml &= "</users>"
    FileDelete($path)
    FileWrite($path, $xml)
EndFunc ;==> Save database do file xml.

Func getDatabaseUser($name, ByRef $handle)
    Local $user = $handle.item($name)
    If IsArray($user) Then Return $user
    Return -1
EndFunc ;==> Return info about user in database or -1 if not found.

Func transferXMLToDictionary($file)
    Local $msxml, $users, $database, $name
    $msxml = ObjCreate('MSXML2.DOMDocument.6.0')
    $msxml.load($file)
    $users = $msxml.SelectNodes("//users/user")
    $database = ObjCreate("Scripting.Dictionary")
    For $user In $users
        $name = $user.getAttribute("name")
        Local $table = [$name, $user.getAttribute("passwd"), $user.getAttribute("range"), $user.getAttribute("group")]
        $database.add($name, $table)
    Next
    Local $retn = [$msxml, $database]
    Return $retn
EndFunc ;==> Read path for file and export it to dictionary. Return array with [0] - xml handle, [1] - dictionary handle.

Func createXMLDatabase($path, $amount)
    Local $xml
    $xml &= '<?xml version="1.0" encoding="UTF-8" standalone="no"?>' & @CRLF
    $xml &= '<users>' & @CRLF
    For $i = 1 To $amount
        $xml &= @TAB & '<user name="User' & $i & '" passwd="123456" range="6" group="none"/>' & @CRLF
    Next
    $xml &= "</users>"
    FileDelete($path)
    FileWrite($path, $xml)
EndFunc ;==> Create database xml with users

Func error()
    Return -1
EndFunc

Func setTime($msg="")
    print("setTime: " & $msg)
    $TIME = TimerInit()
EndFunc

Func getTimeDiff()
    print("getTimeDiff: " & Int(TimerDiff($TIME) * 100) / 100 & "ms")
EndFunc

Func print($data)
    Return ConsoleWrite($data & @CRLF)
EndFunc ;==> Wpisuje wartość + znak nowej linji do SCITE.print()

#EndRegion

 

Sped results

setTime: Creating database xml with 100000 users..
Database has been created.
getTimeDiff: 386.17ms
setTime: Transfering Database from xml file to dictionary it take some time don't close script..
Transfer has end.
getTimeDiff: 11302.85ms
setTime: Serching dictionary for user nr 100000..
getTimeDiff: 0.06ms
User has been found.
User100000
123456
6
none
setTime: Change user name for Ascer..
getTimeDiff: 0.03ms
Changed user name for Ascer
setTime: Save database to file xml..
Saved database do file.
getTimeDiff: 2063.39ms

 

Edited February 24, 2020 by Ascer

[Jos]

Moved to the appropriate forum.

Moderation Team

[RTFC]

Try a ramdisk or memory-mapping the file.

[Ascer]

6 minutes ago, RTFC said:

Try a ramdisk or memory-mapping the file.

Could you post some example how it works?

[RTFC]

There's this thing called the world-wide intertubes; it actually contains lots of useful information (here and there).

Imdisk quick review video here; download software here and just run the GUI, or google "imdisk example". Create/mount your ramdisk, copy/move your file there, and access that instead of the original file location. Done.

The memory-mapping Help content contains an example on that very page I linked to. Or google "site:autoitscript.com memory-mapping" or such like.

Edited February 22, 2020 by RTFC

[jchd]

Try a memory-based SQLite database with a carefully-designed schema. Backup it from disk file to memory at startup and backup it from time to time and before program termination.

[Ascer]

31 minutes ago, RTFC said:

There's this thing called the world-wide intertubes; it actually contains lots of useful information (here and there).

Imdisk quick review video here; download software here and just run the GUI, or google "imdisk example". Create/mount your ramdisk, copy/move your file there, and access that instead of the original file location. Done.

The memory-mapping Help content contains an example on that very page I linked to. Or google "site:autoitscript.com memory-mapping" or such like.

 

2 minutes ago, jchd said:

Try a memory-based SQLite database with a carefully-designed schema. Backup it from disk file to memory at startup and backup it from time to time and before program termination.

 

If you can't post simple examples is ok just tell not everyones are masters of Autoit.

[jchd]

Hard to make a realistic example out of thin air. We aren't "masters" of your application".

Post significant example data, constraints and example queries.
As far as I can see you have 100k usernames (length, casing & diacritics sensivity) and each user has a table (#rows?, #columns?) of data (provide some samples with datatypes).

[Ascer]

4 minutes ago, jchd said:

Hard to make a realistic example out of thin air. We aren't "masters" of your application".

Post significant example data, constraints and example queries.
As far as I can see you have 100k usernames (length, casing & diacritics sensivity) and each user has a table (#rows?, #columns?) of data (provide some samples with datatypes).

As you my see reading my first post i don't have such as file because i don't know which should perfect fit for this job.

Imagine you have 100,000 users and each one have own "name", "passwd" and a lot of more info.

He send packet -> login to your server and you need to load all possible info about him and respond fast as possible.

How you will find this info?

[jchd]

25 minutes ago, Ascer said:

Imagine you have 100,000 users and each one have own "name", "passwd" and a lot of more info.

I translate this in:
name char
credentialhash blob (never store a passphrase!)
but what's "a lot of more info"?

You said "multirows table", but how does that translate in the real world?

[Ascer]

5 minutes ago, jchd said:

I translate this in:
name char
credentialhash blob (never store a passphrase!)
but what's "a lot of more info"?

You said "multirows table", but how does that translate in the real world?

We make 9 posts but without any line of code.

<user name="Tom" passwd="123456" isLogin="0" level="50" guild="none"/>

This is how it could look inside xml file.

Now imagine you have 100,000 similar lines and you want to get info name, passwd, isLogin, level, guild only for single user that login to your app.

[jchd]

1 minute ago, Ascer said:

We make 9 posts but without any line of code.

You didn't provide useful information until above post!

That is only 100k rows, not 100k multirows tables.

Note that it looks like it's for a game server. Remember that if that's the case, mods may consider that against forum rules.
I'll try to make up something soon.

[Ascer]

11 minutes ago, jchd said:

You didn't provide useful information until above post!

That is only 100k rows, not 100k multirows tables.

Note that it looks like it's for a game server. Remember that if that's the case, mods may consider that against forum rules.
I'll try to make up something soon.

This is not game cheating i try to create international chat for talk on games between players.

This is example how to read data using xml file but is too slow for me.

Global $TIME = 0

Local $msxml = ObjCreate('MSXML2.DOMDocument.6.0')
$msxml.load("users.xml")

setTime("Searching for user name: User1000")
Local $user = $msxml.SelectSingleNode("//users/user[@name='User1000']")
print("name: " & $user.getAttribute("name"))
print("passwd: " & $user.getAttribute("passwd"))
print("isLogin: " & $user.getAttribute("isLogin"))
print("range: " & $user.getAttribute("range"))
print("group: " & $user.getAttribute("group"))
getTimeDiff()

Func setTime($msg="")
    print("setTime: " & $msg)
    $TIME = TimerInit()
EndFunc

Func getTimeDiff()
    print("getTimeDiff: " & Int(TimerDiff($TIME) * 100) / 100 & "ms")
EndFunc

Func print($input)
    Return ConsoleWrite($input & @CRLF)
EndFunc  ;==>Write text to SCITE console

xml file with 1000 example users

users.xml

 

[jchd]

I'm a fervent user of SQLite Expert (there's a free version you can download) and I used it to build something along these lines.

I created a disk DB named 'test.sq3'

I created there a SQL table:
CREATE TABLE [Users](
  [Login] CHAR PRIMARY KEY NOT NULL,
  [CredHash] BLOB NOT NULL,
  [Logged] INT NOT NULL DEFAULT 0,
  [Level] INT NOT NULL DEFAULT 1,
  [Guild] CHAR NOT NULL DEFAULT '') WITHOUT ROWID;

I used another existing DB which holds a table of 109582 english words to populate the table Users:
attach 'C:\Users\...path...\vocab.sq3' as v;
insert into users select word, md5(word), 0, 1, 'guild ' || upper(word) from v.en;
detach v;

That step took 262ms on a slow PC. md5() is an addon function of mine, found in my extension unifuzz (search that).
I used that just for providing an example, that isn't a reference implementation!

Changing some data:
update users set level = 8, guild = 'myguild' where login = 'natation';

Now you can query the Users table:
select * from users where login in ('forte', 'natation');

I've joined the resulting DB; try SQLite Expert free to experiment with it.

You can do that with the sqlite.au3 include file and functions.
Now if a disk file is too slow (even an SSD), you can still use addon functions from extensions I've made available to backup the DB to/from disk/memory. The setup is a little more involved but is only done at startup and termination. Search my posts for SQLite backup and load extension.

Of course, AutoIt being only interpreted makes it a bit slow. Doing the same from C would be hundreds of times faster. DllCalls are slow due to passing data in/out.

 

Test_.7z

Edited February 22, 2020 by jchd

[argumentum]

1 hour ago, jchd said:

WITHOUT ROWID

Why would having no RowID be beneficial or preferable ?, I'm curious 

[jchd]

Since we are (expectedly) only querying by login (username) we don't need a rowid and that saves an unneeded index as well. Even if we have to query by guild a rowid or an alias wouldn't make us any good.

If for instance we would like to make a guild table (for DB integrity) and have the users table field guild refer to that foreign key, an integer primary key would be useful in guild table and users.guild would be of type int as well. In this case, explicitely declaring integer primary key means it's a named alias of rowid, which is beneficial since implicit rowids can change when vaccumed while explicit integer primary keys are left verbatim.

[argumentum]

2 hours ago, jchd said:

md5() is an addon function of mine, found in my extension unifuzz (search that)

I've searched for that and the version I found that can be downloaded,  does not have the md5(). Have you complied another ? 

Edit: found this one from Dec. 2019, and found no "hash" or "md5" in the C source  

Edited February 22, 2020 by argumentum

[jchd]

Oops, sorry, I forgot it's part of another dll! That dll is x86 for now.

Of course in a real-world app, one would store the login and a strong hash of {login, passphrase, salt}.  It's the responsability of the calling app to get login + passphrase and compute the hash from there, in which case the _Crypt* functions would help, then query the DB for a corresponding entry.

I just wanted to show how to make a realistic schema and deal with storage of the various data.

md5.zip

Edited February 22, 2020 by jchd

[Ascer]

Thanks for replay.

This is black magic for me without any Autoit source example i could say ehm..

[Nine]

@jchd That is an interesting statement.  When I was working at Oracle, we were using ROWID all the time even if the table has a unique index.  It was very practical when we were reusing the same row multiple times. The typical usage was when you fetch a row, make some modifications on it, and  then update that row, using the ROWID on the update was by far the fastest way to do it.  Maybe SQLite does not react as Oracle do when manipulating ROWID.