Platform 1.11.5 or above in implementation

This is very useful feedback @willa

What is the value of this global property? search.caseSensitiveDatabaseStringComparison What is the value of this global property? person.attributeSearchMatchMode

This is in relation to TRUNK-4563 and TRUNK-2472

1 Like

@dkayiwa Thanks for getting back and sorry for a late reply. Apparently the issues you have mentioned is exactly what we were facing. However there is another observation for us. I ran the bare queries on MySQL with and without lower() and below are the results.

The query generated by Platform 1.11.5 without any modification took 3.17 minutes:

select 
	this_.patient_id as person1_216_4_, 
	this_1_.uuid as uuid216_4_, 
	this_1_.gender as gender216_4_, 
	this_1_.birthdate as birthdate216_4_, 
	this_1_.birthdate_estimated as birthdate5_216_4_, 
	this_1_.birthtime as birthtime216_4_, 
	this_1_.dead as dead216_4_, 
	this_1_.death_date as death8_216_4_, 
	this_1_.deathdate_estimated as deathdate9_216_4_, 
	this_1_.cause_of_death as cause10_216_4_, 
	this_1_.creator as creator216_4_, 
	this_1_.date_created as date12_216_4_, 
	this_1_.changed_by as changed13_216_4_, 
	this_1_.date_changed as date14_216_4_, 
	this_1_.voided as voided216_4_, 
	this_1_.voided_by as voided16_216_4_, 
	this_1_.date_voided as date17_216_4_, 
	this_1_.void_reason as void18_216_4_, 
	this_.patient_id as patient1_229_4_, 
	this_.creator as creator229_4_, 
	this_.date_created as date3_229_4_, 
	this_.changed_by as changed4_229_4_, 
	this_.date_changed as date5_229_4_, 
	this_.voided as voided229_4_, 
	this_.voided_by as voided7_229_4_, 
	this_.date_voided as date8_229_4_, 
	this_.void_reason as void9_229_4_, 
	case when exists (select * from patient p where p.patient_id = this_1_.person_id) then 1 
		else 0 
		end as formula1_4_, 
	name1_.person_name_id as person1_221_0_, 
	name1_.uuid as uuid221_0_, 
	name1_.preferred as preferred221_0_, 
	name1_.person_id as person4_221_0_, 
	name1_.prefix as prefix221_0_, 
	name1_.given_name as given6_221_0_, 
	name1_.middle_name as middle7_221_0_, 
	name1_.family_name_prefix as family8_221_0_, 
	name1_.family_name as family9_221_0_, 
	name1_.family_name2 as family10_221_0_, 
	name1_.family_name_suffix as family11_221_0_, 
	name1_.degree as degree221_0_, name1_.creator as creator221_0_, 
	name1_.date_created as date14_221_0_, 
	name1_.changed_by as changed15_221_0_, 
	name1_.date_changed as date16_221_0_, 
	name1_.voided_by as voided17_221_0_, 
	name1_.date_voided as date18_221_0_, 
	name1_.void_reason as void19_221_0_, 
	name1_.voided as voided221_0_, 
	attribute2_.person_id as person3_216_6_, 
	attribute2_.person_attribute_id as person1_6_, 
	attribute2_.person_attribute_id as person1_217_1_, 
	attribute2_.uuid as uuid217_1_, 
	attribute2_.person_id as person3_217_1_, 
	attribute2_.person_attribute_type_id as person4_217_1_, 
	attribute2_.value as value217_1_, 
	attribute2_.creator as creator217_1_, 
	attribute2_.date_created as date7_217_1_, 
	attribute2_.changed_by as changed8_217_1_, 
	attribute2_.date_changed as date9_217_1_, 
	attribute2_.voided_by as voided10_217_1_, 
	attribute2_.date_voided as date11_217_1_, 
	attribute2_.voided as voided217_1_, 
	attribute2_.void_reason as void13_217_1_, 
	attributet3_.person_attribute_type_id as person1_218_2_, 
	attributet3_.uuid as uuid218_2_, 
	attributet3_.name as name218_2_, 
	attributet3_.description as descript4_218_2_, 
	attributet3_.format as format218_2_, 
	attributet3_.foreign_key as foreign6_218_2_, 
	attributet3_.sort_weight as sort7_218_2_, 
	attributet3_.searchable as searchable218_2_, 
	attributet3_.creator as creator218_2_, 
	attributet3_.date_created as date10_218_2_, 
	attributet3_.changed_by as changed11_218_2_, 
	attributet3_.date_changed as date12_218_2_, 
	attributet3_.retired_by as retired13_218_2_, 
	attributet3_.date_retired as date14_218_2_, 
	attributet3_.retire_reason as retire15_218_2_, 
	attributet3_.retired as retired218_2_, 
	attributet3_.edit_privilege as edit17_218_2_, 
	ids4_.patient_id as patient3_216_7_, 
	ids4_.patient_identifier_id as patient1_7_, 
	ids4_.patient_identifier_id as patient1_230_3_, 
	ids4_.uuid as uuid230_3_, ids4_.patient_id as patient3_230_3_, 
	ids4_.identifier as identifier230_3_, 
	ids4_.identifier_type as identifier5_230_3_, 
	ids4_.location_id as location6_230_3_, 
	ids4_.date_created as date7_230_3_, 
	ids4_.date_voided as date8_230_3_, 
	ids4_.preferred as preferred230_3_, 
	ids4_.voided as voided230_3_, 
	ids4_.void_reason as void11_230_3_, 
	ids4_.creator as creator230_3_, 
	ids4_.voided_by as voided13_230_3_, 
	ids4_.changed_by as changed14_230_3_, 
	ids4_.date_changed as date15_230_3_ 
