Amibroker + COM and RDBMS concepts

mastermind007

Well-Known Member
#1
Today I encountered situation which at first glance appears as a very stubborn bug. However, after closer look, I realize that the fault is not in the Amibroker and hence this share.

Normally, after I start Amibroker, I also start another app that feeds RT data Amibroker thru its COM functions. Once that is done, I concentrate on trading and do not tinker too much with Amibroker.

Today, However after starting Amibroker and its feeder, I uploaded 1 scrip's data manually using File->import function while the feeder app was running.

To my surprise, Amibroker refused to plot newly imported data on the chart, so I imported same thing again. No improvement. :mad:

Checked logs to see the error and nothing was there. :mad::mad:

Checked Quote editor and manually imported data was showing there. :mad::mad::mad:

Imported again; Refreshed few times and Nothing would get Amibroker to plot the manually imported data. :confused::confused:

After 3:30PM, I shutdown the feeder app and almost instantly the data appeared on chart.

This behaviour is more or less correct when we consider software principles of RDBMS wherein database guarantees that concurrent users will not see same data differently.

In other words, Amibroker was preserving the data state that the feeder application was reading and hence it prevented chart from rendering it.
 

TracerBullet

Well-Known Member
#2
Today I encountered situation which at first glance appears as a very stubborn bug. However, after closer look, I realize that the fault is not in the Amibroker and hence this share.

Normally, after I start Amibroker, I also start another app that feeds RT data Amibroker thru its COM functions. Once that is done, I concentrate on trading and do not tinker too much with Amibroker.

Today, However after starting Amibroker and its feeder, I uploaded 1 scrip's data manually using File->import function while the feeder app was running.

To my surprise, Amibroker refused to plot newly imported data on the chart, so I imported same thing again. No improvement. :mad:

Checked logs to see the error and nothing was there. :mad::mad:

Checked Quote editor and manually imported data was showing there. :mad::mad::mad:

Imported again; Refreshed few times and Nothing would get Amibroker to plot the manually imported data. :confused::confused:

After 3:30PM, I shutdown the feeder app and almost instantly the data appeared on chart.

This behaviour is more or less correct when we consider software principles of RDBMS wherein database guarantees that concurrent users will not see same data differently.

In other words, Amibroker was preserving the data state that the feeder application was reading and hence it prevented chart from rendering it.
RDBMS does not imply permanent lock. You may only need lock while the update takes place. Inserts & Selects happen all the time. Anyway, you can see the data in quote editor too so AB db was updated.

Why chart did not refresh manually imported data but continues to refresh COM imports, i dont know. maybe bug?
 

mastermind007

Well-Known Member
#3
RDBMS does not imply permanent lock. You may only need lock while the update takes place. Inserts & Selects happen all the time. Anyway, you can see the data in quote editor too so AB db was updated.

Why chart did not refresh manually imported data but continues to refresh COM imports, i dont know. maybe bug?
No, I've retracted from calling it a bug. I'd hate to call it a feature but this is something that people need to guard against.

Amibroker does not have Commit equivalent (not that I know of, at least) and seems to loosely follows RDBMS concepts by preventing "dirty" read by disallowing data from manual import to interfere with the COM.

Scrip was being updated from feeder also so it was preserved the state in case feeder also attempted to read
 

TracerBullet

Well-Known Member
#4
No, I've retracted from calling it a bug. I'd hate to call it a feature but this is something that people need to guard against.

Amibroker does not have Commit equivalent (not that I know of, at least) and seems to loosely follows RDBMS concepts by preventing "dirty" read by disallowing data from manual import to interfere with the COM.

Scrip was being updated from feeder also so it was preserved the state in case feeder also attempted to read
i dont understand what you are saying. why are you trying to relate AB with Database. AB's data is internal to AB. We dont need to care unless you are reading it directly.
Even for an application with DB, there is no need to freeze view for one application instance. In most IT work you have multiple database servers representing same data connected to much more number of clients that view and modify same database.

1) Why do you think manual import is dirty while COM import is not. Each COM call would be atomic and seperate. There is no need for any memory of previous COM calls and certainly AB wont try to remember source of each import/COM call.

2) Anyway, i tested with RTD feeder running, scrip displayed, manual import of 1 row. I see the data in chart

