We’ve come to rely quite heavily on DynamicQuery for customizations and the approach has been working very well for us.
We’ve just run into a very worrying problem, though, where a BAQ takes less than a second to return a few hundred records in the BAQ designer, but literally minutes to do the same thing when calling either “Execute” or “ExecuteByID” with identical parameters through the DynamicQuery BO. That has slowed down a key tool to the point of being unusable.
Running some tests shows that it is the specific execution step that takes the time, and a trace shows nothing unusual (dynamic query always seems to pass a shocking amount of data, but that’s always the case and no more so for this one).
Any ideas what might be going wrong and, more to the point, how to get over it?
This seems a little odd and I have used a lot of dynamic queries so interested in seeing the resolution. Could you post the dynamic query code and an export of the baq?
We have a resolution, but I’m not sure it’s much help to us or anyone else. It gets weirder.
One user, somewhat inadequately trained, had taken several of his Epicor screens apart by separating the panels across multiple monitors under the impression that each bit was an independent entity. I only discovered this when trying to talk him through something and encountered confusion from his end over where the “save” button was. One of the screens he was using contained the errant dynamic query.
Having talked him through putting everything back as it should be, the dynamic query returned to normal speed, not only for him but everybody else too.
I’m slightly at a loss what he can have done, but we have at least undone it.
Maybe he put the BAQ/Dashboard Panel really far from the Published (trigger) column and hopping across that many pixels can be daunting! UI operations are expensive!!! LoL
I had a similar problem. We had a BAQ that would run fine but when used within a dashboard it would time out. I ended up using the SQL profiler to catch the query being executed in SQL. Turns out the query SQL sees is different to the one in the BAQ. Epicor wraps the BAQ query into another query and that is executed in SQL. Once i had the actual query i could put that into SQL tuning advisory and it recommended some indexes and they fixed the issue.