from 
	patient this_ inner join person this_1_ on this_.patient_id=this_1_.person_id 
	inner join person_name name1_ on this_.patient_id=name1_.person_id 
	left outer join person_attribute attribute2_ on this_.patient_id=attribute2_.person_id 
	left outer join person_attribute_type attributet3_ on attribute2_.person_attribute_type_id=attributet3_.person_attribute_type_id 
	left outer join patient_identifier ids4_ on this_.patient_id=ids4_.patient_id 
where 
(((name1_.voided=0 and (name1_.given_name like 'wafula' or name1_.middle_name like 'wafula' or name1_.family_name like 'wafula' or name1_.family_name2 like 'wafula')))
	or ((attributet3_.searchable=1 and attribute2_.voided=0 and lower(attribute2_.value) like 'wafula'))
	or (ids4_.voided=0 and lower(ids4_.identifier) like 'wafula'))
and this_.voided=0 
order by attribute2_.voided asc, attribute2_.date_created desc, ids4_.voided asc, ids4_.preferred desc, ids4_.identifier_type desc,
ids4_.date_created desc, name1_.given_name asc, name1_.middle_name asc, name1_.family_name asc;

Modifying the above query removing the lower() mysql function made the query to take about 46 seconds which is a massive improvement. However this is too long for practical purposes so I decided to rid attribute searches altogether because we don’t actually search for attributes at AMPATH (or so it seems). The performance improved further down to about 10 seconds. But then again this is still not enough so I decided to separate the name searches from identifier searches, finally this gave results near instantaneously:

https://github.com/mhawila/openmrs-core/commit/47f108046cde27b98d3055edaaac9ea9f7ca8d6b

Remarks & Afterthoughts

When going through the HibernatePatientDAO class I realize there is a lot of flexibilities provided by the API which is not accessible via UI. I was thinking it could be better to expose these functionalities to provide the users with enough options to tweak the patient search to suit their purposes. I had to manually do the quick fix because the default implementation assumes several things which may be okay for majority of purposes but not exactly suitable for some few implementation like AMPATH.

Any thoughts on this would be nice.

Thanks.

2 Likes

@willa excellent work! Do you think you can create a ticket and pull request?

