Forum Discussion
Helper IImport from SQL after changing in advanced editor very slow
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%'
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: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.
Regards,
Xiaoxin Sheng
4 Replies
- lbendlin
Super User
Is the new query only slow in Power BI or also in SSMS? Is Query folding enabled or not?
Martin74The 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
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: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.
Regards,
Xiaoxin Sheng
- Martin74
The problem seems to be in the wildcard %%. Thanks for your reply.
Kind regards,
Martin
