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 <[email protected]> Sender: [email protected] Date: Thu, 24 Jun 2010 12:01:14 To: Lars Helge Øverland<[email protected]> Cc: dhis2-devs<[email protected]> Subject: Re: [Dhis2-devs] Prune OrganisationUnit Speed Problem 2010/6/24 Lars Helge Øverland <[email protected]>: > > > On Thu, Jun 24, 2010 at 9:59 AM, Quang Nguyen <[email protected]> > 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 : [email protected] >> Unsubscribe : https://launchpad.net/~dhis2-devs >> More help : https://help.launchpad.net/ListHelp >> > > >_______________________________________________ > Mailing list: https://launchpad.net/~dhis2-devs > Post to : [email protected] > Unsubscribe : https://launchpad.net/~dhis2-devs > More help : https://help.launchpad.net/ListHelp > > _______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp _______________________________________________ Mailing list: https://launchpad.net/~dhis2-devs Post to : [email protected] Unsubscribe : https://launchpad.net/~dhis2-devs More help : https://help.launchpad.net/ListHelp
