We’ve run into an issue a couple of times where a custom JPQL filters against datagrids have produced execution plans in SQL Server that run very poorly (1-2 minutes). Taking the exact same query and parameters for the query and running it in Management studio results in much better performance (1-3 seconds) with a very different execution plan. We’ve never run into this type of behavior with our previous application. I didn’t know if CUBA was doing anything different like pre-building the query or anything that would affect the execution plan.
I’ve deleted the cached execution plan in SQL Server and let it regenerate but this didn’t help. I’ve noticed that the execution plan has warnings in it for a mismatched data type parameter against the DTYPE columns, I’ve created the same mismatch in Management studio but that doesn’t change the performance.
I think CUBA guys will ask you to provide a sample project with reproducable test case and all information: SQL Server version, JDBC driver, CUBA version etc etc.
So that they can investigate your case in detail.
Hi Josh,
We are aware of the problem - faced it on one of our projects. It stems from the fact that by default MS SQL caches execution plans for queries without taking into account current parameter values.
We are going to provide some way to pass hints for particular JPQL queries to make MS SQL use more optimal strategies, like OPTIMIZE FOR UNKNOWN.
Created issue: Optimize query plan execution for prepared queries on MSSQL · Issue #1929 · cuba-platform/cuba · GitHub
Sounds great. Thank you. Hopefully this will resolve the issue. It looks like it’s scheduled for 7.1? I haven’t updated to 7 yet but I hope to do so soon.