What is faster char(9) or int?

[Former Member]

Dear HANA Gurus.

What is faster in HANA for selecting -  varchar(9) field or integer field?

With high cardinality values. By example order number.

[Former Member]

You need to give more info. Can you give example DDL and queries.

John

[Former Member]

Yes, it's column store

and yes again,  nvarchar(9) vs int.

So, who is the winner nvarchar(9) or int?

[rindia]

Hi Mikhail,

I would say INT for below reasons:

in INT type we can store numeric data only but in CHAR can be numeric or text.

In CHAR if we are storing the numeric data only and while comparing, it will convert to INT(VARCHAR) which needs additional processing.

Ex: var1 is defined as char(1).

If var1 > 9 then .......

Regards

Raj

[Former Member]

Hi Raj,

Certainly its an overhead to convert it but I have some thoughts on this.

In such a specific case as you describe where it is being used in a procedure via a variable then wouldn't the variable be converted only once for the procedure run? Which I think should not add by much to the overall time taken for the SELECT and the conversion.

So if the requirement is such to use NVARCHAR or NCHAR, one should not hesitate.

--

Shreepad

[rindia]

Hi Shreepad,

Agree.

Conversion happens internally whenever the comparision happens for the above case. For existing model there is no choice but for new, definitely to think about it, though it plays minor role in terms of performance.

Raj

[Former Member]

Hi Mikhail,

I have not tried this out myself for large data sets.

But as per my understanding of the architecture of HANA if you are using Column Store table both should be same.

As HANA Stores every column table in a format known as Dictionary Encoding. This is pretty complex for me to be explained here. But basically in the background in both the cases the Select will run on a similar Attribute Vector (A kind of list of indices).

If you need details on Dictionary Encoding you may go through the course on In-Memory Architecture available on openhpi.de.