RE: [personal kdb+] unique index

Community Support
,
1

User-Agent: Workspace Webmail 5.16.0Message-Id: <20160101200302.85f80dae80d1d2f2e266ec6278e6cbe8.4a08ca37d3.wbe@email07.europe.secureserver.net>From: “David Demner (AquaQ)” <david.demner>To: personal-kdbplus@googlegroups.comSubject: RE: [personal kdb+] unique indexDate: Fri, 01 Jan 2016 20:03:02 -0700Mime-Version: 1.0

Selecting that many records basically returns the whole table so you don’t get a gain from g# but you do pay the overhead of having it. Using only 10 cust, g# is faster:


q)n:1300000 q)a:([]cust:n?`8; v1:n?100; v2:n?100; v3:n?100) q)b:10#distinct a`cust q)\ts select from a where cust in b 19 18874704 q)update `g#cust from `a `a q)\ts select from a where cust in b 0 1008
vs using 100000 cust as below is way slower:
q)a:([]cust:n?`8; v1:n?100; v2:n?100; v3:n?100) q)b:100000#distinct a`cust q)\ts select from a where cust in b 23 18874704 q)update `g#cust from `a `a q)\ts select from a where cust in b 160 4719024

`u# won't help because it's not the table that you're applying the index to but the list; and it wouldn't help anyway for 100k cust (same problem as above)
2

So I guess the query searches table ‘a’ for each value in ‘b’,   so indexing on ‘b’ doesn’t help.

The following query runs much faster but unfortunately only finds the first occurrence, whereas I need all occurrences. I wonder if there is way to tweak this without a big hit on performance

\t a (a`cust)?b
0