# Query Performance

**URL:** <https://forum.kx.com/t/query-performance/11602>\
**Category:** Community Support\
**Tags:** kdb-and-q\
**Created:** [August 18, 2017, 3:32pm UTC](https://forum.kx.com/t/query-performance/11602 "2017-08-18T15:32:00Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![Birzos](https://avatars.discourse-cdn.com/v4/letter/b/ec9cab/32.png) [@Birzos](https://forum.kx.com/u/Birzos)\
**Post date:** [August 18, 2017, 3:32pm UTC](https://forum.kx.com/t/query-performance/11602/1 "2017-08-18T15:32:00Z")

</div>

The following query takes \<1millisecond to run returning 61 records.

`select sym,bar,timestamp:floor((`long$(“p”$bar))-`long$1970.01.01D00:00)%1e6,open,high,low,close,volume, dx:0 from `forex\_bar where bar within (2017.08.18T10:00;2017.08.18T11:00),sym=`GBPUSD`

When adding dx functional select it adds 10ms to the query.

`select sym,bar,timestamp:floor((`long$(“p”$bar))-`long$1970.01.01D00:00)%1e6,open,high,low,close,volume, dx:raze {[x;y]?[`fxsc1\_bar;((\>=;`bar;(-;x;00:06:00.000));(=;`sym;enlist y));0b;enlist [`close]!enlist (first;`close);10]`close} '[bar;sym] from `forex\_bar where bar within (2017.08.18T10:00;2017.08.18T11:00),sym=`GBPUSD`

Could anyone suggest a way to speed it up as in some cases query times go from 1-2ms to 100-200ms with the functional select added.

---

<div class="post-metadata">

**Author:** ![effbiae](https://avatars.discourse-cdn.com/v4/letter/e/ecae2f/32.png) [@effbiae](https://forum.kx.com/u/effbiae)\
**Post date:** [August 18, 2017, 7:15pm UTC](https://forum.kx.com/t/query-performance/11602/2 "2017-08-18T19:15:00Z")

</div>

if you increase the time period in your ‘where’ clause to two hours, does your query time from 2-4ms to 200-400ms?  
can you write dx without ‘each’?

---

<div class="post-metadata">

**Author:** ![rathore\_ajay1](https://avatars.discourse-cdn.com/v4/letter/r/9de0a6/32.png) [@rathore\_ajay1](https://forum.kx.com/u/rathore_ajay1)\
**Post date:** [August 18, 2017, 7:45pm UTC](https://forum.kx.com/t/query-performance/11602/3 "2017-08-18T19:45:00Z")

</div>

Looking at your code, the functional select and iteration seems unnecessary, this increases the complexity to O(n), I “think” you can eliminate the iteration by using binr,

`select sym,bar,timestamp:floor((`long$(“p”$bar))-`long$1970.01.01D00:00)%1e6,open,high,low,close,volume, dx:close bar binr (bar-00:06:00) from `forex\_bar where bar within (2017.08.18T10:00;2017.08.18T11:00),sym=`GBPUSD`

---

<div class="post-metadata">

**Author:** ![Birzos](https://avatars.discourse-cdn.com/v4/letter/b/ec9cab/32.png) [@Birzos](https://forum.kx.com/u/Birzos)\
**Post date:** [August 22, 2017, 4:56pm UTC](https://forum.kx.com/t/query-performance/11602/4 "2017-08-22T16:56:00Z")

</div>

Thanks for the replies.

@effbiae Yes, if increase the timeframe the query times go up a lot.

@Ajay The problem is that the data is inconsistent which appears to not fit with the KDB model, have instead put the update in to a timer event where the upd sets a flag and the code only pulls the data it needs to update, this seems to work more efficiently. It purely is a subselect for a specific value, changed it from time based to bar based, it’s not ideal but it does work and appears to be faster.

`dx:raze {[x;y;z] -1#?[`forex\_bar;((\<=;`bar;x);(=;`sym;enlist y));0b;enlist [`close]!enlist (z xprev;`close);-500]`close} '[bar;sym;dz]`