Optimization of mySQL to XML output...

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 ("");
while ( $DataName = mysql_fetch_object( $Result ) )
{
//$Return .= "".$DataName->DataName." name>".$DataName->IOCode."".$DataName-
>COICOP."";
print ("");
print ("".$DataName->DataName."");
print ("".$DataName->code1."");
print ("".$DataName->code2."");
print ("".$DataName->IOComName."");
print ("42");
print ("".$DataName->high_total_IOComName."");
print ("".$DataName->high_prod_IOComName."");
print ("".$DataName->high_whole_IOComName."");
print ("".$DataName->high_whole_IOComName."");
print ("".$DataName->high_whole_IOComName."");
print ("");
}
print ("");
mysql_free_result( $Result );
}

Re: Optimization of mySQL to XML output...

jfizer@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 ("");
> while ( $DataName = mysql_fetch_object( $Result ) )
> {
> //$Return .= "".$DataName->DataName." > name>".$DataName->IOCode."".$DataName-
>> COICOP."";
> print ("");
> print ("".$DataName->DataName."");
> print ("".$DataName->code1."");
> print ("".$DataName->code2."");
> print ("".$DataName->IOComName."");
> print ("42");
> print ("".$DataName->high_total_IOComName."");
> print ("".$DataName->high_prod_IOComName."");
> print ("".$DataName->high_whole_IOComName."");
> print ("".$DataName->high_whole_IOComName."");
> print ("".$DataName->high_whole_IOComName."");
> print ("");
> }
> print ("");
> 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.
jstucklex@attglobal.net
==================

Re: Optimization of mySQL to XML output...

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 ("");
> while ( $DataName = mysql_fetch_object( $Result ) )
> {
> //$Return .= "".$DataName->DataName." > name>".$DataName->IOCode."".$DataName->COICOP."";
>
> print ("");
> print ("".$DataName->DataName."");
> print ("".$DataName->code1."");
> print ("".$DataName->code2."");
> print ("".$DataName->IOComName."");
> print ("42");
> print ("".$DataName->high_total_IOComName."");
> print ("".$DataName->high_prod_IOComName."");
> print ("".$DataName->high_whole_IOComName."");
> print ("".$DataName->high_whole_IOComName."");
> print ("".$DataName->high_whole_IOComName."");
> print ("");
> }
> print ("");
> 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=wget1.6+windows&btnG=Se arch
and on the command line, issue
wget http://example.com/script.php
and see how fast it is (the time will be shown)

Re: Optimization of mySQL to XML output...

> 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.

Re: Optimization of mySQL to XML output...

On Oct 24, 8:21 pm, jfizer wrote:
> > 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 .= "$var_1$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.

Re: Optimization of mySQL to XML output...

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.

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

j> Whats more, it turns out that the resulting XML file is over five
j> megs, 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

Re: Optimization of mySQL to XML output...

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.
>
> j> Could very well be, I've not tried to time the difference if any when
> j> the application consumes the XML.
>
> j> Whats more, it turns out that the resulting XML file is over five
> j> megs, 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.
jstucklex@attglobal.net
==================