varchar in splay columns

[david_bieber]

Hi All,

I have a large files of around 500mb with around 25 columns which have a imported into KDB.

The file contains a mixture of symbols, varchar and integers in the table which I call tX

For instance a subset of the meta data I import looks like this

meta tX
c     t   f  a
id    s
ti    i
p     f
str1  C
str2  C
str3  C
str4  C
str5  C

I want to store this file as a splay so

1) can create a empty splay table

empty table
sX: ([] id:`symbol$(); ti:`int$() ; p:`float$(); str1:(); str2(); str3(); str4(); str5())

or

table with nulls
sX: ([] id:enlist `; ti:enlist 0Ni ; p:enlist 0n; str1:enlist "None"; str2: enlist "None" str3: enlist "None"; str4: enlist "None"; str5: "None")

2) enumerate and create a splay sX

sX: .Q.en[`:c:/test] sX
`:c:/test/sX/ set sX

3) and upsert the table tX

tX: .Q.en[`:c:/test] tX
`:c:/test/sX/ upsert tX

For the empty sX table (the first one) kdb hangs but for the null sX table (the second one) it works and splays the table as expected.

However if I now type

meta sX

it takes more that 60s to calculate. In fact any calculation is very slow on this splay table. However the calculation on tX (the imported table) is fast. In addition if I cast everything to symbols and then splay the table, the calculations is fast.

I have been trying to replicate this error in a small piece of code without success. It seems to be specific the the table I am loading. Does anyone have any ideas or suggestions of where I am going wrong. I am completely at a loss...

Could there be special characters that are not allowed in varchar?

Is there a limit on the number of varchar columns?

Any advice would be greatly appreciated.

Thanks

David

[trentkg]

For splayed tables there are very strict type requirements. You cannot have 0h lists in your tables. 

run 

distinct type''[sX]

on your table (when it is populated) and ensure that no types are 0h. 

Splaying is also an optimisation, that may come with some overhead. It would not surprise me if your table is not large enough to utilize the speedup of this optimisation. 

[david_bieber]

Hi KDB Group, 

I have found out that if you cast varchar columns to symbols the time required to calculate "meta" of the splay decreases. 

However I still not not understand the relationship between the number of varchar columns in a splay table and the impact on performance. Any insights would be appreciated.

David