Edit - Quote Editor is wierd though. It sometimes shows that record (is dated before all data) if i click down at bottom but then it disappers. Chart is fine
Btw, is there any way to stop quote editor from scrolling automatically when Feeder pushes new data. Annoying if you wanted to delete the data
 
Last edited:
#5
Wouldn't this behavior be due to the feeder creating/holding the current working instance of AB?


Happy :)
 

TracerBullet

Well-Known Member
#6
Wouldn't this behavior be due to the feeder creating/holding the current working instance of AB?


Happy :)
Do you mean because AB was started from feeder? It should not make any difference i think.
I always start AB first and then my feeder so that AB does not get closed later. With this manual import displayed on chart immediately. Dunno why it fails for MM.
 

mastermind007

Well-Known Member
#7
Wouldn't this behavior be due to the feeder creating/holding the current working instance of AB?
Do you mean because AB was started from feeder? It should not make any difference i think.
I always start AB first and then my feeder so that AB does not get closed later. With this manual import displayed on chart immediately. Dunno why it fails for MM.
TracerBullet said:
i dont understand what you are saying. why are you trying to relate AB with Database. AB's data is internal to AB. We dont need to care unless you are reading it directly.
Even for an application with DB, there is no need to freeze view for one application instance. In most IT work you have multiple database servers representing same data connected to much more number of clients that view and modify same database.

1) Why do you think manual import is dirty while COM import is not. Each COM call would be atomic and seperate. There is no need for any memory of previous COM calls and certainly AB wont try to remember source of each import/COM call.

2) Anyway, i tested with RTD feeder running, scrip displayed, manual import of 1 row. I see the data in chart

Edit - Quote Editor is wierd though. It sometimes shows that record (is dated before all data) if i click down at bottom but then it disappers. Chart is fine
Btw, is there any way to stop quote editor from scrolling automatically when Feeder pushes new data. Annoying if you wanted to delete the data
No, instance of Amibroker had not started from feeder application. It was started before feeder.

Yes, update happens thru COM and yes, COM does hold some kind of instance pointer to Amibroker while it remains connected

Were you feeding and manually importing rows of the same ticker? I had imported close to 200 ticks manually.

I am comparing it with RDBMS because both are databases. Amibroker seems to be using simple ISAM file structure and file for each ticker is standalone. Its got quite straight forward and simple approach to managing data. RDBMS have a luxury of COMMIT function and they too would not show data inserted by User A to User B until it is committed. Much to my disappointment, saving database is not exactly same as Commit.

Quote Editor of Amibroker is a bit primitive and under-focused and under-developed part of Amibroker.

Once I had inserted wrong data by mistake and deleting it from quote editor was painful. Easier approach was to export all data of all tickers using AFL to flat csv file; correct the CSV file using text editor; Delete all tickers from amibroker and reimport it back.
 

trash

Well-Known Member
#8
Quote Editor of Amibroker is a bit primitive and under-focused and under-developed part of Amibroker.

Once I had inserted wrong data by mistake and deleting it from quote editor was painful. Easier approach was to export all data of all tickers using AFL to flat csv file; correct the CSV file using text editor; Delete all tickers from amibroker and reimport it back.
??

Just mark the wrong lines in the editor and delete them all at once by using either CTRL + LMB or Shift + LMB. In addition there is a "Go to selected" button in quote editor. You click the wrong data in the chart and then you click that button. The selected bar data will be at the bottom of quote editor. And what editor of which software does display quotes of all symbols at once? No MT4, no NT, no MC, no MS.

Anyway if you want to fix quotes of multiple symbols at once then just use exploration and filter date range of wrong data. Then export the result list, delete current symbols of DB and re-import exported data. No need to use csv editor.

