dhis2-devs team mailing list archive

[Lars Helge Øverland <larshelge@xxxxxxxxx>]

On Thu, Jun 24, 2010 at 9:59 AM, Quang Nguyen <quangnguyen111@xxxxxxxxx>wrote:

> 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?
>
The original reason was to cater for more than one type of sources, but that
need has never emerged. So we have decided to remove it (rather have
OrganisationUnit directly on DataValue) but haven't had the time to
implement yet.

Lars




>
> 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*
>
>
>
>
> _______________________________________________
> Mailing list: https://launchpad.net/~dhis2-devs
> Post to     : dhis2-devs@xxxxxxxxxxxxxxxxxxx
> Unsubscribe : https://launchpad.net/~dhis2-devs
> More help   : https://help.launchpad.net/ListHelp
>
>