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.
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