Autonumber, Indexing, Key/Value Theory Question

[jrdunson]

Hey everyone, as a newbie... I was just looking through and learning about the use of Autonumber().

I didn't realize what it was doing until yesterday...and somehow missed the obvious (!?). I thought autonumber would create an index ... sort of a key/value table, where the key was a sequential number, and the value was the field value (from the field that was used with the autonumber() function).  So, I thought something like:

AutoNumber(Lastname,'Lastname') As Lastname

... would generate this:

                1, Smith

                2, Redmond

                3, Cromstrom

                4, Wunderlich

                5, Harmsen

And then, when referenced (in a chart or table), the lastname would read "Smith" or "Redmond", etc. However, I discovered (much to my ignorance), that is it the number itself, not the name, that is shown in chart/table objects, etc. So "Smith" is shown as '1', "Redmond" as '2', etc.

Question1: What if the users need to see the value, instead of the number? Would it best to create a second table... as a reference for an autonumber field? For example, if I had a list of donations, in a table, called Gifts:

Original Load Script:

Gifts:

Load

                GiftKey

                ,GiftID

                ,GiftDate

                ...

SQL Select …

;

I could change the Load Script to read as follows:

New Load Script:

Gifts:

Load

                GiftKey

                ,AutoNumber(GiftKey) As GK_Link

                ,GiftID

                ,GiftDate

                ...

SQL Select …

;

GiftKeys:

Load

                GiftKey

                ,GK_Link

RESIDENT Gifts;

DROP GiftKey from Gifts;

So the Autonumber field, GK_Link is really a new, optimized lookup table (and the field value, Giftkey, is moved to another table, called GiftKeys):

As far as the GiftKey, above...

• What if Giftkeys is referenced by other, multiple tables?  Will this cause other performance issues? For instance, if a ‘Matching Gifts’ table needs to link to Gift dimensions (Gifts table) via the GiftKey field (see below).
• Also what if multiple expressions refer to the GiftKey in the Giftkeys table… [rather than in the Gifts Table, as previously?].  Will not additional hops be less efficient?

I wondered if this would be a performance improvement, or not? Would the GiftKeys table (optimized for minimal Symbol space…in terms of a sequential number, GK_Link) provide better efficiency/speed than originally? ….Even if the field values (Giftkey), now stored in another table, required the same amount of storage space in a symbol table?

I tested these question by  running the numbers through Rob Wunderlich’s Document Analyzer.  Here were the results]:

FieldName	Distinct Values	Symbol Width	Symbol Bytes
Original Results (Using GiftKey alone)
			19,536,021
GiftKey	2,170,669	8.00	19,536,021
Modified Results (Generating GK_Link, Moving GiftKey to new reference table called GiftKeys)
			21,706,690
GiftKey	2,170,669	8.00	19,536,021
GK_Link	2,170,669	0	2,170,669

My results? While I did reduce the Symbol width to 0, for the GK_Link, from the original Gifts table (now an autonumber field), I added the same number of rows/storage space in the reference table (GiftKeys) for the field values (GIftKey). In effect, I just added two million rows… and increased, not decreased, storage space, load-time, etc. [just the opposite of my goal]... [comment...now obvious, to a newbie like me].

Questions: So, is there a way to use autonumber (or similar), for any fields we need to keep, and reference the field values?

More importantly, Is there another way to create an index in Qlikview?

- Is there a “Create Index” mechanism in Qlikview (similar to databases such as Informix or MS SQL Server)?

- And would such indexes be more proficient regarding speed (less load time, fast calculation / recalculation times, etc.)  - or would they just add additional storage space... for the same amount/number of rows from the original column?

Jarrell

[Oleg_Troyansky · Accepted solution]

Hi Jarrell,

welcome to QlikView! In short, let me state two facts, and you will have to take my word for it now, or you will have to read one or more QlikView books to fully appreciate the underlying technology.

1. QlikView does indexing for all fields automatically. It happens "under the hood" and we, QlikView developers, don't need to worry about it. There is no need to create any more indexes to optimize QlikView performance. It's already optimized for us.

2. We use Autonumber() to save on the memory footprint for meaningless concatenated keys, those that we definitely don't need to store. If you intend to store the original key field along with the autonumbered version of it, it becomes counter-productive. You will consume more memory with no added benefit whatsoever.

If you'd like to learn more about QlikView performance tuning, I'd recommend to check out some of the available QlikView books - in particular, Steven Redmond's Mastering QlikView and my new book QlikView Your Business. Both books teach the fundamentals of QlikView performance, in two different ways.

I'd also recommend to check out the agenda of the Masters Summit for QlikView, the only advanced technical venue available for QlikView developers. In the Masters Summit, we teach Performance Tuning, and we discuss these issues in detail.

cheers,

Oleg Troyansky

QlikView Your Business: An expert guide to Business Discovery with QlikView and Qlik Sense

[jrdunson]

Thanks Oleg,

I was aware of the your first point already (automatic indexing)... but wondering about ways to somehow further optimize in light of what I've read about autonumber.  Thank you for point 2, confirming the point about meaningless keys (i.e. without values).

Thanks also for the recommendations,

Jarrell