Find the queries and indexes that cost you time

The report is never precise

"The overnight job took four hours instead of forty minutes." "The patient search screen hangs." "It was fast yesterday." Nobody hands you a statement, a plan and a row count. So the work splits into four steps, and the tools on this page follow them: find the cause, confirm it, fix it, verify that the fix is what changed.

1. Find the cause — scan the schema, look at what is cached, read the plan.
2. Confirm it — profile the statement and see where the time goes.
3. Fix it — statistics, an index, or a purged cached query.
4. Verify — re-run the profile and compare.

Step 1: find the cause

Scan the namespace for schema problems

The schema scanner walks a whole namespace and reports what it finds, grouped into warnings and errors. Two findings come up in nearly every legacy schema:

• Redundant indexes. An index whose leading columns are already covered by another index. It costs you on every insert and update and buys you nothing on read. Twenty years of "let us add an index for this report" leaves a lot of them behind.
• Foreign keys with no index. A referential constraint with no index on the referencing column. Every delete on the parent table turns into a full scan of the child table to check the constraint. This is the classic cause of a delete that takes minutes.

Beyond those, the scan reports the smaller structural problems that accumulate in a schema nobody has audited: definitions that no longer match the data, and objects that look like leftovers from a migration. Findings are sorted by severity so an error is never buried under thirty warnings.

Look at what IRIS has cached

InterSystems IRIS does not re-plan a statement every time you run it. On the first prepare it builds a plan and stores it as a cached query, keyed on the statement text with the literals stripped out. Every later execution of that statement reuses the stored plan.

That is why "it was fast yesterday" is a real diagnosis and not a shrug. The plan was built when the table held eight thousand rows and the optimiser quite reasonably chose a scan. The table now holds four million rows. The statement has not changed, the data has, and the plan is frozen in between. Nothing in the query text tells you this — you have to look at the cache.

The cached query analyser lists the cached queries in a namespace with their statement text, execution counts and the time they have cost, so the expensive ones sort to the top. From the same list you can delete a single cached query or clear a set of them, which forces the next execution to re-prepare against today's statistics.

Read the execution plan

Plans are shown in the SQL editor itself, next to the statement that produced them. You do not copy the query into a second tool to find out that the loop you expected to be driven by an index is a full scan of the outer table.

Step 2: confirm it with a profile

A plan tells you what the optimiser intends. Profiling tells you what actually happened. SQL DATA LENS drives IRIS's own PTools and PStats instrumentation: run the statement under the profiler and you get per-module counts of rows processed, global references and time, rather than a single total that leaves you guessing which part of the plan was expensive.

This is the step that stops you from optimising the wrong thing. A plan with an ugly-looking scan over a 300-row lookup table is not your problem; a tidy index loop executed 1.2 million times is.

Step 3: fix it

Statistics, and why new statistics may not take effect

The optimiser plans against table statistics: extent size, the selectivity of each field, block counts. TUNE TABLE measures those from the current data. It is worth running after a table has changed volume by an order of magnitude, after the first real data load on a new system, and after a migration that moved data in with statistics left at their defaults.

Then comes the part that trips people up. New statistics do not reach a plan that has already been built. The cached queries are still holding plans derived from the old numbers, so the same statement stays just as slow and it looks as though tuning did nothing.

That is what %RECOMPILE_CQ is for. Run with that option, Tune Table recompiles the cached queries affected by the tables it has just measured, so the new statistics actually get used. The tool exposes it as an option when you start a Tune Table run, alongside the choice of a single table or the whole schema.

Indexes

From the scan findings, the two common fixes are adding the index behind an unindexed foreign key and dropping an index another index already covers. Both are DDL, both are your decision, and both want a cached query recompile afterwards for the same reason as above.

How big is that table, actually?

SELECT COUNT(*) on a 400-million-row table is not a way to learn a table's size. It is a way to occupy the instance for several minutes and get one number.

Instead, SQL DATA LENS reads the storage side directly: which globals a table maps to, how much space those globals occupy, and which database the storage actually lives in — which is not always the database you assume, once subscript-level mapping is involved. That gives you the two facts a performance conversation needs early: the real size of the data, and where on disk it sits.

If you want to go one level further down and read the global contents themselves, that is the Global Browser. For the process, lock and log side of a slow system — who is blocking whom right now — see Administration.

Step 4: verify

Re-run the profile on the same statement and compare it with the run you kept from step 2. Check that the plan changed in the way you expected, and that the cached query you purged has been rebuilt. A fix you cannot demonstrate is a fix you will be asked about again next quarter.

Try it on your own namespace

The performance suite is in every edition, including Free. Point it at a namespace you already suspect and read the scan results — that first list is usually enough to know whether the rest of this page is relevant to you. The download includes a full 30-day Pro trial, with no registration.

Download SQL DATA LENS for Windows →