Named Query vs Stored Procedure Performance at scale

Question similar to: Named Queries vs. Stored Procedures, but more focused on the performance aspect of things.

I have an application with about 150 different named queries of all shapes and sizes. That are all scoped to read data through ~10 views that parse the data down into the correct structure and bring together all of the various datapoints the application needs.

When we started this application the Ignition Find and Replace did not go through named queries, and all the named queries were stored as binaries, so it felt like a no-brainer to put all of the SQL code in MSSQL stored procedures. This way, if needed, we could export all the schema for a database, and “Find and replace” through the queries at mass.

Essentially each of our named queries is just the Exec This.Procedure :Param1 :Param2 that is required to execute the procedure.

Now that (in 8.1.13 I think?) all of the queries are stored as .sql files, it seems like it may have some ansilarry benefits to store them inside Ignition instead. This would make it easier to version control new queries, and allow multiple versions of the same app, pointed to the same database, to function differently if that’s desirable (for testing out new dev features, etc.)

I am curious if there are any performance problems with this? Technically since for each query its the same query being provided to MSSQL every time, it should optimize it and store that plan in cache for re-use, meaning that ad-hoc SQL is equally affective as Stored Procedures. However I am not sure if I am missing anything else?

I am curious on thoughts and opinions.

Notably from @pturmel because he knows everything

1 Like

Probably should test this. IIRC, true stored procedures’ plans will be held until table stats are updated–effectively indefinitely. An ad-hoc cache may not persist as long.

Aside from that, and maybe even if plan caching is less than ideal, I’d migrate to NQs. Your reasoning makes sense. With the possible exception of security-related tasks. True stored procedures can typically be marked to execute with their creator’s privileges instead of the caller’s. This makes moderated access to sensitive information possible without trusting the Ignition designer too much.

1 Like

I think the Version Control point you make and the additional (albeit small) benefit that all your resources are in Designer will make your troubleshooting easier (as opposed to having to switch between Designer and a DB Workbench) are reasons enough.

It’s nice knowing you can Control+F in designer and know its searching both your code and data sources.

Recently I’ve had to deal with stored procedures a bunch. I think its much more preferable to write and read database transaction logic that is written in python with named queries compared to the a stored procedure written in SQL. In my case I converted a 500 line Stored procedure to something that was less than 100 lines of python which to me makes it much more easily maintainable/extendable (and something I could expect someone else to read and understand in a faster time). However this whole paragraph is just personal preference - if you enjoy writing and reading SQL, this doesn’t apply.

1 Like