Import from SQL after changing in advanced editor very slow

[Martin74]

Hello Everyone,

Just a simple query which I use to get some data. After changing the sensors, the specified path which is comming from the BMS system of course also results in another string in SQL.

To get the data, I had to change the string in the advanced editor. After this the import is very slow, a refresh takes the same time to complete (about 5-7 minutes).

I set up a new file with the same (new) query, this is super fast. So my conlcusion; there must be something in the former file.

Below the query I use, the only change I made you can see between old en new.

Al the other templates, files etc I made with a lot of more (complex) data works fine as usual (nothing changed). It's not a big issue, but I'm wondering what causes the problem of a very slow import. Anyone a idea?

SELECT [PointName]
,[UTCDateTime]
,[ActualValue]
FROM [JCIHistorianDB].[dbo].[RawAnalog]
WHERE UTCDateTime > DATEADD(MONTH, -3, GETDATE())
Old: AND PointName LIKE '%TT1-Kunst%' OR PointName LIKE '%MT1-Kunst%'

New: AND PointName LIKE '%TT1-Ruimtetemp Kunst%' OR PointName LIKE '%MT1-Ruimtevocht Kunst%'

[v-shex-msft · Accepted solution]

HI @Martin74,

Have you tested in SSMS to confirm if these processings taking the same processing times?
If that is the case, I'd like to suggest you check the TSQL document about improve performance of use 'like' function and wildcard:

T-SQL: Improve the Performance for LIKE Wildcard by Changing the Collation - TechNet Articles - Unit...

How to make SQL Server Wildcard Searches Faster (mssqltips.com)

BTW, does any other advanced operation include in your query tables? (e.g. reference across different query tables, invoke the custom function, merge/combine, and iterator calculations...)

If that is the case, you can consider taking a look at the following blog to add buffer functions to package these types of processes to reduce the spending time.

Nested functions and Table.Buffer() - Exceed.

Chris Webb's BI Blog: Improving Power Query Calculation Performance With List.Buffer() Chris Webb's ...

Regards,

Xiaoxin Sheng

[Martin74]

The problem seems to be in the wildcard %%. Thanks for your reply.

Kind regards,

Martin

[lbendlin]

Is the new query only slow in Power BI or also in SSMS?  Is Query folding enabled or not?

[Martin74]

The Process takes only a lot of time in Power BI, as Xiaoxin Sheng mentioned the problem is in the use of the wildcard %%. I will need to play with this to figure the right solution, I am pleased with al your replys it helps me a lot. Thank you for your reply,

Kind regards,

Martin