These are in oracle, but here are some of our indexes:
In our live I2b2 we add this extra one that is recommended by I2b2 along with
the standard I2b2 queries.
CREATE INDEX
FACT_CNPT_PAT_ENCT_IDX
ON
OBSERVATION_FACT
(
CONCEPT_CD,
INSTANCE_NUM,
PATIENT_NUM,
ENCOUNTER_NUM
);
That didn't work for ACT which requires faster turnaround times. I basically
analyzed the queries that shrine spits out and wrote some custom indexes and
used the explain plans to ensure there would likely be some utilization, so on
that observation_fact we also have:
CREATE INDEX
FACT_NOLOB
ON
OBSERVATION_FACT
(
PATIENT_NUM,
START_DATE,
CONCEPT_CD,
ENCOUNTER_NUM
);
CREATE INDEX
FACT_PATCON_DATE_PRVD_IDX
ON
OBSERVATION_FACT
(
PATIENT_NUM,
CONCEPT_CD,
START_DATE,
END_DATE
);
--This one was huge for the temporal queries.
CREATE INDEX
TEMPORALOBS
ON
OBSERVATION_FACT
(
CONCEPT_CD,
START_DATE,
PATIENT_NUM
);
CREATE INDEX
OBS_FACT_ENC_NUM_BI
ON
OBSERVATION_FACT
(
ENCOUNTER_NUM
);
CREATE INDEX
OBS_FACT_PAT_NUM_BI
ON
OBSERVATION_FACT
(
PATIENT_NUM
);
This one I added to concept_dimension to facilitate the joins as it was getting
bigger, but I can't say if it's actually being used.
CREATE INDEX
ACT_CONCEPT_DIM_PATH
ON
CONCEPT_DIMENSION
(
CONCEPT_PATH,
CONCEPT_CD
);
-----Original Message-----
From: Gpc-dev <gpc-dev-boun...@listserv.kumc.edu> On Behalf Of
gpc-dev-requ...@listserv.kumc.edu
Sent: Wednesday, April 03, 2019 12:00 PM
To: gpc-dev@listserv.kumc.edu
Subject: Gpc-dev Digest, Vol 62, Issue 3
Send Gpc-dev mailing list submissions to
gpc-dev@listserv.kumc.edu
To subscribe or unsubscribe via the World Wide Web, visit
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
or, via email, send a message with subject or body 'help' to
gpc-dev-requ...@listserv.kumc.edu
You can reach the person managing the list at
gpc-dev-ow...@listserv.kumc.edu
When replying, please edit your Subject line so it is more specific than "Re:
Contents of Gpc-dev digest..."
Today's Topics:
1. MS-SQL sites -- CREATE INDEX commands for OBSERVATION_FACT?
(Pedersen, Jay G)
----------------------------------------------------------------------
Message: 1
Date: Tue, 2 Apr 2019 21:37:45 +0000
From: "Pedersen, Jay G" <jay.peder...@unmc.edu>
To: "gpc-dev@listserv.kumc.edu" <gpc-dev@listserv.kumc.edu>
Subject: MS-SQL sites -- CREATE INDEX commands for OBSERVATION_FACT?
Message-ID:
<dm5pr0501mb370265091776e57ce0224c03f7...@dm5pr0501mb3702.namprd05.prod.outlook.com>
Content-Type: text/plain; charset="iso-8859-1"
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.
-------------- next part --------------
An HTML attachment was scrubbed...
URL:
<http://listserv.kumc.edu/pipermail/gpc-dev/attachments/20190402/6f230bf5/attachment-0001.html>
------------------------------
Subject: Digest Footer
_______________________________________________
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
------------------------------
End of Gpc-dev Digest, Vol 62, Issue 3
**************************************
_______________________________________________
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
