I want to create a separate tool in Ignition Perspective for sql query optimization. This tool should show list of queries, time taken by them, whether it is slow/moderate and how it can be improved as well. Is it something possible ?
Any such tool would need to look at the execution plan of the query to determine how the query is planned to run, and if there are any slow things identified like full table scans
The official recommendation is to remove Ignition from the picture and test queries directly with the database management software, as slow queries are normally a database issue, not an Ignition issue:
That being said, a “Query Execution Analysis” test tool would be an interesting project.
Looking at named queries only, I suppose you could (1) get a list of queries by listing the files in the directory, then (2) you could inspect the files and try to determine the parameters required, then (3) a script could be generated to test and time all the queries.
This sounds like a lot of work that will produce mediocre results at best.
Maybe this is a good task for an AI agent. You could start with logging all the queries and feeding the log to the agent, and have it write the results to a db table, which can the be displayed in Ignition.
Sounds like fun. Good luck.
If this is for fun and a learning project I say go for it there's a lot of things you can touch on and learn - gathering what queries are being run by ignition, parsing the logs etc. Ignition already logs slow queries and I have a script to fetch them from a while ago.
If this is for production I might say to not bother. Find the slow queries that Ignition logs, use your database software of choice to analyze the query and dissect from there. Theres not much more to it in my experience and I don't know what value add Ignition will do for you here outside of maybe curating slow queries into a db / email for you or a DBA on a regular basis which could admittedly be helpful.