How to optimize a batch pivotization?

Question

I have a datetime list (which for some reason I call it column date) containing over 1k datetime.

adates:2017.10.20T00:02:35.650 2017.10.20T01:57:13.454 ...

For each of these dates I need to select the data from some table, then pivotize by a column t i.e. expiry, add the corresponding date datetime as column to the pivotized table and stitch together the pivotization for all the dates. Note that I should be able to identify which pivotization corresponds to a date and that's why I do it one by one:

fPivot:{[adate;accypair]
     t1:select from volatilitysurface_smile where date=adate,ccypair=accypair;
     mycols:`atm`s10c`s10p`s25c`s25p;
     t2:`t xkey 0!exec mycols#(stype!mid) by t:t from t1;
     t3:`t xkey select distinct t,tenor,xi,volofvol,delta_type,spread from t1;
     result:ej[`t;t2;t3];
     :result}

I then call this function for every datetime adates as follows:

raze {[accypair;adate] `date xcols update date:adate from fPivot[adate;accypair] }[`EURCHF] @/: adates;

this takes about 90s. I wonder if there is a better way e.g. do a big pivotization rather than running one pivotization per date and then stitching it all together. The big issue I see is that I have no apparent way to include the date attribute as part of the pivotization and the date can not be lost otherwise I can't reconciliate the results.

Answer 1

OK I solved the issue by creating a batch version of the pivotization that keeps the date (datetime) table field when doing the group by bit needed to pivot i.e. by t:t from ... to by date:date,t:t from .... It went from 90s down to 150 milliseconds.

fBatchPivot:{[adates;accypair]
    t1:select from volatilitysurface_smile where date in adates,ccypair=accypair;
    mycols:`atm`s10c`s10p`s25c`s25p;
    t2:`date`t xkey 0!exec mycols#(stype!mid) by date:date,t:t from t1;
    t3:`date`t xkey select distinct date,t,tenor,xi,volofvol,delta_type,spread from t1;
    result:0!(`date`t xasc t2 ij t3);
    :result}