Named Parameters in the Web Console

query
n1ql

#1

Hello -

We have a N1QL query that a handful of our data folks use for adhoc analysis. The queries have a few large arrays of values we use in an IN statement (example below). They need to be able to switch between 5 or so code sets when running their query.

We have an index on v.drgCode, such that performance is GREAT when the array of codes is included in the query. However if we use a sub-select inside the WHERE clause to source the codes out of a document, performance is terrible.

Upon further inspection, when using the sub-select, the drgCodes aren’t making it into the range criteria for the drgCode index. We then tried using the “Named Parameters” in the Web Console, but we couldn’t get this to work either. So our two questions are:

  1. Is there a way to get the query optimizer to execute the sub-selects first to get the data inside the sub-select to help the parent query qualify for better indexing?

  2. If #1 isn’t possible, is it possible to used name parameters to store the drgCode sets?

SELECT v.tenantName,v.visitNumber, v.drgCode, SUBSTR(v.dischargeDate,0,10) As dischargeDate,meta(c).id
FROM cloud_med v
JOIN cloud_med c ON KEY "visit-" || c.tenantName || "-" || c.visitNumber FOR v
WHERE v.type = "visit" AND c.type = "charges" 
AND v.tenantName IN ["testTenant"]
AND v.dischargeDate >= SUBSTR(DATE_ADD_STR(CLOCK_STR(),-120,"day"),0,10)
AND v.drgCode IN ["013","017","022","027","030","033","036","039","042","053","060","063","066","072","076","079","084","087","090","093","096","099","114","117","122","130","132","134","136","138","148","156","159","165","168","179","182","185","188","192","195","198","201","203","218","221","230","241","244","254","257","262","272","282","285","290","293","295","298","301","310","316","328","331","334","337","340","343","346","349","352","355","358","370","373","376","379","382","387","390","395","407","410","413","416","419","422","425","434","437","440","443","446","455","458","465","468","473","476","479","482","487","489","494","497","499","502","505","508","512","514","517","520","538","541","544","547","550","561","566","572","575","578","581","583","585","594","599","601","615","618","621","624","627","630","639","645","655","658","661","664","667","670","672","675","684","688","692","700","708","710","712","714","716","718","724","730","735","738","741","743","745","747","750","756","759","761","766","801","804","810","816","822","825","828","830","836","839","842","845","848","855","858","869","903","905","909","921","929","941","946","950","959","965","976","983","986","989"]

#2

If sub SELECT in where clause executed only when outer table is scanned (index Scan+Fetch). So those values not available and can’t be pushed as index ranges. Instead it needs fetch full range from index on that key and filter needs to be applied as post indexscan/fetch filter.

Alter native if subquery is independent execute standalone generate as array and then use as named parameter and execute as adhoc query.
v.drgCode IN $dgcodes
dgcodes=[“013”,“017”,“022”,“027”]
Non adhoc query will be same issue because because during prepare time dgcodes doesn’t know it need to full range scan on that index key.

Click Preferences in Query Work bench and set like below