I have a recurring problem when using Ignition to query larger result sets from databases. When I run a query through SQL Developer the result set (including export) takes 2-3 minutes for ~50K rows of 12 columns; executing the same SQL from Ignition will take more than 10 minutes before timing out (both times with expected business load on the database). The development Ignition server I am working on shows the CPU never goes over 20% and memory doesn't go over 2GB (8GB allocated). I have the same trend when working with other databases in the production environment against production databases. Ignition takes three times the duration to get the full result set compared to a database tool (VS Code, SQL Developer, SSMS). Both Ignition and SQL Developer are using Java-based connectors, right? Please help me understand what is causing the inefficiency.
UPDATE based on comments:
It hasn't seemed to matter how I try to get the data: Database Query Browser, scripting, or binding. The more columns the bigger the factor.
Sample query below. This is representative of the complexity. I changed some names to post it but didn't change the complexity. Timing the query this morning (no business load on Sunday AM) yielded a factor of 2.5 increase in time between SQL Developer with export and the Ignition Database Query Browser tool. This was repeatable over two runs each.
SELECT
"Transaction Seq" TRANS_SEQ
,TRUNC("Account Date") ACCOUNT_DATE
,"Project" PROJECT
,"Sub Project" SUB_PROJECT
,"Activity" ACTIVITY
,"Activity Seq" ACTIVITY_SEQ
,"Resource ID" RESOURCE_ID
,"Employee" EMPLOYEE
,"Quantity" QTY
FROM SOURCE_TABLE
WHERE 1=1
AND "Org Code" = 'ExampleCode'
AND "Quantity" <> 0
ORDER BY "Transaction Seq" ASC
Yes. It doesn't matter if I use the Query Browser, binding, or script to execute the query. I have tried to keep it all gateway-side by using messaging as well but it always takes significantly longer than executing from a SQL tool on my workstation (same PC as the client and designer tests).
You'll probably need to post your specific query for anyone to really be able to offer much help without guessing. If that's not possible you may consider reaching out to support.
Indexed or not, the indexes are consistent between the different methods of executing the same SQL on the database that has significantly different performance. I can't see the index definitions on this database. "Transaction Seq" is unique in the table so likely the primary key but I can't tell that for sure.
More than likely this is a side effect of Oracle-specific functionality in the Oracle JDBC driver that SQL Developer uses (because it is from Oracle and works with Oracle) and Ignition does not (because it only use the cross-brand standard features of JDBC). Ignition also does no streaming of result sets--it accumulates an entire result in its own Dataset format, then hands it off.
Consider writing a simple java program that exercises the same query using JDBC and without using cursors or any brand-specific features.
Thank you for the explanation. Although the java testing sounds interesting it won't make Ignition faster. I'll handle avoiding timeouts with a pagination approach.
Eewww! So this is a query for a UI? In that case, aim for query response times of no more than a couple seconds. Preferably half a second or less. Even getting close to timeout when querying for a UI is a big red flag, IMNSHO.
This is not for a UI. I am using Ignition to get a subset of data from an enterprise resource and make a local copy to run some analytics tools on. This will be refreshed using a timed script for once a day. The query duration using Ignition has timed out with a several minute timeout duration for reasons unknown to me. I don't have access to look at the database stats or optimize the view that IT gave me to work with. At this point I am trying to find a workaround. The data should be stable enough to grab in chunks.
Thanks for your input. I've learned a lot from reading your posts over the years.