BAQ 100x slower in Production than Dev/test (Vote for this Idea for an easier fix in the future)

Same BAQ (one I made to compare BOMs)

  • In Production (today): 127 seconds
  • In Dev DB (refreshed a few weeks ago): 1.2 seconds
  • Prod and Dev are on the same machine
  • I even exported the (“slow”) Prod BAQ and imported it into Dev and it still runs faster (1.3 seconds) in Dev!

It’s just this one that is doing this, and only today (so far).

For example...

Another BAQ (analyzes PartDtl)

  • In Production: 47 seconds
  • In Dev DB: 55 seconds
  • Those are normal times; there are a lot of factors there, but point is that the times are similar in both environments

So what is it about this BAQ that makes it so slow in Prod?

I did the SQL plan for each one. I know the pic is uber-small, but clearly they actually use a different flow to get there. WHY?

I see the bit about adding an index, and I have seen some chatter on that here @Noffie @ERPSysAdmin

Specifically it says:

/*
Missing Index Details from Rev_PROD.sqlplan
The Query Processor estimates that implementing the following index could improve the query cost by 11.2492%.
*/

/*
USE [Production]
GO
CREATE NONCLUSTERED INDEX [<Name of Missing Index, sysname,>]
ON [Erp].[PartRev] ([Approved],[Company])
INCLUDE ([PartNum],[EffectiveDate],[Plant])
GO
*/

So I guess (a) where do I see what indexes do exist, and (b) do you really think that’s the problem?

Thanks in advance.

So your statistics may be out of date @JasonMcD

I would suggest you run a quick statiscs update on all tables involved in this BAQ in Production and see what happens. 9/10 times it flies right after. I’m not sure how you are doing maintainence on your DB but statistics are as important if not more than indexes sometimes.

Try this (this takes a few seconds to run even on the largest of tables and it shouldn’t hurt much maybe a small performance hit for a little while but perfectly fine in my eyes.

UPDATE STATISTICS Erp.PartRev WITH FULLSCAN
UPDATE STATISTICS Erp.<YourOtherTables> WITH FULLSCAN
UPDATE STATISTICS Erp.<YourOtherTables> WITH FULLSCAN
.
.
.

Ask @jgiese.wci what a difference a statistics update can make. We’ve both been blow out of the water before. Also you may have old / cached plans on this BAQ in SQL you can change that by adding an OPTION(RECOMPILE) at the bottom of the query. This is kind of tricky to do, but if you don’t have an order by clause then go go
image

image

This forces SQL to ditch its old Plan and generate a new one. You pay for it a little by having to generate a new plan but the cost is small compared to the 100X you are seeing now.

Try one and then the other if the first one doesn’t work I’m curious as to what you find. Adding an index is fine but those suggestions are query specific and those indexes though will help your one query, may hurt other areas of the system so be careful.

8 Likes

@josecgomez What things that you mentioned can/should I do on the Production DB right now - while people are in the system?

I mean, I get that the BAQ thing is OK. I mean the SQL stuff.

Both of those should be fine to do right now, you may experience a brief performance hit.
Try the option recompile first since that only affects the BAQ itself.

But the Statistics is fine I do it all the time as long as you keep to the few tables in taht BAQ. You should be doing a full statistics update pretty frequently as part of your maintenance.

1 Like

@josecgomez BAQ thing (in Production) did this:

image

Wow, is all I can say.

Just wow. 120 seconds → 0.9 seconds.

3 Likes

A bad plan will ruin your day, bad plans are usually because of bad statistics. I’d wager if you do the stats update and remove the recompile thing you’ll get similar results.

BTW that option recompile is a bit of a hack I’m sure you could tell, we are “injecting” a bit of SQL magic via that Order By Clause. So I can’t guarantee it’ll work forever

–Cheers.

2 Likes

That is where you are wrong. It’s all magic to me.

Meh, maybe a little less magic as each year goes by. Still, 99% magic.

1 Like

PS, you are right, of course, no statistics plan/job in SSMS that I see. I remember stats being an issue before, and I thought I/someone had set up something for that, but guess not.

Will do!

@Patrick.Ferrington, @Edge any chance we can get an OPTION(RECOMPILE) checkbox on the BAQ to bypass bad plans… pretty please with a cherry on top.

5 Likes

PSA for future readers, check off Persist in Query as Jose’s screenshot that I borrowed below shows. Otherwise it goes away after you close the window.

If it is anything like the QueryOldCompanySecurity setting, don’t even reopen the Execution Settings window, or if you do, click cancel. (See linked post below.)

image

1 Like

@josecgomez , if you have the “Auto create statistics” and “Auto update statistics” properties of the database set to true, is there any added benefit of updating statistics periodically through a maintenance plan?

I assume you mean something besides the hack around with the Order By clause? we can probably figure something out. timing is bad though - you’ll forget about this before I can sneak into the next ‘current’ release.
Do you think an actual fully exposed checkbox on the main UI or its own specific execution setting rather than the order by work around?

1 Like

Either a Checkbox or an execution option would be nice the ability to control which plan / index / stat a query uses would be nice though too advanced for “generic” users. And I understand how allowin just arbitraty SQL Inject would be bad. So maybe a few pre-set options that can be selected just like we do that OrderBy Hack but with the a few (all ??) of the query hint options

If not at least the OPTION recompile one would be grand.

2 Likes

Yes

2 Likes

I can also put it in the ideas portal where it can be properly ignored like the rest of them :rofl::rofl::rofl::rofl: (just kidding :yum:) if it’ll make it easier to push it through

1 Like

Okay I put it in the ideas portal @pferrington : KNTC-I-2380

Go and vote for it … EVERYONE!

3 Likes

Is this different than Josh’s Allow means of clearing cache plans for BAQs | Kinetic Ideas (aha.io)

Nope, pretty similar.

Next version has OPTION execution setting so you can add whatever you need there. Also there will be option to get query statistics

5 Likes

Oh, and also actual execution plan will be shown, not estimated

2 Likes