DocNet Posted June 20, 2015 Share Posted June 20, 2015 (edited) Hi all, I am getting xml feeds from an xml feeder and transfer that feeds to my database. First i used simplexml to load all that data, but simplexml is slow for large files becaese it loads all them in memory, and i switch to xmlreader for first time... I am using both, and reader, and simplexml because of xpath. I want to get just specific id and show them as fastest possible, entire feed has about 20 000 elements, i need to get just few elements that are different then in my database... It takes me 40 seconds, just for one oddslist which contains about 25-30 elements, i need to get 600 oddlist, what is wrong? public function __call($name,$params){ $reader = new XMLReader(); $url = $this->buildUrl($name,$params); $xml = ''; $expression = 'Odds[(contains(FixtureMatch_Id,"333719"))]'; $reader = new XMLReader(); $reader->open($url); while($reader->read()){ if($reader->nodeType == XMLREADER::ELEMENT){ $node = $reader->expand(); $dom = new DomDocument(); $sxe = simplexml_import_dom($dom->importNode($node,true)); $xml = $sxe->xpath($expression); $reader->read(); var_dump($xml); }} return $xml; } This is how xml looks like (XMLSOCCER.COM->OddsList->Odds): array (size=37) 0 => object(SimpleXMLElement)[14] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Bet-At-Home' (length=11) public 'UpdatedDate' => string '2015-05-23T14:58:39.55' (length=22) public 'Type' => string 'Over/Under 2.5' (length=14) public 'HomeOdds' => string '1.4' (length=3) public 'AwayOdds' => string '2.73' (length=4) 1 => object(SimpleXMLElement)[12] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Interwetten' (length=11) public 'UpdatedDate' => string '2015-05-23T14:58:39.677' (length=23) public 'Type' => string 'Over/Under 2.5' (length=14) public 'HomeOdds' => string '1.3' (length=3) public 'AwayOdds' => string '3' (length=1) 2 => object(SimpleXMLElement)[16] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Bet365' (length=6) public 'UpdatedDate' => string '2015-05-23T14:58:40.677' (length=23) public 'Type' => string 'Handicap' (length= public 'HomeOdds' => string '2.15' (length=4) public 'DrawOdds' => string '4' (length=1) public 'AwayOdds' => string '2.5' (length=3) public 'Handicap' => string '1' (length=1) 3 => object(SimpleXMLElement)[17] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Bet-At-Home' (length=11) public 'UpdatedDate' => string '2015-05-23T14:58:40.77' (length=22) public 'Type' => string 'Handicap' (length= public 'HomeOdds' => string '1.87' (length=4) public 'DrawOdds' => string '3.97' (length=4) public 'AwayOdds' => string '2.67' (length=4) public 'Handicap' => string '1' (length=1) 4 => object(SimpleXMLElement)[18] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Mybet' (length=5) public 'UpdatedDate' => string '2015-05-23T14:58:40.863' (length=23) public 'Type' => string 'Handicap' (length= public 'HomeOdds' => string '2.15' (length=4) public 'DrawOdds' => string '3.6' (length=3) public 'AwayOdds' => string '2.75' (length=4) public 'Handicap' => string '1' (length=1) 5 => object(SimpleXMLElement)[19] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Unibet' (length=6) public 'UpdatedDate' => string '2015-05-23T14:58:39.847' (length=23) public 'Type' => string 'Double Chance' (length=13) public 'HomeOdds' => string '2.07' (length=4) public 'DrawOdds' => string '1.21' (length=4) public 'AwayOdds' => string '1.22' (length=4) 6 => object(SimpleXMLElement)[20] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string '888' (length=3) public 'UpdatedDate' => string '2015-05-23T14:58:39.973' (length=23) public 'Type' => string 'Double Chance' (length=13) public 'HomeOdds' => string '2.07' (length=4) public 'DrawOdds' => string '1.21' (length=4) public 'AwayOdds' => string '1.22' (length=4) 7 => object(SimpleXMLElement)[21] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Expekt' (length=6) public 'UpdatedDate' => string '2015-05-23T13:28:25.28' (length=22) public 'Type' => string 'Double Chance' (length=13) public 'HomeOdds' => string '2' (length=1) public 'DrawOdds' => string '1.2' (length=3) public 'AwayOdds' => string '1.22' (length=4) 8 => object(SimpleXMLElement)[22] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Mybet' (length=5) public 'UpdatedDate' => string '2015-05-23T14:58:40.52' (length=22) public 'Type' => string 'Double Chance' (length=13) public 'HomeOdds' => string '2.15' (length=4) public 'DrawOdds' => string '1.18' (length=4) public 'AwayOdds' => string '1.27' (length=4) 9 => object(SimpleXMLElement)[23] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'BetClic' (length=7) public 'UpdatedDate' => string '2015-05-23T13:28:26.03' (length=22) public 'Type' => string 'Double Chance' (length=13) public 'HomeOdds' => string '2' (length=1) public 'DrawOdds' => string '1.2' (length=3) public 'AwayOdds' => string '1.22' (length=4) 10 => object(SimpleXMLElement)[24] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Bet-At-Home' (length=11) public 'UpdatedDate' => string '2015-05-23T14:58:40.05' (length=22) public 'Type' => string 'Double Chance' (length=13) public 'HomeOdds' => string '1.87' (length=4) public 'DrawOdds' => string '1.21' (length=4) public 'AwayOdds' => string '1.18' (length=4) 11 => object(SimpleXMLElement)[25] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Betfred' (length=7) public 'UpdatedDate' => string '2015-05-23T13:28:25.5' (length=21) public 'Type' => string 'Double Chance' (length=13) public 'HomeOdds' => string '2.1' (length=3) public 'DrawOdds' => string '1.2' (length=3) public 'AwayOdds' => string '1.22' (length=4) 12 => object(SimpleXMLElement)[26] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Betfred' (length=7) public 'UpdatedDate' => string '2015-05-23T13:28:26.64' (length=22) public 'Type' => string 'Asian Handicap' (length=14) public 'HomeOdds' => string '3.4' (length=3) public 'AwayOdds' => string '1.33' (length=4) public 'Handicap' => string '0' (length=1) 13 => object(SimpleXMLElement)[27] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'WilliamHill' (length=11) public 'UpdatedDate' => string '2015-05-23T05:57:54.43' (length=22) public 'Type' => string 'Asian Handicap' (length=14) public 'HomeOdds' => string '3.25' (length=4) public 'AwayOdds' => string '1.33' (length=4) public 'Handicap' => string '0' (length=1) 14 => object(SimpleXMLElement)[28] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Pinnacle' (length= public 'UpdatedDate' => string '2015-05-23T14:58:40.957' (length=23) public 'Type' => string 'Asian Handicap' (length=14) public 'HomeOdds' => string '1.91' (length=4) public 'AwayOdds' => string '2.03' (length=4) public 'Handicap' => string '0.75' (length=4) 15 => object(SimpleXMLElement)[29] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string '188Bet' (length=6) public 'UpdatedDate' => string '2015-05-23T14:58:41.05' (length=22) public 'Type' => string 'Asian Handicap' (length=14) public 'HomeOdds' => string '1.97' (length=4) public 'AwayOdds' => string '1.98' (length=4) public 'Handicap' => string '0.75' (length=4) 16 => object(SimpleXMLElement)[30] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Betway' (length=6) public 'UpdatedDate' => string '2015-05-23T13:28:22.69' (length=22) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.25' (length=4) public 'DrawOdds' => string '4' (length=1) public 'AwayOdds' => string '1.7' (length=3) 17 => object(SimpleXMLElement)[31] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Ladbrokes' (length=9) public 'UpdatedDate' => string '2015-05-23T14:58:37.857' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.5' (length=3) public 'DrawOdds' => string '4' (length=1) public 'AwayOdds' => string '1.73' (length=4) 18 => object(SimpleXMLElement)[32] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Skybet' (length=6) public 'UpdatedDate' => string '2015-05-23T13:28:24.157' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.33' (length=4) public 'DrawOdds' => string '3.8' (length=3) public 'AwayOdds' => string '1.67' (length=4) 19 => object(SimpleXMLElement)[33] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Unibet' (length=6) public 'UpdatedDate' => string '2015-05-23T14:58:37.747' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.2' (length=3) public 'DrawOdds' => string '4.1' (length=3) public 'AwayOdds' => string '1.72' (length=4) 20 => object(SimpleXMLElement)[34] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string '888' (length=3) public 'UpdatedDate' => string '2015-05-23T14:58:37.983' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.2' (length=3) public 'DrawOdds' => string '4.1' (length=3) public 'AwayOdds' => string '1.72' (length=4) 21 => object(SimpleXMLElement)[35] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'BWin' (length=4) public 'UpdatedDate' => string '2015-05-23T14:58:38.743' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.4' (length=3) public 'DrawOdds' => string '3.8' (length=3) public 'AwayOdds' => string '1.72' (length=4) 22 => object(SimpleXMLElement)[36] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Expekt' (length=6) public 'UpdatedDate' => string '2015-05-23T13:28:22.253' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.1' (length=3) public 'DrawOdds' => string '3.9' (length=3) public 'AwayOdds' => string '1.75' (length=4) 23 => object(SimpleXMLElement)[37] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Betfred' (length=7) public 'UpdatedDate' => string '2015-05-23T13:28:22.563' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.33' (length=4) public 'DrawOdds' => string '4.2' (length=3) public 'AwayOdds' => string '1.75' (length=4) 24 => object(SimpleXMLElement)[38] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Mybet' (length=5) public 'UpdatedDate' => string '2015-05-23T14:58:38.853' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.4' (length=3) public 'DrawOdds' => string '4.2' (length=3) public 'AwayOdds' => string '1.7' (length=3) 25 => object(SimpleXMLElement)[39] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'BetClic' (length=7) public 'UpdatedDate' => string '2015-05-23T13:28:24.03' (length=22) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.15' (length=4) public 'DrawOdds' => string '3.9' (length=3) public 'AwayOdds' => string '1.75' (length=4) 26 => object(SimpleXMLElement)[40] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'WilliamHill' (length=11) public 'UpdatedDate' => string '2015-05-23T05:57:50.907' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.5' (length=3) public 'DrawOdds' => string '3.75' (length=4) public 'AwayOdds' => string '1.75' (length=4) 27 => object(SimpleXMLElement)[41] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Bet365' (length=6) public 'UpdatedDate' => string '2015-05-23T14:58:37.56' (length=22) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.2' (length=3) public 'DrawOdds' => string '4.2' (length=3) public 'AwayOdds' => string '1.73' (length=4) 28 => object(SimpleXMLElement)[42] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Pinnacle' (length= public 'UpdatedDate' => string '2015-05-23T14:58:37.653' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.2' (length=3) public 'DrawOdds' => string '4.27' (length=4) public 'AwayOdds' => string '1.83' (length=4) 29 => object(SimpleXMLElement)[43] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Bet-At-Home' (length=11) public 'UpdatedDate' => string '2015-05-23T14:58:38.09' (length=22) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '3.83' (length=4) public 'DrawOdds' => string '4.06' (length=4) public 'AwayOdds' => string '1.78' (length=4) 30 => object(SimpleXMLElement)[44] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Interwetten' (length=11) public 'UpdatedDate' => string '2015-05-23T14:58:38.357' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.2' (length=3) public 'DrawOdds' => string '3.7' (length=3) public 'AwayOdds' => string '1.75' (length=4) 31 => object(SimpleXMLElement)[45] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string '5Dimes' (length=6) public 'UpdatedDate' => string '2015-05-23T14:58:38.477' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.4' (length=3) public 'DrawOdds' => string '4.21' (length=4) public 'AwayOdds' => string '1.81' (length=4) 32 => object(SimpleXMLElement)[46] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Doxxbet' (length=7) public 'UpdatedDate' => string '2015-05-23T14:58:38.587' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.5' (length=3) public 'DrawOdds' => string '3.9' (length=3) public 'AwayOdds' => string '1.66' (length=4) 33 => object(SimpleXMLElement)[47] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string '188Bet' (length=6) public 'UpdatedDate' => string '2015-05-23T14:58:38.977' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.15' (length=4) public 'DrawOdds' => string '4.3' (length=3) public 'AwayOdds' => string '1.76' (length=4) 34 => object(SimpleXMLElement)[48] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Coral' (length=5) public 'UpdatedDate' => string '2015-05-23T14:58:39.12' (length=22) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.33' (length=4) public 'DrawOdds' => string '3.75' (length=4) public 'AwayOdds' => string '1.75' (length=4) 35 => object(SimpleXMLElement)[49] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string '10Bet' (length=5) public 'UpdatedDate' => string '2015-05-23T14:58:39.243' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.2' (length=3) public 'DrawOdds' => string '4.05' (length=4) public 'AwayOdds' => string '1.77' (length=4) 36 => object(SimpleXMLElement)[50] public 'FixtureMatch_Id' => string '333719' (length=6) public 'Bookmaker' => string 'Sportingbet' (length=11) public 'UpdatedDate' => string '2015-05-23T14:58:39.443' (length=23) public 'Type' => string '1X2' (length=3) public 'HomeOdds' => string '4.2' (length=3) public 'DrawOdds' => string '4.2' (length=3) public 'AwayOdds' => string '1.8' (length=3) array (size=0) Help me please, i tried google, stack, dic but no one gives me real answer. Edited June 20, 2015 by DocNet Quote Link to comment https://forums.phpfreaks.com/topic/296934-how-to-get-part-of-xml-faster-using-xmlreader/ Share on other sites More sharing options...
mac_gyver Posted June 20, 2015 Share Posted June 20, 2015 are you using the XMLSOCCER.COM api's parameters to get just the data you want, for example GetAllOddsByFixtureMatchId, or are you getting all of the data and trying to parse it yourself? Quote Link to comment https://forums.phpfreaks.com/topic/296934-how-to-get-part-of-xml-faster-using-xmlreader/#findComment-1514462 Share on other sites More sharing options...
DocNet Posted June 20, 2015 Author Share Posted June 20, 2015 are you using the XMLSOCCER.COM api's parameters to get just the data you want, for example GetAllOddsByFixtureMatchId, or are you getting all of the data and trying to parse it yourself? Yes, i am using GetAllOddsByFixtureMatchId, from this source http://www.xmlsoccer.com/FootballData.asmx?op=GetOddsByFixtureMatchId, as you can see it takes two parameters, api key which is my payed api key, and FixID, before inserting odds, i inserted fixtures (in range of -30 +10 days from now), so to get odds i must to list all fix-ids that i have already in db, and parse it to GetAllOddsByFixtureMatchId, which returns me all odds for that id. Quote Link to comment https://forums.phpfreaks.com/topic/296934-how-to-get-part-of-xml-faster-using-xmlreader/#findComment-1514470 Share on other sites More sharing options...
DocNet Posted June 20, 2015 Author Share Posted June 20, 2015 Why code in ELSE part of function takes me more time than this in if, in else part i just check if there is no same updated dates than update them. function sendOddsToDb(){ try{ $this->conn->connect(); $this->PDO->beginTransaction(); $numOdds = $this->countRows('odds'); $stmt = $this->PDO->prepare($this->insOdds); $odds=''; if($numOdds==0){ //IF IS EMOTY ODDS TABLE $fixId = $this->PDO->query("select Id from fixtures where Id>0 and Status!='Finished'"); $fixId->setFetchMode(PDO::FETCH_ASSOC); while($id = $fixId->fetch()){ //GET ALL ODDS FEEDS $odds=$this->soccer->GetAllOddsByFixtureMatchId( array("fixtureMatch_Id"=>$id['Id']))->OddsList; foreach($odds->Odds as $key=>$value){ //STORE IT TO TABLE $stmt->bindParam(':fixturematchid',$value->FixtureMatch_Id); $stmt->bindParam(':bookmaker',$value->Bookmaker); $stmt->bindParam(':updatedDate',$value->UpdatedDate); $stmt->bindParam(':type',$value->Type); $stmt->bindParam(':homeodds',$value->HomeOdds); $stmt->bindParam(':drawodds',$value->DrawOdds); $stmt->bindParam(':awayodds',$value->AwayOdds); $stmt->bindParam(':handicap',$value->Handicap); $stmt->execute(); //EXEC } } } else{ //IF IS NOT EMPTY ODDS TABLE $updDate = $this->PDO->prepare("select FixtureMatch_Id,UpdatedDate from odds where FixtureMatch_Id>0"); $updDate->execute(); $this->soccer->setExpression($expresion); while($row = $updDate->fetch()){//GET FEEDS WHICH UPDATED DATE IS NOT IN DB $updDate->setFetchMode(PDO::FETCH_ASSOC); $odds = $this->soccer->GetAllOddsByFixtureMatchId( array("fixtureMatch_Id"=>$row['FixtureMatch_Id']))->xpath('Odds[not((contains(UpdatedDate,"'.$row['UpdatedDate'].'")))]'); foreach($odds as $key=>$value){ //STORE IT TO TABLE $stmt->bindParam(':fixturematchid',$value->FixtureMatch_Id); $stmt->bindParam(':bookmaker',$value->Bookmaker); $stmt->bindParam(':updatedDate',$value->UpdatedDate); $stmt->bindParam(':type',$value->Type); $stmt->bindParam(':homeodds',$value->HomeOdds); $stmt->bindParam(':drawodds',$value->DrawOdds); $stmt->bindParam(':awayodds',$value->AwayOdds); $stmt->bindParam(':handicap',$value->Handicap); $stmt->execute(); //EXEC } } } if(!empty($odds)){ //var_dump($odds); }else{ echo 'EMPTY'; } $this->PDO->commit(); //SAVE $this->conn->close(); //CLOSE } catch(XMLSoccerException $e){ echo "XMLSoccerException: ".$e->getMessage(); } catch(PDOException $e){ echo "PDOException: ".$e->getMessage(); $this->PDO->rollback(); } } Quote Link to comment https://forums.phpfreaks.com/topic/296934-how-to-get-part-of-xml-faster-using-xmlreader/#findComment-1514477 Share on other sites More sharing options...
mac_gyver Posted June 21, 2015 Share Posted June 21, 2015 (edited) here's the likely problem that's causing the else {} block of code to take (much) longer than the initial storing of the data in the if(){} block of code. you are storing multiple rows for each FixtureMatch_Id, one for each bookmaker and, i would guess, type combination. so, for any FixtureMatch_Id, you have 20+ rows. when you are updating the information, in the else {} block, your select query is just getting the FixtureMatch_Id and UpdatedDate, and the rows in the result set will be in no particular order. you are getting all the rows from your table, 20+ rows for each FixtureMatch_Id, times the number of different FixtureMatch_Id values. as you are looping over the database rows, you are hitting the XMLSOCCER.COM api for each row. for any particular FixtureMatch_Id value, you are retrieving the same data from the api 20+ times, repeated for each different FixtureMatch_Id value. for what you are doing (as far as i can tell), if you set up the correct unique composite index on your database table, you can use the code in your if(){} block, without the condition around it, with a REPLACE ... query, to INSERT new data and UPDATE/REPLACE existing data. the unique composite index would be over the - fixturematch_id, bookmaker, and type columns. note: this method will REPLACE data in the table that has the same updateddate as the 'new' data, wasting some database processing time. if you want to avoid this extra work on the database side, you would have to select the fixturematch_id, bookmaker, updateddate, and type from the table first, and detect when those four combined values are different from the data being put into the query. the most efficient code for doing this would be to concatenate those four values, with a unique separator character ( a | for example) and store the values in an array. you can then concatenate the data values using that same separator and use an in_array() statement to find if the composite value is already in the database data. if it's not found, run the query. if it is found, don't run the query. lastly, the bindparam() statements don't belong inside the loop. the only thing that goes inside the loop are statements that populate the variables that were bound and the ->execute() statement. edit: if you use an INSERT ... ON DUPLICATE KEY UPDATE ... query, instead of the REPLACE query i mentioned, it may (untested) be more efficient and you won't need to first check if the updateddate has changed, assuming that the UPDATE part of this type of query operates the same as any UPDATE query, which doesn't actually perform the UPDATE (writing the data) if the new data values are the same as the existing data in the record. Edited June 21, 2015 by mac_gyver Quote Link to comment https://forums.phpfreaks.com/topic/296934-how-to-get-part-of-xml-faster-using-xmlreader/#findComment-1514488 Share on other sites More sharing options...
DocNet Posted June 21, 2015 Author Share Posted June 21, 2015 here's the likely problem that's causing the else {} block of code to take (much) longer than the initial storing of the data in the if(){} block of code. you are storing multiple rows for each FixtureMatch_Id, one for each bookmaker and, i would guess, type combination. so, for any FixtureMatch_Id, you have 20+ rows. when you are updating the information, in the else {} block, your select query is just getting the FixtureMatch_Id and UpdatedDate, and the rows in the result set will be in no particular order. you are getting all the rows from your table, 20+ rows for each FixtureMatch_Id, times the number of different FixtureMatch_Id values. as you are looping over the database rows, you are hitting the XMLSOCCER.COM api for each row. for any particular FixtureMatch_Id value, you are retrieving the same data from the api 20+ times, repeated for each different FixtureMatch_Id value. for what you are doing (as far as i can tell), if you set up the correct unique composite index on your database table, you can use the code in your if(){} block, without the condition around it, with a REPLACE ... query, to INSERT new data and UPDATE/REPLACE existing data. the unique composite index would be over the - fixturematch_id, bookmaker, and type columns. note: this method will REPLACE data in the table that has the same updateddate as the 'new' data, wasting some database processing time. if you want to avoid this extra work on the database side, you would have to select the fixturematch_id, bookmaker, updateddate, and type from the table first, and detect when those four combined values are different from the data being put into the query. the most efficient code for doing this would be to concatenate those four values, with a unique separator character ( a | for example) and store the values in an array. you can then concatenate the data values using that same separator and use an in_array() statement to find if the composite value is already in the database data. if it's not found, run the query. if it is found, don't run the query. lastly, the bindparam() statements don't belong inside the loop. the only thing that goes inside the loop are statements that populate the variables that were bound and the ->execute() statement. edit: if you use an INSERT ... ON DUPLICATE KEY UPDATE ... query, instead of the REPLACE query i mentioned, it may (untested) be more efficient and you won't need to first check if the updateddate has changed, assuming that the UPDATE part of this type of query operates the same as any UPDATE query, which doesn't actually perform the UPDATE (writing the data) if the new data values are the same as the existing data in the record. Right, this explains why it wait longer than IF part of code, but still this is not solution of problem. XMLReader is pull parser, so he does not load full feeda as simplexml, just part that i set in xpath, but this is not true for me, it loads whole feed and returns me xpath when he finish whole feed read. I want to get this search from xpath faster.. Quote Link to comment https://forums.phpfreaks.com/topic/296934-how-to-get-part-of-xml-faster-using-xmlreader/#findComment-1514495 Share on other sites More sharing options...
DocNet Posted June 21, 2015 Author Share Posted June 21, 2015 Please check a way how i call xpath, i think that is problem. $reader = new XMLReader(); $reader->open($url); $dom = new DomDocument(); while($reader->read() && $reader->nodeType == XMLREADER::ELEMENT){ $xml = simplexml_import_dom($dom->importNode($reader->expand(),true)); } and call is this: $odds = $this->soccer->GetAllOddsByFixtureMatchId( array("fixtureMatch_Id"=>$id['Id']))->xpath('Odds[(contains(Bookmaker,"Interwetten"))]'); Quote Link to comment https://forums.phpfreaks.com/topic/296934-how-to-get-part-of-xml-faster-using-xmlreader/#findComment-1514496 Share on other sites More sharing options...
DocNet Posted June 21, 2015 Author Share Posted June 21, 2015 (edited) This is asXML $xml <XMLSOCCER.COM> <OddsList> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Bet-At-Home</Bookmaker> <UpdatedDate>2015-06-20T19:42:32.943</UpdatedDate> <Type>Over/Under 2.5</Type> <HomeOdds>2.22</HomeOdds> <AwayOdds>1.58</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Interwetten</Bookmaker> <UpdatedDate>2015-06-20T19:42:33.113</UpdatedDate> <Type>Over/Under 2.5</Type> <HomeOdds>1.85</HomeOdds> <AwayOdds>1.75</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>188Bet</Bookmaker> <UpdatedDate>2015-06-20T19:42:33.27</UpdatedDate> <Type>Over/Under 2.5</Type> <HomeOdds>2.11</HomeOdds> <AwayOdds>1.8</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Pinnacle</Bookmaker> <UpdatedDate>2015-06-20T19:42:32.803</UpdatedDate> <Type>Over/Under 2.5</Type> <HomeOdds>2.33</HomeOdds> <AwayOdds>1.65</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Bet365</Bookmaker> <UpdatedDate>2015-06-20T19:42:34.263</UpdatedDate> <Type>Handicap</Type> <HomeOdds>3.1</HomeOdds> <DrawOdds>3.5</DrawOdds> <AwayOdds>2</AwayOdds> <Handicap>-1</Handicap> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Bet-At-Home</Bookmaker> <UpdatedDate>2015-06-20T19:42:34.373</UpdatedDate> <Type>Handicap</Type> <HomeOdds>1.17</HomeOdds> <DrawOdds>5.41</DrawOdds> <AwayOdds>10.32</AwayOdds> <Handicap>1</Handicap> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Mybet</Bookmaker> <UpdatedDate>2015-06-20T19:42:34.5</UpdatedDate> <Type>Handicap</Type> <HomeOdds>3.35</HomeOdds> <DrawOdds>3.5</DrawOdds> <AwayOdds>1.85</AwayOdds> <Handicap>-1</Handicap> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Expekt</Bookmaker> <UpdatedDate>2015-06-20T19:42:33.41</UpdatedDate> <Type>Double Chance</Type> <HomeOdds>1.15</HomeOdds> <DrawOdds>1.85</DrawOdds> <AwayOdds>1.3</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Bet-At-Home</Bookmaker> <UpdatedDate>2015-06-20T19:42:33.787</UpdatedDate> <Type>Double Chance</Type> <HomeOdds>1.17</HomeOdds> <DrawOdds>1.8</DrawOdds> <AwayOdds>1.25</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Mybet</Bookmaker> <UpdatedDate>2015-06-20T19:42:34.037</UpdatedDate> <Type>Double Chance</Type> <HomeOdds>1.15</HomeOdds> <DrawOdds>1.85</DrawOdds> <AwayOdds>1.25</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>BetClic</Bookmaker> <UpdatedDate>2015-06-20T19:42:34.16</UpdatedDate> <Type>Double Chance</Type> <HomeOdds>1.15</HomeOdds> <DrawOdds>1.85</DrawOdds> <AwayOdds>1.3</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Unibet</Bookmaker> <UpdatedDate>2015-06-20T19:42:33.52</UpdatedDate> <Type>Double Chance</Type> <HomeOdds>1.22</HomeOdds> <DrawOdds>1.84</DrawOdds> <AwayOdds>1.29</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>888</Bookmaker> <UpdatedDate>2015-06-20T19:42:33.647</UpdatedDate> <Type>Double Chance</Type> <HomeOdds>1.22</HomeOdds> <DrawOdds>1.84</DrawOdds> <AwayOdds>1.29</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Pinnacle</Bookmaker> <UpdatedDate>2015-06-20T19:42:34.733</UpdatedDate> <Type>Asian Handicap</Type> <HomeOdds>1.92</HomeOdds> <AwayOdds>2</AwayOdds> <Handicap>-0.5</Handicap> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>WilliamHill</Bookmaker> <UpdatedDate>2015-06-20T19:42:34.623</UpdatedDate> <Type>Asian Handicap</Type> <HomeOdds>1.36</HomeOdds> <AwayOdds>3</AwayOdds> <Handicap>0</Handicap> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Betfred</Bookmaker> <UpdatedDate>2015-06-20T19:42:34.86</UpdatedDate> <Type>Asian Handicap</Type> <HomeOdds>1.36</HomeOdds> <AwayOdds>3</AwayOdds> <Handicap>0</Handicap> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>188Bet</Bookmaker> <UpdatedDate>2015-06-20T19:42:34.967</UpdatedDate> <Type>Asian Handicap</Type> <HomeOdds>1.89</HomeOdds> <AwayOdds>2.03</AwayOdds> <Handicap>-0.5</Handicap> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Doxxbet</Bookmaker> <UpdatedDate>2015-06-20T19:42:31.63</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.85</HomeOdds> <DrawOdds>3.3</DrawOdds> <AwayOdds>3.92</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>BetSafe</Bookmaker> <UpdatedDate>2015-06-20T19:42:30.74</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.84</HomeOdds> <DrawOdds>3.25</DrawOdds> <AwayOdds>4.25</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Pinnacle</Bookmaker> <UpdatedDate>2015-06-20T19:42:30.257</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.92</HomeOdds> <DrawOdds>3.4</DrawOdds> <AwayOdds>4.8</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>888</Bookmaker> <UpdatedDate>2015-06-20T19:42:31.02</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.9</HomeOdds> <DrawOdds>3.4</DrawOdds> <AwayOdds>4</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Skybet</Bookmaker> <UpdatedDate>2015-06-20T19:42:32.177</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.91</HomeOdds> <DrawOdds>3.4</DrawOdds> <AwayOdds>4</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>WilliamHill</Bookmaker> <UpdatedDate>2015-06-20T19:42:29.91</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.91</HomeOdds> <DrawOdds>3.3</DrawOdds> <AwayOdds>4.33</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Bet365</Bookmaker> <UpdatedDate>2015-06-20T19:42:30.02</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.83</HomeOdds> <DrawOdds>3.4</DrawOdds> <AwayOdds>4.5</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Expekt</Bookmaker> <UpdatedDate>2015-06-20T19:42:30.13</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.82</HomeOdds> <DrawOdds>3.1</DrawOdds> <AwayOdds>4.6</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Betfred</Bookmaker> <UpdatedDate>2015-06-20T19:42:30.473</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.83</HomeOdds> <DrawOdds>3.3</DrawOdds> <AwayOdds>4.33</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Bet-At-Home</Bookmaker> <UpdatedDate>2015-06-20T19:42:31.13</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.82</HomeOdds> <DrawOdds>3.18</DrawOdds> <AwayOdds>4.25</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Interwetten</Bookmaker> <UpdatedDate>2015-06-20T19:42:31.397</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.8</HomeOdds> <DrawOdds>3.4</DrawOdds> <AwayOdds>4</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>BWin</Bookmaker> <UpdatedDate>2015-06-20T19:42:31.77</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.85</HomeOdds> <DrawOdds>3.3</DrawOdds> <AwayOdds>3.9</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Mybet</Bookmaker> <UpdatedDate>2015-06-20T19:42:31.943</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.85</HomeOdds> <DrawOdds>3.3</DrawOdds> <AwayOdds>4</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>BetClic</Bookmaker> <UpdatedDate>2015-06-20T19:42:32.067</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.82</HomeOdds> <DrawOdds>3.1</DrawOdds> <AwayOdds>4.6</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>188Bet</Bookmaker> <UpdatedDate>2015-06-20T19:42:32.27</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.86</HomeOdds> <DrawOdds>3.15</DrawOdds> <AwayOdds>4.05</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>10Bet</Bookmaker> <UpdatedDate>2015-06-20T19:42:32.473</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.83</HomeOdds> <DrawOdds>3.2</DrawOdds> <AwayOdds>4.4</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Sportingbet</Bookmaker> <UpdatedDate>2015-06-20T19:42:32.707</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.83</HomeOdds> <DrawOdds>3.25</DrawOdds> <AwayOdds>4.33</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>NordicBet</Bookmaker> <UpdatedDate>2015-06-20T19:42:30.63</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.84</HomeOdds> <DrawOdds>3.25</DrawOdds> <AwayOdds>4.25</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Ladbrokes</Bookmaker> <UpdatedDate>2015-06-20T19:42:30.85</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.85</HomeOdds> <DrawOdds>3.1</DrawOdds> <AwayOdds>4.25</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>Unibet</Bookmaker> <UpdatedDate>2015-06-20T19:42:30.38</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.9</HomeOdds> <DrawOdds>3.4</DrawOdds> <AwayOdds>4</AwayOdds> </Odds> <Odds> <FixtureMatch_Id>346076</FixtureMatch_Id> <Bookmaker>5Dimes</Bookmaker> <UpdatedDate>2015-06-20T19:42:31.507</UpdatedDate> <Type>1X2</Type> <HomeOdds>1.92</HomeOdds> <DrawOdds>3.4</DrawOdds> <AwayOdds>4.8</AwayOdds> </Odds> </OddsList> <AccountInformation>Data requested at 6/21/2015 9:36:23 AM from ip, Username: xxx. Your current supscription runs out on 7/7/2015 9:21:10 PM.</AccountInformation> </XMLSOCCER.COM><XMLSOCCER.COM> <OddsList/> <AccountInformation>Data requested at 6/21/2015 9:36:23 AM from ip, Username: xxx. Your current supscription runs out on 7/7/2015 9:21:10 PM.</AccountInformation> </XMLSOCCER.COM><XMLSOCCER.COM> <OddsList/> <AccountInformation>Data requested at 6/21/2015 9:36:24 AM from ip, Username: xxx. Your current supscription runs out on 7/7/2015 9:21:10 PM.</AccountInformation> </XMLSOCCER.COM><XMLSOCCER.COM> <OddsList/> <AccountInformation>Data requested at 6/21/2015 9:36:24 AM from ip, Username: xxx. Your current supscription runs out on 7/7/2015 9:21:10 PM.</AccountInformation> </XMLSOCCER.COM> from this code: public function __call($name,$params){ $url = $this->buildUrl($name,$params); $xml = ''; $reader = new XMLReader(); $reader->open($url); $dom = new DomDocument(); $pathXML = ''; while($reader->read() && $reader->nodeType == XMLREADER::ELEMENT){ $xml = simplexml_import_dom($dom->importNode($reader->expand(),true)); /*if($this->getExpression()){ $xml->xpath($this->getExpression()); }*/ } $xml->xpath('/XMLSOCCER.COM/OddsList/Odds'); echo $xml->asXML(); if(strstr($xml[0],"To avoid misuse of the service")){ switch($name){ case "GetLiveScore": case "GetLiveScoreByLeague": case "GetOddsByFixtureMatchID": case "GetHistoricMatchesByLeagueAndSeason": case "GetAllTeams": case "GetAllTeamsByLeagueAndSeason": throw new XMLSoccerException($xml[0],constant("self::TIMEOUT_".$name)); default: throw new XMLSoccerException($xml[0],self::TIMEOUT_Others); } } return $xml; } As you can see, xpath doesnt work here :S Edited June 21, 2015 by DocNet Quote Link to comment https://forums.phpfreaks.com/topic/296934-how-to-get-part-of-xml-faster-using-xmlreader/#findComment-1514498 Share on other sites More sharing options...
mac_gyver Posted June 21, 2015 Share Posted June 21, 2015 (edited) xpath doesn't work here you shouldn't be trying to put any search/filter functionality into the low level interface/driver. the purpose of that code is to serve as an interface between the application and the api. that's all it should do. it's your application code that knows what the data means and is where any data processing belongs. xpath is used to select nodes from an XML document. you don't need to find the nodes containing the data, you already have them. for bulk data processing, looking for each individual data record in the xml is not efficient. just loop over the bulk xml data and determine for each record if you need to insert a new database row for it (it doesn't exist at all) or update/replace the values in an existing database row. Edited June 21, 2015 by mac_gyver Quote Link to comment https://forums.phpfreaks.com/topic/296934-how-to-get-part-of-xml-faster-using-xmlreader/#findComment-1514513 Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.