Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Batch inserts of resources to db especially during sync #2716

Open
LZRS opened this issue Nov 8, 2024 · 0 comments
Open

Batch inserts of resources to db especially during sync #2716

LZRS opened this issue Nov 8, 2024 · 0 comments
Labels
effort:small Small effort - 2 days P2 Medium priority issue

Comments

@LZRS
Copy link
Collaborator

LZRS commented Nov 8, 2024

Describe the solution you'd like
When inserting many resources, this method
does a save for each resource individually after generating and saving the associated index entities to the relevant tables.

Doing the inserts in some form of batch would probably offer some performance improvement, especially during sync, although not sure how significant.

Additional context
Example, when saving ServiceRequests

00:27:44.014  V  "INSERT OR REPLACE INTO `ResourceEntity` (`id`,`resourceUuid`,`resourceType`,`resourceId`,`serializedResource`,`versionId`,`lastUpdatedRemote`,`lastUpdatedLocal`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','8dd791f8-ee0a-48dc-8554-2359b075734e','{"resourceType":"ServiceRequest","id":"8dd791f8-ee0a-48dc-8554-2359b075734e","meta":{"versionId":"2","lastUpdated":"2024-11-08T21:27:43.388+00:00","source":"#a810de60de0ed9e7","tag":[{"system":"https://smartregister.org/care-team-tag-id","code":"3e005baf-854b-40a7-bdd5-9b73f63aa9a3","display":"Practitioner CareTeam"},{"system":"https://smartregister.org/organisation-tag-id","code":"41eae946-bdc4-4179-b404-6503ff12f59c","display":"Practitioner Organization"},{"system":"https://smartregister.org/practitioner-tag-id","code":"49b72a3d-44cd-4a74-9459-4dc9f6b543fa","display":"Practitioner"},{"system":"https://smartregister.org/location-tag-id","code":"b11dd2d
00:27:44.020  V  "INSERT OR REPLACE INTO `ReferenceIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_value`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','encounter','ServiceRequest.encounter','Encounter/d8ed8eb1-aeee-4ff7-a7cb-edbcb27ee2b4')"
00:27:44.020  V  "INSERT OR REPLACE INTO `ReferenceIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_value`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','subject','ServiceRequest.subject','Patient/b8975214-1372-473e-a839-c5b31a1438ce')"
00:27:44.021  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','code','ServiceRequest.code','http://snomed.info/sct','44383000')"
00:27:44.021  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','category','ServiceRequest.category','http://snomed.info/sct','44383000')"
00:27:44.022  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','intent','ServiceRequest.intent','http://hl7.org/fhir/request-intent','order')"
00:27:44.022  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','priority','ServiceRequest.priority','http://hl7.org/fhir/request-priority','urgent')"
00:27:44.022  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','status','ServiceRequest.status','http://hl7.org/fhir/request-status','revoked')"
00:27:44.023  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','_id','ServiceRequest.id',NULL,'8dd791f8-ee0a-48dc-8554-2359b075734e')"
00:27:44.023  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','_tag','ServiceRequest.meta.tag','https://smartregister.org/care-team-tag-id','3e005baf-854b-40a7-bdd5-9b73f63aa9a3')"
00:27:44.024  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','_tag','ServiceRequest.meta.tag','https://smartregister.org/organisation-tag-id','41eae946-bdc4-4179-b404-6503ff12f59c')"
00:27:44.024  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','_tag','ServiceRequest.meta.tag','https://smartregister.org/practitioner-tag-id','49b72a3d-44cd-4a74-9459-4dc9f6b543fa')"
00:27:44.024  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','_tag','ServiceRequest.meta.tag','https://smartregister.org/location-tag-id','b11dd2d1-d2c4-4573-bdd1-5bbade37b886')"
00:27:44.025  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','_tag','ServiceRequest.meta.tag','https://smartregister.org/app-version','1.1.0-diabetesCompass')"
00:27:44.025  V  "INSERT OR REPLACE INTO `UriIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_value`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','_source','ServiceRequest.meta.source','#a810de60de0ed9e7')"
00:27:44.025  V  "INSERT OR REPLACE INTO `DateTimeIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_from`,`index_to`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','occurrence','ServiceRequest.occurrence',1725408000000,1726012800000)"
00:27:44.026  V  "INSERT OR REPLACE INTO `DateTimeIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_from`,`index_to`) VALUES (nullif(0, 0),x'5117aacacf524da9827371ed0968ee66','ServiceRequest','_lastUpdated','ServiceRequest.meta.lastUpdated',1731101263388,1731101263388)"


