How to improve the performance of the following SQL statement using HIGH VOLUME of DATA
Melissa Laurel
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
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
--
