Atomic

a.q

\c 15 300i

/ c: count | th: table handle | ht: historical table | st: snapshot table | rt: realtime table | tx: transaction

/ source:  "abcdefghijklmnopqrstuvwxyz" (size=26)
/ input: (# of groups)     1   2  3  4  5  6  7  8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
/ output (# of handles): 676 169 81 49 36 25 16 16 9  9  9  9  4  4  4  4  4  4  4  4  4  4  4  4  4  1
getHdbTableHandles:{[crossProductAlphabetSize]`$(":hdb/",/:raze g,\:/:g:cut[crossProductAlphabetSize;"abcdefghijklmnopqrstuvwxyz"]),'"/"}
getAllHdbTableHandles:{
  /:raze getHdbTableHandles each 1+til 26 / all tables ~1200
  /:raze getHdbTableHandles each 4 5 / 74 tables
  /:raze getHdbTableHandles each 25 26 / 5 tables
  `:hdb/aa/`:hdb/bb/}

resetHdb:{resetHdbTables each getAllHdbTableHandles[]}
resetHdbTables:{[th].[;();:;([] e:`long$(); v:`char$(); `s#t:`long$())] each th}
appendToHdbTableFrom:{[th;c;f].[th;();,;([]e:c?100000*count f;v:c?f;t:(1+$[0~count tx:select [-1] t from th;0;first tx`t]))]}
appendToHdbTable:appendToHdbTableFrom[;;"abcdefghijklmnopqrstuvwxyz"]
appendToAllHdbTables:{[c]appendToHdbTable[;c] each getAllHdbTableHandles[]}

generateRandomHdbTableFrom:{[c;u]flip`e`v`t!(c?c*4;u@c?count u;1+til c)}
generateRandomHdbTable:generateRandomHdbTableFrom[;"abcdefghijklmnopqrstuvwxyz"]

/////////////////////////////////////////////////////////////////////////////
/ Historical DB (sync or async write?)

/ RESULTS in MBP with 16 GB, 2.6 GHz Intel Core i7 and SSD:
/ ~1157 HDB Tables;
resetHdb[]
/q)\t appendToAllHdbTables[1]
/323
/q)\t appendToAllHdbTables[1]
/308
/q)\t appendToAllHdbTables[1]
/287
/q)\t appendToAllHdbTables[1]
/288
/q)\t appendToAllHdbTables[1]
/292
/q)\t appendToAllHdbTables[10]
/414
/q)\t appendToAllHdbTables[10]
/414
/q)\t appendToAllHdbTables[10]
/419
/q)\t appendToAllHdbTables[100]
/421
/q)\t appendToAllHdbTables[100]
/424
/q)\t appendToAllHdbTables[100]
/438
/q)\t appendToAllHdbTables[1000]
/696
/q)\t appendToAllHdbTables[1000]
/800
/q)\t appendToAllHdbTables[1000]
/739
/q)\t appendToAllHdbTables[1000]
/805
\t appendToAllHdbTables[1000000] /  1 million EVT rows each; total space 19.8GB
/214871 3.5min
/q)\t appendToAllHdbTables[1]
/471
/q)\t appendToAllHdbTables[1]
/479
/q)\t appendToAllHdbTables[1]
/468
/q)\t appendToAllHdbTables[1]
/481
/q)\t appendToAllHdbTables[10]
/508
/q)\t appendToAllHdbTables[10]
/481
/q)\t appendToAllHdbTables[10]
/479
/q)\t appendToAllHdbTables[10]
/479
/q)\t appendToAllHdbTables[10]
/477
/q)\t appendToAllHdbTables[100]
/715
/q)\t appendToAllHdbTables[100]
/829
/q)\t appendToAllHdbTables[100]
/728
/q)\t appendToAllHdbTables[100]
/733
/q)\t appendToAllHdbTables[1000]
/883
/q)\t appendToAllHdbTables[1000]
/858
/q)\t appendToAllHdbTables[1000]
/1062
/q)\t appendToAllHdbTables[1000]
/918
/q)\t appendToAllHdbTables[1000]
/918

/////////////////////////////////////////////////////////////////////////////
/ Background Engine

