PHP 5, XSL transformations of big files
Meglio
I'm going to make in auto some tasks that I'm doing manually every
day. I'm uploading big XML file (~20mb) and then I'm using XSLT schema
to transform it to MySQL queries file.
I have used 3rd tools to process XSLT and it takes few seconds to
process my big XML file.
But now I'm moving to PHP so I'm trying to use XSL PHP extension to
apply my XSL file. But... it takes minutes... I allowed 10 minutes
( set_time_limit(600) ) and it still crashes and says that time
excited.
So please somebody help me to find a way out of the impasse. I can't
move forward now because of slow XSL processing.
The code I'm using is:
*******
// Load the XML source
$xml = new DOMDocument;
$xml->load('extracted/marketplace_feed_v1.xml');
$xsl = new DOMDocument;
$xsl->load('cb_marketplace_feed_compact.xsl');
// Configure the transformer
$proc = new XSLTProcessor;
$proc->importStyleSheet($xsl); // attach the xsl rules
$sqlCommands = $proc->transformToXML($xml);
if ($sqlCommands == FALSE)
{
echo "XSLT transformation failed";
exit;
}
if (file_put_contents('cbfeed.sql', $sqlCommands) == FALSE);
{
echo 'Failed to save XSLT transformation result to the file
"cbfeed.sql"';
exit;
}
*******
You can upload files used in my script here:
http://megliosoft.org/meglio/cbfeed/marketplace_feed_v1.xml
http://megliosoft.org/meglio/cbfeed/cb_marketplace_feed_compact.xsl
Any information may help.
Thanks,
Anton
Joe Fawcett
"Meglio" <x.me...@gmail.com> wrote in message
news:17d78148-5bff-4483...@26g2000hsk.googlegroups.com...
Perhaps move to a different programming model? You could use a streaming
interface such as SAX and create the SQL statements as you go for example. I
don't really know what capabilities PHP has in this regard.
--
Joe Fawcett (MVP - XML)
http://joe.fawcett.name
Martin Honnen
> Perhaps move to a different programming model? You could use a streaming
> interface such as SAX and create the SQL statements as you go for
> example. I don't really know what capabilities PHP has in this regard.
PHP 5 has an XmlReader API
http://www.php.net/manual/en/book.xmlreader.php and an events based API
similar to SAX http://www.php.net/manual/en/book.xml.php
--
Martin Honnen --- MVP XML
http://JavaScript.FAQTs.com/
Neil Smith [MVP Digital Media]
client upload time required to get the content onto the server - PHP5
uses Libxml for processing and XSL work, which is very fast.
You should check not only the max_execution_time, but also the php.ini
setting for max_input_time, which refers to the upload duration :
http://uk.php.net/manual/en/info.configuration.php#ini.max-input-time
Those settings are distinct, separate timeouts.
Note that if you're using the XSL extension then you're working with
DOM which creates the structure in memory - make sure you have enough
memory allocated to PHP to process the nodes in a 20MB input document.
Another approach would be just to accept the file upload, flag the
file content as a DB record "to process" and do the processing out of
band as a scheduled task / cron job on a beefier backend server.
HTH
Cheers - Neil
On Sat, 3 May 2008 02:57:46 -0700 (PDT), Meglio <x.me...@gmail.com>
wrote:
------------------------------------------------
Digital Media MVP : 2004-2008
http://mvp.support.microsoft.com/mvpfaqs
Meglio
wrote:
> Time exceeded when uploading file content is usually related to the
> client upload time required to get the content onto the server - PHP5
> uses Libxml for processing and XSL work, which is very fast.
>
> You should check not only the max_execution_time, but also the php.ini
> setting for max_input_time, which refers to the upload duration :http://uk.php.net/manual/en/info.configuration.php#ini.max-input-time
>
> Those settings are distinct, separate timeouts.
>
> Note that if you're using the XSL extension then you're working with
> DOM which creates the structure in memory - make sure you have enough
> memory allocated to PHP to process the nodes in a 20MB input document.
>
> Another approach would be just to accept the file upload, flag the
> file content as a DB record "to process" and do the processing out of
> band as a scheduled task / cron job on a beefier backend server.
>
> HTH
> Cheers - Neil
>
No problem with time execution, no problem with memory limits. The
problem is script execution time - it do not completed even in 10
minutes.
Anton
Joe Fawcett
"Meglio" <x.me...@gmail.com> wrote in message
news:8d76aeb6-9196-45c5...@m73g2000hsh.googlegroups.com...
You had three quick responses yet seem to have picked on the one that least
suited you, did any of the other suggestions help?
Meglio
> "Meglio" <x.meg...@gmail.com> wrote in message
No. I do not know how to move forward to solve this problem.
Neil Smith [MVP Digital Media]
wrote:
>> > No problem with time execution, no problem with memory limits. The
>> > problem is script execution time - it do not completed even in 10
>> > minutes.
>>
>> > Anton
>>
>> You had three quick responses yet seem to have picked on the one that least
>> suited you, did any of the other suggestions help?
>>
>
>No. I do not know how to move forward to solve this problem.
I'll take a look at the XSL to see if it's doing unnecessary work.
In the meantime, profiling PHP using XDebug and analysing the output
using the CacheGrind program is an effective way to find out if PHP is
spending time in certain tasks which you can rewrite. 10 minutes
sounds really excessive though even for 20MB.
Cheers - Neil
Meglio
wrote:
> On Sun, 4 May 2008 04:51:45 -0700 (PDT), Meglio <x.meg...@gmail.com>
I have not skills to use CacheGrind and XDebug with PHP so it will
take me really long time to do it.
and yes, thank you if you will find how to improve the XSLT.
Thanks,
Anton
Neil Smith [MVP Digital Media]
wrote:
>>
>> >> > No problem with time execution, no problem with memory limits. The
>> >> > problem is script execution time - it do not completed even in 10
>> >> > minutes.
>>
>> >> You had three quick responses yet seem to have picked on the one that least
>> >> suited you, did any of the other suggestions help?
>>
>> >No. I do not know how to move forward to solve this problem.
>> In the meantime, profiling PHP using XDebug and analysing the output
>I have not skills to use CacheGrind and XDebug with PHP
Actually, you can just run those on a windows workstation with minimal
effort - cachegrind just reads the xdebug extension's output profile -
I set those up last week first time in about 20 minutes. I'm sure you
could too.
>> I'll take a look at the XSL to see if it's doing unnecessary work.
Well the bad news is the XSLT you provided is doing at least twice the
work it needs to - the are two instances of the call to process
<xsl:for-each select="./Category"> at the start of the stylesheet.
The next things I noticed are
(1) you're using XML default output - setting <xsl:output type="text"
/> would be more appopriate for generating a SQL script for
consumption by the DB server.
(2) You're using xsl:for-each, which is slower than using
xsl:apply-templates in pretty much all XSL processors I can think of
(3) You're unnecessarily asking the processor to traverse to the
current parent node (which you're already in) by using the syntax like
<xsl:value-of select="./Gravity" /> - try -
<xsl:value-of select="Gravity" /> instead
(4) You're doing a lot of tail recursion to quote strings, and the
actual quoting can still be bypassed in several ways (which is a
security risk if you don't trust the source provider)
As Joe noted, PHP5 supports XMLTextReader. In the document above,
you're not doing anything other than a forward read - there's no need
to invoke XSL to transform to flat text, as other tools exist.
Similarly, although SimpleXML might look attractive, it will also load
the entire document into memory, which isn't what we want here.
I spent a while to run through this and generated the following
script, which is close to your requirements (obviously run locally
checking SQL field order and that no unexpected blank entries are
made). It outputs the SQL lines as fast as possible, and discards used
data by resetting sitenodes array on each pass.
Probably you'd want to use this as a starting point to create a class
rather than just a bunch of recursive functions.
Note we're using PDO to generate the quoted string (enable that
extension, which you should be using anyway), and only quoting the
listed strings in your original XSL (adding Id field, which you forgot
to quote in the original XSL - which is a string)
After a few optimisations, I ran this on your data in 170 seconds -
including building the SQL output script (14.5MB). I did that on a
Celeron 400 Mhz with 160MB of memory, PHP5, MySQL5 and Apache 2.
It should be a very small part of a minute on a modern machine ;-))
HTH
Cheers - Neil
================================================
<?php
error_reporting(E_ALL);
ini_set('max_execution_time', 3600);
$start = microtime(true);
define('CONFIG_OUTPUT_SQL', 'D:\\htdocs\\XML\\sqlout.sql');
define('CONFIG_SQL_USER', 'your_db_username');
define('CONFIG_SQL_PASS', 'your_db_password');
define('CONFIG_SQL_CONN',
'mysql:host=127.0.0.1;port=3306;dbname=your_default_db');
define('CONFIG_INPUT_XML',
'D:\\htdocs\\XML\\marketplace_feed_v1.xml');
$conn = new PDO(CONFIG_SQL_CONN, CONFIG_SQL_USER, CONFIG_SQL_PASS);
$fp = fopen(CONFIG_OUTPUT_SQL, 'w+');
$reader = new XMLReader;
$reader->open(CONFIG_INPUT_XML);
while ($reader->read()) {
if ($reader->nodeType == XMLReader::ELEMENT && $reader->name
== 'Category') {
parseCategoryNodes();
}
}
$reader->close();
$conn->close();
fclose($fp);
function parseCategoryNodes() {
global $reader, $conn, $fp;
$sQuery = "INSERT INTO marketplace_tmp (category, subcategory,
id, title, description, popularityrank, hasrecurringproducts, gravity,
earnedpersale, percentpersale, totalearningspersale, totalrebillamt,
referred, commission) VALUES ";
while ($reader->read()) {
if ($reader->nodeType == XMLReader::ELEMENT) {
switch ($reader->name) {
case 'Name' :
$reader->read();
if ($reader->nodeType ==
XMLReader::TEXT) {
$current_category =
$conn->quote($reader->value);
}
break;
case 'Site' :
$sitenodes = parseSiteNodes();
$query = $sQuery. "(";
$query .= $current_category.",
";
$query .= $current_category.",
";
$query .= join(", ",
$sitenodes);
$query .= ")\r\n";
fwrite($fp, $query);
} // End switch ($reader->name)
}
} // End while ($reader->read())
fclose($fp);
}
function parseSiteNodes() {
global $reader, $conn;
$sitenodes = array();
while ($reader->read()) {
// Check if we reached the end of the 'Site' container node,
return the resultset
if ($reader->nodeType == XMLReader::END_ELEMENT &&
$reader->name == 'Site') {
return $sitenodes;
}
if ($reader->nodeType == XMLReader::ELEMENT) {
// DB field names happen to be lower case here (but needn't be
for MySQL)
$nodename = strtolower($reader->name);
// Read next text node which follows the field name element
$reader->read();
if ($reader->nodeType == XMLReader::TEXT ||
$reader->nodeType == XMLReader::CDATA) {
// Process site nodes with category info into quoted DB strings
depending on driver
if ($nodename == 'id' || $nodename ==
'title' || $nodename == 'description') {
$sitenodes[$nodename] =
$conn->quote($reader->value);
} else {
$sitenodes[$nodename] =
($reader->value == '' ? 0 : $reader->value);
}
}
}
}
}
$end = microtime(true);
print("Duration : ".(($end - $start) * 1000)." msec");
?>
================================================
Meglio
wrote:
> On Sun, 4 May 2008 13:12:49 -0700 (PDT), Meglio <x.meg...@gmail.com>
Thanks for this well explained idea. I'll play with it soon and will
let you know what I got.
Meglio
work it needs to - the are two instances of the call to process
<xsl:for-each select="./Category"> at the start of the stylesheet
I'm not doing the same thing twice. Actually what I'm doing here is I
get categories and subcategories:
Simplified XML structure shown below:
<category>
<site>...</site>
<site>...</site>
<site>...</site>
<category> <?-- THIS IS SUBCATEGORY -->
<site>...</site>
<site>...</site>
<site>...</site>
</category>
<category>
Meglio
wrote:
> On Sun, 4 May 2008 13:12:49 -0700 (PDT), Meglio <x.meg...@gmail.com>
Hi, Neil.
Thank you again for you comments and advices.
I improved your PHP script (you do not take into account that there
are subcategories inside categories) and I embedded it to my
automation and it works good and fast now (up to 1 minute).
Thank you very much for you help! I can donate some encouragement if
you have PayPal or Google Checkout account ;) You helped me a lot
really!!
Anton
Neil Smith [MVP Digital Media]
wrote:
>> Digital Media MVP : 2004-2008http://mvp.support.microsoft.com/mvpfaqs
>
>Hi, Neil.
>Thank you again for you comments and advices.
>
>I improved your PHP script (you do not take into account that there
>are subcategories inside categories) and I embedded it to my
>automation and it works good and fast now (up to 1 minute).
>
>Thank you very much for you help! I can donate some encouragement if
>you have PayPal or Google Checkout account ;) You helped me a lot
>really!!
No worries - I hadn't worked with the TextReader before and it might
come in useful in other projects I'm working on.
You're right I hadn't see the bit about subcategories, and I know
there were missing semicolon delimiter separating the output SQL too.
1 minute sounds good, much better than the XSL processing then.
Perhaps you'd like to send $5 to the disasters ermergency committee
for Burma, if you're feeling generous ;-)
Cheers - Neil
Meglio
wrote:
> On Mon, 12 May 2008 09:36:22 -0700 (PDT), Meglio <x.meg...@gmail.com>
Buy the way, interesting, where is the page I can donate to the
disasters ermergency committee
for Burma? lol
Anton
Neil Smith [MVP Digital Media]
wrote:
>Buy the way, interesting, where is the page I can donate to the
>disasters ermergency committee
>for Burma? lol
Oh here (in case anybody else wants to, too ;-)
It aggregates donations to groups like oxfam, red cross and so forth,
and only swings into action when there's a really major need.