<html xmlns:v="urn:schemas-microsoft-com:vml" xmlns:o="urn:schemas-microsoft-com:office:office" xmlns:w="urn:schemas-microsoft-com:office:word" xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" xmlns="http://www.w3.org/TR/REC-html40"><head><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii"><meta name=Generator content="Microsoft Word 15 (filtered medium)"><style><!--
/* Font Definitions */
@font-face
{font-family:"Cambria Math";
panose-1:2 4 5 3 5 4 6 3 2 4;}
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:MotterTektura;
panose-1:2 0 5 7 5 0 0 2 0 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri",sans-serif;}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:#0563C1;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:#954F72;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri",sans-serif;}
.MsoChpDefault
{mso-style-type:export-only;
font-family:"Calibri",sans-serif;}
@page WordSection1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.WordSection1
{page:WordSection1;}
--></style><!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]--></head><body lang=EN-US link="#0563C1" vlink="#954F72"><div class=WordSection1><p class=MsoNormal><span lang=EN-CA>Hi,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>I'm having a problem with the performance of a query and I'm hoping someone can help.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>The main problem is that this query takes literally minutes to run. Looking at the QEP, things seem to go off the rails in the lower right-half of the QEP. There is a huge chunk of CPU time on the right-join to the proj-rest node that got data from secondary index alt2_d4. There is also then a huge burst of disk time when it appears Ingres is pulling more data from base table d4.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>The trouble is I don't know what I can do to minimize these issues. I tried rewriting the query to bypass the view completely, and that had a minimal effect, but I feel like there is something fundamentally wrong with out approach to indexes or how the query is written.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>In any case, I thought I would appeal to smarter people than me. :)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>I've tried to provide as much information as possible below (or at least what I think is useful) but if I've missed something please let me know and I'll supply it.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>This particular set of information is from Ingres 10.x 64-bit (the free one) but we've also tried it on an Ingres 9.x system (also free, also 64-bit). All caches are enabled in cbf, and the guidelines are set to HUGE. All remaining memory is file-system level caching. The server is a RHEL 7.3 64-Bit linux system. The table in question has ~2.5 million rows, which is a ton.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>1) The base table for the query is described below:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>help table d4;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Executing . . .<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>Name: d4<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Version: II10.0<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Page size: 32768<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Cache priority: 0<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Alter table version: 0<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Alter table totwidth: 4684<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Row width: 4684<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Number of rows: 2464056<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Storage structure: isam with unique keys<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Compression: none<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Duplicate Rows: not allowed<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Number of pages: 410781<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Overflow data pages: 600<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Optimizer statistics: yes; see avg count below, more info in the iistats catalog<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>Column Information:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Key Avg Count<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Column Name Type Length Nulls Defaults Seq Per Value<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xorder_no integer 4 no no 1 unique<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>vrorderid varchar 15 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xpid integer 4 no no 1047.9<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xcauthor integer 4 no no<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xcsource integer 4 yes null 11352.6<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xparent integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xlastcompletion integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xplan_id integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xfamily integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>drecord ingresdate no no<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>berror integer 1 no no<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>status integer 1 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>istate integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>strt_date ingresdate yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>end_date ingresdate yes null 32.4<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>dend_save ingresdate yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>norder_type integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>type integer 1 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xstand integer 4 yes null 22.5<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>dbilling ingresdate yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>ddeactivate ingresdate yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xcphysician_deactivate integer 4 yes null 16423.5<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xcpersonnel_deactivate integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xcprocedure integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>topic varchar 40 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>cmmcn char 1 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>prcs char 1 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>prcs_prsnl integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>prcs_time ingresdate yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>nordr_crrd integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>crrd char 1 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>crrd_prsnl1 integer 4 yes null 8925.8<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>crrd_prsnl2 integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>crrd_time ingresdate yes null 2.8<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>verif char 1 yes null 410586.8<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>verif_prsnl integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>verif_time ingresdate yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>cdeact_verif char 1 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>dverif_deact ingresdate yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>cdeactack char 1 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xdeactack integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>ddeactack ingresdate yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xcondition integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xcondition2 integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>descr varchar 3000 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>nwmdose integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>nacdose integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>npcdose integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>nhsdose integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>namdose integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>npmdose integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>nprndose integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>npre_dialysis integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>nintra_dialysis integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>npost_dialysis integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>nperiod integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>nprd_typ integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>bwasted integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>bprinted integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>bsig integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>nappt integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>dhold ingresdate yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>ccomments varchar 700 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xdiscipline integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>dnext_date ingresdate yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>btiming_pointer integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>btiming_units integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>burgency integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>bnonesrd integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xsession integer 4 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>vnecomments varchar 500 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>vsigencrypt varchar 20 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>vsigencrypt2 varchar 20 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>bexportcreate integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>bexportdeact integer 2 yes null<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>Secondary indexes:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Index Name Structure Keyed On<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>alt10_d4 isam xcphysician_deactivate<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>alt11_d4 isam crrd_time<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>alt15_d4 btree xorder_no<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>alt1_d4 isam xpid<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>alt2_d4 btree verif<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>alt4_d4 isam xcsource<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>alt50_d4 btree xorder_no<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>alt5_d4 isam xstand<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>alt6_d4 isam crrd_prsnl1, crrd_time<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>alt7_d4 isam xstand<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>alt8_d4 isam end_date<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>2) The indexes for the table are described below:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>create index alt10_d4 on d4 (xcphysician_deactivate,cdeact_verif,status,xFamily,bError)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>with key=(xcphysician_deactivate), structure=isam;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>create index alt11_d4 on d4 (crrd_time) with key = (crrd_time), structure = isam;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>create index alt15_d4 on d4 (xOrder_No, bUrgency, Crrd, dNext_Date) with key=(xOrder_No), structure=btree;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>create index alt1_d4 on d4 (xpid,drecord,topic,type,xorder_no,crrd,prcs,xcsource, berror,verif, cdeact_verif, status, crrd_time, norder_type, xstand, strt_date,dnext_date, dDeactivate) with key = (xpid), structure = isam;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>create index alt2_d4 on d4 (verif, cdeact_verif, istate, cmmcn, xcsource,xcPhysician_deactivate,xOrder_no) with key = (verif), structure = btree;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>create index alt4_d4 on d4 (xcsource,drecord,topic,type,xorder_no,crrd,prcs,xpid, berror,verif,status) with key = (xcsource), structure = isam;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>create index alt50_d4 on d4 (xOrder_No, bUrgency, Crrd, dNext_Date) with key=(xOrder_No), structure=btree; <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>create index alt5_d4 on d4 (xstand,verif_prsnl,berror,crrd_time) with key=(xstand), structure=isam;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>create index alt6_d4 on d4 (crrd_prsnl1, crrd_time) with key=(crrd_prsnl1, crrd_time), structure=isam; <o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>create index alt7_d4 on d4 (xstand, type, crrd) with key = (xstand), structure = isam;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>create index alt8_d4 on d4 (end_date, xorder_no, berror, status) with key = (end_date), structure = isam;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>2) The query itself uses a view instead of a direct query agaisnt the table. The view is described below:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>create view d4_v99<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>(<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>xorder_no, bType, norder_type, dRecord, dStart, dEnd,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>iStatus, iState, vTopic, vDescr, vVerif, Vcrrd,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>vPrcs, xcsource, vAuthName, vCmmcn, xPlan, xcdeact,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>vDeactName, xStand, xFamily, cdeact_verif, dDeactivate,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>hsp_pid, xpid, vname_given1, vname_given2, vname_family, berror<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>as select<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.xorder_no, /* 1 unique order number */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.type, /* 2 standing/specific */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.norder_type, /* 3 order type */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.drecord, /* 4 record date */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.strt_date, /* 5 order start date */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.end_date, /* 6 order and date */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.status, /* 7 order status */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>ifnull(d4.iState, 99), /* 8 order state */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.topic, /* 9 order topic */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.Descr, /* 10 Order description */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>ifnull(d4.verif,'N'), /* 11 verification status */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>ifnull(d4.crrd, 'N'), /* 12 carried status */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>ifnull(d4.prcs, 'N'), /* 13 acknowledged status */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.xcsource, /* 14 author */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>c35_act.Full_Name, /* 15 acknowledging personal */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.cmmcn, /* 15b communication */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.xPlan_ID, /* 15c reference to Care Plan */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.xcphysician_deactivate, /* 16 physician deactivating */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>c35_deact.full_name, /* 17 physician deactivating */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.xStand, /* 18 standing / specific */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.xFamily, /* 19 reference to the parent order */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>ifnull(d4.cdeact_verif, 'N'), /* 20 deactivation verified status */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d4.dDeactivate, /* 21 date deactivated */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d1.hsp_pid, /* 22 patient identifier */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d0.xpid, /* 23 internal patient index */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d0.name_given1, /* 24 first name */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d0.name_given2, /* 25 second name */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d0.name_family, /* 26 family name */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>ifnull(d4.berror, 0) /* 27 discard status */<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>from d4<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>inner join d0 on d0.xpid = d4.xpid<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>inner join d1 on d1.xpid = d4.xpid<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>left join c35 c35_act on d4.xcsource = c35_act.xcsource<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>left join c35 c35_deact on d4.xcphysician_deactivate = c35_deact.xcsource;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>4) The query, along with its QEP, is described below:<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>set qep;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>set optimizeonly;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> SELECT top 1000<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.dRecord,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.dStart,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.dEnd,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.vTopic,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.vVerif,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.xOrder_no,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.vCrrd,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.vPrcs,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.xcSource,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.vAuthName,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.xcDeact,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.vDeactName,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.bError,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.bType,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.iStatus,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.cdeact_verif,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.dDeactivate,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.iState,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.hsp_pid,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.xPID,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.vname_given1,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.vname_given2,<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> d4_v99.vname_family<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> from d4_v99<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> WHERE (<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> (xcSource = 24093 AND vVerif != 'Y' )<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> or (xcDeact = 24093<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> AND cdeact_verif != 'Y'<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> and iStatus = 0) )<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> AND vCmmcn != 'A'<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> AND xPlan IS NULL<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> AND berror != 1<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> AND istate != 4<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> AND xOrder_No NOT IN<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> (SELECT xOrder from r59<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> where xSource = 24093) ;<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Executing . . .<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>********************************************************************<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>QUERY PLAN 34,1, no timeout, of main query<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA> Hash Join(xpid)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Heap<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Pages 759 Tups 4549<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> D48778 C26835<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> / \<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Hash Join(xpid) right join<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Heap Hash Join(xcphysician_deactivate)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Pages 12 Tups 2611 Heap<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> D29 C105 Pages 35 Tups 4549<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> D48749 C26613<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> / \ / \<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Proj-rest Proj-rest Proj-rest right join<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Heap Sorted(NU) Heap Hash Join(xcsource)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Pages 6 Tups 2615 Pages 35 Tups 2614 Pages 1 Tups 627 Heap<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> D16 C26 D13 C26 D2 C6 Pages 31 Tups 4549<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> D48747 C26549<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> / / / / \<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>d1 alt1_d0 c35 Proj-rest T Join(tidp)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Hashed(NU) I(d0) (c35_deact) Heap Heap<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>Pages 130 Tups 2615 B-Tree(NU) Hashed(NU) Pages 1 Tups 627 Pages 26 Tups 4549<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Pages 51 Tups 2614 Pages 18 Tups 627 D2 C6 D48745 C26484<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> / / \<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> c35 right join d4<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> (c35_act) Hash Join(xorder) Isam(NU)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Hashed(NU) Partial(tidp) Pages 410751 Tups 2463873<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Pages 18 Tups 627 Pages 396 Tups 45492<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> D5682 C25574<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> / \<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Proj-rest Proj-rest<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Sorted(NU) Sorted(NU)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Pages 1 Tups 1 Pages 641 Tups 90983<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> D2 C0 D5513 C24640<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> / /<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> alt1_r59 alt2_d4<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> I(r59) I(d4)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> B-Tree(NU) B-Tree(NU)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> Pages 9 Tups 1 Pages 22052 Tups 2464046<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>********************************************************************<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA>E_OP0008 query optimized<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> - trace flag indicates no execution should take place<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA> (Thu Oct 18 13:10:49 2018)<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>mvf<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p><p class=MsoNormal><span lang=EN-CA>--<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA style='font-size:9.5pt;font-family:"Arial",sans-serif;color:#222222;background:white'>Michel Forget (</span><span lang=EN-CA style='font-size:9.5pt;font-family:"Arial",sans-serif'><a href="mailto:mvf@cyberren.com"><span style='color:blue;background:white'>mvf@cyberren.com</span></a><span style='color:#222222;background:white'> / Direct Line: 780-458-4989 x102)</span><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA style='font-size:9.5pt;font-family:"Arial",sans-serif;color:#222222;background:white'>Special Projects Analyst</span><span lang=EN-CA style='font-size:9.5pt;font-family:"Arial",sans-serif;color:#222222'><o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA style='font-size:14.0pt;font-family:MotterTektura;color:#548DD4;background:white'>Cybernius </span><span lang=EN-CA style='font-size:14.0pt;font-family:MotterTektura;color:#943634;background:white'>Medical</span><span lang=EN-CA style='font-size:14.0pt;font-family:MotterTektura;color:#548DD4;background:white'>, </span><span lang=EN-CA>Ltd.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA style='font-size:6.0pt'>This e-mail may contain confidential and/or privileged information. This information is intended only for the use of the individual(s) and entity(ies) to whom it is addressed. If you are the intended recipient, further disclosures are prohibited without proper authorization. If you are not the intended recipient (or have received this e-mail in error) please notify the sender immediately and destroy this e-mail. Any unauthorized copying, disclosure or distribution of the material in this e-mail is strictly forbidden and possibly a violation of federal or state law and regulations. Cybernius Medical, its subsidiaries, and affiliates hereby claim all applicable privileges related to this information.<o:p></o:p></span></p><p class=MsoNormal><span lang=EN-CA><o:p> </o:p></span></p></div></body></html>