Meer kracht en snelheid uit mijn SQL-tabellen

Door George van Baasbank op 12-11-2012 13:58

1.854 views

Hallo allemaal,

Op dit moment ben ik bezig met een website voor een Genealogie-vereniging. Ik maak gebruik van een (verzamel)tabel waarin alle persoonsgegevens staan opgenomen. Deze tabel werd tot voor kort bijgewerkt middels een Exceldownload vanuit het genealogieprogramma Aldfaer.
Nu (b)lijkt dat niet toekomstvast te zijn dus ben ik overgestapt naar een wereldwijd formaat, genaamd GEDCOM.
Deze GEDCOM-structuur is veel omvangrijker dan ik met Excel deed. Ik heb wel de keuze gemaakt om de door mij ontwikkelde tabel te handhaven. Dus moet ik het GEDCOM-bestand implementeren in mijn tabel.
Als eerste importeer ik het GEDCOM-bestand in een aantal tabellen zoals deze door de GEDCOM-standaard zijn vastgelegd. Daar ontkom ik niet aan.
Vervolgens ga ik deze tabellen opnemen in mijn eigen verzameltabel. Daar ligt nu voor mij de uitdaging. Het overnemen van de diverse gegevens uit de verschillende tabellen duurt ca. 2 minuten bij ca. 7.000 records. Op zich geen bezwaar daar deze actie slechts een paar keer per jaar voorkomt. Wel moet ik rekening houden met een groei van het aantal records naar wellicht 50.000. Hierdoor gaat de tijd dus ook toenemen.
Ik wil nu kijken om met een betere, krachtiger, opzet van mijn SQL-commando's tijdswinst te boeken. Hiervoor wil ik jullie inschakelen om mijn kennis wellicht te vergroten.

Eerst de structuur van de verschillende tabellen:

ove_gedcom, de verzameltabel t.b.v. mijn website


