topic Re: Dual(Field,recno()) to improve search in App Development

Dual(Field,recno()) to improve search

dyy — Wed, 13 Jun 2018 20:21:28 GMT

Search performance.

We have been having trouble with a 56m distinct value text field (14 characters) on 8 core server with 128gb ram . I decided to try using the dual function on the field dual(field,recno()) and it seemed to have significantly improved the search time for a single value .

NOTE: This is not a Key field.

Has anyone used this method before and are there any drawbacks? The field shows now in the preview in data modeler as an integer/number but we can still search based on the alpha numeric value which is required . I am pleasantly surprised it worked but want to make sure there is no reason we should be concerned.

And, yes , this is not a typical use of Qlik but its what we have to do. Users need to search for an individual item to return only 1 to 4 rows of data and there are 56m and growing distinct values in this field.

Any information would be greatly appreciated.

Re: Dual(Field,recno()) to improve search

petter — Thu, 14 Jun 2018 10:03:04 GMT

The Dual() function will turn off any indexing on the text field and only index the field on the numeric part. You will see that the text will be shown unsorted in a filter pane. So that might be a disadvantage.

My theory on why it is faster to have dual is that the Qlik engine when it doesn't have a text index it will have to turn to a raw full text search and I think that is pretty optimized since it needs to have that to do a complete wildcard search anyway. So an indexed text field that has no numeric part will use the text index that is obviously much slower than the non-indexed text search of a dual field.

If you need to get a dual field with sorted text you can make sure that when you get the field from an external database you make sure that it is sorted in the first place as it is loaded in the load script and associated with a RecNo(). Then it will be sorted correctly alphabetically as well as being a Dual-field when used in the Qlik application.

Re: Dual(Field,recno()) to improve search

dyy — Thu, 14 Jun 2018 11:42:31 GMT

Thank you Peter. This is very helpful and good news.

I will work on the sort order . I am thinking we can use the sort order in the filter pane object in sense to sort by choosing alphabetically if that doesn’t slow down the search

Re: Dual(Field,recno()) to improve search

rwunderlich — Thu, 19 Jul 2018 23:16:38 GMT

Where are you searching? In a list object or smart search?

-Rob

Re: Dual(Field,recno()) to improve search

dyy — Mon, 23 Jul 2018 14:16:09 GMT

Hello Rob

We were using a list box. We escalated this to R&D and they know the issue and have come up with a resolution. I do not have the details but once I do I will share if possible.

My colleague tested this same search in Qlikview and it works great. It is a Sense issue.

Re: Dual(Field,recno()) to improve search

dyy — Mon, 23 Jul 2018 14:16:51 GMT

Thank you Petter

Re: Dual(Field,recno()) to improve search

rwunderlich — Mon, 23 Jul 2018 15:47:37 GMT

I tested on Qlik Sense and could not reproduce the problem, so it does sound like a bug/design issue.

-Rob

Re: Dual(Field,recno()) to improve search

dyy — Mon, 23 Jul 2018 15:59:40 GMT

can you tell me how many rows you used and how long was the alphanum text field?

Re: Dual(Field,recno()) to improve search

rwunderlich — Mon, 23 Jul 2018 16:04:22 GMT

I used 10M rows and the text field was 16 bytes. Very simple data model created with:

Data:

Load

Hash128(RecNo()) as Key,

'A' & chr(65+mod(RecNo(),20)) as Company,

Ceil(rand() * 1000) as Value,

RecNo() as RecId

AutoGenerate 1e7;

June2018 release.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com

Re: Dual(Field,recno()) to improve search

dyy — Mon, 23 Jul 2018 16:10:25 GMT

we had no trouble with that few records as well. But with 62 million UNIQUE values and only one column in the data model we did.

We even used a simulation to create 62 million UNIQUE values with 14 character alpha numeric on our qlik hub and had same issue as on the clients hub

Values must be unique

Debbie Pyykkonen

Senior Implementation Consultant

Email: Debbie.Pyykkonen@qlik.com

Qlik

qlik.com<http://www.qlik.com/>

The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender and delete the material from any computer.

Re: Dual(Field,recno()) to improve search

rwunderlich — Mon, 23 Jul 2018 16:23:07 GMT

Debbie,

My example was 10 million unique 16 byte strings. Hardly a "few values".

Re: Dual(Field,recno()) to improve search

dyy — Mon, 23 Jul 2018 16:24:00 GMT

Also, it was determined NOT to be a bug but working as designed. However, they have a fix for clients like mine who are using qlik from bottom up, not top down to work around this issue.

Re: Dual(Field,recno()) to improve search

dyy — Mon, 23 Jul 2018 16:26:33 GMT

Most definitely not a few records :-). But 62 million unique values did cause this issue . The biggest problem was not the time to search but the fact that the "spinning wheel" was not present while it was searching. So the user did not know it was actually working behind the scenes. R&D did create a fix for this as well. I am waiting to get the full details on the fix from my team. I am on a new project now so not involved anymore