dhis2-devs team mailing list archive

["Saptarshi Purkayastha" <sunbiz@xxxxxxxxx>]

A stored procedure that does cascade operations is one option and a multi-threaded datavalue delete is another option

I agree that it will probably still spend time on orgunits like it is doing with sources


Sent from my BlackBerry®

-----Original Message-----
From: Bob Jolliffe <bobjolliffe@xxxxxxxxx>
Sender: dhis2-devs-bounces+sunbiz=gmail.com@xxxxxxxxxxxxxxxxxxx
Date: Thu, 24 Jun 2010 12:01:14 
To: Lars Helge Øverland<larshelge@xxxxxxxxx>
Cc: dhis2-devs<dhis2-devs@xxxxxxxxxxxxxxxxxxx>
Subject: Re: [Dhis2-devs] Prune OrganisationUnit Speed Problem

2010/6/24 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

Though I'm sure we all agree this is probably a good idea in terms of
tidying up the data model, I don't think it will be the silver bullet
solution to this problem.  My guess is that time spent deleting
sources would simply shift to extra time spent deleting orgunits :-(

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

_______________________________________________
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