Hi, we have seen this issue since we started using ignition. Why Table component is so slow to display ? We use data binding where sql script runs and fills the table. The table is hidden and only if it gets data then it shows up. We have couple of input texts and when all of those are filled, the table fires query to fetch data and then it should show up, sometimes its like 5-10 seconds. The query is not the issue, it runs really fast like 0,01 seconds. So what could be the issue ?
Vision or Perspective? Add a tag to your question.
sorry, perspective
How many rows does it have? Could it be a rendering problem? Any other bindings on the table? Could we have an export of a demo?
I did some testing. I put the table into fresh new project a gave there simple 100 row return sql script. The table showed up in the one second.
I changed the query in original project and add the same simple query. The table showed up in one second. Data property of the table has binding to SQL named query but it has also three parameters bound to three input text types. I assume binding is the cause
Further tests are showing that bindings are really slowing it down. When I hardcoded values into Where clauses in named query instead of bindings values, its running immediately
Is the query running multiple times with bindings? I had a circular reference in one of my binding once and it destroyed performance in the designer.
I see it runs twice
Can you post the SQL query code? Paste it as text using the </> code formatting button.
A few questions:
- Is the query slow when you test it in the named query using the parameters?
- Is your SQL table indexed appropriately for the fields used in your WHERE clause?
Obviously you also need to find the reason it’s running twice as well and fix that.
SELECT [WorkListId]
,[Date] as Datum
,[MachineId]
,[ProductionOrderId]
,[OperatorPersonalId]
,[SetterPersonalId]
,[Shift]
,[OperationId]
,[Pieces_GoodCount]
,[Pieces_ScrapProcessCount]
,[HoursSpend]
,IIF([Note] is null, '', [Note]) as Note
,[CreatedOn]
,[MinutesSpend_CalculatedSinceStartOfShift]/60 as MinutesSpend_CalculatedSinceStartOfShift
,[MinutesPlanned_ForOperator]/60 as MinutesPlanned_ForOperator
,[Quality_CheckResult]
FROM [Production].[VwWorkList_SpringInsertion]
WHERE
[OperatorPersonalId]=:WorkerId AND
Shift=:ShiftId AND
date=CONVERT(DATETIME,:currentDate,104)
order by CreatedOn desc
Its a view and has only 60 thousand rows
Are you familiar with SQL Server Profiler (if your BD is MS SQL Server)?
I’d like to see what your 2 queries generated using bindings look like. Perhaps one of them returns way too many rows for some reason.
I am using XEvent Profiler. here is the query
Flow for selecting this table
- use datepicker and select date - onchange store selected value to view parameter
- click on textfield, in modal write number to input field - run sql query which returns this row and saves output to view parameter
- click on button and in modal, it loads button and then choose one button, value of it store to view parameter
1,2,3 steps are fast so in total we have 3 parameters and then the sql query runs.
I assume the SQL query bind on data prop in the table runs everytime we change one of those three parameters, than would mean, the query runs at least 3 times and therefore it rerenderes itself ?
and btw I tried loading the table with filled parameters, then I changed only one parameter expecting to load the table faster (running query only once, with already prefilled other params), but it did not so I dont know
back to the sql, I tried running sql query generated by ignition, caught in the XEventProfiler. when it runs with sp_executesql - it runs for more than 5 seconds, plain SQL command runs in less than 1 second.
exec sp_executesql N' declare @workerID int; declare @shiftID nvarchar(2); declare @selectedDate nvarchar(10); set @workerID = @P0; set @shiftID = @P1; set @selectedDate = @P2; SELECT [WorkListId] ,[Date] as Datum ,[MachineId] ,[ProductionOrderId] ,[OperatorPersonalId] ,[SetterPersonalId] ,[Shift] ,[OperationId] ,[Pieces_GoodCount] ,[Pieces_ScrapProcessCount] ,[HoursSpend] ,IIF([Note] is null, '''', [Note]) as Note ,[CreatedOn] ,[MinutesSpend_CalculatedSinceStartOfShift]/60 as MinutesSpend_CalculatedSinceStartOfShift ,[MinutesPlanned_ForOperator]/60 as MinutesPlanned_ForOperator,null as Quality_CheckResult ,[Quality_CheckResult] FROM [Production].[VwWorkList_SpringInsertion] WHERE [OperatorPersonalId]=@workerID AND Shift=@shiftID AND date=CONVERT(DATETIME,@selectedDate,104) order by CreatedOn desc ',N'@P0 bigint,@P1 nvarchar(4000),@P2 nvarchar(4000)','1123',N'1n',N'04.04.2022'
I think I’ve had a case of a query being slow with variables, which is what it seems you’re experiencing with sp_executesql? I’m not sure.
In the link, it says to try using OPTION(RECOMPILE) in the query.
Back to the rendering performance, do you see anything in Gateway > Status > logs? Any errors that might be logging while your view is merely opened in the designer, or when you’re interacting with its inputs?
3 Likes
OMG, this is amazing, the query runs immediately ! Thank you very much