CREATE TABLE ove_gedcom (
  id varchar(50) NOT NULL,
  `code` varchar(25) NOT NULL,
  geslacht varchar(1) NOT NULL,
  tussenvoeg varchar(15) NOT NULL,
  achternaam varchar(50) NOT NULL,
  voornamen varchar(255) NOT NULL,
  initialen varchar(10) NOT NULL,
  eerste varchar(25) NOT NULL,
  roepnaam varchar(50) NOT NULL,
  levenloos varchar(10) NOT NULL,
  geboorte varchar(20) NOT NULL,
  g_ca varchar(25) NOT NULL,
  g_sortdatum varchar(25) NOT NULL,
  g_bron varchar(100) NOT NULL,
  geboorteplaats varchar(50) NOT NULL,
  aangifte varchar(15) NOT NULL,
  a_ca varchar(25) NOT NULL,
  a_sortdatum varchar(25) NOT NULL,
  a_bron varchar(100) NOT NULL,
  partner varchar(75) NOT NULL,
  relatie_id varchar(50) NOT NULL,
  partner_id varchar(50) NOT NULL,
  relatie_soort varchar(25) NOT NULL,
  kerk varchar(25) NOT NULL,
  relatie_datum varchar(15) NOT NULL,
  tr_ca varchar(25) NOT NULL,
  tr_sortdatum varchar(25) NOT NULL,
  tr_plaats varchar(50) NOT NULL,
  tr_bron varchar(100) NOT NULL,
  dp_datum varchar(15) NOT NULL,
  dp_ca varchar(25) NOT NULL,
  dp_sortdatum varchar(25) NOT NULL,
  dp_bron varchar(100) NOT NULL,
  dp_plaats varchar(50) NOT NULL,
  ovl_code varchar(10) NOT NULL,
  ovl_datum varchar(25) NOT NULL,
  ovl_ca varchar(25) NOT NULL,
  ovl_sortdatum varchar(25) NOT NULL,
  ovl_bron varchar(100) NOT NULL,
  ovl_plaats varchar(50) NOT NULL,
  ovl_aang_datum varchar(15) NOT NULL,
  ovl_aang_ca varchar(15) NOT NULL,
  ovl_aang_sortdatum varchar(15) NOT NULL,
  ovl_aang_bron varchar(50) NOT NULL,
  begr_datum varchar(15) NOT NULL,
  begr_ca varchar(15) NOT NULL,
  begr_sortdatum varchar(15) NOT NULL,
  begr_bron varchar(100) NOT NULL,
  begr_plaats varchar(50) NOT NULL,
  ongehuwd varchar(2) NOT NULL,
  kinderloos varchar(2) NOT NULL,
  begr_wijze varchar(50) NOT NULL,
  uitz_geb varchar(5) NOT NULL,
  uitz_rel varchar(5) NOT NULL,
  uitz_kind varchar(5) NOT NULL,
  uitz_ovl varchar(5) NOT NULL,
  adres varchar(50) NOT NULL,
  postcode varchar(10) NOT NULL,
  plaats varchar(50) NOT NULL,
  land varchar(50) NOT NULL,
  opmerking varchar(1000) NOT NULL,
  id_vader varchar(50) NOT NULL,
  naam_vader varchar(50) NOT NULL,
  id_moeder varchar(50) NOT NULL,
  naam_moeder varchar(50) NOT NULL,
  wijz_datum varchar(25) NOT NULL,
  toev_datum varchar(25) NOT NULL,
  nieuw varchar(5) NOT NULL,
  afbeelding varchar(255) NOT NULL,
  teller int(11) NOT NULL AUTO_INCREMENT,
  toegevoegd datetime NOT NULL,
  iid varchar(100) NOT NULL,
  PRIMARY KEY (teller),
  KEY achternaam (achternaam),
  KEY id (id),
  KEY g_sortdatum (g_sortdatum),
  KEY eerste (eerste),
  KEY tussenvoeg (tussenvoeg),
  KEY partner_id (partner_id),
  KEY tr_sortdatum (tr_sortdatum),
  KEY id_moeder (id_moeder),
  KEY id_vader (id_vader),
  KEY voornamen (voornamen),
  KEY naam_moeder (naam_moeder),
  KEY naam_vader (naam_vader),
  KEY relatie_datum (relatie_datum),
  KEY dp_sortdatum (dp_sortdatum),
  KEY ovl_sortdatum (ovl_sortdatum)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1;

De diverse tabel voor GEDCOM


CREATE TABLE ftphp__even (
  etid int(10) unsigned NOT NULL,
  eid varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  iftid int(10) unsigned NOT NULL,
  ifid varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  `type` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `date` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  datec date NOT NULL DEFAULT '0000-00-00',
  plac varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  description text COLLATE utf8_unicode_ci,
  `value` text COLLATE utf8_unicode_ci,
  living tinyint(1) DEFAULT NULL,
  PRIMARY KEY (etid,eid),
  KEY iftid (iftid,ifid),
  KEY datec (datec),
  KEY plac (plac)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Gebeurtenissen zoals overlijden, geboorte e.d.';

CREATE TABLE ftphp__fam (
  ftid int(10) unsigned NOT NULL DEFAULT '0',
  fid varchar(16) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  refn varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  rin varchar(16) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  htid int(10) unsigned NOT NULL DEFAULT '0',
  husb varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  wtid int(10) unsigned NOT NULL DEFAULT '0',
  wife varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `type` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  `date` varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  datec date NOT NULL DEFAULT '0000-00-00',
  plac varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  hsrt tinyint(1) unsigned NOT NULL DEFAULT '0',
  wsrt tinyint(1) unsigned NOT NULL DEFAULT '0',
  chan timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (ftid,fid),
  KEY htid (htid,husb),
  KEY wtid (wtid,wife),
  KEY chan (chan)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE ftphp__fam_chil (
  itid int(10) unsigned NOT NULL,
  iid varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  ftid int(10) unsigned NOT NULL,
  fid varchar(16) COLLATE utf8_unicode_ci NOT NULL,
  rela varchar(16) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'birth',
  sort tinyint(2) unsigned NOT NULL DEFAULT '0',
  PRIMARY KEY (itid,iid,ftid,fid)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

CREATE TABLE ftphp__indi (
  itid int(10) unsigned NOT NULL DEFAULT '0',
  iid varchar(16) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  refn varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  rin varchar(16) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Relatienummer',
  sex char(1) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Geslacht',
  begi_type varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '' COMMENT 'Geboren of Gedoopt',
  begi_date varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  begi_datec date NOT NULL DEFAULT '0000-00-00',
  begi_plac varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  end_type varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  end_date varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  end_datec date NOT NULL DEFAULT '0000-00-00',
  end_plac varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  fams tinyint(1) NOT NULL DEFAULT '0',
  famc tinyint(1) NOT NULL DEFAULT '0',
  living tinyint(1) DEFAULT NULL,
  chan timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (itid,iid),
  KEY chan (chan)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Individuen';

CREATE TABLE ftphp__indi_name (
  itid int(10) unsigned NOT NULL DEFAULT '0',
  iid varchar(16) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  givn varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  surn varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  nick varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  npfx varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  spfx varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  nsfx varchar(128) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  spfx_surn varchar(255) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
  sort tinyint(1) unsigned NOT NULL DEFAULT '0',
  KEY itid (itid,iid),
  KEY surn (surn),
  KEY givn (givn),
  KEY spfx_surn (spfx_surn)
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci COMMENT='Namen van individuen';

Met behulp van de onderstaande code ga ik mijn verzameltabel invullen:


// GEDCOM-tabellen inlezen
    // Eerst de INDI-tabel met de bijbehorende namen
    $sql = "SELECT * FROM ftphp__indi JOIN ftphp__indi_name ON ftphp__indi.iid = ftphp__indi_name.iid";
    $cResultPersonen = mysql_query($sql);
    while($row = mysql_fetch_array($cResultPersonen)) {
        $cIndiRelatie = $row['rin'];
        $cIndiInternId = $row['iid'];      
        $cIndiSortGebDatum = "N-" . $row['begi_datec'];
        $cIndiGebDatum = substr($row['begi_datec'],8,2) . "-" . substr($row['begi_datec'],5,2) . "-" . substr($row['begi_datec'],0,4);
        $cIndiGebPlaats = $row['begi_plac'];
        $cIndiSortOvlDatum = "N-" . $row['end_datec'];
        $cIndiOvlDatum = substr($row['end_datec'],8,2) . "-" . substr($row['end_datec'],5,2) . "-" . substr($row['end_datec'],0,4);
        $cIndiOvlPlaats = $row['end_plac'];
        $cIndiGeslacht = $row['sex'];
        $cIndiGebGed = $row['begi_type'];
        if($cIndiGeslacht == "F") {
            $cIndiGeslacht = "V";
        }
        $cPersoonsnaam = $row['givn'] . " " . $row['surn'];
        $cIndiVoornaam = $row['givn'];
        $cIndiTussenvoeg = $row['spfx'];
        $cIndiAchternaam = $row['surn'];
        $cIndiRoepnaam = $row['nick'];
        $cIndiExplode = explode(" ", $cIndiVoornaam);    
        $cIndiEersteNaam = $cIndiExplode[0];
        $array = explode(' ', $cIndiVoornaam);
        foreach($array as $key => $value) {
            $array[$key] = ucfirst(substr($value, 0, 1)) . '.';
        }
        $cIndiInitialen = strtoupper(implode('', $array));

        $sql = "INSERT INTO `ove_gedcom` (id, geslacht, voornamen, tussenvoeg, achternaam, eerste, geboorte, g_sortdatum,";
        $sql .= " geboorteplaats, ovl_datum, ovl_sortdatum, ovl_plaats,toegevoegd, roepnaam, iid, initialen) ";
        $sql .= " VALUES ('$cIndiRelatie', '$cIndiGeslacht', '$cIndiVoornaam', '$cIndiTussenvoeg', '$cIndiAchternaam','$cIndiEersteNaam','$cIndiGebDatum', '$cIndiSortGebDatum',";
        $sql .= "'$cIndiGebPlaats','$cIndiOvlDatum', '$cIndiSortOvlDatum', '$cIndiOvlPlaats', NOW(), '$cIndiRoepnaam','$cIndiInternId','$cIndiInitialen')";
        $cResult = mysql_query($sql);
    }
    
    // De huwelijksgegevens worden bijgewerkt
    $sql = "SELECT * FROM ftphp__fam";
    $cResultHuwelijk = mysql_query($sql);
    while($row = mysql_fetch_array($cResultHuwelijk)) {
        // Idnummers ophalen
        $cIndiIdMan = $row['husb'];
        $cIndiIdMan2 = substr($cIndiIdMan,3,25);
        $cIndiIdVrouw = $row['wife'];
        $cIndiIdVrouw2 = substr($cIndiIdVrouw,3,25);
        // Huwelijksgegevens ophalen
        $cIndiHuwSortDatum = "N-" . $row['datec'];
        $cIndiHuwDatum = substr($row['datec'],8,2) . "-" . substr($row['datec'],5,2) . "-" . substr($row['datec'],0,4);
        $cIndiHuwPlaats = $row['plac'];
        // Naam partner ophalen
        $sql = "SELECT * FROM ftphp__indi_name WHERE iid = '$cIndiIdVrouw' LIMIT 1";
        $cResult = mysql_query($sql);
        $row = mysql_fetch_array($cResult);
        $cIndiNaamVrouw = $row['givn'] . " " . $row['spfx'] . " " . $row['surn'];
        $sql = "SELECT * FROM ftphp__indi_name WHERE iid = '$cIndiIdMan' LIMIT 1";
        $cResult = mysql_query($sql);
        $row = mysql_fetch_array($cResult);
        $cIndiNaamMan = $row['givn'] . " " . $row['spfx'] . " " . $row['surn'];
        
        $sql = "UPDATE `ove_gedcom` SET tr_sortdatum = '$cIndiHuwSortDatum', tr_plaats = '$cIndiHuwPlaats', relatie_datum = '$cIndiHuwDatum', partner_id = '$cIndiIdVrouw2', partner = '$cIndiNaamVrouw' WHERE iid = '$cIndiIdMan';";
        $cResult = mysql_query($sql);
        $sql = "UPDATE `ove_gedcom` SET tr_sortdatum = '$cIndiHuwSortDatum', tr_plaats = '$cIndiHuwPlaats', relatie_datum = '$cIndiHuwDatum', partner_id = '$cIndiIdMan2', partner = '$cIndiNaamMan' WHERE iid = '$cIndiIdVrouw';";
        $cResult = mysql_query($sql);
    }
    
    // Gebeurtenissen bijwerken
    // Geboortes (doodgeboren)
    $sql = "SELECT * FROM ftphp__even WHERE description = 'stillborn'";
    $cResult3 = mysql_query($sql);
    while($row = mysql_fetch_array($cResult3)) {
        $cPersoonId = $row['ifid'];
        $cKenmerk = "Ja";
        $sql = "UPDATE ove_gedcom SET levenloos = '$cKenmerk' WHERE iid = '$cPersoonId'";
        $cResult = mysql_query($sql);
    }
    // Aangifte geboorten
    $sql = "SELECT * FROM ftphp__even WHERE description = 'birth registration'";
    $cResult4 = mysql_query($sql);
    while($row = mysql_fetch_array($cResult4)) {
        $cPersoonId = $row['ifid'];
        $cAangDatum = substr($row['datec'],8,2) . "-" . substr($row['datec'],5,2) . "-" . substr($row['datec'],0,4);
        $cAangSortDatum = "N-" . $row['datec'];
        $sql = "UPDATE ove_gedcom SET a_sortdatum = '$cAangSortDatum', aangifte = '$cAangDatum' WHERE iid = '$cPersoonId'";
        $cResult = mysql_query($sql);
    }
    // Aangifte overlijden
    $sql = "SELECT * FROM ftphp__even WHERE description = 'death registration'";
    $cResult4 = mysql_query($sql);
    while($row = mysql_fetch_array($cResult4)) {
        $cPersoonId = $row['ifid'];
        $cAangDatum = substr($row['datec'],8,2) . "-" . substr($row['datec'],5,2) . "-" . substr($row['datec'],0,4);
        $cAangSortDatum = "N-" . $row['datec'];
        $sql = "UPDATE ove_gedcom SET ovl_aang_sortdatum = '$cAangSortDatum', ovl_aang_datum = '$cAangDatum' WHERE iid = '$cPersoonId'";
        $cResult = mysql_query($sql);
    }
    
    // Begraven en crematie
    $sql = "SELECT * FROM ftphp__even WHERE type = '*crem*' OR type = '*buri*'";
    $cResult5 = mysql_query($sql);
    while($row = mysql_fetch_array($cResult5)) {
        $cPersoonId = $row['ifid'];
        $cWijzeBegraven = $row['type'];
        if($cWijzeBegraven == "*crem*") {
            $cWijzeBegraven = "Crematie";
        } elseif($cWijzeBegraven == "*buri*") {
            $cWijzeBegraven = "Begraven";
        }
        $cBegraafPlaats = $row['plac'];
        $cAangDatum = substr($row['datec'],8,2) . "-" . substr($row['datec'],5,2) . "-" . substr($row['datec'],0,4);
        $cAangSortDatum = "N-" . $row['datec'];
        $sql = "UPDATE ove_gedcom SET begr_wijze = '$cWijzeBegraven', begr_datum = '$cAangDatum', begr_sortdatum = '$cAangSortDatum', begr_plaats = '$cBegraafPlaats'  WHERE iid = '$cPersoonId'";
        $cResult = mysql_query($sql);
    }
    
    // Zoek namen ouders
    $sql = "SELECT * FROM ove_gedcom";
    $cResult6 = mysql_query($sql);
    while($row = mysql_fetch_array($cResult6)) {
        $cPersoonId = $row['iid'];
        // Zoek naar ouders
        $sql2 = "SELECT * FROM ftphp__fam_chil WHERE iid = '$cPersoonId' AND rela = 'birth' LIMIT 1 ";
        $cResult7 = mysql_query($sql2);
        $row2 = mysql_fetch_array($cResult7);
        $cOudersRelatie = $row2['fid'];
        $sql3 = "SELECT * FROM ftphp__fam WHERE fid = '$cOudersRelatie' LIMIT 1";
        $cResult8 = mysql_query($sql3);
        $row3 = mysql_fetch_array($cResult8);
        $cIdVader = $row3['husb'];
        $cIdMoeder = $row3['wife'];
        $sql = "SELECT * FROM ftphp__indi WHERE iid = '$cIdVader'LIMIT 1 ";
        $cResultX = mysql_query($sql);
        $rowX = mysql_fetch_array($cResultX);
        $cIdVader2 = $rowX['rin'];
        $sql = "SELECT * FROM ftphp__indi WHERE iid = '$cIdMoeder' LIMIT 1";
        $cResultY = mysql_query($sql);
        $rowY = mysql_fetch_array($cResultY);
        $cIdMoeder2 = $rowY['rin'];
        $sql4 = "SELECT * FROM ftphp__indi_name WHERE iid = '$cIdVader' LIMIT 1 ";
        $cResult9 = mysql_query($sql4);
        $row4 = mysql_fetch_array($cResult9);
        $cNaamVader = $row4['givn'] . " " . $row4['spfx'] . " " . $row4['surn'];
        $sql5 = "SELECT * FROM ftphp__indi_name WHERE iid = '$cIdMoeder' LIMIT 1 ";
        $cResult9 = mysql_query($sql5);
        $row4 = mysql_fetch_array($cResult9);
        $cNaamMoeder = $row4['givn'] . " " . $row4['spfx'] . " " . $row4['surn'];
        $sql = "UPDATE ove_gedcom SET naam_vader = '$cNaamVader', naam_moeder = '$cNaamMoeder', id_vader = '$cIdVader2', id_moeder = '$cIdMoeder2'  WHERE iid = '$cPersoonId'";
        $cResult = mysql_query($sql);        
    }
}

Hebben jullie suggesties om vooral het laatste deel te optimaliseren?

George

Bart van der Veen

12-11-2012 14:11

Niet echt mijn gebied van expertise maar je zou eens kunnen beginnen met

- SELECT * FROM te vervangen door SELECT kolom1,kolom2,enz FROM
- kijk eens naar de joins http://www.tizag.com/mysqlTutorial/mysqljoins.php nested querys zijn niet best volgens mij

Ger van Steenderen

16-11-2012 12:43

Allereerst moet je afleren om SELECT * te gebruiken, je doet dit herhaaldelijk terwijl je maar 1 kolom nodig hebt.
Het punt is en blijft dat je het jezelf onnodig moeilijk maakt door gegevens uit verschillende tabellen over te hevelen naar één verzameltabel. Als je ze kunt selecteren om een verzameltabel bij te werken, kan je ze ook op het moment dat je ze wilt presenteren selecteren.

Je eerste while lus kan je in één query kwijt, dmv van INSERT INTO .... SELECT. Ik weet de kolommen uit de brontabel niet dus maar even een simpel voorbeeldje:


INSERT INTO table1 (c1,c2,c3)
SELECT t2.c1, t2.c2, t3.c3 FROM table2 AS t2
JOIN table3 ON t2.c1 = t3.c2

George van Baasbank

16-11-2012 14:04 gewijzigd op 16-11-2012 16:24

Ger,

Als ik jouw suggestie opvolg moet ik veel pagina's opnieuw schrijven. Het is maar net waar je voor kiest.

Ik kies dus voor jouw laatste suggestie met de php-code.

Ik ga aan de slag

Ik heb de volgende code aangepast. Als ik alleen het eerste deel van de code uitvoer, t.e.m. de "INSERT" dan gaat alles goed. Voeg ik hetb tweede deel erbij, het deel met de "UPDATE" dan ontstaat de PROXY-fout. De database is echter JUIST bijgewerkt.
Het lijkt er op dat na het afsluiten van beide routrines er iets gaat "zweven".

Wie heeft hier ervaring mee?


// GEDCOM-tabellen inlezen
    // Eerst de INDI-tabel met de bijbehorende namen
    $sql = "SELECT * FROM ftphp__indi JOIN ftphp__indi_name ON ftphp__indi.iid = ftphp__indi_name.iid";
    $cResultPersonen = mysql_query($sql);
    $nAantalPersonen = mysql_num_rows($cResultPersonen);
    while($row = mysql_fetch_array($cResultPersonen)) {
        $cIndiRelatie = $row['rin'];
        $cIndiInternId = $row['iid'];      
        $cIndiSortGebDatum = "N-" . $row['begi_datec'];
        $cIndiGebDatum = substr($row['begi_datec'],8,2) . "-" . substr($row['begi_datec'],5,2) . "-" . substr($row['begi_datec'],0,4);
        $cIndiGebPlaats = $row['begi_plac'];
        $cIndiSortOvlDatum = "N-" . $row['end_datec'];
        $cIndiOvlDatum = substr($row['end_datec'],8,2) . "-" . substr($row['end_datec'],5,2) . "-" . substr($row['end_datec'],0,4);
        $cIndiOvlPlaats = $row['end_plac'];
        $cIndiGeslacht = $row['sex'];
        $cIndiGebGed = $row['begi_type'];
        if($cIndiGeslacht == "F") {
            $cIndiGeslacht = "V";
        }
        $cPersoonsnaam = $row['givn'] . " " . $row['surn'];
        $cIndiVoornaam = $row['givn'];
        $cIndiTussenvoeg = $row['spfx'];
        $cIndiAchternaam = $row['surn'];
        $cIndiRoepnaam = $row['nick'];
        $cIndiExplode = explode(" ", $cIndiVoornaam);    
        $cIndiEersteNaam = $cIndiExplode[0];
        $array = explode(' ', $cIndiVoornaam);
        foreach($array as $key => $value) {
            $array[$key] = ucfirst(substr($value, 0, 1)) . '.';
        }
        $cIndiInitialen = strtoupper(implode('', $array));

        $sql = "INSERT INTO ove_genealogie (id, geslacht, voornamen, tussenvoeg, achternaam, eerste, geboorte, g_sortdatum,";
        $sql .= " geboorteplaats, ovl_datum, ovl_sortdatum, ovl_plaats,toegevoegd, roepnaam, iid, initialen) ";
        $sql .= " VALUES ('$cIndiRelatie', '$cIndiGeslacht', '$cIndiVoornaam', '$cIndiTussenvoeg', '$cIndiAchternaam','$cIndiEersteNaam','$cIndiGebDatum', '$cIndiSortGebDatum',";
        $sql .= "'$cIndiGebPlaats','$cIndiOvlDatum', '$cIndiSortOvlDatum', '$cIndiOvlPlaats', NOW(), '$cIndiRoepnaam','$cIndiInternId','$cIndiInitialen')";
        $cResult = mysql_query($sql);
    }
    
    mysql_close($verbinding);
                
    // De huwelijksgegevens worden bijgewerkt
    
    include "include/connectie.inc";
    
    $sql = "SELECT husb,wife,plac,datec,givn,spfx,surn,iid FROM ftphp__fam JOIN ftphp__indi_name ON ftphp__fam.husb = ftphp__indi_name.iid OR ftphp__fam.wife = ftphp__indi_name.iid ";
    $cResultHuwelijk = mysql_query($sql);
    $nAantal = mysql_num_rows($cResultHuwelijk);
    if($nAantal > 0) {
        while($row = mysql_fetch_array($cResultHuwelijk)) {
            // Idnummers ophalen
            $cIndiIdMan = $row['husb'];
            $cIndiIdMan2 = substr($cIndiIdMan,3,25);
            $cIndiIdVrouw = $row['wife'];
            $cIndiIdVrouw2 = substr($cIndiIdVrouw,3,25);
            // Huwelijksgegevens ophalen
            $cIndiHuwSortDatum = "N-" . $row['datec'];
            $cIndiHuwDatum = substr($row['datec'],8,2) . "-" . substr($row['datec'],5,2) . "-" . substr($row['datec'],0,4);
            $cIndiHuwPlaats = $row['plac'];
            $cIndiNaam = $row['givn'] . " " . $row['spfx'] . " " . $row['surn'];
            
            $sql1 = "UPDATE ove_genealogie SET tr_sortdatum = '$cIndiHuwSortDatum', tr_plaats = '$cIndiHuwPlaats', relatie_datum = '$cIndiHuwDatum', partner_id = '$cIndiIdVrouw2', partner = '$cIndiNaam' WHERE iid = '$cIndiIdMan';";
            $cResultMan = mysql_query($sql1);
        
            $sql2 = "UPDATE ove_genealogie SET tr_sortdatum = '$cIndiHuwSortDatum', tr_plaats = '$cIndiHuwPlaats', relatie_datum = '$cIndiHuwDatum', partner_id = '$cIndiIdMan2', partner = '$cIndiNaam' WHERE iid = '$cIndiIdVrouw';";
            $cResultVrouw = mysql_query($sql2);
        }
    }
    
    mysql_close($verbinding);

Ger van Steenderen

16-11-2012 18:08

George van Baasbank op 15/11/2012 12:39:37 (ander topic)

Proxy Error

The proxy server received an invalid response from an upstream server.
The proxy server could not handle the request POST /gedcom2ove.php.

Reason: Error reading from remote server

Apache/2.2.16 (Debian) Server at ov.vanbaasbank.nl Port 80

Dit lijkt mij een apache fout, en geen PHP fout.
Dus het lijkt erop dat je script wel wordt uitgevoerd maar dat de response te lang uitblijft.

Stelling

PHP (via mysql, mysqli of PDO) is de client, en MySQL is de server.
Elke connectie die je maakt, elke query die je uitvoert (mysql_select_db is ook een query - USE dbname -)
betekent een client-server roundup.

Stelling 2

PHP is een hypertext processor en geen programmeertaal (waaraan je eigenlijk dit soort intensieve processen moet overlaten). Voor een programmeertaal geldt ook overigens ook gewoon de het laatste gedeelte van de eerste stelling.

Waar het op neer komt, is dat je het aantal query's moet minimaliseren.
Je krijgt nu al problemen bij 7000 records, laat staan bij 50.000 records.

George van Baasbank

16-11-2012 18:13

Zoals je kunt zien heb ik in het tweede gedeelte de code aangepast d.m.v. een JOIN. Dit werkt goed alleen heb ik deze routine moeten splitsen vanwege gegevens van man en vrouw.
Maar dit is toch niet teveel?

Ger van Steenderen

16-11-2012 19:33

Blijkbaar wel, hoeveel records staan er in ftphp_fam? Probeer het anders eerst eens met alleen 1 update, en meet eens wat voor tijd je daarmee kwijt bent.

- Ariën -

16-11-2012 19:37

Als ik die tabellen met de prefixxen (zoals: uitz_) zie, dan kan er op dit punt best wel wat genormaliseerd worden, door deze in aparte tabellen te groeperen, en te koppellen per userID. Of als het een op meer is, met een koppeltabel.

Lees ook eens: http://www.yapf.net/index.php/Database_ontwerp_101

John D

16-11-2012 19:45 gewijzigd op 16-11-2012 20:29

16 indexen op een tabel is zeer heftig voor MySQL, bij een insert of update waarbij de index betrokken is is tijdrovend. Zeer zeker bulk inserts/updates. Je performance valt of staat met een goed doordacht en ontwikkeld datamodel. Helaas ontbreekt dat bij 95% van de ontwikkelaars omdat men altijd gefocussed is op klikkerdeklik resltaat-paginaatje-voor-je-neus-leuk! Daardoor ontstaat het probleem "moet ik veel pagina's opnieuw schrijven". Kortom, begin aan een nieuwe release, werk je datamodel goed uit en alles zal veel makkelijker worden. Ga zeer zeker geen data zitten processen van tabel naar tabel en ook niet van tabel naar php om dan in php het juiste record op te pikken voor display. Zo process je bakken met onzinnige data.[edit]@Aar één op meer niet oplossen met een koppeltabel maar gewoon parent-child (1-op-n). Een koppeltabel gebruik je bij meer-op-meer relaties (n-op-m).[edit]Na nog eens lezen lijkt het toch wel zinvol om de wereldwijd gebruikte standaard over te nemen?? Nogmaals: 1x een fikse release aanpassing en dataconversie klaar!

Reageren

Inloggen om te reageren