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