Dual(Field,recno()) to improve search

[dyy]

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.

[petter · Accepted solution]

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.

[dyy]

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

[rwunderlich]

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

-Rob

[dyy]

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.

[dyy]

Thank you Petter

[rwunderlich]

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

-Rob

[dyy]

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

[rwunderlich]

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

[dyy]

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.