Optimization of mySQL to XML output...

[jfizer]

I'm trying to print out a table of mySQL data as XML. However, the
performance I'm getting is REALY bad. Adding the header("Content-Type:
$mime;charset=$ charset"); statement triples the load time of the data
for example. Can someone with more php experience then I look this
over and tell me that I'm stupid and doing it wrong?

define( "DATABASE_SERVE R", "localhost" );
define( "DATABASE_USERN AME", "root" );
define( "DATABASE_PASSW ORD", "" );
define( "DATABASE_NAME" , "mydatabase " );

$method=$_GET['method'];

//connect to the database
$mysql = mysql_connect(D ATABASE_SERVER, DATABASE_USERNA ME,
DATABASE_PASSWO RD);
mysql_select_db ( DATABASE_NAME );
function getDataNames()
{
$Query = "SELECT * from `My Data`";
$Result = mysql_query( $Query );
//$charset = "iso-8859-1";
//$mime = "text/xml";
//header("Content-Type: $mime;charset=$ charset");
print ("<response> ");
while ( $DataName = mysql_fetch_obj ect( $Result ) )
{
//$Return .= "<data><name>". $DataName->DataName."</
name><iocode>". $DataName->IOCode."</iocode><coicop> ".$DataName-

>COICOP."</coicop></data>";

print ("<data>");
print ("<name>".$Data Name->DataName."</name>");
print ("<code1>".$Dat aName->code1."</code1>");
print ("<code2>".$Dat aName->code2."</code2>");
print ("<code3>".$Dat aName->IOComName."</code3>");
print ("<code4>42</code4>");
print ("<code5>".$Dat aName->high_total_IOC omName."</code5>");
print ("<code6>".$Dat aName->high_prod_IOCo mName."</code6>");
print ("<code7>".$Dat aName->high_whole_IOC omName."</code7>");
print ("<code8>".$Dat aName->high_whole_IOC omName."</code8>");
print ("<code9>".$Dat aName->high_whole_IOC omName."</code9>");
print ("</data>");
}
print ("</response>");
mysql_free_resu lt( $Result );
}

[Jerry Stuckle]

jf****@vintara. com wrote:

I'm trying to print out a table of mySQL data as XML. However, the
performance I'm getting is REALY bad. Adding the header("Content-Type:
$mime;charset=$ charset"); statement triples the load time of the data
for example. Can someone with more php experience then I look this
over and tell me that I'm stupid and doing it wrong?

define( "DATABASE_SERVE R", "localhost" );
define( "DATABASE_USERN AME", "root" );
define( "DATABASE_PASSW ORD", "" );
define( "DATABASE_NAME" , "mydatabase " );

$method=$_GET['method'];

//connect to the database
$mysql = mysql_connect(D ATABASE_SERVER, DATABASE_USERNA ME,
DATABASE_PASSWO RD);
mysql_select_db ( DATABASE_NAME );
function getDataNames()
{
$Query = "SELECT * from `My Data`";
$Result = mysql_query( $Query );
//$charset = "iso-8859-1";
//$mime = "text/xml";
//header("Content-Type: $mime;charset=$ charset");
print ("<response> ");
while ( $DataName = mysql_fetch_obj ect( $Result ) )
{
//$Return .= "<data><name>". $DataName->DataName."</
name><iocode>". $DataName->IOCode."</iocode><coicop> ".$DataName-

>COICOP."</coicop></data>";

print ("<data>");
print ("<name>".$Data Name->DataName."</name>");
print ("<code1>".$Dat aName->code1."</code1>");
print ("<code2>".$Dat aName->code2."</code2>");
print ("<code3>".$Dat aName->IOComName."</code3>");
print ("<code4>42</code4>");
print ("<code5>".$Dat aName->high_total_IOC omName."</code5>");
print ("<code6>".$Dat aName->high_prod_IOCo mName."</code6>");
print ("<code7>".$Dat aName->high_whole_IOC omName."</code7>");
print ("<code8>".$Dat aName->high_whole_IOC omName."</code8>");
print ("<code9>".$Dat aName->high_whole_IOC omName."</code9>");
print ("</data>");
}
print ("</response>");
mysql_free_resu lt( $Result );
}

Before you can optimize it, you need to find out where the hangup is.
Is it in your PHP code or MySQL?

Try inserting some calls to microtime() at strategic places in your code
and displaying out the results.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===

[shimmyshack]

On Oct 24, 5:51 pm, jfi...@vintara. com wrote:

I'm trying to print out a table of mySQL data as XML. However, the
performance I'm getting is REALY bad. Adding the header("Content-Type:
$mime;charset=$ charset"); statement triples the load time of the data
for example. Can someone with more php experience then I look this
over and tell me that I'm stupid and doing it wrong?

define( "DATABASE_SERVE R", "localhost" );
define( "DATABASE_USERN AME", "root" );
define( "DATABASE_PASSW ORD", "" );
define( "DATABASE_NAME" , "mydatabase " );

$method=$_GET['method'];

//connect to the database
$mysql = mysql_connect(D ATABASE_SERVER, DATABASE_USERNA ME,
DATABASE_PASSWO RD);
mysql_select_db ( DATABASE_NAME );

