Skip to content

Instantly share code, notes, and snippets.

@a2ndrade

a2ndrade/a.q

Last active August 5, 2016 15:44
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save a2ndrade/d95eb35b6bd419dfd6ab to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save a2ndrade/d95eb35b6bd419dfd6ab to your computer and use it in GitHub Desktop.
Download ZIP

Revisions

  1. a2ndrade revised this gist Aug 5, 2016. 1 changed file with 3 additions and 3 deletions.
    6 changes: 3 additions & 3 deletions a.q
    View file
    Original file line number Diff line number Diff line change
    @@ -215,10 +215,10 @@ getAllRtdbTableHandles:{raze getRtdbTableHandles each 1+til 26}

    ////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
    / all operations with 1 million rows
    /q)\t kb:`e xkey select from `:hdb/bb
    /4
    /q)\t ka:`e xkey select from `:hdb/aa
    /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
  2. a2ndrade revised this gist Jul 27, 2016. No changes.
  3. a2ndrade revised this gist Jul 26, 2016. No changes.
  4. a2ndrade revised this gist Jul 25, 2016. No changes.
  5. a2ndrade revised this gist Jul 25, 2016. No changes.
  6. a2ndrade revised this gist Jul 20, 2016. No changes.
  7. a2ndrade revised this gist Jul 18, 2016. 1 changed file with 0 additions and 235 deletions.
    235 changes: 0 additions & 235 deletions p.q
    View file
    Original file line number Diff line number Diff line change
    @@ -1,235 +0,0 @@
    \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 kb:`e xkey select from `:hdb/bb
    /4
    /q)\t ka:`e xkey select from `:hdb/aa
    /2
    /.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
  8. a2ndrade revised this gist Jul 18, 2016. No changes.
  9. a2ndrade revised this gist Jul 18, 2016. 1 changed file with 235 additions and 0 deletions.
    235 changes: 235 additions & 0 deletions a.q
    View file
    Original file line number Diff line number Diff line change
    @@ -0,0 +1,235 @@
    \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 kb:`e xkey select from `:hdb/bb
    /4
    /q)\t ka:`e xkey select from `:hdb/aa
    /2
    /.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
  10. a2ndrade renamed this gist Jul 18, 2016. 1 changed file with 0 additions and 0 deletions.
    0 a.q → p.q
    View file
    File renamed without changes.
  11. a2ndrade revised this gist Jul 18, 2016. 1 changed file with 35 additions and 8 deletions.
    43 changes: 35 additions & 8 deletions a.q
    View file
    Original file line number Diff line number Diff line change
    @@ -6,9 +6,13 @@
    / 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}
    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 getAllTableHandles}
    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"]
    @@ -22,7 +26,7 @@ generateRandomHdbTable:generateRandomHdbTableFrom[;"abcdefghijklmnopqrstuvwxyz"]

    / RESULTS in MBP with 16 GB, 2.6 GHz Intel Core i7 and SSD:
    / ~1157 HDB Tables;
    /q)resetHdb[]
    resetHdb[]
    /q)\t appendToAllHdbTables[1]
    /323
    /q)\t appendToAllHdbTables[1]
    @@ -53,7 +57,7 @@ generateRandomHdbTable:generateRandomHdbTableFrom[;"abcdefghijklmnopqrstuvwxyz"]
    /739
    /q)\t appendToAllHdbTables[1000]
    /805
    /q)\t appendToAllHdbTables[1000000] / 1 million EVT rows each; total space 19.8GB
    \t appendToAllHdbTables[1000000] / 1 million EVT rows each; total space 19.8GB
    /214871 3.5min
    /q)\t appendToAllHdbTables[1]
    /471
    @@ -95,11 +99,12 @@ generateRandomHdbTable:generateRandomHdbTableFrom[;"abcdefghijklmnopqrstuvwxyz"]
    /////////////////////////////////////////////////////////////////////////////
    / Background Engine

    generateSnapshotTable:{[th;tx]tx:$[-1~tx;0Wj;tx];`e xkey update `u#e from `v xasc select last v by e from th where t <= tx}
    persistSnapshotTable:{[th;tx].[0N!`$ssr[-1_string th;"hdb";"rtdb"];();:;generateSnapshotTable[th;tx]];}
    persistAllSnapshotTables:{[tx]do[c:count ths:2#getAllHdbTableHandles[];th:ths@(-1+count ths)-c-:1;persistSnapshotTable[th;tx]]}
    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]]}

    /q)t persistAllSnapshotTables[-1]
    \t persistAllSnapshotTables[-1]
    /756940 12mins
    /q)\w
    /125152 939524096 939524096 0 0 17179869184
    @@ -206,3 +211,25 @@ getAllRtdbTableHandles:{raze getRtdbTableHandles each 1+til 26}
    /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 kb:`e xkey select from `:hdb/bb
    /4
    /q)\t ka:`e xkey select from `:hdb/aa
    /2
    /.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
  12. a2ndrade revised this gist Nov 12, 2015. 1 changed file with 7 additions and 7 deletions.
    14 changes: 7 additions & 7 deletions a.q
    View file
    Original file line number Diff line number Diff line change
    @@ -10,7 +10,7 @@ getAllHdbTableHandles:{raze getHdbTableHandles each 1+til 26}

    resetHdb:{resetHdbTables each getAllTableHandles}
    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]))]}
    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[]}

    @@ -131,12 +131,12 @@ getAllRtdbTableHandles:{raze getRtdbTableHandles each 1+til 26}

    / 6-way inner join; >700,000 records each table
    / a:get `:rtdb/aa
    / b:([e:`long$((count a)?100000*26)] v:`a$first value flip select e from a)
    / c:([e:`long$((count b)?100000*26)] v:`b$first value flip select e from b)
    / d:([e:`long$((count c)?100000*26)] v:`c$first value flip select e from c)
    / e:([e:`long$((count d)?100000*26)] v:`d$first value flip select e from d)
    / f:([e:`long$((count e)?100000*26)] v:`e$first value flip select e from e)
    / g:([e:`long$((count f)?100000*26)] v:`f$first value flip select e from f)
    / 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
    /----------------------------------------------------------------------------------------------------
  13. a2ndrade revised this gist Nov 10, 2015. 1 changed file with 0 additions and 7 deletions.
    7 changes: 0 additions & 7 deletions a.q
    View file
    Original file line number Diff line number Diff line change
    @@ -137,13 +137,6 @@ getAllRtdbTableHandles:{raze getRtdbTableHandles each 1+til 26}
    / e:([e:`long$((count d)?100000*26)] v:`d$first value flip select e from d)
    / f:([e:`long$((count e)?100000*26)] v:`e$first value flip select e from e)
    / g:([e:`long$((count f)?100000*26)] v:`f$first value flip select e from f)
    / b:`e xkey update `u#e from `v xasc select last v by e from b
    / c:`e xkey update `u#e from `v xasc select last v by e from c
    / c:`e xkey update `u#e from `v xasc select last v by e from c
    / d:`e xkey update `u#e from `v xasc select last v by e from d
    / e:`e xkey update `u#e from `v xasc select last v by e from e
    / f:`e xkey update `u#e from `v xasc select last v by e from f
    / g:`e xkey update `u#e from `v xasc select last v by e from g
    /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
    /----------------------------------------------------------------------------------------------------
  14. a2ndrade revised this gist Nov 10, 2015. 1 changed file with 2 additions and 34 deletions.
    36 changes: 2 additions & 34 deletions a.q
    View file
    Original file line number Diff line number Diff line change
    @@ -21,7 +21,7 @@ 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;
    / ~1157 HDB Tables;
    /q)resetHdb[]
    /q)\t appendToAllHdbTables[1]
    /323
    @@ -99,39 +99,7 @@ generateSnapshotTable:{[th;tx]tx:$[-1~tx;0Wj;tx];`e xkey update `u#e from `v xas
    persistSnapshotTable:{[th;tx].[0N!`$ssr[-1_string th;"hdb";"rtdb"];();:;generateSnapshotTable[th;tx]];}
    persistAllSnapshotTables:{[tx]do[c:count ths:2#getAllHdbTableHandles[];th:ths@(-1+count ths)-c-:1;persistSnapshotTable[th;tx]]}

    /q).Q.w[]
    /used| 123712
    /heap| 67108864
    /peak| 67108864
    /wmax| 0
    /mmap| 0
    /mphy| 17179869184
    /syms| 587
    /symw| 19230
    /q)t createAllSnapshotTables[]
    /587179
    /q).Q.w[]
    /used| 132880
    /heap| 1140850688
    /peak| 1140850688
    /wmax| 0
    /mmap| 0
    /mphy| 17179869184
    /syms| 2851
    /symw| 116538
    /q).Q.gc[]
    /1073741824
    /q).Q.w[]
    /used| 124544
    /heap| 67108864
    /peak| 1140850688
    /wmax| 0
    /mmap| 0
    /mphy| 17179869184
    /syms| 2853
    /symw| 116641

    /q)t createAllSnapshotTables[]
    /q)t persistAllSnapshotTables[-1]
    /756940 12mins
    /q)\w
    /125152 939524096 939524096 0 0 17179869184
  15. a2ndrade revised this gist Nov 10, 2015. 1 changed file with 83 additions and 9 deletions.
    92 changes: 83 additions & 9 deletions a.q
    View file
    Original file line number Diff line number Diff line change
    @@ -1,6 +1,6 @@
    \c 25 300i
    \c 15 300i

    / c: count | ht: historical table | st: snapshot table | rt: realtime table | tx: transaction
    / 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
    @@ -10,10 +10,13 @@ getAllHdbTableHandles:{raze getHdbTableHandles each 1+til 26}

    resetHdb:{resetHdbTables each getAllTableHandles}
    resetHdbTables:{[th].[;();:;([] e:`long$(); v:`char$(); `s#t:`long$())] each th}
    appendToHdbTableFrom:{[ht;c;f].[ht;();,;([]e:c?100000*count f;v:c?f;t:(1+$[0~count tx:select [-1] t from ht;0;first tx@`t]))]}
    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?)

    @@ -92,8 +95,9 @@ appendToAllHdbTables:{[c]appendToHdbTable[;c] each getAllHdbTableHandles[]}
    /////////////////////////////////////////////////////////////////////////////
    / Background Engine

    createSnapshotTable:{[ht;tx]tx:$[-1~tx;0Wj;tx];`e xkey update `u#e from `v xasc select last v by e from ht where t <= tx}
    createAllSnapshotTables:{do[c:count ths:1#getAllHdbTableHandles[];th:ths@(-1+count ths)-c-:1;.[0N!`$ssr[-1_string th;"hdb";"rtdb"];();:;createSnapshotTable[th;-1]]]}
    generateSnapshotTable:{[th;tx]tx:$[-1~tx;0Wj;tx];`e xkey update `u#e from `v xasc select last v by e from th where t <= tx}
    persistSnapshotTable:{[th;tx].[0N!`$ssr[-1_string th;"hdb";"rtdb"];();:;generateSnapshotTable[th;tx]];}
    persistAllSnapshotTables:{[tx]do[c:count ths:2#getAllHdbTableHandles[];th:ths@(-1+count ths)-c-:1;persistSnapshotTable[th;tx]]}

    /q).Q.w[]
    /used| 123712
    @@ -145,6 +149,69 @@ createAllSnapshotTables:{do[c:count ths:1#getAllHdbTableHandles[];th:ths@(-1+cou
    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$first value flip select e from a)
    / c:([e:`long$((count b)?100000*26)] v:`b$first value flip select e from b)
    / d:([e:`long$((count c)?100000*26)] v:`c$first value flip select e from c)
    / e:([e:`long$((count d)?100000*26)] v:`d$first value flip select e from d)
    / f:([e:`long$((count e)?100000*26)] v:`e$first value flip select e from e)
    / g:([e:`long$((count f)?100000*26)] v:`f$first value flip select e from f)
    / b:`e xkey update `u#e from `v xasc select last v by e from b
    / c:`e xkey update `u#e from `v xasc select last v by e from c
    / c:`e xkey update `u#e from `v xasc select last v by e from c
    / d:`e xkey update `u#e from `v xasc select last v by e from d
    / e:`e xkey update `u#e from `v xasc select last v by e from e
    / f:`e xkey update `u#e from `v xasc select last v by e from f
    / g:`e xkey update `u#e from `v xasc select last v by e from g
    /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

    @@ -167,7 +234,14 @@ getAllRtdbTableHandles:{raze getRtdbTableHandles each 1+til 26}
    / Throughput : 10-250 writes per second ****w/o transaction log****

    ////////////////////////////////////////////////////////////////////////////////////
    / Misc

    generateRandomHdbTableFrom:{[c;u]flip`e`v`t!(c?c*4;u@c?count u;1+til c)}
    generateRandomHdbTable:generateRandomHdbTableFrom[;"abcdefghijklmnopqrstuvwxyz"]
    / 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]
  16. a2ndrade revised this gist Nov 9, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion a.q
    View file
    Original file line number Diff line number Diff line change
    @@ -93,7 +93,7 @@ appendToAllHdbTables:{[c]appendToHdbTable[;c] each getAllHdbTableHandles[]}
    / Background Engine

    createSnapshotTable:{[ht;tx]tx:$[-1~tx;0Wj;tx];`e xkey update `u#e from `v xasc select last v by e from ht where t <= tx}
    createAllSnapshotTables:{do[c:count ths:getAllHdbTableHandles[];0N!th:ths@(-1+count ths)-c-:1;.[`$ssr[-1_string th;"hdb";"rtdb"];();:;createSnapshotTable[th;-1]]]}
    createAllSnapshotTables:{do[c:count ths:1#getAllHdbTableHandles[];th:ths@(-1+count ths)-c-:1;.[0N!`$ssr[-1_string th;"hdb";"rtdb"];();:;createSnapshotTable[th;-1]]]}

    /q).Q.w[]
    /used| 123712
  17. a2ndrade revised this gist Nov 9, 2015. 1 changed file with 3 additions and 3 deletions.
    6 changes: 3 additions & 3 deletions a.q
    View file
    Original file line number Diff line number Diff line change
    @@ -18,7 +18,7 @@ appendToAllHdbTables:{[c]appendToHdbTable[;c] each getAllHdbTableHandles[]}
    / Historical DB (sync or async write?)

    / RESULTS in MBP with 16 GB, 2.6 GHz Intel Core i7 and SSD:
    / 1157 HDB Tables; 1 million EVT rows each; total space 19.8GB
    / 1157 HDB Tables;
    /q)resetHdb[]
    /q)\t appendToAllHdbTables[1]
    /323
    @@ -50,7 +50,7 @@ appendToAllHdbTables:{[c]appendToHdbTable[;c] each getAllHdbTableHandles[]}
    /739
    /q)\t appendToAllHdbTables[1000]
    /805
    /q)\t appendToAllHdbTables[1000000]
    /q)\t appendToAllHdbTables[1000000] / 1 million EVT rows each; total space 19.8GB
    /214871 3.5min
    /q)\t appendToAllHdbTables[1]
    /471
    @@ -128,7 +128,7 @@ createAllSnapshotTables:{do[c:count ths:getAllHdbTableHandles[];0N!th:ths@(-1+co
    /symw| 116641

    /q)t createAllSnapshotTables[]
    /756940
    /756940 12mins
    /q)\w
    /125152 939524096 939524096 0 0 17179869184
    /q).Q.gc[]
  18. a2ndrade revised this gist Nov 9, 2015. 1 changed file with 148 additions and 16 deletions.
    164 changes: 148 additions & 16 deletions a.q
    View file
    Original file line number Diff line number Diff line change
    @@ -1,26 +1,152 @@
    \c 25 200i
    \c 25 300i

    / c: count | ht: historical table | st: snapshot table | rt: realtime table | tx: transaction
    generateRandomHdbFrom:{[c;u]flip`e`v`t!(c?c*4;u@c?count u;1+til c)}
    generateRandomHdb:generateRandomHdbFrom[;"abcdefghijklmnopqrstuvwxyz"]
    appendToHdbFrom:{[ht;c;u]ht upsert flip(c?100000*count u;c?u;(1+$[0~count lastTx:select [-1] t from ht;0;first lastTx@`t]))}
    appendToHdb:appendToHdbFrom[;;"abcdefghijklmnopqrstuvwxyz"]

    createSnapshotTable:{[ht;tx]tx:$[-1~tx;0Wj;tx];s:`v xasc select e,v from ht where t <= tx;w:last each value group s@`e;`e xkey s@w}
    / 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}

    / input: 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: 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
    generateRandomTableHandles:{[crossProductAlphabetSize]`$(":db/",/:raze T,\:/:T:cut[crossProductAlphabetSize;"abcdefghijklmnopqrstuvwxyz"]),'"/"}
    resetHdb:{resetHdbTables each getAllTableHandles}
    resetHdbTables:{[th].[;();:;([] e:`long$(); v:`char$(); `s#t:`long$())] each th}
    appendToHdbTableFrom:{[ht;c;f].[ht;();,;([]e:c?100000*count f;v:c?f;t:(1+$[0~count tx:select [-1] t from ht;0;first tx@`t]))]}
    appendToHdbTable:appendToHdbTableFrom[;;"abcdefghijklmnopqrstuvwxyz"]
    appendToAllHdbTables:{[c]appendToHdbTable[;c] each getAllHdbTableHandles[]}

    resetHdbTables:{[th]set[;([] e:`long$(); v:`char$(); t:`long$())] each th}
    /////////////////////////////////////////////////////////////////////////////
    / Historical DB (sync or async write?)

    resetHdb:{}
    / RESULTS in MBP with 16 GB, 2.6 GHz Intel Core i7 and SSD:
    / 1157 HDB Tables; 1 million EVT rows each; total space 19.8GB
    /q)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
    /q)\t appendToAllHdbTables[1000000]
    /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

    / RUN
    / appendToTable[;1000000] each th
    / appendToTable[;10] each th
    /////////////////////////////////////////////////////////////////////////////
    / Background Engine

    / RESULTS in MBP with 16 GB, 2.6 GHz Intel Core i7 and SSD:
    createSnapshotTable:{[ht;tx]tx:$[-1~tx;0Wj;tx];`e xkey update `u#e from `v xasc select last v by e from ht where t <= tx}
    createAllSnapshotTables:{do[c:count ths:getAllHdbTableHandles[];0N!th:ths@(-1+count ths)-c-:1;.[`$ssr[-1_string th;"hdb";"rtdb"];();:;createSnapshotTable[th;-1]]]}

    /q).Q.w[]
    /used| 123712
    /heap| 67108864
    /peak| 67108864
    /wmax| 0
    /mmap| 0
    /mphy| 17179869184
    /syms| 587
    /symw| 19230
    /q)t createAllSnapshotTables[]
    /587179
    /q).Q.w[]
    /used| 132880
    /heap| 1140850688
    /peak| 1140850688
    /wmax| 0
    /mmap| 0
    /mphy| 17179869184
    /syms| 2851
    /symw| 116538
    /q).Q.gc[]
    /1073741824
    /q).Q.w[]
    /used| 124544
    /heap| 67108864
    /peak| 1140850688
    /wmax| 0
    /mmap| 0
    /mphy| 17179869184
    /syms| 2853
    /symw| 116641

    /q)t createAllSnapshotTables[]
    /756940
    /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}

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

    / 169 Tables
    / 169 million rows appended in 22 seg. total ~2.87 GB (169 tables, 1 million each)
    @@ -38,4 +164,10 @@ resetHdb:{}
    / 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
    / Throughput : 10-250 writes per second ****w/o transaction log****

    ////////////////////////////////////////////////////////////////////////////////////
    / Misc

    generateRandomHdbTableFrom:{[c;u]flip`e`v`t!(c?c*4;u@c?count u;1+til c)}
    generateRandomHdbTable:generateRandomHdbTableFrom[;"abcdefghijklmnopqrstuvwxyz"]
  19. a2ndrade revised this gist Nov 8, 2015. No changes.
  20. a2ndrade revised this gist Nov 8, 2015. 1 changed file with 3 additions and 1 deletion.
    4 changes: 3 additions & 1 deletion a.q
    View file
    Original file line number Diff line number Diff line change
    @@ -12,7 +12,9 @@ createSnapshotTable:{[ht;tx]tx:$[-1~tx;0Wj;tx];s:`v xasc select e,v from ht wher
    / output: 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
    generateRandomTableHandles:{[crossProductAlphabetSize]`$(":db/",/:raze T,\:/:T:cut[crossProductAlphabetSize;"abcdefghijklmnopqrstuvwxyz"]),'"/"}

    persistEmptyTables:{[th]set[;([] e:`long$(); v:`char$(); t:`long$())] each th}
    resetHdbTables:{[th]set[;([] e:`long$(); v:`char$(); t:`long$())] each th}

    resetHdb:{}

    / RUN
    / appendToTable[;1000000] each th
  21. a2ndrade renamed this gist Nov 7, 2015. 1 changed file with 0 additions and 0 deletions.
    0 atomic.q → a.q
    View file
    File renamed without changes.
  22. a2ndrade revised this gist Nov 7, 2015. 1 changed file with 4 additions and 4 deletions.
    8 changes: 4 additions & 4 deletions atomic.q
    View file
    Original file line number Diff line number Diff line change
    @@ -14,9 +14,9 @@ generateRandomTableHandles:{[crossProductAlphabetSize]`$(":db/",/:raze T,\:/:T:c

    persistEmptyTables:{[th]set[;([] e:`long$(); v:`char$(); t:`long$())] each th}

    / run
    appendToTable[;1000000] each th
    appendToTable[;10] each th
    / RUN
    / appendToTable[;1000000] each th
    / appendToTable[;10] each th

    / RESULTS in MBP with 16 GB, 2.6 GHz Intel Core i7 and SSD:

    @@ -36,4 +36,4 @@ appendToTable[;10] each th
    / 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
    / Throughput : 10-250 writes per second w/o transaction log
  23. a2ndrade revised this gist Nov 7, 2015. 1 changed file with 17 additions and 15 deletions.
    32 changes: 17 additions & 15 deletions atomic.q
    View file
    Original file line number Diff line number Diff line change
    @@ -1,20 +1,22 @@
    / util
    appendToTable:{[tableHandle;numberOfRows]
    V:"abcdefghijklmnopqrstuvwxyz" / universe of values
    e:numberOfRows?1000000000 / 1 billion possible entities
    v:numberOfRows?V / select #rows value
    t:1+$[0N~ct:last get[tableHandle]@`t;0;ct] / next transaction id
    .[tableHandle;();,;flip`e`v`t!(e;v;t)]
    :e}

    / setup
    N:14 / 1,2,3,4,6,8,12,14 yield 676,169,81,49,25,16,9,4 combinations, respectively
    tableHandles:`$(":db/",/:raze T,\:/:T:cut[N;"abcdefghijklmnopqrstuvwxyz"]),'"/"
    \c 25 200i

    / c: count | ht: historical table | st: snapshot table | rt: realtime table | tx: transaction
    generateRandomHdbFrom:{[c;u]flip`e`v`t!(c?c*4;u@c?count u;1+til c)}
    generateRandomHdb:generateRandomHdbFrom[;"abcdefghijklmnopqrstuvwxyz"]
    appendToHdbFrom:{[ht;c;u]ht upsert flip(c?100000*count u;c?u;(1+$[0~count lastTx:select [-1] t from ht;0;first lastTx@`t]))}
    appendToHdb:appendToHdbFrom[;;"abcdefghijklmnopqrstuvwxyz"]

    createSnapshotTable:{[ht;tx]tx:$[-1~tx;0Wj;tx];s:`v xasc select e,v from ht where t <= tx;w:last each value group s@`e;`e xkey s@w}

    / input: 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: 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
    generateRandomTableHandles:{[crossProductAlphabetSize]`$(":db/",/:raze T,\:/:T:cut[crossProductAlphabetSize;"abcdefghijklmnopqrstuvwxyz"]),'"/"}

    persistEmptyTables:{[th]set[;([] e:`long$(); v:`char$(); t:`long$())] each th}

    / run
    set[;([] e:`long$(); v:`char$(); t:`long$())] each tableHandles
    appendToTable[;1000000] each tableHandles
    appendToTable[;10] each tableHandles
    appendToTable[;1000000] each th
    appendToTable[;10] each th

    / RESULTS in MBP with 16 GB, 2.6 GHz Intel Core i7 and SSD:

  24. a2ndrade revised this gist Nov 7, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion atomic.q
    View file
    Original file line number Diff line number Diff line change
    @@ -3,7 +3,7 @@ appendToTable:{[tableHandle;numberOfRows]
    V:"abcdefghijklmnopqrstuvwxyz" / universe of values
    e:numberOfRows?1000000000 / 1 billion possible entities
    v:numberOfRows?V / select #rows value
    t:numberOfRows#1+last get[tableHandle]@`t / next transaction id
    t:1+$[0N~ct:last get[tableHandle]@`t;0;ct] / next transaction id
    .[tableHandle;();,;flip`e`v`t!(e;v;t)]
    :e}

  25. a2ndrade revised this gist Nov 7, 2015. 1 changed file with 1 addition and 1 deletion.
    2 changes: 1 addition & 1 deletion atomic.q
    View file
    Original file line number Diff line number Diff line change
    @@ -34,4 +34,4 @@ appendToTable[;10] each tableHandles
    / 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
    / Throughput : 10-250 writes per second w/o transaction log
  26. a2ndrade revised this gist Nov 7, 2015. 1 changed file with 6 additions and 4 deletions.
    10 changes: 6 additions & 4 deletions atomic.q
    View file
    Original file line number Diff line number Diff line change
    @@ -1,4 +1,4 @@
    /util
    / util
    appendToTable:{[tableHandle;numberOfRows]
    V:"abcdefghijklmnopqrstuvwxyz" / universe of values
    e:numberOfRows?1000000000 / 1 billion possible entities
    @@ -8,8 +8,8 @@ appendToTable:{[tableHandle;numberOfRows]
    :e}

    / setup
    n:14 / 1,2,3,4,6,8,12,14 yield 676,169,81,49,25,16,9,4 combinations, respectively
    tableHandles:`$(":db/",/:raze T,\:/:T:cut[n;"abcdefghijklmnopqrstuvwxyz"]),'"/"
    N:14 / 1,2,3,4,6,8,12,14 yield 676,169,81,49,25,16,9,4 combinations, respectively
    tableHandles:`$(":db/",/:raze T,\:/:T:cut[N;"abcdefghijklmnopqrstuvwxyz"]),'"/"

    / run
    set[;([] e:`long$(); v:`char$(); t:`long$())] each tableHandles
    @@ -32,4 +32,6 @@ appendToTable[;10] each tableHandles

    / 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
    / after that, 10 rows are appended to all 4 tables in ~4ms

    / Throughput : 10-250 writes per second
  27. a2ndrade revised this gist Nov 7, 2015. 1 changed file with 8 additions and 4 deletions.
    12 changes: 8 additions & 4 deletions atomic.q
    View file
    Original file line number Diff line number Diff line change
    @@ -19,13 +19,17 @@ appendToTable[;10] each tableHandles
    / RESULTS in MBP with 16 GB, 2.6 GHz Intel Core i7 and SSD:

    / 169 Tables
    / 169 million rows appended in 22 seg. total 2.88 GB (169 tables, 1 million each)
    / 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 400MB (25 tables, 1 million each)
    / 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 140MB (9 tables, 1 million each)
    / after that, 10 rows are appended to all 9 tables in ~8ms
    / 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
  28. a2ndrade revised this gist Nov 7, 2015. 1 changed file with 6 additions and 2 deletions.
    8 changes: 6 additions & 2 deletions atomic.q
    View file
    Original file line number Diff line number Diff line change
    @@ -8,7 +8,7 @@ appendToTable:{[tableHandle;numberOfRows]
    :e}

    / setup
    n:12 / 1,2,3,4,6,8,12,14 yield 676,169,81,49,25,16,9,4 combinations, respectively
    n:14 / 1,2,3,4,6,8,12,14 yield 676,169,81,49,25,16,9,4 combinations, respectively
    tableHandles:`$(":db/",/:raze T,\:/:T:cut[n;"abcdefghijklmnopqrstuvwxyz"]),'"/"

    / run
    @@ -24,4 +24,8 @@ appendToTable[;10] each tableHandles

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

    / 9 Tables
    / 9 million rows appended in 1.5 seg. total 140MB (9 tables, 1 million each)
    / after that, 10 rows are appended to all 9 tables in ~8ms
  29. a2ndrade revised this gist Nov 7, 2015. 1 changed file with 19 additions and 8 deletions.
    27 changes: 19 additions & 8 deletions atomic.q
    View file
    Original file line number Diff line number Diff line change
    @@ -1,16 +1,27 @@
    / sample data

    n:2 / 1,2,3,4,6,8,12,14 yield 676,169,81,49,25,16,9,4 combinations, respectively
    tableHandles:`$(":db/",/:raze T,\:/:T:cut[n;"abcdefghijklmnopqrstuvwxyz"]),'"/"
    set[;([] e:`long$(); v:`char$(); t:`long$())] each tableHandles / persist
    appendToTable[;1000000] each tableHandles / append 1 million records

    /util
    appendToTable:{[tableHandle;numberOfRows]
    V:"abcdefghijklmnopqrstuvwxyz" / universe of values
    e:numberOfRows?1000000000 / 1 billion possible entities
    v:numberOfRows?V / select #rows value
    t:numberOfRows#1+last get[tableHandle]@`t / next transaction id
    .[tableHandle;();,;flip`e`v`t!(e;v;t)]
    :e}

    / setup
    n:12 / 1,2,3,4,6,8,12,14 yield 676,169,81,49,25,16,9,4 combinations, respectively
    tableHandles:`$(":db/",/:raze T,\:/:T:cut[n;"abcdefghijklmnopqrstuvwxyz"]),'"/"

    / run
    set[;([] e:`long$(); v:`char$(); t:`long$())] each tableHandles
    appendToTable[;1000000] each tableHandles
    appendToTable[;10] each tableHandles

    / RESULTS in MBP with 16 GB, 2.6 GHz Intel Core i7 and SSD:

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

    \t appendToTable[`:db/sample;1000000] / append 1 million entries
    / 25 Tables
    / 25 million rows appended in 3.7 seg. total 400MB (25 tables, 1 million each)
    / after that, 10 rows are appended to all 25 tables in ~25ms
  30. a2ndrade revised this gist Nov 7, 2015. 1 changed file with 3 additions and 3 deletions.
    6 changes: 3 additions & 3 deletions atomic.q
    View file
    Original file line number Diff line number Diff line change
    @@ -1,15 +1,15 @@
    / sample data

    n:14 / 1,2,3,4,6,8,12,14 yield 676,169,81,49,25,16,9,4 combinations, respectively
    tableHandles:`$(":db/",/:raze T,\:/:T:cut[14;"abcdefghijklmnopqrstuvwxyz"]),'"/"
    n:2 / 1,2,3,4,6,8,12,14 yield 676,169,81,49,25,16,9,4 combinations, respectively
    tableHandles:`$(":db/",/:raze T,\:/:T:cut[n;"abcdefghijklmnopqrstuvwxyz"]),'"/"
    set[;([] e:`long$(); v:`char$(); t:`long$())] each tableHandles / persist
    appendToTable[;1000000] each tableHandles / append 1 million records

    appendToTable:{[tableHandle;numberOfRows]
    V:"abcdefghijklmnopqrstuvwxyz" / universe of values
    e:numberOfRows?1000000000 / 1 billion possible entities
    v:numberOfRows?V / select #rows value
    t:numberOfRows#1+last get[`:db/sample]@`t / next transaction id
    t:numberOfRows#1+last get[tableHandle]@`t / next transaction id
    .[tableHandle;();,;flip`e`v`t!(e;v;t)]
    :e}