# Slow Performance for SQL of Larger Result Sets **URL:** \ **Category:** General Discussion\ **Tags:** database, ignition81\ **Created:** [February 25, 2023, 5:48pm UTC](https://forum.inductiveautomation.com/t/slow-performance-for-sql-of-larger-result-sets/70326 "2023-02-25T17:48:43Z")\ **Posts on this page:** 10\ **Page:** 1
**Author:** ![Ahiero](https://avatars.discourse-cdn.com/v4/letter/a/a698b9/32.png) [@Ahiero](https://forum.inductiveautomation.com/u/Ahiero)\ **Post date:** [February 25, 2023, 5:48pm UTC](https://forum.inductiveautomation.com/t/slow-performance-for-sql-of-larger-result-sets/70326/1 "2023-02-25T17:48:43Z")
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: 1. 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. 2. 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. ```auto 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 ``` ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [February 26, 2023, 12:24am UTC](https://forum.inductiveautomation.com/t/slow-performance-for-sql-of-larger-result-sets/70326/2 "2023-02-26T00:24:49Z")
How are you executing the query in ignition? Have you tried using the query browser? [https://docs.inductiveautomation.com/display/DOC81/Database+Query+Browser](https://docs.inductiveautomation.com/display/DOC81/Database+Query+Browser) ---
**Author:** ![Ahiero](https://avatars.discourse-cdn.com/v4/letter/a/a698b9/32.png) [@Ahiero](https://forum.inductiveautomation.com/u/Ahiero)\ **Post date:** [February 26, 2023, 2:57pm UTC](https://forum.inductiveautomation.com/t/slow-performance-for-sql-of-larger-result-sets/70326/3 "2023-02-26T14:57:40Z")
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). ---
**Author:** ![jlandwerlen](https://avatars.discourse-cdn.com/v4/letter/j/bc8723/32.png) [@jlandwerlen](https://forum.inductiveautomation.com/u/jlandwerlen)\ **Post date:** [February 26, 2023, 4:16pm UTC](https://forum.inductiveautomation.com/t/slow-performance-for-sql-of-larger-result-sets/70326/4 "2023-02-26T16:16:48Z")
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. ---
**Author:** ![JordanCClark](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/jordancclark/32/83362_2.png) [@JordanCClark](https://forum.inductiveautomation.com/u/JordanCClark)\ **Post date:** [February 27, 2023, 12:24pm UTC](https://forum.inductiveautomation.com/t/slow-performance-for-sql-of-larger-result-sets/70326/5 "2023-02-27T12:24:41Z")
Are there any indexes? I'd say "Transaction Seq" would benefit from one. ---
**Author:** ![Ahiero](https://avatars.discourse-cdn.com/v4/letter/a/a698b9/32.png) [@Ahiero](https://forum.inductiveautomation.com/u/Ahiero)\ **Post date:** [February 27, 2023, 1:05pm UTC](https://forum.inductiveautomation.com/t/slow-performance-for-sql-of-larger-result-sets/70326/6 "2023-02-27T13:05:47Z")
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. ---
**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:** [February 27, 2023, 1:19pm UTC](https://forum.inductiveautomation.com/t/slow-performance-for-sql-of-larger-result-sets/70326/7 "2023-02-27T13:19:52Z")
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. ---
**Author:** ![Ahiero](https://avatars.discourse-cdn.com/v4/letter/a/a698b9/32.png) [@Ahiero](https://forum.inductiveautomation.com/u/Ahiero)\ **Post date:** [February 27, 2023, 2:23pm UTC](https://forum.inductiveautomation.com/t/slow-performance-for-sql-of-larger-result-sets/70326/8 "2023-02-27T14:23:27Z")
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. ---
**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:** [February 27, 2023, 2:31pm UTC](https://forum.inductiveautomation.com/t/slow-performance-for-sql-of-larger-result-sets/70326/9 "2023-02-27T14:31:56Z")
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. ---
**Author:** ![Ahiero](https://avatars.discourse-cdn.com/v4/letter/a/a698b9/32.png) [@Ahiero](https://forum.inductiveautomation.com/u/Ahiero)\ **Post date:** [February 27, 2023, 2:54pm UTC](https://forum.inductiveautomation.com/t/slow-performance-for-sql-of-larger-result-sets/70326/10 "2023-02-27T14:54:05Z")
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.