Datasource Timeout for initial preview when using uniqueidentifier, due to 100% cpu SQL Server

[cmorgan091]

I am experiencing a timeout error when first defining a connection to a table (using DirectQuery) on a SQL Server database.

The error message states:

DataSource.Error: Microsoft SQL: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Details:
DataSourceKind=SQL
DataSourcePath=servername;database
Message=Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
Number=-2
Class=11

This is when connecting to a fairly large table, with 18.7 million rows in accounting for a size of 2.6GB according to SQL Server.

When the SQL query runs I can watch this on the SQL server and see it consuming upto 100% CPU.  The problem appears to be that the uniqueidentifier (a GUID) which is the PK for the table is being re-cast to an nvarchar(max) prior to sorting the data and obtaining the first 4096 rows of data used for the preview.  I see this by observing the SQL query which is similar to below:

select top 4096
[$Ordered].[DocumentGUID],
[$Ordered].[NodeGUID],
[$Ordered].[year],
[$Ordered].[key],
[$Ordered].[value],
[$Ordered].[formula],
[$Ordered].[error],
[$Ordered].[TextValue],
[$Ordered].[DisplayType],
[$Ordered].[unit],
[$Ordered].[ColumnName],
[$Ordered].[Report],
[$Ordered].[ReportSectionGUID]
from
(
select convert(nvarchar(max), [$Table].[DocumentGUID]) as [DocumentGUID],
convert(nvarchar(max), [$Table].[NodeGUID]) as [NodeGUID],
[$Table].[year] as [year],
[$Table].[key] as [key],
[$Table].[value] as [value],
[$Table].[formula] as [formula],
[$Table].[error] as [error],
[$Table].[TextValue] as [TextValue],
[$Table].[DisplayType] as [DisplayType],
[$Table].[unit] as [unit],
[$Table].[ColumnName] as [ColumnName],
[$Table].[Report] as [Report],
[$Table].[ReportSectionGUID] as [ReportSectionGUID]
from [dbo].[Prices] as [$Table]
) as [$Ordered]
order by [$Ordered].[NodeGUID]

As you can see in the above query both DocumentGUID and NodeGUID (which are defined as uniqueidentifier in the SQL table) are being forced to convert to nvarchar(max).  This causes the query to take over 5 minutes to complete and thus times out.  Conversely when I remove the conversion part and just run a raw query to do the order by and select top I get a near instantaneous result (since there is a clustered index on NodeGUID the PK).

So what I am wanting to know is if there is a way to stop this behaviour of converting uniqueidentifier to nvarchar(max) in the preview page, as to the best of my knowledge this does not happen anywhere else and so once the preview is complete, the rest of the report runs very quickly.

Alternatively there may be another issue that I am unaware of, any help would be greatly appreciated.

SQL Server is 2014 and running the November x64 version of PowerBI.

Many thanks, Chris

[v-caliao-msft]

Hi @cmorgan091,

For the query performance, integers are faster than strings. Since strings are stored in a hash table, they are effectively referenced twice, once for the hash value and once to fetch the string associated with that value. If the strings are highly unique this can cause an increase in memory requirements. 

So in your scenario, why do you need to conver to string? What the issue if you use the query below?
select top 4096
[$Ordered].[DocumentGUID],
[$Ordered].[NodeGUID],
[$Ordered].[year],
[$Ordered].[key],
[$Ordered].[value],
[$Ordered].[formula],
[$Ordered].[error],
[$Ordered].[TextValue],
[$Ordered].[DisplayType],
[$Ordered].[unit],
[$Ordered].[ColumnName],
[$Ordered].[Report],
[$Ordered].[ReportSectionGUID]
from
(
select [$Table].[DocumentGUID] as [DocumentGUID],
[$Table].[NodeGUID] as [NodeGUID],
[$Table].[year] as [year],
[$Table].[key] as [key],
[$Table].[value] as [value],
[$Table].[formula] as [formula],
[$Table].[error] as [error],
[$Table].[TextValue] as [TextValue],
[$Table].[DisplayType] as [DisplayType],
[$Table].[unit] as [unit],
[$Table].[ColumnName] as [ColumnName],
[$Table].[Report] as [Report],
[$Table].[ReportSectionGUID] as [ReportSectionGUID]
from [dbo].[Prices] as [$Table]
) as [$Ordered]
order by [$Ordered].[NodeGUID]

Regards,

Charlie Liao

[cmorgan091]

Hi @v-caliao-msft

I completely agree with the sentiment, however I think there has been some miscommunication.

This is not my query, this is the query that PowerBI is sending to SQL Server by default.

Steps to reproduce:

Create a table in SQL server whose PK is based on datatype uniqueidentifier.

Add that table to PowerBI whilst running SQL Profiler in the background to log the queries.

When PowerBI first generates it "preview" of the data, it runs the query as below, where it is running a convert from uniqueidentifier to nvarchar(max) (which is crazy tbh).  This takes forever to do on a multimillion row table.

Why can't the initial preview get the data WITHOUT the convert?  This would lead to completion in sub second timescales (due to having a clustered index on the underlying uniqueidentifier datatype).

An advanced option to be able to omit this convert would be awesome!  (or even just doing the convert on the $Ordered part since the order by would already have completed by that point)

Thanks for looking into this

Chris

[rboyers]

Hi Chris

Did you find a workaround for this?

Thanks!

Rob

[cmorgan091]

Unfortunately I never did.  From what I can see it seems like the kind of SQL format that Entity Framework uses, however when doing similar kinds of queries in EF there is no need to cast these data types.

I haven't looked at it in quite a while so the latest version of PowerBI may have resolved it, but if not then it is a very annoying bug