generateSnapshotTable:{[th;tx]tx:$[-1~tx;0Wj;tx];() xkey update `u#e from `v xasc select last v by e from th where t <= tx}
persistSnapshotTable:{[th;tx].[0N!`$ssr[string th;"hdb";"rtdb"];();:;generateSnapshotTable[th;tx]];}
// tables are written one at a time b/c we cannot fit all of them in memory
persistAllSnapshotTables:{[tx]do[c:count ths:getAllHdbTableHandles[];th:ths@(-1+count ths)-c-:1;persistSnapshotTable[th;tx]]}

\t persistAllSnapshotTables[-1]
/756940 12mins
/q)\w
/125152 939524096 939524096 0 0 17179869184
/q).Q.gc[]
/872415232
/q)\w
/125152 67108864 939524096 0 0 17179869184

/////////////////////////////////////////////////////////////////////////////
/ Real-time DB

/ source:  "abcdefghijklmnopqrstuvwxyz" (size=26)
/ input: (# of groups)     1   2  3  4  5  6  7  8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
/ output (# of handles): 676 169 81 49 36 25 16 16 9  9  9  9  4  4  4  4  4  4  4  4  4  4  4  4  4  1
getRtdbTableHandles:{[crossProductAlphabetSize]`$(":rtdb/",/:raze g,\:/:g:cut[crossProductAlphabetSize;"abcdefghijklmnopqrstuvwxyz"])}
getAllRtdbTableHandles:{raze getRtdbTableHandles each 1+til 26}

/ range queries / paging
/q)a:get `:rtdb/aa
/q)\t:100 select from a where v="f"
/3
/q)b:`e xasc a
/q)\t:100 select from b where v="f"
/255
/q)\t:1000 select [25] from a where v="l"
/3
/[A\t:1000 select [25] from b where v="l"
/2108

/ 6-way inner join; >700,000 records each table
/ a:get `:rtdb/aa
/ b:([e:`long$((count a)?100000*26)] v:`a$exec e from a)
/ c:([e:`long$((count b)?100000*26)] v:`b$exec e from b)
/ d:([e:`long$((count c)?100000*26)] v:`c$exec e from c)
/ e:([e:`long$((count d)?100000*26)] v:`d$exec e from d)
/ f:([e:`long$((count e)?100000*26)] v:`e$exec e from e)
/ g:([e:`long$((count f)?100000*26)] v:`f$exec e from f)
/q)select e, v, v.e, v.v, v.v.e, v.v.v, v.v.v.e, v.v.v.v, v.v.v.v.e, v.v.v.v.v, v.v.v.v.v.e, v.v.v.v.v.v, v.v.v.v.v.v.e, v.v.v.v.v.v.v from g where v.v.v.v.v.v.v="a"
/e       v       e1      v1      e2      v2      e3      v3      e4      v4      e5      v5   e6   v6
/----------------------------------------------------------------------------------------------------
/2551962 1212660 1212660 562791  562791  302528  302528  1944305 1944305 226886  226886  59   59   a
/330811  2425946 2425946 682145  682145  2557747 2557747 2130724 2130724 1984860 1984860 167  167  a
/604934  138123  138123  844191  844191  1274836 1274836 1442355 1442355 75099   75099   173  173  a
/537687  237218  237218  2496620 2496620 1584186 1584186 116213  116213  1680022 1680022 226  226  a
/2241894 1304112 1304112 2211893 2211893 1443621 1443621 405374  405374  1498402 1498402 246  246  a
/2597440 604817  604817  2206476 2206476 954912  954912  1965228 1965228 2141420 2141420 620  620  a
/2048682 1200358 1200358 2496026 2496026 2321398 2321398 1250701 1250701 282990  282990  691  691  a
/2260400 1829468 1829468 1918513 1918513 665858  665858  1711819 1711819 2107293 2107293 760  760  a
/2062671 958318  958318  1137506 1137506 1255301 1255301 1676709 1676709 623108  623108  763  763  a
/1484085 1068714 1068714 432969  432969  255655  255655  1150057 1150057 771105  771105  773  773  a
/802785  923265  923265  1809151 1809151 837898  837898  1219314 1219314 1733637 1733637 795  795  a
/342155  1008619 1008619 395634  395634  185095  185095  1831358 1831358 1631805 1631805 900  900  a
/842763  1278366 1278366 1914570 1914570 1728390 1728390 2265205 2265205 1694638 1694638 997  997  a
/1955063 2085073 2085073 68989   68989   1868701 1868701 1987359 1987359 1107222 1107222 1014 1014 a
/2184095 743466  743466  1413979 1413979 2479078 2479078 2530141 2530141 769094  769094  1036 1036 a
/2348590 339783  339783  1682360 1682360 1386295 1386295 1380836 1380836 2493437 2493437 1039 1039 a
/2551811 2443367 2443367 603514  603514  1231797 1231797 2401962 2401962 1398119 1398119 1093 1093 a
/1703181 2382242 2382242 664449  664449  1002290 1002290 2405670 2405670 305844  305844  1180 1180 a
/1958907 1489006 1489006 2584627 2584627 1860499 1860499 2304432 2304432 1498434 1498434 1257 1257 a
/1589195 324759  324759  789424  789424  1047664 1047664 1084867 1084867 1206856 1206856 1339 1339 a
/..
/q).Q.w[]
/used| 135396288 / 135MB
/heap| 201326592 / 200MB
/peak| 268435456
/wmax| 0
/mmap| 0
/mphy| 17179869184
/syms| 1768
/symw| 68991
/q)\t select e, v, v.e, v.v, v.v.e, v.v.v, v.v.v.e, v.v.v.v, v.v.v.v.e, v.v.v.v.v, v.v.v.v.v.e, v.v.v.v.v.v, v.v.v.v.v.v.e, v.v.v.v.v.v.v from g where v.v.v.v.v.v.v="a"
/71

/////////////////////////////////////////////////////////////////////////////
/ Earlier TESTS

/ 169 Tables
/ 169 million rows appended in 22 seg. total ~2.87 GB (169 tables, 1 million each)
/ after that, 10 rows are appended to all 169 tables in 50-200 ms

/ 25 Tables
/ 25 million rows appended in 3.7 seg. total ~425MB (25 tables, 1 million each)
/ after that, 10 rows are appended to all 25 tables in ~25ms

/ 9 Tables
/ 9 million rows appended in 1.5 seg. total ~153MB (9 tables, 1 million each)
/ after that, 10 rows are appended to all 9 tables in ~8ms

/ 4 Tables
/ 4 million rows appended in 0.6 seg. total ~68MB (4 tables, 1 million each)
/ after that, 10 rows are appended to all 4 tables in ~4ms

/ Throughput : 10-250 writes per second ****w/o transaction log****

////////////////////////////////////////////////////////////////////////////////////
/ Experimental

  / ([] e:`long$(); v:`a$`e!`long$(); t:`long$())
