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?
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.
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.
Stored procedures are compiled. What that means is that the database server saves information pertaining to the execution plan so that it doesn't have to figure that out when it decides how to get your data for you.
I don't really use views. I have read in a SQL blog (years ago) that there are times when views are good and times when they are bad but I don't know what those are. The main reason I don't use views is that people that worked where I work before I was here used them excessively and made disgusting queries that needlessly add complication and produce very ugly execution plans because they're doing lots of redundant work as the queries join a bunch of views (7+ joins of views in a query that could be written much simpler).
The only way to know for sure if a view is helping in your setup is to compare the execution plan.
Sometimes the impact of something will vary depending on how much data your have in your database even on the same schema.
I only really use views for keeping a common place where virtual columns are defined as Phil just mentioned or for queries that I use all the time in both Ignition/DB that are full of joins and are used frequently. In my application we track invoices and to show all the data regarding an invoice - what customer it belongs to down to what project and sales order, required 7 joins, so a SELECT * FROM view_invoices saves a lot of time since we use that query all the time in our application or in the db directly.
I think Materialized Views may be able to improve performance, otherwise plain views are essentially the named queries of the database.