Question

Slow List View Load

  • 17 November 2017
  • 4 replies
  • 1524 views

Hi,

 

I'm experimenting with K2 Five, and I have built a few test forms based on tutorials and with one exception everything is running pretty smoothly. The exception is, whenever I use associations with smart objects, the list views (which were auto generated by K2) load very slowly; like 60+ seconds to load a simple list of 5 records. (sometimes my browser even prompts me "This is taking a long time, do you want to wait or stop, ..."). There is nothing advanced in these test forms, and there is not even a lot of data (5-10 test records for each smart object and only about 5 properties for each smart object), but the slow load behavior is consistently slow whenever the smart objects use associations, and consistently fast when the smart objects do not use associations. Can anyone point me in the right direction to troubleshoot this?

 

Thank you!


4 replies

1.  What type of SmartObjects are you making use of?  SmartBox, SQL, Web Service, Active Directory, etc.?


 


2.  If you execute some of these SmartObjects outside the context of SmartForms, perhaps via the SmartObject Service Tester tool (C:Program Files(x86)K2 blackpearlBin), how does the execution time look?  This would help to determine if it is something with the data access/service broker layer or with the UI rendering layer (or combination of both) and where we can shave the load time.


 


Using association will add some overhead, but perhaps the same functionality/requirement can be implemented without usage of the auto generation and discovery of associations, as per:


 









Use associations sparingly Discovering associations between SmartObjects can add overhead. For example, while a view can discover an association for the current SmartObject and automatically generate a drop-down list for you, it may be more efficient at runtime if you remove the association and create the drop-down list manually, pointing it to the relevant SmartObject. This way, you remove the additional overhead added by the association.

 


https://help.k2.com/kb001859


* this KB will also have other performance considerations

Thank you for the suggestion Tin,

If I use the Smart Object Service Tester tool as you suggest, I execute the list method of the Smart Object that uses the SQL Server Service Instance and it is slow. There are 21,000 records in the table, and whether I query the top 10 results or all 21,000, it takes 12 seconds to return the results. Screenshot attached. If I run a raw select query (in Microsoft SQL Server Manager) the results are instant (to rule out the query or the table being the source of the slowness). It's a pretty basic setup, so could it have something to do with the speed of the communication between K2 and the SQL server? Do you have any furhter suggestions to improve this?

 



Thank you,
Brian

Is the SQL Server that is housing this database is different from the one housing the K2 database?


If so, you may be able to improve performance by setting 'Use Native SQL Execution' = true and 'On Different SQL Server' = true.



"If the Use Native SQL Execution setting is True and the On Different SQL Server setting is True, K2 needs to execute the sp_addlinkedserver stored procedure on the target database to set up the necessary resources. To do so, the K2 Service Account must be granted SysAdmin permissions on the target database. These permissions can be revoked after the linked server has been added."

https://help.k2.com/onlinehelp/k2blackpearl/userguide/4.7/default.htm#ServiceBrokers/SQLServer/SQLService_Troubleshooting.htm#splinkedserver

This will add a linked server from the SQL Server hosting the K2 database to the SQL server containing your business database to possibly improve execution performance as per:

https://help.k2.com/displaycontent.aspx?id=5998


 


I believe you can continue to use the SmartObject Service Tester to test, as just the SmartObject exection is already slow (so we can disregard form loads for the time-being).

Thanks Tin,

I've tried what you suggested, SQL is on a different server and I have set the configuration accrodingly. sp_addlinkedserver has been created on the target database. There is still no improvement in speed.


One thing I noticed while I was exploring the SmartObject Service tester was that I could enter an improperly formatted query - and it still took *personal details removed* seconds to return an error. So it is clear that the delay is not due to a slow query, but I'm guessing it must have something to do with the connection or authentication. The SQL is on a different server, but it is in the same room as the K2 server, so I doubt the delay has anything to do with data transfer speed.

 

Could you suggest any other avenues I can troubleshoot?

 

I appreciate your help -Brian

Reply