count() is too slow. Why?
I have a table with 5M rows, the table contains lab observation codes and display names, both columns have type varchar(2000) and both are indexed.
The query looks like:
select code_1_text, count(code_1_text)
from demo.observation_lab
group by code_1_text
order by 2 desc
The table contains ~1000 distinct display names.
It takes 4 minutes for the query to complete on a VM with some pretty old Xeon, 4 cores, 32G RAM, NVME SSD and Linux on board.
Isn't it too slow? During the execution I see no active hardware resources consumption - CPU load is 12-25%, RAM is almost free, swap file is not used.
The execution plan is as follows:
Relative Cost = 3098072
• Call module F, which populates temp-file B.
• Call module J, which populates temp-file C.
• Read temp-file C, looping on count([value]) and a counter.
• For each row:
- Output the row.
Module: F
• Divide master map demo.observation_lab.IDKEY into subranges of IDs.
• Call module A in parallel on each subrange, piping results into temp-file D.
• Read temp-file D, looping on a counter.
• For each row:
- Check distinct values for %SQLUPPER(code_1_text) using temp-file B,
subscripted by a hash of the %SQLUPPER(code_1_text).
- For each distinct row:
· Add a row to temp-file B, subscripted by the hash,
with node data of %SQLUPPER(code_1_text).
- Update the accumulated count([value]) in temp-file B,
subscripted by the hash
Module: J
• Read temp-file B, looping on the hash subscript.
• For each row:
- Add a row to temp-file C, subscripted by count([value]) and a counter,
with node data of the uncollate expression.
Module: A
• Call module B, which populates temp-file A.
• Read temp-file A, looping on the hash subscript.
• For each row:
- Add a row to temp-file D, subscripted by a counter, with node data of %SQLUPPER(code_1_text) and count([value]).
Module: B
• Read master map demo.observation_lab.IDKEY, looping on the subrange of ID.
• For each row:
- Check distinct values for %SQLUPPER(code_1_text) using temp-file A,
subscripted by a hash of the %SQLUPPER(code_1_text).
- For each distinct row:
· Add a row to temp-file A, subscripted by the hash,
with node data of %SQLUPPER(code_1_text).
- Update the accumulated count([value]) in temp-file A,
subscripted by the hash
Why is it using temp files instead of RAM?
Also, if I rewrite the query to aggregate lab observation codes (which are much shorter than display names) the query starts fetching almost immediately.
Comments
From that query plan it looks like you don't have a index on code_1_text or the index isn't being used.
Perhaps try running a tune table.
Hi Peter and thanks a lot for the idea. There is an index for that column. I rebuilt the index and now it is fast. So simple.
The Temp file often times uses a process private global
Process-private globals are written to the IRISTEMP database. In contrast to global variables, InterSystems IRIS does not treat a SET or KILL of a local variable or a process-private global as a journaled transaction event; rolling back the transaction has no effect on these operations.
That being said as Peter mentioned if you had an index on code_1_text you could greatly improve performance. In fact I suspect this type of query would be completely index satisfiable, ie the query would only examine the index and would not have to go to the mastermap for even better performance.
Depending on the legth of values for code_1_text if you do chose to add an index you might want to define the property as
Property code_!_text as %String(COLLATION="SQLUPPER(113)",MAXLEN=32000);
by setting the collation and length. If the values exceed the maximum global subscript length and you do not do this you could encounter <subscript> errors.
A subscript has an illegal value or a global reference is too long. For further details, refer to $ZERROR. For more information on maximum length of global references, see “Determining the Maximum Length of a Subscript”.
source code of dc.observation_lab
Class dc.a Extends %Persistent [ SqlTableName = observation_lab ]
{
Index iCode On code;
Index iCodeText On codetext;
Property code As %String(MAXLEN = 2000);
Property codetext As %String(MAXLEN = 2000) [ SqlFieldName = code_1_text ];
/// d ##class(dc.a).Test()
ClassMethod Test(N = {5e6})
{
s t=$zh
d DISABLE^%NOJRN
k ^dc.aD,^dc.aI
f i=1:1:N s c=i-1#1000+1,^dc.aD(i)=$lb("",c,"code_1_text_"_c)
s ^dc.aD=N
d ENABLE^%NOJRN
w "(time) insert = ",$zh-t,!
s t=$zh
d ..%BuildIndices(,,,$$$NO)
w "(time) %BuildIndices = ",$zh-t,!
s t=$zh
d $system.SQL.TuneTable("dc.observation_lab",,1,,,,,,,"100%")
;&sql(TUNE TABLE dc.observation_lab %SAMPLE_PERCENT '100%')
w "(time) tune table = ",$zh-t,!
}
}The following query does not use iCode/iCodeText indexes (2025.2.CE):
select code_1_text, count(code_1_text) from dc.observation_lab group by code_1_text order by 2 desc
PS: if possible, I would try to convert the text into a number in some way and index this number already, perhaps even with the bitmap type.