We're experiencing performance issues in Universal with a certain view. I already tried using ForceRecompile in extended properties to make it load faster but that doesn’t seem to have an effect. I rewrote the sp_prepexec statement to a regular select query and it's just as slow. This is the query that’s being run on SQL Server:
SELECT t1.Ttsf_row_number], t1.,bedrijf_nummer], t1.,klant_groep_nummer], t1.,klant_nummer] FROM ( SELECT ROW_NUMBER() OVER (ORDER BY t1.Yklant_nummer] ASC, t1.,bedrijf_nummer] ASC, t1.,klant_groep_nummer] ASC) AS )tsf_row_number], t1.,bedrijf_nummer], t1.,klant_groep_nummer], t1.,klant_nummer] FROM ( SELECT t1.* FROM Fvw_example] t1 ) t1 WHERE t1.Eklant_groep_soort] = @p0 AND (isnull(t1.klant_geblokkeerd_voor,0) in (0,1) ) AND (exists (select kg.klant_groep_nummer_hoofd from klant_groep kg where kg.bedrijf_nummer = t1.bedrijf_nummer and kg.klant_groep_nummer_hoofd= t1.klant_groep_nummer) ) AND t1.Dnaam] LIKE @p1 ) t1 WHERE t1.Ebedrijf_nummer] = @p2 AND t1.Dklant_groep_nummer] = @p3 AND t1.Dklant_nummer] = @p4 ORDER BY t1.Ytsf_row_number] ASC OFFSET 0 ROWS OPTION (RECOMPILE)
As you can see it contains OPTION (RECOMPILE) at the very end but it doesn't seem to have any effect. This query takes 90 seconds to produce the output and it's just a single record.
The standard count on the list takes 55 seconds to complete despite it also containing OPTION (RECOMPILE). These long query times render this particular screen in Universal rather impossible to work with.
I understand it can be difficult to evaluate this from a query alone. I'm just looking for clues as to how to improve performance.
Page 1 / 1
Hi Roland,
Performance issues can be challenging to tackle. In this case we'll have to dissect the different things this query is doing and go through it to identify where improvements can be done.
It's a little difficult for me to know what is a prefilter, what is a filter and so forth so I'll base myself on the query itself rather than referring to the model object/feature that is causing the query to appear.
Findings:
SELECT ROW_NUMBER() OVER (ORDER BY t1.[klant_nummer] ASC, t1.[bedrijf_nummer] ASC, t1.[klant_groep_nummer] ASC) AS [tsf_row_number]
This code introduces a unique row number which is ordered by three columns. To get this row_number, the RDBMS has to first have the complete dataset filtered, and after that will order that dataset and place a row_number. This can cost quite some time if it is a large dataset.
SELECT t1.* FROM [vw_example] t1
View code can have use all kinds of complicated Joins, Group by clases and much more. I can’t say much about that but you could look into only selecting data from this view and see if this is performant. Missing indexes can also be a cause of less performing views. Identify if the join clauses, where causes among other parts are covered by indexes.
WHERE t1.1klant_groep_soort] = @p0 AND (isnull(t1.klant_geblokkeerd_voor,0) in (0,1)
The isnull(t1.klant_geblokkeerd_voor,0) in (0,1) does require the RDBMS to replace NULL values with a 0, to thereafter compare it to a 0 or 1. In your view you may already by able to use the isnull to ensure klant_geblokkeerd_voor has a value prior to this filtering.
AND (exists (select kg.klant_groep_nummer_hoofd from klant_groep kg where kg.bedrijf_nummer = t1.bedrijf_nummer and kg.klant_groep_nummer_hoofd= t1.klant_groep_nummer)
An exists is quite performant when it can use an index. In this case, an index on bedrijf_nummer and klant_groep_nummer_hoofd would help this exists.
Hope this helps! Also take a look at the execution plan (SQL Server) when this query is run. That will give valuable insights into which indexes are used, where they lack, and what choices are made by the RDBMS.
The issue is the row_number() inside the subquery. As soon as I take it out the query finishes almost instantaneously. Unfortunately I can't influence how Universal compiles the query which contains a combination of prefilters and a value inside a filter form. So the question is how to code around it, if even possible.
@Mark Jongeling Sorry I only read your response after I posted this reaction. So the issue is with the row_number(). I'm not entirely sure why this query is being run by Universal as I don't know what it does.
Screenshot from the database event log:
Hi Roland,
Does this happen during a particular action? Like
when opening the document via the menu,
or via a process flow,
or when changing the sorting,
or during adding, updating or deleting a record,
or something else?
And what is the Refresh after update setting for this view? (Subjects > Settings)
When you insect the execution plan via SSMS ofr instance, is it only the row number that takes so long, or are there other things that need improvements?
A rather long list of queries pops up and I'm not sure what they all are or when they happen. The issue is that quite a lot of output in the log is delayed.
After opening the view from the main menu an empty list pops up and produces and runs this slow query among others:
Then I filter on a field containing the letter “A” which happens rather fast. Then when I edit the first record in the resulting list I get another long list of slow queries among which the one I posted earlier. After clicking ‘edit’ the form seems to keep loading things in the background even when I can already start editing the fields. The same happens again when I click to save. Editing and then saving is now a slow process.
By the way, this also happens in the Windows GUI via Indicium basic. The Windows GUI using a direct SQL connection experiences none of these issues.
It surprises me that the i_ui_col_form can become so slow. I think we'll have to take a closer look and analyze the performance. Could you raise a TCP ticket?
I can confirm the slow loading of i_ui_col_form but also i_ui_task_parmtr are hitting he hardest on our side as well. Especially when talking against the SF. Not seen a 90 second one yet, but a 9-10 second per column is quite a lot of course.
i_ui_col_form
i_ui_task_parmtr
Similar to Roland the issue does not exist when using the Windows GUI against the database.
Before I raise a ticket I'd really like to pinpoint exactly what's happening. I just found out that setting extended property ForceRecompile in IAM doesn’t seem to work.
The development version of the application adds option(recompile) to the query but despite above setting the production application does not. In production the query running time is over 8 seconds while in development it's only 80 ms. I've already stopped and restarted Indicium to try and make it pick up the extended property but it won't work.
@Roland , ForceRecompile is an Application extended property, not a Global extended property. In IAM, under Authorization > Applications > General settings > Extended properties
Indicium will pick it up in a minute if a change is made in the Application extended properties.
@Mark_Plaggenborg We are aware of this occurring. Universal GUI currently decides to call the i_ui_task_parmtr and i_ui_report_parmtr for every Task and Report bound to the subject you are opening.
On the SF metasource, the data isn't as optimized as when running the application on the IAM metasource as Synchronizing to IAM takes away a lot of the recalculations. This generally means the i_ui's on the SF metasource perform less than when running on the IAM metasource.
I do am curious, are you using Role simulation? If so, the performance should be a lot better when no role is being simulated as the effective rights per columns do not have to be calculated on demand.
When opening the application on the SF metasource, do you make use of HTTPS?
@Mark Jongeling I did indeed notice that the performance on IAM is a lot better, so for the end product we are not too worried.
We do develop without the role simulation. Only when we are setting up the roles we use this to confirm the result. The screenshot is also without role simulation at work.
We are running indicium and the universal GUI in Azure (Web App) and in HTTPS mode.
@Roland , ForceRecompile is an Application extended property, not a Global extended property. In IAM, under Authorization > Applications > General settings > Extended properties
Indicium will pick it up in a minute if a change is made in the Application extended properties.
Thanks Mark. That worked and now the list loads a lot quicker. An update of a single record still takes about 11 seconds though and I've noticed there's no option(recompile) in the update query. When I manually add it in SSMS and run the code the update happens instantly. Is there a way to set forcerecompile for update statements?
I'm not quite sure what update statement you are referring to. To my knowledge there is no update being done in the i_ui views on the SF metasource side.
Force recompile currently only works for SELECT statements that Indicium runs.
I'm not quite sure what update statement you are referring to. To my knowledge there is no update being done in the i_ui views on the SF metasource side.
Force recompile currently only works for SELECT statements that Indicium runs.
I'm sorry. I wasn't being very clear in my earlier comments. In this case I’m querying a table (or rather a view in this case). The select statement is much quicker now but updates are still very slow. Is there a reason for not supporting force recompile for update statements? In this specific case it would solve a rather big performance hit on sql server.
Is there a reason for not supporting force recompile for update statements?
Not sure, but I would imagine this was not needed as no one ran into the issue yet. If possible, force recompile should be avoided but indeed, there are valid cases. Feel free to raise an idea for this.
So far so good. The update statement at the bottom (marked with the red box) runs in 80 ms with recompile in SSMS so I will post an idea for that one. This is now a workable situation for the end-users which was the main goal. From a performance perspective I wonder how many more update statements could benefit from this, if not for UX then for reducing server load.
The other queries do run with recompile but due to a combination of prefilters and a filter form SQL server seems to have a hard time optimizing these. I've optimized the view today. I’ll try again in the future to cut some more off these running times.
