Comments (6)
Update: This issue is being worked on.
from alappextensions.
Hi,
Whether or not you will hit this SQL limit should depend on the filter you set. You may hit the limit with one filter and not with another. If you do hit the SQL limit there's unfortunately not much we can do at this time...
If you could share your repro, I can take a look if there's something else going wrong in this scenario.
br/Gert
from alappextensions.
Hi @grobyns
I'm sorry but you are wrong. Is not the user that sets 2100 parameters, but the Dimension Filter page!
and this is the result:
You need only 20K of G/L Entries and 30K of Dimension Set Entries to reproduce the issue or use a real customer database or use the stress test codeunit that I've attached.
Actually Dimension Filter is unusable in a real scenario:
I think this issue is partially related to #730 but anyway, in this scenario, Dimension Set Filter will never work. Maybe a new type of field such as "Filter on Related Table" can resolve the issue applying the WHERE clause in "Dimension Set Entry" table instead of passing thousand of parameters for "Dimension Set ID" in "G/L Entry".
S.
from alappextensions.
Hi,
You misunderstood my reply. The filter set by the page is determined by what the user enters on the Dimension Filter page, this is what I meant. Depending on what the user enters the filter may or may not exceed the SQL limit.
Knowing whether it will or will not exceed the SQL limit is not simple. The page will construct a simple filter by concatenating all the dimension set id's that match what the user put in. This can be in the hundreds of thousands of Dimension Set ID's, the number or length really doesn't matter. The server will take this simple filter and optimize it. As a simple analogy, if you pass in '1|2|3|4|5' the server will turn it into '1..5'. This optimized filter is then passed to SQL and if that contains more than 2100 elements, SQL rejects it.
In your case, if you are lucky, the filter ends up having a lot of consecutive ranges and the server can simplify it down to something SQL can manage. If you are unlucky, then it cannot. Of course you can help by putting in more specific filters. If you put in a specific value for Category then your filter is much more likely to pass. If instead you put in a specific value for Customer you will be less likely to get a filter SQL can handle.
The Dimension Filter page is meant to be a quick ad-hoc way of getting some insights out of the dimensions. It is not meant to replace Analysis by Dimensions or PowerBI to do analysis and reporting. If this feature doesn't work for you, you'll need to rely on these other reporting mechanisms to get the data you need.
br/Gert
from alappextensions.
Hi @grobyns
I hope you consider a new application enhancement, such as query optimizer for pages with flowfield / factboxes. The issue is caused by query generator, a simple JOIN could bypass the SQL limit.
We cannot answer to the customer: "Try... if you are lucky, you get the response!"
It's not a problem to have unusable function inside NAV/365BC ... is a problem if you remove us the possibility to maintain indexes and flowfield (#730) ... so a case like this is critical.
Thank you.
S.
from alappextensions.
Fixing this turned out to require a larger effort. We have various efforts in motion, but it will take us some time to get to the bottom of this. I will close this issue, as we will not report back progress on the larger efforts here in this repo, which is about extensibility issues. Thank you for reporting this issue.
from alappextensions.
Related Issues (20)
- [Event Request] Codeunit 13 "Gen. Jnl.-Post Batch" - New event OnBeforeCheckDocumentNo (BaseApp 24.0)
- [Event Request] Codeunit 22 "Item Jnl.-Post Line" - 3 event changes (BaseApp 24.0)
- Internal procedures in Coduenit 7312 Create Pick
- [CHANGE REQUEST] report 5754 "Create Pick"
- Codeunit 311 (Item-Check Avail.): Add a new event in procedure AsmOrderLineShowWarning
- [Event Parameter Change] Table 111 Sales Shipment Line - procedure PostItemJnlLine - Event OnInsertInvLineFromShptLineOnAfterInsertAllLines
- [Bug]: "IsHandled" does not exit the "DeleteAssemblyDocument" function .
- [EVENT REQUEST] codeunit 5804 ItemCostManagement – OnBeforeCheckNegCost
- Add event OnBeforeTestDate into codeunit 396 NoSeriesManagement
- Report 722 "Phys. Inventory List" - move TempReservationEntryBuffer variable to protected
- [Access Request] page 11502 "Swiss QR-Bill Create Vend Bank" - SetDetails / SetDetails
- "Currency Exchange Rate"."Fix Exchange Rate Amount" - Option to Enum
- Codeunit 7000 "Sales Price Calc. Mgt." event on procedure GetSalesLineLineDisc
- [Bug]: change the calculation of: - Amount (17) in G/L Entry (17) - Base (8) and Amount (9) in VAT Entry (254) when posting an invoice in foreign currency. HOT 1
- [Event Request] Table 36 "Sales Header" - new event OnBeforeSendToPosting (BaseApp 24.0)
- Add event OnAfterCopyPeriodToBuf into codeunit 7153 "Item Analysis Management"
- Add event OnBeforeSetLineAndColumnDim into codeunit 7153 "Item Analysis Management" HOT 2
- Add events OnAfterSetLineAndColumnDim and OnBeforeSetLineAndColumnDim into codeunit 7130 "Item Budget Management"
- Add event OnBeforeDimCodeToType into codeunit 7130 "Item Budget Management"
- [Request for External] table 1205 "Credit Transfer Register" - procedure Reexport
Recommend Projects
-
React
A declarative, efficient, and flexible JavaScript library for building user interfaces.
-
Vue.js
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
-
Typescript
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
-
TensorFlow
An Open Source Machine Learning Framework for Everyone
-
Django
The Web framework for perfectionists with deadlines.
-
Laravel
A PHP framework for web artisans
-
D3
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
-
Recommend Topics
-
javascript
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
-
web
Some thing interesting about web. New door for the world.
-
server
A server is a program made to process requests and deliver data to clients.
-
Machine learning
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
-
Visualization
Some thing interesting about visualization, use data art
-
Game
Some thing interesting about game, make everyone happy.
Recommend Org
-
Facebook
We are working to build community through open source technology. NB: members must have two-factor auth.
-
Microsoft
Open source projects and samples from Microsoft.
-
Google
Google ❤️ Open Source for everyone.
-
Alibaba
Alibaba Open Source for everyone
-
D3
Data-Driven Documents codes.
-
Tencent
China tencent open source team.
from alappextensions.