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 (office) 402-739-3496 (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. ___ Gpc-dev mailing list Gpc-dev@listserv.kumc.edu http://listserv.kumc.edu/mailman/listinfo/gpc-dev
Canceled: GPC Dev Weekly Teleconference
BEGIN:VCALENDAR METHOD:CANCEL PRODID:Microsoft Exchange Server 2010 VERSION:2.0 BEGIN:VTIMEZONE TZID:Central Standard Time BEGIN:STANDARD DTSTART:16010101T02 TZOFFSETFROM:-0500 TZOFFSETTO:-0600 RRULE:FREQ=YEARLY;INTERVAL=1;BYDAY=1SU;BYMONTH=11 END:STANDARD BEGIN:DAYLIGHT DTSTART:16010101T02 TZOFFSETFROM:-0600 TZOFFSETTO:-0500 RRULE:FREQ=YEARLY;INTERVAL=1;BYDAY=2SU;BYMONTH=3 END:DAYLIGHT END:VTIMEZONE BEGIN:VEVENT ORGANIZER;CN="Verhagen, Laurel A":MAILTO:VERHAGEN.LAUREL@marshfieldresearch.o rg ATTENDEE;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;RSVP=FALSE;CN=gpc-dev@l istserv.kumc.edu:MAILTO:gpc-dev@listserv.kumc.edu ATTENDEE;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;RSVP=FALSE;CN="Roush, St effani":MAILTO:roush.steff...@marshfieldresearch.org ATTENDEE;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;RSVP=FALSE;CN=Reid.Holb r...@hsc.utah.edu:MAILTO:reid.holbr...@hsc.utah.edu ATTENDEE;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;RSVP=FALSE;CN="Osinski, Kristen (kosin...@mcw.edu)":MAILTO:kosin...@mcw.edu ATTENDEE;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;RSVP=FALSE;CN=Susan.Rea @imail.org:MAILTO:susan@imail.org ATTENDEE;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;RSVP=FALSE;CN=Zahra.Hos sein...@northwestern.edu:MAILTO:zahra.hossein...@northwestern.edu ATTENDEE;ROLE=REQ-PARTICIPANT;PARTSTAT=NEEDS-ACTION;RSVP=FALSE;CN="Gorski,Ma rshall (marshall.gor...@cerner.com)":MAILTO:marshall.gor...@cerner.com DESCRIPTION;LANGUAGE=en-US:**Due to lack of agenda items\, the conference c all today is canceled.\n\n--\nWhen: Occurs every Tuesday from 11:00 AM to 12:00 PM\, effective 9/25/18-12/31/19. (UTC-06:00) Central Time (US & Cana da)\n\n1. Please join my meeting: https://global.gotomeeting.com/meet ing/join/817393381\n2. Use your microphone and speakers (VoIP) - a he adset is recommended. Or\, call in using your telephone.\nUnited States (T oll-free): +1 (571) 317-3131\n\n Access Code: 817-393-381\n Audi o PIN: Shown after joining the meeting\n\n Meeting ID:817-393-381\n\n An agenda is due to the gpc-dev mailing list a day in advance:\nhttp://lis tserv.kumc.edu/pipermail/gpc-dev/\n\nFor more information see:\nhttps://in formatics.gpcnetwork.org/trac/Project/wiki/SoftwareDev#next-agenda\n\n\n\n UID:04008200E00074C5B7101A82E008702697DE8D4ED401000 01000C3448E6A9D2EC145B538E24776B0FC8A RECURRENCE-ID;TZID=Central Standard Time:20190402T11 SUMMARY;LANGUAGE=en-US:Canceled: GPC Dev Weekly Teleconference DTSTART;TZID=Central Standard Time:20190402T11 DTEND;TZID=Central Standard Time:20190402T12 CLASS:PUBLIC PRIORITY:1 DTSTAMP:20190402T121238Z TRANSP:TRANSPARENT STATUS:CANCELLED SEQUENCE:9 LOCATION;LANGUAGE=en-US:https://global.gotomeeting.com/meeting/join/8173933 81 X-MICROSOFT-CDO-APPT-SEQUENCE:9 X-MICROSOFT-CDO-OWNERAPPTID:-1374578718 X-MICROSOFT-CDO-BUSYSTATUS:FREE X-MICROSOFT-CDO-INTENDEDSTATUS:FREE X-MICROSOFT-CDO-ALLDAYEVENT:FALSE X-MICROSOFT-CDO-IMPORTANCE:2 X-MICROSOFT-CDO-INSTTYPE:3 X-MICROSOFT-DONOTFORWARDMEETING:FALSE X-MICROSOFT-DISALLOW-COUNTER:TRUE END:VEVENT END:VCALENDAR ___ Gpc-dev mailing list Gpc-dev@listserv.kumc.edu http://listserv.kumc.edu/mailman/listinfo/gpc-dev