00:27:44.097  V  "INSERT OR REPLACE INTO `ResourceEntity` (`id`,`resourceUuid`,`resourceType`,`resourceId`,`serializedResource`,`versionId`,`lastUpdatedRemote`,`lastUpdatedLocal`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','b0f864ff-11bb-4323-9bff-68079fe13593','{"resourceType":"ServiceRequest","id":"b0f864ff-11bb-4323-9bff-68079fe13593","meta":{"versionId":"1","lastUpdated":"2024-11-08T21:27:43.388+00:00","source":"#a71c7487773fb027","tag":[{"system":"https://smartregister.org/care-team-tag-id","code":"3e005baf-854b-40a7-bdd5-9b73f63aa9a3","display":"Practitioner CareTeam"},{"system":"https://smartregister.org/organisation-tag-id","code":"41eae946-bdc4-4179-b404-6503ff12f59c","display":"Practitioner Organization"},{"system":"https://smartregister.org/practitioner-tag-id","code":"49b72a3d-44cd-4a74-9459-4dc9f6b543fa","display":"Practitioner"},{"system":"https://smartregister.org/location-tag-id","code":"b11dd2d
00:27:44.103  V  "INSERT OR REPLACE INTO `ReferenceIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_value`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','encounter','ServiceRequest.encounter','Encounter/641e73bc-37da-4192-86a8-66697bb69c6c')"
00:27:44.103  V  "INSERT OR REPLACE INTO `ReferenceIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_value`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','subject','ServiceRequest.subject','Patient/d5203ddf-7728-47ad-a03b-98dc9b62b9f6')"
00:27:44.104  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','code','ServiceRequest.code','http://snomed.info/sct','44383000')"
00:27:44.104  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','category','ServiceRequest.category','http://snomed.info/sct','44383000')"
00:27:44.105  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','intent','ServiceRequest.intent','http://hl7.org/fhir/request-intent','order')"
00:27:44.105  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','priority','ServiceRequest.priority','http://hl7.org/fhir/request-priority','urgent')"
00:27:44.106  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','status','ServiceRequest.status','http://hl7.org/fhir/request-status','active')"
00:27:44.106  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','_id','ServiceRequest.id',NULL,'b0f864ff-11bb-4323-9bff-68079fe13593')"
00:27:44.106  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','_tag','ServiceRequest.meta.tag','https://smartregister.org/care-team-tag-id','3e005baf-854b-40a7-bdd5-9b73f63aa9a3')"
00:27:44.107  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','_tag','ServiceRequest.meta.tag','https://smartregister.org/organisation-tag-id','41eae946-bdc4-4179-b404-6503ff12f59c')"
00:27:44.107  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','_tag','ServiceRequest.meta.tag','https://smartregister.org/practitioner-tag-id','49b72a3d-44cd-4a74-9459-4dc9f6b543fa')"
00:27:44.108  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','_tag','ServiceRequest.meta.tag','https://smartregister.org/location-tag-id','b11dd2d1-d2c4-4573-bdd1-5bbade37b886')"
00:27:44.108  V  "INSERT OR REPLACE INTO `TokenIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_system`,`index_value`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','_tag','ServiceRequest.meta.tag','https://smartregister.org/app-version','2.0.0-diabetesCompassClinic')"
00:27:44.108  V  "INSERT OR REPLACE INTO `UriIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_value`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','_source','ServiceRequest.meta.source','#a71c7487773fb027')"
00:27:44.109  V  "INSERT OR REPLACE INTO `DateTimeIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_from`,`index_to`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','occurrence','ServiceRequest.occurrence',1730764800000,1731369600000)"
00:27:44.109  V  "INSERT OR REPLACE INTO `DateTimeIndexEntity` (`id`,`resourceUuid`,`resourceType`,`index_name`,`index_path`,`index_from`,`index_to`) VALUES (nullif(0, 0),x'9a1a0e2a1929428fa3ff1305ac9fc410','ServiceRequest','_lastUpdated','ServiceRequest.meta.lastUpdated',1731101263388,1731101263388)"

Would you like to work on the issue?
Please state if this issue should be assigned to you or who you think could help to solve this issue.
@aditya-07 aditya-07 added P2 Medium priority issue effort:small Small effort - 2 days labels Nov 11, 2024
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
effort:small Small effort - 2 days P2 Medium priority issue
Projects
Android FHIR SDK
Status: New
Milestone
No milestone
Development

No branches or pull requests
2 participants
@LZRS @aditya-07