Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Daryl-Lynch-Bzy
Resident Rockstar
Resident Rockstar

Does using BigInt Datatype in SQL table for Low Cardinality Column impair data transfer

I noticed recently that BigInt datatype and large non-sequential numbers were being used for Primary and Foreign Keys in SQL Database.  It occurred to me that large numbers could slow the data transfer from SQL to Power BI beause each BigInt record would require 8 bytes instead of 2 bytes for smallint and 1 byte for tinyint.  Luckily, the VertiPaq Engine in Power BI is not inheirenting this limitation.

 

Is it common to use BigInt for Low Cardinality columns?  Thoughts?? 

Power BI BigInt Import vs SmallInt Import.docx

@cwebb 

2 REPLIES 2
v-yingjl
Community Support
Community Support

Hi @Daryl-Lynch-Bzy ,

From this article which has been mentioned in the doc, implicit conversions in SQL Server can result in performance degradation.

Value conversions in SQL Server follow preset precedence rules. Small data types are always up-converted to larger data types. Then SQL Server can compare the values.

vyingjl_0-1631668869933.png

 

In addition, when right-sizing data types, a best practice is to analyze whether a data type is the appropriate container for the value that will be stored.

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks @v-yingjl.  The behaviour of the Power BI does seem to up-convert the integers.  I am happy that Power BI does this.  With the way the VertiPaq stores data (i.e. the dictionary), all key are converted to HASH values anyway. 

 

The point of my post is more the pipeline between Server (SQL or others) and Client (Power BI Webservice).  The data volume will be potentially larger and therefore slower because the Server has less optimal Integers.  However, it would still be far worse to pull the String value instead of the Int value.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors