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. 

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.

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.