I have a baq that runs in 18 seconds for some users and times out for others. I doubt this is a security issue as our IT security manager can’t run it without timeout. This likely rules out an access issue. Any ideas?
Does it include customers, orders, or quotes? That is, could territory be in play?
It’s an open orders analysis so yes. What do I do about this?
However, we have a different dashboard and baq that has the same order info and everyone can run it no problems.
I would compare the two BAQs to see what’s different to find the performance issue. Is linkage the same? Table order? Filtering?
This might not improve your timeout issue if its linked just to specific users, but where we have had a BAQ timeout for large query with multiple subqueries, adding a table criteria onto every table (for company = your company) sped things up a lot.
You could also try increasing the query timeout settings on the BAQ:
1 Like
This sounds like typical bad blab / parameter sniffing issue
Add option recompile do a search on the forum about this
1 Like
I looked up option recompile and will give this a try, although can you explain how this works to a novice.
What are the cons to using this?
You are asking SQL server to generate a new execution plan every time the query is run this is “pricey” though in the grand scheme of things it is a lot less pricey than using a bad cached plan. There are quite a few entries in the forum about how all this works if you are curious do a bit of searching on
Parameter Sniffing (I think we covered this in one of our podcast episodes)
Here’s a few
1 Like
Cons is that your query will recompile on each run and consume additional processor resources for it.
If it is not running every 10 sec, it might be acceptable.
1 Like