function getDataNames()
{
$Query = "SELECT * from `My Data`";
$Result = mysql_query( $Query );
//$charset = "iso-8859-1";
//$mime = "text/xml";
//header("Content-Type: $mime;charset=$ charset");
print ("<response> ");
while ( $DataName = mysql_fetch_obj ect( $Result ) )
{
//$Return .= "<data><name>". $DataName->DataName."</
name><iocode>". $DataName->IOCode."</iocode><coicop> ".$DataName->COICOP."</coicop></data>";

print ("<data>");
print ("<name>".$Data Name->DataName."</name>");
print ("<code1>".$Dat aName->code1."</code1>");
print ("<code2>".$Dat aName->code2."</code2>");
print ("<code3>".$Dat aName->IOComName."</code3>");
print ("<code4>42</code4>");
print ("<code5>".$Dat aName->high_total_IOC omName."</code5>");
print ("<code6>".$Dat aName->high_prod_IOCo mName."</code6>");
print ("<code7>".$Dat aName->high_whole_IOC omName."</code7>");
print ("<code8>".$Dat aName->high_whole_IOC omName."</code8>");
print ("<code9>".$Dat aName->high_whole_IOC omName."</code9>");
print ("</data>");
}
print ("</response>");
mysql_free_resu lt( $Result );

}

tha fact that adding a header triples the load time suggests to me
that what you are annoyed about is the rendering time, rather than the
script time.
(I havent looked at the script to optimise it)
I suggest you goto the command line and simply save the file
grab wget for windows
http://www.google.com/search?hl=en&q...ws&btnG=Search
and on the command line, issue
wget http://example.com/script.php
and see how fast it is (the time will be shown)

[jfizer]

tha fact that adding a header triples the load time suggests to me
that what you are annoyed about is the rendering time, rather than the
script time.

Could very well be, I've not tried to time the difference if any when
the application consumes the XML.

Whats more, it turns out that the resulting XML file is over five
megs, which would be the major source of the performance bottle neck.

Other then that, any major issues with my php code? I'm new to the
language (this is my first project with it) and I'm not sure I'm doing
things in the optimal manor.

[shimmyshack]

On Oct 24, 8:21 pm, jfizer <jfi...@vintara .comwrote:

tha fact that adding a header triples the load time suggests to me
that what you are annoyed about is the rendering time, rather than the
script time.

Could very well be, I've not tried to time the difference if any when
the application consumes the XML.

Whats more, it turns out that the resulting XML file is over five
megs, which would be the major source of the performance bottle neck.

Other then that, any major issues with my php code? I'm new to the
language (this is my first project with it) and I'm not sure I'm doing
things in the optimal manor.

well i might use $tempXML .= "<el_1>$var _1</el_1><el_2>$var _2</
el_2>.....";
then print it once.

however have you thought of calling

mysqldump -q -X -u user - p password

using

$command = 'mysqldump -q -X -u user - p password';

passthru($comma nd);

it might be faster! follow jerry's advice and add some calls to
microtime in certain lines and use the difference to see where the
bottlenecks are occuring, since your file is so big i would be tempted
to use on the fly gzipping to get that data down to approximately
1/8th of its size - depending on the data. i know that adds
compression and decompression overhead, but it will speed up the
actual download for a file that size.

also try removing the while loop and using
$num = mysql_num_field s ($query);
to get the number of rows returned, then
use a for loop to concatenate a variable, then print it once you exit
the for loop.
again untested, it might be faster, i seem to remember while loops can
be slow, but i havent actually benchmarked this statement so dont
trust it.

[AnrDaemon]

Greetings, jfizer.
In reply to Your message dated Wednesday, October 24, 2007, 23:21:11,

>tha fact that adding a header triples the load time suggests to me
that what you are annoyed about is the rendering time, rather than the
script time.

jCould very well be, I've not tried to time the difference if any when
jthe application consumes the XML.

jWhats more, it turns out that the resulting XML file is over five
jmegs, which would be the major source of the performance bottle neck.

Make sure You have output_bufferin g option enabled and set to value around
Your typical page output size.
It should help speed up page output a bit.
This option disabled by default IIRC.
Check phpinfo(); from Your website for more information.
You may also try to zip output traffic if client application supports that.
Keywords: Output control functions, ob_gzhandler
--
Sincerely Yours, AnrDaemon <an*******@free mail.ru>

[Jerry Stuckle]

AnrDaemon wrote:

Greetings, jfizer.
In reply to Your message dated Wednesday, October 24, 2007, 23:21:11,

>>tha fact that adding a header triples the load time suggests to me
that what you are annoyed about is the rendering time, rather than the
script time.

jCould very well be, I've not tried to time the difference if any when
jthe application consumes the XML.

jWhats more, it turns out that the resulting XML file is over five
jmegs, which would be the major source of the performance bottle neck.

Make sure You have output_bufferin g option enabled and set to value around
Your typical page output size.
It should help speed up page output a bit.
This option disabled by default IIRC.
Check phpinfo(); from Your website for more information.
You may also try to zip output traffic if client application supports that.
Keywords: Output control functions, ob_gzhandler

Output buffering won't do him any good. In fact, it will slow things down.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attgl obal.net
=============== ===