dhis2-devs team mailing list archive

[Quang Nguyen <quangnguyen111@xxxxxxxxx>]

Hi all,

I am working on pruning organization unit function.
Currently it is lack of speed with hibernate queries. It takes hours for
pruning the demo database (with millions of data values) from dhis website.
Then I switch to use sql query instead. The taking time is reduced to more
than 9 minutes, much better, but we need more.
I guess the most taking time is used for deleting data values.
I did a small experiment by running sql queries directly on Navicat (a
PostgreSQL database client).
And an interesting result is the most time taking is not for deleting
datavalues, longest time is spend for deleting sources (see PS for detailed
result).

I guess the constraints to source table cause this ponderousness. I myself
now still have no solution to speed-up this procedure.
And I wonder why do we need the source table with only on column?

Comment please! :-)
Quang

PS:
Detailed result:

[SQL] delete from datavalue where sourceid in (264, 326850, 551, 1117,
326849, 1103, 1114, 1100, 1109, 1107, 553, 1161, 1169, 1164, 326848, 326847,
548, 1069, 1062, 73739, 1065, 1050, 1054, 1058, 1072, 73737, 550, 8399,
1095, 542, 326846, 623, 619, 614, 621, 627, 617, 625, 547, 326845, 1046,
1043, 1038, 543, 326844, 651, 326843, 646, 642, 639, 326842, 636, 73729,644,
167623, 24765, 630, 552, 1156, 1147, 326841, 1151, 1122, 326840, 1126, 1142,
1144, 326839, 545, 826, 828, 824, 822, 830, 326838, 820, 546, 8390, 1006,
8394, 172174, 326837, 326836, 851, 326835, 326833, 172172, 8392, 8382,
326830, 172176, 8386, 843, 326826, 326825, 849, 326823, 832, 8380, 172178,
978, 326820, 1023, 326819, 969, 8384, 839, 973, 8388, 326834, 326832,
326831, 24759, 976, 73747, 73733, 326828, 834, 326829, 847, 326827, 853,
1029, 1027, 8522, 326824, 15, 845,326822, 1010, 109308, 326821, 841, 836,
73735, 544, 653, 541, 595, 609, 2732, 598, 602, 540, 583, 326818, 326817,
585, 581, 574, 580, 586, 577, 172168, 579, 576, 578, 573, 326815, 326816,
73727, 24767, 549, 1088, 1082, 952);
*Time: 60.094ms
Affected rows: 244152*


[SQL] delete from organisationunit where organisationunitid in (264, 326850,
551, 1117, 326849, 1103, 1114, 1100, 1109, 1107, 553, 1161, 1169, 1164,
326848, 326847, 548, 1069, 1062, 73739, 1065, 1050, 1054, 1058, 1072, 73737,
550, 8399, 1095, 542, 326846, 623, 619, 614, 621, 627, 617, 625, 547,
326845, 1046, 1043, 1038, 543, 326844, 651, 326843, 646, 642, 639, 326842,
636, 73729,644, 167623, 24765, 630, 552, 1156, 1147, 326841, 1151, 1122,
326840, 1126, 1142, 1144, 326839, 545, 826, 828, 824, 822, 830, 326838, 820,
546, 8390, 1006, 8394, 172174, 326837, 326836, 851, 326835, 326833, 172172,
8392, 8382, 326830, 172176, 8386, 843, 326826, 326825, 849, 326823, 832,
8380, 172178, 978, 326820, 1023, 326819, 969, 8384, 839, 973, 8388, 326834,
326832, 326831, 24759, 976, 73747, 73733, 326828, 834, 326829, 847, 326827,
853, 1029, 1027, 8522, 326824, 15, 845,326822, 1010, 109308, 326821, 841,
836, 73735, 544, 653, 541, 595, 609, 2732, 598, 602, 540, 583, 326818,
326817, 585, 581, 574, 580, 586, 577, 172168, 579, 576, 578, 573, 326815,
326816, 73727, 24767, 549, 1088, 1082, 952);
*Time: 0.658ms
Affected rows: 166*


[SQL] delete from source where sourceid in (264, 326850, 551, 1117, 326849,
1103, 1114, 1100, 1109, 1107, 553, 1161, 1169, 1164, 326848, 326847, 548,
1069, 1062, 73739, 1065, 1050, 1054, 1058, 1072, 73737, 550, 8399, 1095,
542, 326846, 623, 619, 614, 621, 627, 617, 625, 547, 326845, 1046, 1043,
1038, 543, 326844, 651, 326843, 646, 642, 639, 326842, 636, 73729,644,
167623, 24765, 630, 552, 1156, 1147, 326841, 1151, 1122, 326840, 1126, 1142,
1144, 326839, 545, 826, 828, 824, 822, 830, 326838, 820, 546, 8390, 1006,
8394, 172174, 326837, 326836, 851, 326835, 326833, 172172, 8392, 8382,
326830, 172176, 8386, 843, 326826, 326825, 849, 326823, 832, 8380, 172178,
978, 326820, 1023, 326819, 969, 8384, 839, 973, 8388, 326834, 326832,
326831, 24759, 976, 73747, 73733, 326828, 834, 326829, 847, 326827, 853,
1029, 1027, 8522, 326824, 15, 845,326822, 1010, 109308, 326821, 841, 836,
73735, 544, 653, 541, 595, 609, 2732, 598, 602, 540, 583, 326818, 326817,
585, 581, 574, 580, 586, 577, 172168, 579, 576, 578, 573, 326815, 326816,
73727, 24767, 549, 1088, 1082, 952);
*Time: 76.377ms
Affected rows: 166*