How to improve the performance of the following SQL statement using HIGH VOLUME of DATA

Skip to first unread message

Melissa Laurel

Nov 30, 2018, 12:18:00 PM11/30/18
to IRIS, Caché, Ensemble
I have the following query:



, et.TestSet

, source.ResultText AS Source

, et.DateAuth

, e.DateRecd

, ma.AccessionNo

, ts.Name AS TestSetName

, p.Name AS PatientName

, e.RefDoc

, p.DOB

, p.Sex

, c.Name AS ClientName

, c.Code AS ClientCode

, culture.TestCode

, culture.ResultText

, culture.ResultValue

, mio.Status

, ist.Name as IsolateStrengthName

, o.OrgName

, ar.ID as AntibioticResultID

, ar.MIC

, ar.Result as AntibioticResult

, a.AntName as AntibioticName

FROM SQLUser.Episodes e

JOIN SQLUser.EpisodeTests et ON e.EpisodeNo = et.EpisodeNo

JOIN SQLUser.TestSets ts ON et.TestSet = ts.TestSet

JOIN SQLUser.ClinicDoctors cd ON e.RefDoc = cd.DoctorCode

JOIN SQLUser.Clinics c ON cd.Clinic = c.Code

JOIN SQLUser.Patients p ON e.UnitNumber = p.UnitNumber

JOIN SQLUser.MicroAccession ma ON et.EpisodeNo = ma.EpisodeNo AND et.TestSet = ma.TestSet

JOIN SQLUser.Results source ON e.EpisodeNo = source.EpisodeNo AND source.TestSet = ts.TestSet AND source.TestCode IN (








LEFT OUTER JOIN SQLUser.Results culture ON e.EpisodeNo = culture.EpisodeNo AND culture.TestSet = ts.TestSet AND culture.TestCode IN (








JOIN SQLUser.MicroIsolOrganisms mio ON ma.AccessionNo = mio.AccessionNo

LEFT OUTER JOIN SQLUser.IsolatedOrganisms io ON ma.EpisodeNo = io.EpisodeNo AND ma.TestSet = io.TestSet

LEFT OUTER JOIN SQLUser.IsolateStrength ist ON io.Strength = ist.IsolateStrength

LEFT OUTER JOIN SQLUser.Organisms o ON io.OrganismCode = o.OrgCode

LEFT OUTER JOIN SQLUser.AntibioticResults ar ON io.EpisodeNo = ar.EpisodeNo AND io.IsolateNo = ar.Isolate AND io.TestSet = ar.TestSet

LEFT OUTER JOIN SQLUser.Antibiotics a ON ar.Antibiotic = a.AntCode

WHERE et.DateAuth BETWEEN {?From Date} AND {?To Date}

AND mio.Status = 'Report'


As the volume of data increase, the query runs slower. We had a suggestion to create an index for each item that is in the JOIN statement above.

For example: JOIN SQLUser.Patients p ON e.UnitNumber = p.UnitNumber

An index would have to be created on UnitNumber located on both tables SQLUSER.Patients and SQLUSER.Episodes.

Is that the only resolution available to make this query more efficient?

Thank you in advance,


Brendan Bannon

Nov 30, 2018, 1:57:45 PM11/30/18

Hi Melissa


Step 1 of an SQL performace problem is to run TuneTable on all the tables involved


From terminal:  Do $SYSTEM.SQL.TuneTable(“TableName”,1,1)


Or on all tables in the namespace:  Do $SYSTEM.SQL.TuneTable(“*”,1,1)


This will record information about your data that will help the Cache generate a better plan for your queries.


Things we want to know that TuneTable gets:


1)       ExtentSize – number of rows in the table

2)      Selectivity - % of rows returned for 1 value of a property

3)      Outliers – if one value shows up way more often then the rest what is it

4)      Block Count – the size of the index

If you want more detail have a look at:


First Look: Optimizing SQL Performance with InterSystems Products




InterSystems SQL Optimization Guide > Optimizing Tables



Yes providing an index on the JOIN properties is a VERY GOOD idea.  We have 2 main types of indices, Standard and Bitmap.  The guides above will explain when you should use one over the other.  Really quick the fewer distinct values the faster a Bitmap will be, so a Boolean value is a great option for a Bitmap index.



The next thing to index would be the fields you have conditions on.


Finally if you want to see what we are doing you can look at the Show Plan of the query and it will give you a description of the order we are going to look at the tables.  Normally the most important thing is getting the starting table right.



So run TuneTable, setup some indices, and then look at the Show Plan.


If you want to share the Show Plan here I can explain what we are doing and maybe make some suggestions.


For more detailed help give InterSystems Support a call and we can talk over all this stuff  617 621 0700




IRIS, Caché, Ensemble
You received this message because you are subscribed to the Google Groups "IRIS, Caché, Ensemble" group.
To unsubscribe from this group and stop receiving emails from it, send an email to
To post to this group, send email to
For more options, visit

Andrew Makinson

Dec 1, 2018, 11:42:47 AM12/1/18
How often is this SQL Being run? 

Its really dependant on what this is. Is it an application query or an on demand daily report query? 

Would it not be more beneficial to create a view for example and select from that view?

Sent from my iPhone

Dec 1, 2018, 11:42:47 AM12/1/18
to IRIS, Caché, Ensemble
Can you provide the SQL query plan.  That would provide a great deal of insight as to what the query is doing.
Reply all
Reply to author
0 new messages