topic Re: Autonumber or Autonumber# in QlikView

Autonumber or Autonumber#

renjithpl — Thu, 21 Jan 2016 12:51:52 GMT

Hi All,

Which function will reduce the number of bytes of a complex field.

Eg,Autonumber(Field1 & '##' & Field2) as Reduced

Autonumberhash128(Field1 & '##' & Field2) as Reduced

THanks

Ren

Re: Autonumber or Autonumber#

maniram23 — Thu, 21 Jan 2016 12:59:37 GMT

HI,

Autonumberhash128(Field1,'##',Field2)

it will reduce the number of bytes.

Re: Autonumber or Autonumber#

swuehl — Thu, 21 Jan 2016 13:01:14 GMT

I believe the final symbol size should be identical, minimal in both cases

Second function only calculates an intermediate temporary hash value from the argument.

I personally would go for Autonumber().

Re: Autonumber or Autonumber#

renjithpl — Thu, 21 Jan 2016 13:42:22 GMT

Thanks for the reply would like to keep it open as of now.

Re: Autonumber or Autonumber#

swuehl — Thu, 21 Jan 2016 13:54:05 GMT

Sure.

For these kind of questions, it's also easy to create a test script and actually see how it performs on your real data.

AFAIK, if you create sequential integers using autonumber / autonumberhas128, QV is actually not storing a symbol table at all, since it can be derived from the bit pointers itself.

Re: Autonumber or Autonumber#

Thu, 21 Jan 2016 14:28:26 GMT

I would prefer Autonumber function.

Re: Autonumber or Autonumber#

HirisH_V7 — Thu, 21 Jan 2016 15:06:00 GMT

Hi ,

Check this ,

AutoNumber() vs Auto..Hash() | Qlik Community

HTH,
HirisH

Re: Autonumber or Autonumber#

Peter_Cammaert — Thu, 21 Jan 2016 16:04:06 GMT

Stefan, this is interesting.

However, I guess I'm not getting your point in its entirety (my bad). And Rob is letting go as soon as he touches AutoNumberHashxxx (see the link in Hirish' post a little further)

The hash variants serve a purpose I think, but indeed it isn't easy to determine what purpose exactly.

IMHO as long as a script is reading field text values (which the OP will get when inserting ## into the key values) to autonumber/autonumberhash, there must be a symbol table somewhere. Otherwise you'll number identical strings twice. The advantage of the hash functions is that the symbol table will store comparison values of predetermined size (either 16 bytes or 64 bytes), while an autonumber table has to store every string but doesn't know beforehand what the maximum text string size will be.

Since a hash function tries to calculate a unique value for every string imaginable, I think in the long run (high cardinality and large strings) the hash functions will be more RAM efficient than just autonumber.

Does the autonumber symbol table survive script execution? I shouldn't because the only value QV needs in order to do whatever it needs to do after a reload is the autonumber value itself. However, a reload has its own memory requirements and these must be taken into account as well.

I don't really know.for sure. I think Renjith is right in keeping the discussion open for a while longer.

Re: Autonumber or Autonumber#

rwunderlich — Thu, 21 Jan 2016 17:12:57 GMT

Peter,

Interesting point on the RAM requirements of both during reload. It would be interesting to test. I wouldn't be surprised if AutoNumber() was hashing it's temp lookup table, and therefore using the same RAM as AutoNumberHash*() functions anyways. Let us know if you get a chance to test.

-Rob

Re: Autonumber or Autonumber#

swuehl — Thu, 21 Jan 2016 18:25:30 GMT

Peter,

yes, there need to be a symbol table / lookup table / hash table during the LOAD, I was talking about the resulting data model.

The bit stuffed pointers are just the index of the values' symbol table positions, right?

If you have sequential integer numbers 1 to 10 as field values, like generated by an AutonumberXXX() function call, the index pointers will be 1 to 10, too, so each value's pointer is equal to the value.

Hence, there is no need to store the symbol table at all (and that is AFAIK what QV does, deriving the symbols directly from the pointer index in the record table). Again, 'storing' means surviving the script execution.

AutonumberHash*() and Autonumber() function should basically result in the same, but your point is valid, the way to achieve it might be different in terms of memory consumption / CPU cycles needed during the script execution.

I think the only persons that can really answer this question might be the developers at Qlik, maybe this would be a topic for a Qlik design blog post by hic‌.

Re: Autonumber or Autonumber#

Peter_Cammaert — Fri, 22 Jan 2016 00:07:45 GMT

Rob,

I agree with your reasoning about AutoNumber() probably doing a hash behind the scenes by itself. But my limited develoment skills would automatically get stuck again: what kind of behind-the-scenes-hash? Knowing Qlik, they would choose an opportunistic but well considered approach: a best-guess hash? a suits-all hash? a dynamically adapting hash? (I don't know if such a thing even exists)

QlikView always faces a suffocating compromise: speed vs. memory consumption vs. coverage. Will QV-assumptions work for all cases that are due to follow? Will we hit the RAM limit of the machine we're running on? And since there isn't really a RAM limit anymore since the introduction of virtual memory (an S360-first if I'm not mistaken), what horrible performance will we get when paging mechanisms start to harass disk drives?

On the other hand, I still can't imagine Qlik pushing both functions for all these years when Autonumber simply invalidates Autonumberhash' existence.

Anyway, I'll try to figure out some kind of test for this, and report back (message received). However, these days I'm continuously living under the impression that these things must have been discovered before (maybe more than once) by you and the other Qlik-universe stalwarts. And that this knowledge simply doesn't bubble up like it should. Do you have any links to previous discussions to share?

Best,

Peter

Re: Autonumber or Autonumber#

rwunderlich — Sun, 24 Jan 2016 00:08:28 GMT

I've never got an answer as to why both functions exist. But I just ran a quick test and discovered something interesting. It was a quick test, and I plan to do some more formal testing next week.

As previously discussed, there is no difference in the resulting data model between AutoNumber() or AutoNumberHash256(). With a simple test of

AutoNumber(SKey)

AutoNumberHash256(SKey)

Both used the same amount of memory, which lends credence to the idea that AutoNumber uses a hash for the temporary table. Both take the same amount of time to execute. (I haven't done CPU timing tests yet). It may be that both are doing the same code path under the covers.

I observed something interesting when trying a typical real-world test, auto numbering multiple fields.

AutoNumber(SKey & '_' & Key2)

AutoNumberHash256(SKey,'-', Key2)

Both used the same amount of memory. However, AutoNumber() took significantly longer to execute. I believe that's because of the overhead of creating the new temporary parameter string SKey & '_' & Key2. I think further testing will isolate this.

While it's a best practice to include a separator like '_' between fields in AutoNumber, the separator isn't required in the hash version -- which makes it syntactically easier. I frequently use it for that reason alone.

AutoNumberHash256(SKey, Key2).

So it looks like for multiple fields, AutoNumberHash256() may be faster to use. That is, neither is faster but the prep required for AutoNumber() increases execution time. I need to do some more testing before I'm fully convinced, but perhaps others will chime in with what they know or can produce in testing.

-Rob

http://masterssummit.com

http://qlikviewcookbook.com