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
sundeep579
Frequent Visitor

VARCHAR vs NVARCHAR when using DirectQuery in Power BI

I am currently designing reports in Power BI using DirectQuery to connect to Azure SQL Server. The reports that I am designing are performance sensitive since I need to show all the rows in a table visual. While designing the database tables, we have used VARCHAR to yeild better performace since they consume less space when compared to NVARCHAR columns. But in Power BI, there is just one string datatype called 'TEXT'(as specified in the below link) which stores unicode characters. Will all my string data from SQL Server which is in ASCII be converted to Unicode when it is read in Power BI ? If this is the case, then using VARCHAR in Sql Server can negatively impact performace.

 

Please let me know your thoughts on this and what datatype should I use in Sql Server to yield better performance in Power BI reports.

 

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-data-types/

6 REPLIES 6
RosaIsela06
New Member

Hi 

I'm facing same problem, Did you find any solution?

HelloWorld1
Frequent Visitor

any update about this issue ? thank you !

v-qiuyu-msft
Community Support
Community Support

Hi @sundeep579,

 

Choose VARCHAR or NVARCHAR data type based on which kind of data you want to stored on database, the data type defined on SQL database side will not have big affect when query data from the Power BI.

 

In your scenario, as you use DirectQuery to get data, you can take a look at this article to consider performance.

 

Best Regards,
Qiuyun Yu

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

Hi Qiuyun,

 

Thanks for your response. The reason for raising this post is that the client has asked us to improve the performance of a particular table visual in the report. The table visual loads about 100,000 - 200,000 rows depending on a slicer selection. So, I looked at the SQL Server table from where data is being loaded for the visual. The table had couple of NVARCHAR columns and few INTEGER monthly sales columns. I converted both the NVARCHAR columns to VARCHAR columns since they will always store english characters in order to reduce the size of the table and in-turn reduce the size of the data that the report has to read from the database. The NVARCHAR to VARCHAR conversion reduced the size of the table by about 25%(from 480 MB to 360 MB). But after making this change, the performance of the report has come down instead of improving. This is when, I came across the below article which says that Power BI supports only Unicode TEXT datatype. So, Is Power BI trying to convert ASCII strings to Unicode strings which is what is causing the performance degradation in my case ? Is there anyway to avoid this so that I can yield better performance using VARCHAR columns ?

 

https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-data-types/

 

I'm facing same issue. DId you find any solution?

Hi sundeep579,

 

By any chance did you get any answer for this question ? maybe you test it, please share 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

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.