/ value flip select e from a
/ {[th;c;f]([e:`long$()] v:`long$())}[`;10;"abcdefghijklmnopqrstuvwxyz"]
/getHdbTableTypeFrom:{[th;types]n:ssr[-1_string th;":hdb/";""];m:((*) over `int$n) mod count types;types@m}
/getHdbTableType:getHdbTableTypeFrom[;`boolean`int`long`float`datetime]
/findForeignKeyTableHandle:{[th;prefix;suffix]$[(not "aa"~s)&((~)over s)&2~count s:ssr[(-1*count suffix)_string th;prefix;""];`$prefix,2#`char$-1+`int$s@0,suffix;`]}
/findSnapshotTableForeignKey:findForeignKeyTableHandle[;":rtdb/";""]
/getSnapshotTableTypeFrom:{[th;types]n:ssr[string th;":rtdb/";""];m:((*) over `int$n) mod count types;$[`fk~t:types@m;`fk;t]}
/getSnapshotTableType:getSnapshotTableTypeFrom[;`int`float`datetime]


////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
/ all operations with 1 million rows
/q)\t ka:`e xkey select from `:rtdb/aa/
/2
/q)\t kb:`e xkey select from `:rtdb/bb/
/4
/.Q.w[]`mmap > 0, i.e. ka & kb are memory-mapped, not loaded into memory!
/\t ij[ka;kb]
/~254 for hdb; ~167 for rtdb
/ids:100?exec e from ka
/q)\t:100 select from ka where e in ids
/23 /cold + `u#e
/q)\t:100 select from ka where e in ids
/4 /hot + `u#e
/q)\t:100 select from `:rtdb/aa/ where e in ids
/49 / with `u#e
/q)\t:100 select from `:rtdb/aa/ where e in ids
/1862 / without `u#e
/q)\t:100 select count v from `:rtdb/aa/ where v in ("aeiou")
/44