[solved] slow queries (from dicom viewers) due to image counting

Finally I had some free time to pinpoint exactly why certain dicom viewers seem much faster than others when it comes to query response times.

I'm talking about the most common type of query like 'show me everything from yesterday/today', which in our case, means about 200-250 results (per day).

Now the problem: some viewers (like Efilm) query results are almost instant. On others (like Radiant or the one from Agfa, I can't remember its name right now) it takes about 10seconds to see the results.

I took a closer look and realized it all boils down to a single difference in the queries - image count. Efilm for example does NOT ask for the number of images up front. Only after user clicks (selects) a certain study - only then there is a query about all kinds of details (image numbers,series etc) regarding this, and only this, one study. This is fast.. barely noticeable.

On the other hand, the other viewers want to know everything up front. Including the number of images in every single study listed.

Here is an example step by step. First - the general query:

SELECT DISTINCT DICOMStudies.StudyDate, DICOMStudies.StudyTime, DICOMStudies.AccessionN, DICOMStudies.StudyModal, DICOMStudies.ReferPhysi, DICOMStudies.StudyDescr, DICOMStudies.PatientNam, DICOMStudies.PatientID, DICOMStudies.PatientBir, DICOMStudies.PatientSex, DICOMStudies.StudyInsta, DICOMStudies.StudyID FROM DICOMStudies 
WHERE DICOMStudies.StudyDate >= E'20190715' and DICOMStudies.StudyDate <= E'20190715' ORDER BY (DICOMStudies.PatientNam)

This one takes about 200ms. No problems here since this is a very infrequent query so 200ms sounds acceptable.

Efilm stops here and shows results. In other cases this is followed by a bunch of additional queries like this one:

SELECT DISTINCT COUNT(1) FROM DICOMImages, DICOMSeries, DICOMStudies WHERE DICOMStudies.StudyDate = E'20190703' and DICOMStudies.StudyTime = E'082206.312000' and DICOMStudies.AccessionN = E'320634-2019' and (DICOMStudies.StudyModal = E'CT' or DICOMStudies.StudyModal LIKE E'CT\\\\%' or DICOMStudies.StudyModal LIKE E'%\\\\CT\\\\%' or DICOMStudies.StudyModal LIKE E'%\\\\CT') and DICOMStudies.ReferPhysi = E'John Smith' and DICOMStudies.StudyDescr = E'Head^HeadStd (Adult)' and UPPER(DICOMStudies.PatientNam) = E'PATRYK^KMIEC' and DICOMStudies.PatientID = E'111111111' and DICOMStudies.PatientBir = E'19570101' and DICOMStudies.PatientSex = E'M' and DICOMStudies.StudyInsta = E'1.2.826.0.1.3680043.2.1326.17.632826.1.2034153.12.936083' and DICOMStudies.StudyID = E'-1' and DICOMSeries.StudyInsta = DICOMStudies.StudyInsta and DICOMImages.SeriesInst = DICOMSeries.SeriesInst

This looks complicated but it's just an study image count. This one takes somewhere between 15 to 25ms. Now you might think this is fast but remember we have to do this for EACH study which means (in our case) *200-250. This means, lets say, 220 separate queries, each taking say 20ms so it sums up to about 4.5sec. From real life testing I know this number is closer to 6-7sec probably due to network overhead/latencies/query processing by viewer or some combination of the above.

It might not be that long of a wait, but it is certainly long enough that doctors whine about it.

Honestly I'm not sure how to solve this.I can't force all software engeneers to change their query so it wouldn't include the image count... and I can't speed up the query cause it's already pretty fast.

The only solution I could think of is to have this number stored as a variable (part of study properties?), so we woudn't have to count images each time someone asks.

Would that be possible? Or maybe there is another solution I missed?

Patryk

EnableComputedFields=0

Perfect! Already tested - instant query responses on both viewers.

Thank you

Best regards

Patryk