I’m chasing a performance issue on a custom dashboard service and it’s got me puzzled.
The symptom
A custom service that builds a dispatch list takes 7–12 s at one customer. Its own timing logs show ~65% of that goes to one step: reading checklist item data, including one attribute on ChecklistItem and ChecklistItemInstance. Two Query Objects, about 4 s each.
In our dev and QA environments the same code, config and flow take milliseconds.
What I’ve ruled out so far
- Data volume: the checklist tables are tiny, a few hundred rows each.
- Indexes and stats: standard CMF indexes, stats fresh (100% sample), nothing disabled.
- Blocking / concurrency: the issue persists even when no one else is using the system.
- The query shape: I rewrote both queries by hand in SSMS (same joins, same attribute table) and they run in 2–6 ms.
Where I’m stuck
Same code, same configuration, a handful of rows, and it’s only slow on this customer’s database. So something in their data must be different, but I haven’t found what yet.
Has anyone run into something like this with checklists or Query Objects? Any ideas on what else I should compare between the customer’s database and ours? Happy to share traces or more details in the comments.
Hi!
This is a common trap when loading checklist items, we tend to forget how big they can get. Customers often paste high-quality images straight into the Instruction field of ChecklistItem, so a handful of items can easily add up to hundreds of MB, and anything that loads the full item drags all of that with it. It’s also something we rarely replicate in our test master data, where the instructions are usually short plain text.
Even if your Query Object doesn’t select the instructions, the SQL it generates can still include them through a join that brings in every column of the checklist item. So check both, the size of the checklist items in the database, and a trace of the query to see whether the instructions are being read.
Best Regards,
Mário Gomes
Hi @Bruno_Magalhaes,
Can you please share in the DevTools menu whether the time being taken is related to the “Stalled” state or the “Waiting for server response”.
I’m asking this because we recently had a more severe performance issue in some clients’ environments than in others, and this can indicate the root cause.
Best Regards,
João Querido
That was it, thanks! The checklist items at the customer were huge: around 300 MB across ~500 items, with single instructions up to 17 MB because of embedded images. Our test data has nothing like that.
The Query Object reads an attribute of the checklist item. To get it, SQL Server generates a join to the attribute table that selects every column of the checklist item instructions included, and pivots over all items. So even though we never asked for the instructions, SQL Server was reading the full 300 MB on every call.
The fix was to remove the attribute from the Query Object, so the join isn’t generated, and load the attribute separately. The checklist step went from ~8 s to ~30 ms, with the same results.