2 Replies Latest reply: Sep 11, 2015 10:53 AM by Jarrell Dunson RSS

    Autonumber, Indexing, Key/Value Theory Question

    Jarrell Dunson

      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):

       

      img1.JPG

      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?

       

      img2.JPG

       

      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

        • Re: Autonumber, Indexing, Key/Value Theory Question
          Oleg Troyansky

          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