Hi,
I have spent a fair amount of time testing how a DAC referencing a view in the database works, specifically around a second independent BQL statement on the view not behaving as you might expect. To demonstrate the issue as clearly as possible I have created a simple, abstract test case running on a local instance.
I have a view:
And, the test case attempts to run 2 BQL statements returning one or more of the following records:
There is a DAC that references the view:
And, here is the Test method that runs the 2 BQL statements:
So here is the issue. When I set a breakpoint at the end of the Test Method and interrogate the contents of q1 and q2, the contents of q2 are wrong. I was expecting 2 records being InventoryID 3236 and 3249, but we have 2 records both being InventoryID 3235 (ie. the record the first BQL statement returned)
What is going on here? Some kind of caching around the contents of the View?
I have used SQL Profiler to check what query is being running to populate q2. This looks correct:
I have also tested what happens if q1 is commented out:
In this case, the contents of q2 have 1 correct record and one incorrect record:
SQL profiler suggests that the query acumatica executed should have returned the two correct records:
So, is their anyway to reliably run a BQL statement on a DAC referencing a view? It seems that the first record returned by the view in the first BQL statement is propagated to subsequent records. The number of records returned is correct (I have done a lot of testing around this behaviour)
Note that my first attempt to achieve my actual objective without views contained a lot of N+1 queries and was very slow to run. This is why I went down the path of using views. I have a work around for the issue which is to:
- Ensure that each view is only queried once. The equivalent of what I have done in this abstract example is:
Where
- Ensure that each query only returns a single record
Is all a bit verbose and messy really. Would like to understand what’s going on here.
We also have examples of DAC referencing views being used in Generic Inquiries returning not the right data. Possibly this is a similar issue?
Thanks in advance for any help,
John.