avniproject / avniproject/avni-server

Denormalise sync parameters on checklist_item table

Open
#623 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
Java
Stars
22
Forks
47
PR merge metrics
No merged PRs in 30d

Description

### WHY?:

![Image](https://github.com/avniproject/avni-server/assets/10369963/b14f0054-ed86-4323-818a-6419148b0a65)

As seen in the above image in the last newrelic entry /txNewChecklistItemEntity is one of the top 15th frequently used request.
For some implementations, 35k pages of checklist items are fetched by getting them via 350 pages(which in general more than the no of program encounters we fetch for a user) which increases the sync time considerably. We see that the 95th percentile as well as avg response time is pretty bad.

**Sample logs**:

![Image](https://github.com/avniproject/avni-server/assets/10369963/63191687-7620-40f2-b645-0f33fece92f7)

### HOW?:
As we see in the below query fetching is already scoped. Hence to optimise the performance, denormalising the sync columns(address_id, aync_concept_1_value, sync_concept_2_value, individual_id) on checklist_item table, will avoid 2 joins that consumes the most time as explained [here](https://explain.depesz.com/s/Qbt0#html).

**Query:**

```
explain analyse select count(*)
from checklist_item
left outer join checklist on checklist_item.checklist_id = checklist.id
inner join program_enrolment on checklist.program_enrolment_id = program_enrolment.id
where program_enrolment.sync_concept_1_value = '8ebbf088-f292-483e-9084-7de919ce67b7'
and program_enrolment.address_id in
(331848,331849,331850,331851,331852,331853,331854,331855,331856,331857,331858,331859,331860,331861,331862,331863,331864,331865,331866,331867,331868,331869,331870,331871,331872,331873,331874,331875,331876,331877,331878,331879,331880,331881,331882,331883,331884,331885,331886,331887,331888,331889,331890,331891,331892,331893,331894,331895,331896,331897,331898,331899,331900,331901,331902,331903,331904,331905,331906,331907,331908,331909,331910,331911,331912,331913,331914,331915,331916,331917,331918,331919,331920,331921,331922,331923,331924,331925,331926,331927,331928,331929,331930,331931,331932,331933,331934,331935,331936,331937,331938,331939,331940,331941,331942,331943,331944,331945,331946,331947,331948,331949,331950,331951,331952,331953,331954,331955,331956,331957,331958,331959,331960,331961,331962,331963,331964,331965,331966,331967,331968,331969,331970,331971,331972,331973,331974,331975,331976,331977,331978,331979,331980,331981,331982,331983,331984,331985,331986,331987,331988,331989,331990,331991,331992,331993,331994,331995,331996,331997,331998,331999,331331,332000,332001,332002,332003,332004,332005,332006,332007,332008,332009,332010,332011,332012,332013,332014,332015,332016,332017,332018,332019,332020,332021,332022,332023,332024,332025,332026,332027,332028,332029,332030,332031,332032,332033,332034,332035,332036,332037,332038,332039,332040,332041,332042,332043,332044,332045,332046,332047,332048,332049,332050,332051,332052,332053,332054,332055,332056,332057,332058,332059,332060,332061,332062,332063,332064,332065,332066,332067,332068,332069,332070,332071,332072,332073,332074,332075,332076,332077,332078,332079,332080,332081,332082,332083,332084,332085,332086,332087,332088,332089,332090,332091,332092,332093,332094,332095,332096,332097,332098,332099,332100,332101,332102,332103,332104,332105,332106,332107,332108,332109,332110,332111,332112,332113,332114,332115,332116,332117,332118,332119,332120,332121,332122,332123,332124,332125,332126,332127,332128,332129,332130,332131,332132,332133,332134,332135,332136,332137,332138,332139,332140,332141,332142,332143,332144,332145,332146,332147,332148,332149,332150,332151,332152,332153,332154,332155,332156,332157,332158,332159,332160,332161,332162,332163,332164,332165,332166,332167,332168,332169,332170,332171,332172,332173,332174,332175,332176,332177,332178,332179,332180,332181,332182,332183,332184,332185,332186,332187,332188,332189,332190,332191,332192,332193,332194,332195,332196,332197,332198,332199,332200,332201,332202,332203,332204,332205,332206,332207,332208,332209,332210,332211,332212,332213,332214,332215,332216,332217,332218,332219,332220,332221,332222,332223,332224,332225,332226,332227,332228,332229,332230,332231,332232,332233,332234,332235,332236,332237,332238,332239,332240,332241,332242,332243,332244,332245,332246,332247,332248,332249,332250,332251,332252,332253,332254,332255,332256,332257,332258,332259,332260,332261,332262,332263,332264,332265,332266,332267,332268,332269,332270,332271,332272,332273,332274,332275,332276,332277,332278,332279,332280,332281,332282,332283,332284,332285,332286,332287,332288,332289,332290,332291,332292,332293,332294,332295,332296,332297,332298,332299,332300,332301,332302,332303,332304,332305,332306,332307,332308,332309,332310,332311,332312,332313,332314,332315,332316,332317,332318,332319,332320,332321,332322,332323,332324,332325,332326,332327,332328,332329,332330,332333,332334,332335,332336,332337,332338,332339,332340,332341,332342,332343,332344,332345,332346,332347,332348,332349,332350,332351,332352,332353,332354,332355,332356,332357,332358,332359,332360,332361,332362,332363,332364,332365,332366,332367,332368,332369,332370,332371,332372,332373,332374,332375,332376,332377,332378,332379,332380,332381,332382,332383,332384,332385,332386,332387,332388,332389,332390,332391,332392,332393,332394,332395,332396,332397,332398,332399,332400,332401,332402,332403,332404,332405,332406,332407,332408,332409,332410,332411,332412,332413,332414,332415,332416,332417,332418,332419,332420,332421,332422,332423,332424,332425,332426,332427,332428,332429,332430,332431,332432,332433,332434,332435,332436,332437,332438,332439,332440,332441,332442,332443,332444,332445,332446,332447,332448,332449,332450,332451,332452,332453,332454,332455,332456,332457,332458,332459,332460,332461,332462,332463,332464,332465,332466,332467,332468,332469,332470,332471,332472,332473,332474,332475,332476,332477,332478,332479,332480,332481,332482,332483,332484,332485,332486,332487,332488,332489,332490,332491,332492,332493,332494,332495,332496,332497,332498,332499,332500,332501,332502,332503,332504,332505,332506,332507,332508,332509,332510,332511,332512,332513,332514,332515,332516,332517,332518,332519,332520,332521,332522,332523,332524,332525,332526,332527,332528,332529,332530,332531,332532,332533,332534,332535,332536,332537,332538,332539,332540,332541,332542,332543,332544,332545,332546,332547,332548,332549,332550,332551,332552,332553,332554,332555,332556,332557,332558,332559,332560,332561,332562,332563,332564,332565,332566,332567,332568,332569,332570,332571,332572,332573,332574,332575,332576,332577,332578,332579,332580,332581,332582,332583,332584,332585,332586,332587,332588,332589,332590,332591,332592,332593,332594,332595,332596,332597,332598,332599,332600,332601,332602,332603,332604,332605,332606,332607,332608,332609,332610,332611,332612,332613,332614,332615,332616,332617,332618,332619,332620,332621,332622,332623,332624,332625,332626,332627,332628,332629,332630,332631,332632,335365,335366,335381,358330,332332,388980);

```

### Acceptance criteria:
- Add the above mentioned columns to checklist_item table
- Handle when saving checklist_item
- Add server migration to add these columns in the existing rows.
- For maha_nayak@apfodisha(has 350 pages of checklist_item) - available in prod - can create similar user in prerelease. Test before and after denormalising and comment the time difference.

Contributor guide

No contributing guide indexed for this repository

Research direction

Start by locating the checklist_item persistence and save path, then inspect the existing server migration approach and the query shown in the issue. Add the four denormalized columns, keep them populated when checklist items are saved, and migrate existing rows. Validate before-and-after sync timing with a user having roughly 350 pages of checklist items, recording the difference in the issue.

Written by the indexing model from the issue text.

Assessment

Tech stack
java, postgresql, spring-boot
Domain
backend, databases, performance
Issue type
Refactor
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
42/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.