# Named Query vs Stored Procedure Performance at scale **URL:** \ **Category:** Ignition\ **Tags:** ignition81\ **Created:** [April 26, 2022, 3:21pm UTC](https://forum.inductiveautomation.com/t/named-query-vs-stored-procedure-performance-at-scale/58878 "2022-04-26T15:21:03Z")\ **Posts on this page:** 12\ **Page:** 1
**Author:** ![kgamble](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/kgamble/32/8019_2.png) [@kgamble](https://forum.inductiveautomation.com/u/kgamble)\ **Post date:** [April 26, 2022, 3:21pm UTC](https://forum.inductiveautomation.com/t/named-query-vs-stored-procedure-performance-at-scale/58878/1 "2022-04-26T15:21:03Z")
Question similar to: [Named Queries vs. Stored Procedures](https://forum.inductiveautomation.com/t/named-queries-vs-stored-procedures/53351), 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 ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [April 26, 2022, 3:29pm UTC](https://forum.inductiveautomation.com/t/named-query-vs-stored-procedure-performance-at-scale/58878/2 "2022-04-26T15:29:33Z")
> [@kgamble](#): > > it should optimize it and store that plan in cache for re-use, meaning that ad-hoc SQL is equally affective as Stored Procedures 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. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [April 26, 2022, 3:42pm UTC](https://forum.inductiveautomation.com/t/named-query-vs-stored-procedure-performance-at-scale/58878/3 "2022-04-26T15:42:44Z")
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. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 10, 2022, 7:46pm UTC](https://forum.inductiveautomation.com/t/named-query-vs-stored-procedure-performance-at-scale/58878/4 "2022-11-10T19:46:53Z")
If my named query runs in 5.644s, would it run faster as a stored procedure? My gateway and database are on different machines. Is it a known that it will be faster or slower? Are there many factors that will tradeoff between the two? Or are there just like one or two factors? ---
**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\ **Post date:** [November 23, 2022, 9:26pm UTC](https://forum.inductiveautomation.com/t/named-query-vs-stored-procedure-performance-at-scale/58878/5 "2022-11-23T21:26:24Z")
Yes. 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. ---
**Author:** ![nminchin](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/nminchin/32/6911_2.png) [@nminchin](https://forum.inductiveautomation.com/u/nminchin)\ **Post date:** [November 23, 2022, 10:20pm UTC](https://forum.inductiveautomation.com/t/named-query-vs-stored-procedure-performance-at-scale/58878/6 "2022-11-23T22:20:05Z")
Any idea how a SQL View compares with an SP? ---
**Author:** ![Steve\_Laubach](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/steve_laubach/32/60900_2.png) [@Steve\_Laubach](https://forum.inductiveautomation.com/u/Steve_Laubach)\ **Post date:** [November 28, 2022, 1:22pm UTC](https://forum.inductiveautomation.com/t/named-query-vs-stored-procedure-performance-at-scale/58878/7 "2022-11-28T13:22:32Z")
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. ---
**Author:** ![pturmel](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/pturmel/32/3295_2.png) [@pturmel](https://forum.inductiveautomation.com/u/pturmel)\ **Post date:** [November 28, 2022, 1:24pm UTC](https://forum.inductiveautomation.com/t/named-query-vs-stored-procedure-performance-at-scale/58878/8 "2022-11-28T13:24:58Z")
I sometimes use views to create virtual columns to be used as DB pens in an EasyChart. That usually means something 1:1 with a real table. ---
**Author:** ![bkarabinchak.psi](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/bkarabinchak.psi/32/83567_2.png) [@bkarabinchak.psi](https://forum.inductiveautomation.com/u/bkarabinchak.psi)\ **Post date:** [November 28, 2022, 1:36pm UTC](https://forum.inductiveautomation.com/t/named-query-vs-stored-procedure-performance-at-scale/58878/9 "2022-11-28T13:36:19Z")
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. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 28, 2022, 2:47pm UTC](https://forum.inductiveautomation.com/t/named-query-vs-stored-procedure-performance-at-scale/58878/10 "2022-11-28T14:47:32Z")
I searched for SQL View on the manual. Didn't see anything. What is meant by SQL View? ---
**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\ **Post date:** [November 28, 2022, 2:58pm UTC](https://forum.inductiveautomation.com/t/named-query-vs-stored-procedure-performance-at-scale/58878/11 "2022-11-28T14:58:44Z")
Views aren't an Ignition thing, they are a SQL thing. > **[SQL CREATE VIEW, REPLACE VIEW, DROP VIEW Statements](https://www.w3schools.com/sql/sql_view.asp)** > > W3Schools offers free online tutorials, references and exercises in all the major languages of the web. Covering popular subjects like HTML, CSS, JavaScript, Python, SQL, Java, and many, many more. ---
**Author:** ![zacharyw.larson](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/zacharyw.larson/32/23749_2.png) [@zacharyw.larson](https://forum.inductiveautomation.com/u/zacharyw.larson)\ **Post date:** [November 28, 2022, 3:00pm UTC](https://forum.inductiveautomation.com/t/named-query-vs-stored-procedure-performance-at-scale/58878/12 "2022-11-28T15:00:50Z")
Thanks