1 Like

@dkayiwa I don’t think the changes I have made can be applied to master because I have made an assumption that any string with a number is an identifier. Do you really think my assumptions can be universal?

No they are not. I just meant a pull request with the real universal thing. :smile:

Just want to give an update to this post that AMPATH has successfully upgraded from 1.9.7 to 1.11.5 a couple weeks ago. One minor issue was identified on Java version compatibility - see Java creating new threads after upgrade to Platform 1.11.5. Except that, the upgrade progress went nice and smooth within a weekend. Big thanks to everyone who has helped during our testing process as well as the AMPATH Team (@willa, @amiencha, @jdick, and more).

2 Likes

Great to hear that the upgrade was successful!

And thanks much for coming back to update us here!

Can someone from your team comment on how you ended up resolving the patient search slowness issue?

1 Like

The answer is in post #9 above.

I see, so ultimately you are forced to run on a (slightly) forked version of openmrs-core. It’s unfortunate that you have to do that.

I guess the slowdown must be due to the fact that in Platform 1.11 we started searching on person attributes:

While that’s a nice feature, if it causes horrible slowdown for large databases, it’s not worth it.

Some possibile approaches:

  1. Quick fix (might work): if no attributes are marked as “searchable” then don’t search for attributes (don’t join against more tables, etc)
  2. Would definitely work: add a global property to enable/disable searching on attributes (and if it’s turned off, then don’t show the “searchable” checkbox on managing attributes)
  3. Right thing to do: rewrite patient searching from scratch, to use lucene/hibernate-search, since this was added in Platform 1.11.

Anyone interested in looking into this?

I am very interested, unfortunately we have things that are coming up in our pipeline that I doubt I will have enough time to look at this. In any case I will do my best to find some time for this, most likely after one month from today.

2 Likes

Hi,

During the upgrade did you get any error? like Error executing SQL alter table reporting_report_design modify report_definition_uuid char(38) not null: Cannot change column ‘report_definition_uuid’: used in a foreign key constraint ‘reporting_report_design_ibfk_3’.

Can you list me the of the modules you are using?

Regards

@willa or anyone else who wants to try rewriting the patient search in lucene, feel free to reach out to me for help. I could provide more details or even help you get started in a short pair programming session.

1 Like

Hi, @steliomo,

Please see https://wiki.ampath.or.ke/x/IACyBQ and https://wiki.ampath.or.ke/x/xgClBQ.

Thanks!

@raff in this commit https://github.com/openmrs/openmrs-core/commit/93e5827912517da29478022e4dccb51e88b8bcbb#diff-c0c661e2e2bb32101232a168bcf42521 you switched the patient dao to use lucene but is seems like it was released in 2.1.0 can this be backported to 2.0.5 since at ampath we are still seem to be having this issue with 2.0.5

@achachiez, TRUNK-425 is a new feature with significant behavior changes, so it’s really not appropriate to backport this to a maintenance release.

Is it possible for AMPATH to upgrade to 2.1.x?

Hi @darius is it safe to do that because I think I read somewhere it wa sort of a developer release. And in case we can upgrade is there some sort of of special upgrade path because I tried it and had an issue when running the migrations ??? liquibase-update-to-latest.xml : Table 'amrs.concept_attribute' doesn't exist??? Unable to update the database. See server error logs for the full stacktrace. ??? Caused By: Precondition Error??? ???There was an error while updating the database to the latest. file: liquibase-update-to-latest.xml. Error: Migration failed for change set liquibase-update-to-latest.xml::201609171146-1::vshankar:??? ??? Reason: ??? ???:??? Does this mean we are missing the migration for the concept_attribute table?

When we use 1.9.2 Search war very fast. But I upgraded server and core version. Currently we are using 2.0.6 . Its too slow. Take 30 seconds for patient search. We have 100000+ patients.

Could you please try setting the search.caseSensitiveDatabaseStringComparison global property to false?

Yes, Its already false.

Can you restart tomcat?