List method from Stored Procedure vs List Method from K2 SMO/Form Rule

  • 10 January 2020
  • 3 replies
  • 265 views
R
Badge +8

Hi there.  

 

I'll ask my question upfront, then I'll explain what we're seeing.  Would love your input.

 

Question:

 

Do you think it's more efficient to render large list views that might be run by many users at the same time using a LIST method off the database view or a LIST method from a SQL stored procedure?  

 

We were always told by various consultants that the best practice on list views is to put the burdon on the SQL database via stored procedure (SPROC) as opposed to using just K2 "list" method on a database table or view. 

 

We built out a new solution that does this...it's a solution that's used by a lot of people (probably 220+ at any one time in additionl to the 4*personal details removed* other users that are using K2 for other solutions) and the list view fires a lot based on the way the solution is designed.  The typical number of records returned on the list would average between 500 and 1500 records (although the list view only shows 20 at a time) with a maximum of maybe 6500 records in extreme cases. 

 

Lately, we've gotten a lot of complaints from the business that the lists are rendering very slowly.  

 

So, going out to the database and running the stored procedure with selected parameters to return about 1200 records, the number of seconds to run the query would typically be about 1-2 seconds.  Often less than a second.

 

Running the same parameters for the related smart object at about the same time (same traffic) with the smart object service tester (at the height of utilization) it would take anywhere from 37 to 65 seconds to return the same 1200 records.  Huge difference.

 

Obviously, running in the form, it would take the same amount of time or longer.  

 

During those very busy times when it was returning results slowly, I reached out to the DBA and he confirmed that the same list stored procedure running over and over again by the number of K2 users in that form at that time did put some strain on the database and took up a goodly percentage of CPU/RAM.  

 

So...as an experiment, we switched from the stored procedure to generate the list view to simply creating a new "LIST" method on the smart object that generates the list directly from the database view.  

 

It is MUCH MUCH MUCH faster...consistently.  What would take 37 seconds to render in the Smart Object Service Tester using the SPROC woudl take like 1.5 seconds just doing a List method directly from the DB view.  

 

We re-built the form to use the list method last night, and it's run perfectly (much faster) all day today.

 

SO....back to my question.  

 

Do you think it's more efficient to render large list views that might be run by many users at the same time using a LIST method off the database view or a LIST method from a stored procedure?  

 

It was hinted to me by a K2 tech support type that with the new version, they put better caching and traffic control in place for K2 to handle some of that stuff.  I can't help but think that might be true from what I'm seeing.

 

What do you think?

 

I hope I explained this in an undestandable way.  

 

Best,

 

Rob

 

3 replies

B
Badge +15

Hi,


 


To be honest, this is the first time I am reading about such a thing. Normally if you want to directly interact with a table, my recommendation is to just stick with a simple SmartObject, and use the SmartObject to perform all the required CRUD functions on the table. I will only recommend a stored procedure if your operation requires multiple updates and joins across multiple tables, and you want a simple one call to the stored procedure to do everything.


 


The difference between using a stored procedure and a List method is that the List method work similar to how you


write an application to query your database. You send the SELECT command over to the SQL Server, then SQL Server executes the command and return it back to your application.


 


With stored procedure, the commands are already stored in your database, so there is no need to send the command over. Thereotically speaking, with a well-designed and optimised stored procedure, it should perform better. But we often have people complaining about their stored procedure's performance dropping after some time.


 


The tricky part is that they will often run the stored procedure directly on SSMS, and since that worked perfectly, they will jump to the conclusions that K2 is the one causing the peformance problem. The truth is, testing the stored procedure directly on SSMS can be inaccurate. If you google for it, you will find articles discussing why executing the stored procedure directly on SSMS can be faster than when executing the stored procedure from the application. This and this article are pretty good in explaining this scenario.


 


I am not an expert with SQL Server, so if there's anything wrong with what I am about to say, or if anyone has more to add on, please do so. From what I understand, a stored procedure's performance can be affected if your execution plan is screwed up. Over time as your tables grow, a stored procedure's execution plan can get outdated. This can in turn affect the performance of your stored procedure. When you execute the stored procedure directly on SSMS, it is using a different execution plan as your application. This explains why the stored procedure seems to be peforming better on SSMS.


 


If you have a really large table, then I do not think it really matter what you use. Note that aside from fetching the results from the database, K2 will also need to format the retrieved results into a format that can be used everywhere in K2. This will incur additional time it takes for the SmartObject to finish executing. That is why the best practice is to separate large dataset, and not retreive everything in one go. For example in your List View, you can configure pagination. Or if you do not need to display everything, set a filter to reduce the number of records that will be retrieved. 

K
Badge +7

Hello  @Robert_Nicholas 

 

Depends on your situation:

 

If the operation requires a large amount of T-SQL code or is performed repetitively, stored procedures can be faster than batches of T-SQL code. During creation, stored procedures are parsed and optimized, and you can use an in-memory version of the procedure after the procedure executes the first time. Each time a T-SQL statement from the client is run, SQL Server compiles, optimizes, and executes the statement. Take advantage of the processing power of the database server when possible because it offloads processing from your application.

 

Pros: for ETL (extract-transform-load data into the db) jobs

-for other database ops tasks

-with triggers

-any time you'd be shuttling a ton of data over the wire only to do something to it then put it back (eg cloning a relationship graph)

-to perform simple tasks that lend themselves well to SQL

 

Cons:

-when you're worried about compatibility between different DBMSs

-when what you're doing is subject to change extremely quickly

 

From the research I have conducted, I would say go ahead and continue and your tests and study says that the reponse time is much quicker rather going the conventional sql route. I repeat: Take advantage of the processing power of the database server when possible because it offloads processing from your application.

 

All the best.

T
Userlevel 5
Badge +13

My differentiator for deciding whether to use a stored procedure or simply list SmartObject method is whether I'm expecting to do any joins in my query, like to lookup display values by lookup Ids to get a fully populated list in one shot as opposed to multiple individual queries.

Reply


View our Community Site Map

Nintex Community Sitemap
Terms & ConditionsCookie settings