We only use a subset of the indexes you listed below. We have: OF_IDX_Modifier, OF_IDX_ClusteredConcept, OF_IDX_UPLOADID, OF_IDX_Encounter_Patient, OF_IDX_Start_Date, OF_IDX_ALLObservation_Fact.
We don't have: OBSERVATION_FACT_PK, OF_IDX_SOURCESYSTEM_CD, OF_TEXT_SEARCH_UNIQUE. I'm guessing some of the indexes we likely missed during upgrades. This might be something we need to look into as well. Thanks, [image006]Eric LaRose Programmer/Analyst Senior | Office of Research Computing & Analytics Marshfield Clinic Health System Office: 715-389-3591 | Ext: 9-3591 From: Gpc-dev [mailto:gpc-dev-boun...@listserv.kumc.edu] On Behalf Of Pedersen, Jay G Sent: Tuesday, April 02, 2019 4:38 PM To: gpc-dev@listserv.kumc.edu Subject: MS-SQL sites -- CREATE INDEX commands for OBSERVATION_FACT? Hi, At UNMC, we are deep into the porting from Oracle to MS-SQL for our i2b2 instance. We are using MS-SQL 2014. We are interested in knowing how other sites are indexing their fact table. The indexing 'by default' for the fact table when installing i2b2 appears to be: I am curious if that is what sites are actually using, and whether they are using COLUMNSTORE indexes or CLUSTERED/NONCLUSTERED indexes. I am guessing that there has been some tweaking to try to optimize performance. Could you send your CREATE INDEX commands if they differ from the following: #1. Primary key CONSTRAINT OBSERVATION_FACT_PK PRIMARY KEY NONCLUSTERED (PATIENT_NUM, CONCEPT_CD, MODIFIER_CD, START_DATE, ENCOUNTER_NUM, INSTANCE_NUM, PROVIDER_ID) #2. Other indexes CREATE NONCLUSTERED INDEX OF_IDX_Modifier ON dbo.OBSERVATION_FACT(MODIFIER_CD) CREATE CLUSTERED INDEX OF_IDX_ClusteredConcept ON dbo.OBSERVATION_FACT(CONCEPT_CD) CREATE NONCLUSTERED INDEX OF_IDX_UPLOADID ON dbo.OBSERVATION_FACT(UPLOAD_ID) CREATE NONCLUSTERED INDEX OF_IDX_Encounter_Patient ON dbo.OBSERVATION_FACT(ENCOUNTER_NUM, PATIENT_NUM, INSTANCE_NUM) CREATE NONCLUSTERED INDEX OF_IDX_SOURCESYSTEM_CD ON dbo.OBSERVATION_FACT(SOURCESYSTEM_CD) CREATE NONCLUSTERED INDEX OF_IDX_Start_Date ON dbo.OBSERVATION_FACT(START_DATE, PATIENT_NUM) CREATE UNIQUE NONCLUSTERED INDEX OF_TEXT_SEARCH_UNIQUE] ON dbo.OBSERVATION_FACT(TEXT_SEARCH_INDEX) CREATE NONCLUSTERED INDEX OF_IDX_ALLObservation_Fact ON dbo.OBSERVATION_FACT(PATIENT_NUM, ENCOUNTER_NUM, CONCEPT_CD, START_DATE, PROVIDER_ID, MODIFIER_CD, INSTANCE_NUM, VALTYPE_CD, TVAL_CHAR, NVAL_NUM, VALUEFLAG_CD, QUANTITY_NUM, UNITS_CD, END_DATE, LOCATION_CD, CONFIDENCE_NUM) Jay Pedersen, M.A. Department of Pathology/Microbiology University of Nebraska Medical Center 985900 Nebraska Medical Center Omaha NE 68198-5900 402-559-9487<tel:402-559-9593> (office) 402-739-3496<tel:402-350-7851> (mobile) The information in this e-mail may be privileged and confidential, intended only for the use of the addressee(s) above. Any unauthorized use or disclosure of this information is prohibited. If you have received this e-mail by mistake, please delete it and immediately contact the sender. ______________________________________________________________________ The contents of this message may contain private, protected and/or privileged information. If you received this message in error, you should destroy the e-mail message and any attachments or copies, and you are prohibited from retaining, distributing, disclosing or using any information contained within. Please contact the sender and advise of the erroneous delivery by return e-mail or telephone. Thank you for your cooperation.
_______________________________________________ Gpc-dev mailing list Gpc-dev@listserv.kumc.edu http://listserv.kumc.edu/mailman/listinfo/gpc-dev
