Named Queries vs. Stored Procedures

Looking for some guidance on best practices for querying/updating the database through Ignition.

Before Ignition, we did almost every database update through stored procedures. Named queries seem to be the native “Ignition way” of doing database updates.

I’m aware that you can call stored procedures from Named Queries, and similarly, through scripting. My question is: what is considered the best practice for updating the database.

Here are some benefits I can think of from each approach:

Stored procedures:

  • Optimized by the database at compile time
  • Enabled conditional logic in the SQL
  • Return multiple types of values

Named queries:

  • Everything is under one roof
  • Less boilerplate code to execute
  • Scalar queries save a lot of code

I tend to use named queries and gateway-scoped “Prep” queries (in script libraries). Mainly to keep everything in Ignition in as DB-brand-agnostic form as possible.

3 Likes

FWIW I don’t think it’s Ignition’s way, but pretty SOP for most web services to be DB agnostic and for the web server software to keep track of NamedQueries so that it knows what it’s allowed to run and not run. Not to mention it then also allows the server (not the db) to do some caching of results (which ignition does with named queries).

I’m with @pturmel - unless absolutely necessary, I keep everything in named queries or at the very least in a self contained that is accessible via gateway message handler.

One pro to this that you didn’t write but that I think is a big plus, is that python is (imo) about 10 times easier to read than SQL.

I’m currently working on a project where there’s a lot of business logic in these 500+ line stored procedures, I converted one of them to a function in Ignition and it’s about 100 lines, which to me is much more readable and therefore maintainable. Unless you’re really used to SQL in which case this probably doesn’t apply.

Heh. Maybe. But I learned SQL about a decade before I learned python, so I might be a “special” case.

1 Like

There is something “pure” about working in only SQL, and after enough days in a row working with it I sort of get it, but it’s definitely a perspective shift to go from python that reads like pseudo code and then SQL, especially going back and forth its like whiplash. Hence why I prefer to keep it all python if possible lol.

So, how do you feel about my view() function? SQL shell with python expressions?

It’s my personal hell!

Jk, it does seem very useful, I’ve not been lucky enough to be allowed to use it though in any projects. Just a little bit of SQL is fine by me.

It’s when I’m reading stored procedures full of temp tables and dynamic SQL it gets my goat.

What is a gateway-scoped “Prep” query?

Any of the system.db.* functions that have “Prep” in their name, used in gateway scope. (That is, excluding vision clients.)

1 Like