By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,694 Members | 1,845 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,694 IT Pros & Developers. It's quick & easy.

Optimization of mySQL to XML output...

P: n/a
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_SERVER", "localhost" );
define( "DATABASE_USERNAME", "root" );
define( "DATABASE_PASSWORD", "" );
define( "DATABASE_NAME", "mydatabase" );

$method=$_GET['method'];

//connect to the database
$mysql = mysql_connect(DATABASE_SERVER, DATABASE_USERNAME,
DATABASE_PASSWORD);
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_object( $Result ) )
{
//$Return .= "<data><name>".$DataName->DataName."</
name><iocode>".$DataName->IOCode."</iocode><coicop>".$DataName-
>COICOP."</coicop></data>";
print ("<data>");
print ("<name>".$DataName->DataName."</name>");
print ("<code1>".$DataName->code1."</code1>");
print ("<code2>".$DataName->code2."</code2>");
print ("<code3>".$DataName->IOComName."</code3>");
print ("<code4>42</code4>");
print ("<code5>".$DataName->high_total_IOComName."</code5>");
print ("<code6>".$DataName->high_prod_IOComName."</code6>");
print ("<code7>".$DataName->high_whole_IOComName."</code7>");
print ("<code8>".$DataName->high_whole_IOComName."</code8>");
print ("<code9>".$DataName->high_whole_IOComName."</code9>");
print ("</data>");
}
print ("</response>");
mysql_free_result( $Result );
}

Oct 24 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
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_SERVER", "localhost" );
define( "DATABASE_USERNAME", "root" );
define( "DATABASE_PASSWORD", "" );
define( "DATABASE_NAME", "mydatabase" );

$method=$_GET['method'];

//connect to the database
$mysql = mysql_connect(DATABASE_SERVER, DATABASE_USERNAME,
DATABASE_PASSWORD);
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_object( $Result ) )
{
//$Return .= "<data><name>".$DataName->DataName."</
name><iocode>".$DataName->IOCode."</iocode><coicop>".$DataName-
>COICOP."</coicop></data>";
print ("<data>");
print ("<name>".$DataName->DataName."</name>");
print ("<code1>".$DataName->code1."</code1>");
print ("<code2>".$DataName->code2."</code2>");
print ("<code3>".$DataName->IOComName."</code3>");
print ("<code4>42</code4>");
print ("<code5>".$DataName->high_total_IOComName."</code5>");
print ("<code6>".$DataName->high_prod_IOComName."</code6>");
print ("<code7>".$DataName->high_whole_IOComName."</code7>");
print ("<code8>".$DataName->high_whole_IOComName."</code8>");
print ("<code9>".$DataName->high_whole_IOComName."</code9>");
print ("</data>");
}
print ("</response>");
mysql_free_result( $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*******@attglobal.net
==================

Oct 24 '07 #2

P: n/a
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_SERVER", "localhost" );
define( "DATABASE_USERNAME", "root" );
define( "DATABASE_PASSWORD", "" );
define( "DATABASE_NAME", "mydatabase" );

$method=$_GET['method'];

//connect to the database
$mysql = mysql_connect(DATABASE_SERVER, DATABASE_USERNAME,
DATABASE_PASSWORD);
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_object( $Result ) )
{
//$Return .= "<data><name>".$DataName->DataName."</
name><iocode>".$DataName->IOCode."</iocode><coicop>".$DataName->COICOP."</coicop></data>";

print ("<data>");
print ("<name>".$DataName->DataName."</name>");
print ("<code1>".$DataName->code1."</code1>");
print ("<code2>".$DataName->code2."</code2>");
print ("<code3>".$DataName->IOComName."</code3>");
print ("<code4>42</code4>");
print ("<code5>".$DataName->high_total_IOComName."</code5>");
print ("<code6>".$DataName->high_prod_IOComName."</code6>");
print ("<code7>".$DataName->high_whole_IOComName."</code7>");
print ("<code8>".$DataName->high_whole_IOComName."</code8>");
print ("<code9>".$DataName->high_whole_IOComName."</code9>");
print ("</data>");
}
print ("</response>");
mysql_free_result( $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)

Oct 24 '07 #3

P: n/a
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.

Oct 24 '07 #4

P: n/a
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($command);

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_fields ($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.

Oct 24 '07 #5

P: n/a
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_buffering 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*******@freemail.ru>

Oct 24 '07 #6

P: n/a
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_buffering 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*******@attglobal.net
==================

Oct 25 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.