I think we just use the defaults that come with i2b2 initially. I have attached
a file of all of ours.
Todd McNeeley
Senior Software Engineer, Information Management Team Development
mcneele...@health.missouri.edu<mailto:mcneele...@health.missouri.edu> |
573-884-3297 direct | 573-219-8341 mobile
Tiger Institute for Health Innovation |
www.tiger-institute.org<http://www.tiger-institute.org/>
P PLEASE CONSIDER THE ENVIRONMENT BEFORE PRINTING THIS EMAIL
From: Gpc-dev <gpc-dev-boun...@listserv.kumc.edu> On Behalf Of Larose, Eric R
Sent: Thursday, April 4, 2019 7:47 AM
To: Pedersen, Jay G <jay.peder...@unmc.edu>
Cc: gpc-dev@listserv.kumc.edu
Subject: RE: MS-SQL sites -- CREATE INDEX commands for OBSERVATION_FACT?
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<mailto: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.
--observation facts indexes
ALTER TABLE [i2b2data].[OBSERVATION_FACT] ADD CONSTRAINT [Observation_Fact_PK]
PRIMARY KEY NONCLUSTERED
(
[PATIENT_NUM] ASC,
[CONCEPT_CD] ASC,
[MODIFIER_CD] ASC,
[START_DATE] ASC,
[ENCOUNTER_NUM] ASC,
[INSTANCE_NUM] ASC,
[PROVIDER_ID] ASC,
[TEXT_SEARCH_INDEX] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS =
ON, FILLFACTOR = 95)
GO
CREATE NONCLUSTERED INDEX [Of_IDx_Allobservation_Fact] ON
[i2b2data].[OBSERVATION_FACT]
(
[PATIENT_NUM] ASC,
[ENCOUNTER_NUM] ASC,
[CONCEPT_CD] ASC,
[START_DATE] ASC,
[PROVIDER_ID] ASC,
[MODIFIER_CD] ASC,
[INSTANCE_NUM] ASC,
[VALTYPE_CD] ASC,
[TVAL_CHAR] ASC,
[NVAL_NUM] ASC,
[VALUEFLAG_CD] ASC,
[QUANTITY_NUM] ASC,
[UNITS_CD] ASC,
[END_DATE] ASC,
[LOCATION_CD] ASC,
[CONFIDENCE_NUM] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = OFF, ALLOW_PAGE_LOCKS =
OFF, FILLFACTOR = 95)
GO
/****** Object: Index [Of_IDx_Concept_CD] Script Date: 4/9/2019 11:33:59 AM
******/
CREATE NONCLUSTERED INDEX [Of_IDx_Concept_CD] ON [i2b2data].[OBSERVATION_FACT]
(
[CONCEPT_CD] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 95)
GO
/****** Object: Index [Of_IDx_Encounter_Patient] Script Date: 4/9/2019
11:34:24 AM ******/
CREATE NONCLUSTERED INDEX [Of_IDx_Encounter_Patient] ON
[i2b2data].[OBSERVATION_FACT]
(
[ENCOUNTER_NUM] ASC,
[PATIENT_NUM] ASC,
[INSTANCE_NUM] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 95)
GO
/****** Object: Index [Of_IDx_Modifier] Script Date: 4/9/2019 11:34:46 AM
******/
CREATE NONCLUSTERED INDEX [Of_IDx_Modifier] ON [i2b2data].[OBSERVATION_FACT]
(
[MODIFIER_CD] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 95)
GO
CREATE NONCLUSTERED INDEX [Of_IDx_Sourcesystem_CD] ON
[i2b2data].[OBSERVATION_FACT]
(
[SOURCESYSTEM_CD] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 95)
GO
CREATE NONCLUSTERED INDEX [Of_IDx_Start_Date] ON [i2b2data].[OBSERVATION_FACT]
(
[START_DATE] ASC,
[PATIENT_NUM] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 95)
GO
CREATE NONCLUSTERED INDEX [Of_IDx_Uploadid] ON [i2b2data].[OBSERVATION_FACT]
(
[UPLOAD_ID] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON,
FILLFACTOR = 95)
GO
CREATE UNIQUE NONCLUSTERED INDEX [Of_Text_Search_Unique] ON
[i2b2data].[OBSERVATION_FACT]
(
[TEXT_SEARCH_INDEX] ASC
)WITH (PAD_INDEX = ON, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF,
IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 95)
GO_______________________________________________
Gpc-dev mailing list
Gpc-dev@listserv.kumc.edu
http://listserv.kumc.edu/mailman/listinfo/gpc-dev
