cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Daryl-Lynch-Bzy
Frequent Visitor

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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors