One property crashes IRIS query

Question

Question

Hello,

One of our clients has a 'Notes' class with over 3 million records. We have a report that pulls data from this table that was taking about an hour to run. Our test environment (which has a copy of the production database) runs the same report query in 1 second.

We attempted to purge and rebuild indices which made an improvement (down to 15 minutes) but still not great.

Next we ran tune table through the Mgmt Portal and it timed out. I then ran tune table via the Terminal and it finished in a couple minutes. Suddenly our application was hanging anywhere this table was referenced. We realized there was one boolean property in the WHERE clause causing the problem. Our only recourse was to remove the offending property from the WHERE clause and filter on that value within the code loop instead. That works fine but we'd still like to know what is going on. Has anyone seen behavior like this and if so how did you deal with it? Thanks very much!!

Product version: IRIS 2019.1

$ZV: IRIS for Windows (x86-64) 2019.1.1 (Build 612_0_19971U) Mon Jun 1 2020 18:51:40 EDT

Discussion (3)3

Log in or sign up to continue

score 0 · Answer 1 · 2025-04-09T13:28:32-04:00

Stephen Canzano · Apr 9

Can you share the query plan?

What mode are you running in the query in(Logical/ODBC/Display)

0 0

score 0 · Answer 2 · 2025-04-09T22:09:45-04:00

This sounds like the tune table messed up the table statistics. I would look at the table statistics for that boolean field. Also i would open a support ticket with WRC on this.

score 0 · Answer 3 · 2025-04-10T11:53:12-04:00

Hi Jerry,

Without knowing more about the specific query plan and IRIS setup it would be hard to know what's going on - I would highly recommend reaching out to the WRC about this if you are able. I will note that TUNE TABLE has had significant functionality improvements since IRIS version 2021.2 (see https://community.intersystems.com/post/20212-sql-feature-spotlight-smar...) , it's possible you are missing a bug fix; again WRC would be the best point of contact. Best of luck!