avniproject / avniproject/avni-server
Denormalise sync parameters on checklist_item table
- Dominant language
- Java
- Stars
- 22
- Forks
- 47
- PR merge metrics
- No merged PRs in 30d
Description
### WHY?:

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**:

### 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