Besides there is an even simpler and unbeatably faster method (if you haven't saved data yet after wrong data was imported) -> open a second instance of Amibroker and then open that DB again, close first AB instance, click save DB in second instance and you are done. Everything is back to same state as it was before database was opened and filled with wrong data. Try that with any other software.
 
Last edited:

trash

Well-Known Member
#9
Quotes by TJ:


Tomasz Janeczko said:
Hello,

You are free to try by yourself, the functionality is already there, just setup a mySQL database and connect to it
http://www.amibroker.com/odbc.html

It will be slow as hell. I have mySQL databases on my server (for different purposes) and 10 million records
table is really hard to work with in terms of performance.

I don't know why some people think that I did not try all those options already. I did. Long time ago.

I have been working with commercial databases 20 years ago when I was working for some Swiss company and
I used all those tools. I don't need to "learn available tools" as you suggested.

Commercial databases have way too many layers and each SQL statement goes thru thousands of lines of code:
parsing SQL queries, sending query over TCP/IP or pipe, reading the file(s) (ReadFile), building temporary tables,
merging/sorting and sending results back over TCP/IP or pipe.

AmiBroker skips all those layers and does bare metal call to Windows API and just does one call (ReadFile) directly to memory. Under no circumstance skipping all those layers will be slower than going thru them all.

Best regards,
Tomasz Janeczko
amibroker.com

Tomasz Janeczko said:
Hello,

No, I don't use any 3rd party database product.
AmiBroker is 100% my own development and database engine is developed by myself.
5.63.0 64-bit just supports larger files.

Best regards,
Tomasz Janeczko
amibroker.com
Tomasz Janeczko said:
Toby Riddell said:
I have some experience architecting & managing infrastructure used by hedge funds for systematic trading, including storage of tick data (database sizes in the tens of TB size range). The preferred solutions are things like StreamBase or kdb+, or they write their own. A large memory footprint is necessary, the data is slurped off disk once and then processed in-memory. Even with a very high performance disk I/O system (possibly using arrays of SSDs), you still want to do as little disk I/O as possible (i.e. do it once to load the data into RAM).

SQL databases are used for managing things like lists of products traded, mapping symbologies between trading venues, trade reconciliation, etc., etc. - smaller scale, more general problems where a general-purpose query language is useful/necessary.

Just wanted to offer another perspective...
Hello,

Precisely.
Exactly the same approach (load once and keep in RAM, as little IO as possible) is used by AmiBroker's own database.

Best regards,
Tomasz Janeczko
amibroker.com
Tomasz Janeczko said:
Hello,

"SQL DB *engine* running on its own system will be MUCH faster than a file database"

Pure NONSENSE.
Think again - any SQL database server *IS* using files in the backend.
SQL databases have only one reasonable purpose - serving hundreds of different clients, not one. That is design principle of client-server structure and these databases were designed to server lots of clients. Using one server to serve one client is just waste of resources. The amount of time typical RDBM system spends parsing SQL queries, preparing results and sending them over TCPIP or pipes is huge. Simpler file databases with native APIs avoid this overhead. That's why for example Berkeley DB is NOT relational database and it uses native API (not some generic SQL interface).

Many programs use 3rd party libraries because it is EASIER to do, i.e. less development cost.

As to AmiBroker, it never sacrifices speed. I don't choose easy way. It does not rely on any 3rd party (slow) DB.
AmiBroker native database can not be beaten by ANY technology, as AmiBroker binary files are loaded straight in RAM in single WinAPI call without any 3rd party layers, without parsing any queries, without translation, without data type conversion, code page handling, without temporary tables, and zillions of extra layers that typical 3rd party solution goes thru. Once data are in ram, they are accessed with the speed of single array pointer reference (i.e. SINGLE CPU instruction).


Your SQL DB server during that time will not even start to parse your SQL statement, not to mention reading any data.

AmiBroker DB is orders of magnitude faster than any SQL database.

Well, enough said - good luck using generic SQL databases. After a couple of months of experimenting you will be back and then fully admire AmiBroker speed.

Best regards,
Tomasz Janeczko
amibroker.com
 
Last edited:

TracerBullet

Well-Known Member
#10
COM does hold some kind of instance pointer to Amibroker while it remains connected
Hardly matters. AB will not change behaviour based on what other app does. COM is simply an IPC call, there is no 'connection' i think. Its like calling a function of another process.

Were you feeding and manually importing rows of the same ticker? I had imported close to 200 ticks manually.
yes, imported one tick and it was shown on chart while feeder was running.

I am comparing it with RDBMS because both are databases. Amibroker seems to be using simple ISAM file structure and file for each ticker is standalone. Its got quite straight forward and simple approach to managing data. RDBMS have a luxury of COMMIT function and they too would not show data inserted by User A to User B until it is committed. Much to my disappointment, saving database is not exactly same as Commit.
i dont know what ISAM is. It does not matter what AB uses, thats internal to it. After import through COM, chart is refreshed. There is no reason why same should not happen with manual import
Anyway, it works for me so there may be some issue in your case.