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

29 views
Skip to first unread message

Melissa Laurel

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

SELECT 

  e.EpisodeNo

, 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 (

'M1010','M1110','M1210','M1910','M2010','M2011','M2100','M2210','M2310','M2410','M2510','M2610','M2611','M2710',

'M2810','M2811','M2910','M2911','M3010','M3110','M3210','M3310','M3410','M3510','M4010','M5110','M5210','M5211',

'M5310','M5311','M5410','M5411','M5412','M5413','M5510','M5511','M5610','M5611','M5710','M5711','M5810','M5811',

'M5910','M5911','M6010','M6110','M6210','M6310','M6410','M6510','M6610','M7010','M7110','M7510','M7610','M7710',

'M7810','M7910','M8010','M8011','M8110','M8210','M8310','M8410','M8510','M8511','M8610','M8611','M8710','M8711',

'M8810','M8910','M9010','M9110','M9210','M9310','M9410','M9510','M3111','M3122','M3138','M3155','M3011','M2111',

'M2311','M2314','M2411','M2413','M9610')

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

'M1000','M1100','M1200','M1900','M2000','M2001','M2100','M2200','M2300','M2400','M2500','M2600','M2601','M2700',

'M2800','M2801','M2900','M2901','M3000','M3100','M3200','M3300','M3400','M3500','M4000','M5100','M5200','M5201',

'M5300','M5301','M5400','M5401','M5402','M5403','M5500','M5501','M5600','M5601','M5700','M5701','M5800','M5801',

'M5900','M5901','M6000','M6100','M6200','M6300','M6400','M6500','M6600','M7000','M7100','M7500','M7600','M7700',

'M7800','M7900','M8000','M8001','M8100','M8200','M8300','M8400','M8500','M8501','M8600','M8601','M8700','M8701',

'M8800','M8900','M9000','M9100','M9200','M9300','M9400','M9500','M3101','M3102','M3103','M3104','M3001','M2101',

'M2301','M2302','M2303','M2304','M9600')

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,

Melissa

Brendan Bannon

unread,
Nov 30, 2018, 1:57:45 PM11/30/18
to intersystems...@googlegroups.com

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

 

Or

 

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

 

 

Brendan

--
--
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 intersystems-publi...@googlegroups.com.
To post to this group, send email to intersystems...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

Andrew Makinson

unread,
Dec 1, 2018, 11:42:47 AM12/1/18
to intersystems...@googlegroups.com
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
--

stephen...@gmail.com

unread,
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
Forward
0 new messages