Hi Freddy,

Maybe Indicium runs this query on SQL Server and SQL Server decides to use a different plan to obtain the data. Could you try running "exec tsf_optimize” on the database and see if that improves the performance?

@Mark Jongeling it improved a little bit but still 19223 milliseconds and in SMS stays well under a second. 

Hello Freddy,

In version 2022.1.13 of Indicium Universal we’ve introduced a feature that helps with troubleshooting performance issues by means of the Server-Timing header.

You can view the performance metrics by opening the developer tools in your browser, selecting the request that causes the issue and then navigating to the Timing tab.

Can you confirm that it is the query that takes this long and not something else that happens while handling the request?

If it is indeed the query that takes long and there are no other factors in play, then please add the extended property force_recompile to your application with the value empresa_view (the subject with the performance issue). If your application already has the force_recompile property, then add empresa_view to the value as a comma-separated list. If the change doesn’t seem to take effect right away, then please try recycling Indicium’s application pool first.

I hope this helps

Hi @Vincent Doppenberg .. 

I think it's the query, see screenshot.  Just another question I am running directly on the SF. This extended property also exists in the SF?  Or how do I force the recompile without extended properties of the IAM?

Hello Freddy,

In the SF you can add them at Runtime Configuration → Extended property. After adding it, you will have to log out of the Universal GUI and log back in.

If this doesn’t work, please verify in the Database Event Log that Indicium has added the RECOMPILE query hint to the query. If not, then please double check the extended property and try recycling Indicium and clearing your browser cookies.
 

I hope this helps.

Hi @Vincent Doppenberg , 

That did the trick, 59ms. 

However I now get an error 500 on the count query… any idea why?

Hello Freddy,

This looks like an issue in Indicium with the count query. I’m guessing that Indicium creates an invalid query due to the RECOMPILE query option. Could copy and paste the count query from the database event log here?

If the query seems to be fine, you could have a look at Indicium error log to find what is causing the error.

SELECT COUNT(*) AS [count]
FROM 
(
SELECT t1.[ativo], @p0 AS [tsf_placeholder], t1.[nome_empresarial]
FROM [empresa_view] t1
OPTION (RECOMPILE)

) t1
WHERE t1.[ativo] = @p1 AND t1.[nome_empresarial] LIKE @p2

This construct is not valid as it seems.

Hello Freddy,

Yes, I expected it to be something like this. The OPTION clause should be on the outer query, not the inner query.

For now it’s probably best to remove the force_recompile option again, even though it is the correct fix for the performance issue, until we have resolved the syntax error in the query. I will look into this issue during the current sprint and it will be resolved in the next version of Indicium Universal.

What is the reason that this query needs a recompile option passed via an extended property? It seems to me that this will be very hard to figure out where / when to apply this.

Hi @Vincent Doppenberg. Also with expression fields this goes wrong:

The following query failed with an exception: 'SELECT t1..natureza_juridica], t1..lookup_955552244]
FROM 
(
SELECT t1..natureza_juridica], t1..lookup_955552244]
FROM 
(
SELECT t1..natureza_juridica], t2..descricao] AS  lookup_955552244]
FROM  empresa_view] t1
LEFT OUTER JOIN  natureza_juridica] t2
 ON t1..natureza_juridica] = t2..natureza_juridica]
WHERE t1..ativo] = @p0 AND t1..cnpj_basico] LIKE @p1
OPTION (RECOMPILE)

) t1
GROUP BY t1..natureza_juridica], t1..lookup_955552244]
) t1
ORDER BY t1..lookup_955552244] ASC'

@Robert Jan de Nie

The reason for the recompile option is to fix a type of parameter sniffing issue that SQL Server suffers from. It is indeed hard to figure out when this is necessary because you can’t really predict running into the issue, you have to fix it reactively. The general symptom is that the query is fast when running it in the SQL Server Management Studio or even in the Windows GUI, but slow when running it in Indicium. However, it is neither an issue with the query nor with Indicium, it’s a SQL Server issue. The perfornance difference lies in the fact that Indicium uses sp_prepexec to execute the query and in rare situations, typically when there is a lot of data involved, SQL Server will use a query plan that is very bad for the set of parameter values. You might suggest to not use sp_prepexec then, but for the most part it is very beneficial to use it. Sometimes, optimizing the statistics for the database or adding an index can nudge SQL Server into picking a much better query plan, other times the best solution is adding the recompile hint.

There’s an interesting post on it here: https://blog.coeo.com/why-can-sp_prepare-cause-you-performance-issues

@Freddy

Thank you for the additional information. I have already fixed all instances of this issue and added unit tests to avoid regression in the future. The fix will ship with the next version of Indicium Universal.

@Vincent Doppenberg  any release date known? We have an event April 12th and we want to showcase this App and I would really like to have this fix as it speeds up the application but without showing any error. Any chance it will ship before that date? 

Hello Freddy,

In that case I will make a hotfix release for you tomorrow or Friday at the latest.

Hello Freddy,

Version 2022.1.16 of Indicium Universal, which fixes this issue, is now available. 

Just upgraded and works like a charm. Thanks a lot.