It's a little unfair on the XSLT stuff, since you're going from DBI to XML to String to XML to XSLT. I need to update XML::Generator::DBI to output SAX2 (which I'm doing right now), after which you'll be able to go direct from XML::Generator::DBI into XML::LibXML::SAX::Builder, and skip the string phase completely.

I may even run the tests to see how it fares for me. I'll reply here after I've made those updates!

Alternatively you can cause XML::Generator::DBI to use the XML::Handler::BuildDOM as a handler in order to generate a DOM object that can then be fed to XML::XSLT again cutting out the intermediate reparse of the XML.

I am toying with the idea of providing a utility to create modules that encapsulate preparsed DOM objects from XSLT so that step can be taken out as well if anyone is interested

/J\

OK, I hacked on this a bit. It seems that almost without a doubt the bottleneck is in XML::Generator::DBI (I added a tiny benchmark to the single sub, and generate took 11u, xslt took 1u and output took 1u. So I'm going to update it to SAX2, and make sure it goes faster. There's a lot of cruft in there, and I've learnt some about how to make DBI calls faster since I wrote it.

You can make things faster by simply changing your generator to:

my $generator = XML::Generator::DBI->new(
     Handler => XML::LibXML::SAX::Builder->new(),
     dbh => $dbh,
     RowElement => "monk"
);
[download]

Which seems to work (even without updating XML::Generator::DBI). Then you have to add ->toString to your calls for the TT level stuff. It makes all three faster - by about 40%.

Ah, it makes a big difference if you also turn off indenting on XML::Generator::DBI (pass NoIndent => 1 to new()). I think that should be the default!

I do realize that there are other XML/XSLT engines out there, and strictly speaking, this method employs non-pure Perl parts

I think that XML::XSLT would have come out very badly in speed terms - I have been favouring ease of maintenance and consistency in my recent refactoring with little concern for speed and efficiency :) Also of course it does ultimately depend on XML::Parser which has a non-perl dependency

/J\

I didn't realize this existed as well; however, I would suspect that in the DBI->XML->XSLT path, the choice of which parser or XSLT library that you use is going to have some, but not a significant impact on the overall speed, assuming that, as with LibXSLT and XML::Parser, there's a non-perl component. As demonstrated by the two pure-perl routes, any significant processing of XML is going to need a boost by having pre-compiled code available for at least parsing the system.

However, I think I'll add the XML::XSLT case as well as skipping the DOM->string->DOM conversion that I do as gellyfish mentioned in reply to Matts response above, as additional tests, just for completeness. (I could also probably improve the xslt sheet itself, for the row coloring code doesn't seem to be overly efficient).

I also understand that GNOME's LibXML (which XML::LibXML uses) is not fully complient with recent W3C specs, so that may be a notch against it, as I'd expect a fully complient library to be a bit more rigorous and thus more CPU demanding.

-----------------------------------------------------
Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
"I can see my house from here!"
It's not what you know, but knowing how to find it if you don't know that's important

There are no known areas where LibXML/LibXSLT are not fully compliant with the relevant W3C specs (they're even authored by the W3C!). If you do know of areas where they fall down on this please do let me know and I'll forward the info on to Daniel.

1) Use prepare_cached() when you can. In this case, you can.
2) Use bind_columns(). It's faster and saves some memory.

I agree, in general you'd use these, but to be fair to XML::Generator::DBI, which to the best of my knowledge you cannot take advantage of these features, I kept them off. Given the baseline performance of the DBI/print system, which did fifty iters without blinking, I would suspect these tricks would only slighty improve the non-XML systems (which all were faster to start with).

-----------------------------------------------------
Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
"I can see my house from here!"
It's not what you know, but knowing how to find it if you don't know that's important

Actually I just uploaded 0.02 of XML::Generator::DBI to CPAN, which can take a prepared statement instead of as string as a parameter to execute(). And it already did the bind_columns trick. It also now makes Indent off the default, so it should be about as fast as I can make it.

The other thing I did was change your '//monks' in your XSLT to just 'monks', which is much faster, since it doesn't examine every single child node.

Technically, you could do the XML side of things without XML::Generator::DBI, and it would be faster since you could code if for your specific case rather than making a general DBI-->XML tool. Then you could take full advantage of all DBI speed tweaks. However, you would probably end up writing a lot more code.

When comparing performance, I do think that being unable to fully use DBI's performance features matters. Personally though, I would be more interested in knowing how easy it is to code the different approaches, since they all seem to perform well enough.

The results are:

XML and XSLT, String Intermediate, XML::XSLT   0.159/s                
+                        --    -62%   -88%   -94%   -95%   -96%   -99%
+  -100%
XML and TT2/XPath                           0.422/s                   
+                   166%    --     -67%   -84%   -87%   -90%   -98%  -
+100%
XML and TT2/Simple                           1.29/s                   
+                   713%    205%   --     -51%   -60%   -69%   -95%  -
+99%
XML and XSLT, String Intermediate, LibXSLT   2.66/s                   
+                  1573%    529%   106%   --     -17%   -36%   -90%  -
+98%
XML and XSLT, XML Intermediate, LibXSLT      3.20/s                   
+                  1915%    657%   148%    20%   --     -23%   -87%  -
+98%
DBI and TT2                                  4.13/s                   
+                  2505%    879%   221%    56%    29%   --     -84%  -
+98%
DBI and CGI                                  25.4/s                   
+                 15931%   5924%  1873%   858%   696%   516%   --    -
+85%
DBI and Print                                 172/s                   
+                108526%  40717% 13269%  6391%  5291%  4071%   578%  -
+-
[download]

Except for changing the XSLT template as Matts indicated, the rest of the files remain unchanged. Here's the updated test code, however...

#!/usr/bin/perl -w

use strict;

use DBI;
use CGI qw/-no_xhtml :standard/;
use XML::Generator::DBI;
use XML::Handler::YAWriter;
use XML::LibXML::SAX::Builder;
use XML::LibXML;
use XML::LibXSLT;
use XML::XSLT;
use Template;
use Data::Dumper;
use Benchmark qw( cmpthese );

$Template::Config::STASH = 'Template::Stash';

my $dbh = DBI->connect( "dbi:Pg:dbname=monksdb", "", "" ) 
  or die $DBI::errstr;

my $query = "SELECT id, name, xp, lat, long FROM monks ORDER BY lat LI
+MIT 25";
my $sth = $dbh->prepare_cached( $query ) or die $DBI::errstr;

my $ya = XML::Handler::YAWriter->new( AsString => 1 );

my $generator = XML::Generator::DBI->new(
                     Handler => $ya,
                     dbh => $dbh,
                     RowElement => "monk"
                    );

my $generator2 = 
  XML::Generator::DBI->new(
               Handler => XML::LibXML::SAX::Builder->new(),
               dbh => $dbh,
               RowElement => "monk" );

my $tt2 = Template->new;
my $tt2_nonXML = "template1.tt2";
my $tt2_XML = "template2.tt2";
my $tt2_XPath = "template3.tt2";

my $parser = new XML::LibXML;
my $xslt = new XML::LibXSLT;
my $sheet = "xslt_sheet.xsl";
my $slt = $parser->parse_file( $sheet );
my $stylesheet = $xslt->parse_stylesheet( $slt );

my $stylesheet2 = XML::XSLT->new( $sheet, warnings => 1 );

open FILE, ">/dev/null" or die "Cannot write out: $!";
my $target = \*FILE;

cmpthese( 100, 
      {
       "DBI and Print" => \&generate_from_straight_dbi_and_print,
       "DBI and CGI" => \&generate_from_straight_dbi_and_cgi,
       "DBI and TT2" => \&generate_from_straight_dbi_and_tt2,
       "XML and TT2/Simple" => \&generate_from_xml_and_tt2_and_xmlsimp
+le,
       "XML and TT2/XPath" => \&generate_from_xml_and_tt2_and_xpath,
       "XML and XSLT, String Intermediate, LibXSLT " => \&generate_fro
+m_xml_and_xslt_string,
       "XML and XSLT, XML Intermediate, LibXSLT" => \&generate_from_xm
+l_and_xslt_xml,
       "XML and XSLT, String Intermediate, XML::XSLT" => \&generate_fr
+om_xmlxslt_xml
       }
);

close FILE;

# Here, we use straight DBI calls and print calls to mark up 
# the table

sub generate_from_straight_dbi_and_print {
#  my $target = shift;

  $sth->execute() or die $DBI::errstr;
  my ( $id, $name, $xp, $lat, $long );
  $sth->bind_columns( \$id, \$name, \$xp, \$lat, \$long );

  print $target "Content-Type: text/html\n\n";
  print $target "<html><body><table>\n";
  my $colorrow = 0;
  while ( $sth->fetch() ) {
    $colorrow = !$colorrow;
    my $color = ( $colorrow ) ? "#FFFFFF" : "#D0D0FF";
    print $target <<ROW;
    <tr>
      <td bgcolor="$color">$id</td>
      <td bgcolor="$color">$name</td>
      <td bgcolor="$color">$xp</td>
      <td bgcolor="$color">$lat</td>
      <td bgcolor="$color">$long</td>
    </tr>
ROW
;
  }
  print $target "</table></body></html>";

}

# Here, we group the results as to make it easier for CGI 
# to print out (avoiding large HERE docs...)

sub generate_from_straight_dbi_and_cgi {
#  my $target = shift;

  $sth->execute() or die $DBI::errstr;
  my ( $id, $name, $xp, $lat, $long );
  $sth->bind_columns( \$id, \$name, \$xp, \$lat, \$long );

  my @data;
  while ( $sth->fetch ) { push @data, [$id, $name, $xp, $lat, $long]; 
+}

  my $colorrow = 0;
  print $target 
    header('text/html'),
    start_html,
    table( 
          map { $colorrow = !$colorrow; 
            my $color = ( $colorrow ) ? "#FFFFFF" : "#D0D0FF";
            Tr( 
               td( {-bgcolor=>$color}, $_ )
              )
          } @data
         ),
    end_html;
}

# Here, we pass the results to Template Toolkit for printing

sub generate_from_straight_dbi_and_tt2 {
#  my $target = shift;

  $sth->execute() or die $DBI::errstr;
  my ( $id, $name, $xp, $lat, $long );
  $sth->bind_columns( \$id, \$name, \$xp, \$lat, \$long );

  my @data;
  while ( $sth->fetch ) { push @data, [$id, $name, $xp, $lat, $long]; 
+}

  print $target header;
  $tt2->process( $tt2_nonXML, { monks => \@data }, $target ) or
    die $tt2->error(),"\n";
}

# Use TT2 again, but now pass it XML and use the XPath module
# for parsing

sub generate_from_xml_and_tt2_and_xmlsimple {
#  my $target = shift;
  my $xml = $generator->execute( $query );

  print $target header;
  $tt2->process( $tt2_XML, { results => $xml }, $target ) or
    die $tt2->error(), "\n";
}

# Use TT2 again, but now pass it XML and use the XPath module
# for parsing

sub generate_from_xml_and_tt2_and_xpath {
#  my $target = shift;
  my $xml = $generator->execute( $query );


  print $target header;
  $tt2->process( $tt2_XPath, { results => $xml }, $target ) or
    die $tt2->error(), "\n";
}

# Use LibXML/LibXSLT to parse the results

sub generate_from_xml_and_xslt_string {
#  my $target = shift;
  my $xml = $generator->execute( $sth );

  print $target header;

  my $source = $parser->parse_string( $xml );
  
  my $results = $stylesheet->transform( $source );
  print $target $stylesheet->output_string( $results );
}

sub generate_from_xml_and_xslt_xml {
#  my $target = shift;
  my $xml = $generator2->execute( $sth );

  print $target header;

  my $results = $stylesheet->transform( $xml );
  print $target $stylesheet->output_string( $results );
}

sub generate_from_xmlxslt_xml {
#  my $target = shift;
  my $xml = $generator->execute( $sth );

  print $target header;

  print $target $stylesheet2->serve( $xml );
}
[download]

-----------------------------------------------------
Dr. Michael K. Neylon - mneylon-pm@masemware.com || "You've left the lens cap of your mind on again, Pinky" - The Brain
"I can see my house from here!"
It's not what you know, but knowing how to find it if you don't know that's important

I've also added code that uses XML::XSLT , which surprising is VERY bad at least as I've used it (did I use it wrong? I couldn't get a pure XML DOM document to work with it...)

Heh, I discovered that there was an evil interaction between XML::Generator::DBI and XML::Handler::BuildDOM - I have sent patches to both Matt and Tim ;-}

I'll update this later with an example of how you might do this when I get a minute

Update: Matt has applied a fix to XML::Generator::DBI and this code now works as billed :

#!/usr/bin/perl -w

use strict;
use XML::XSLT;
use XML::Generator::DBI;
use XML::Handler::BuildDOM;
use XML::DOM;
use DBI;


my $ya = XML::Handler::BuildDOM->new();
my $dbh = DBI->connect("dbi:Informix:tdcusers",'','',{ChopBlanks => 1}
+);
my $generator = XML::Generator::DBI->new(
                                         Handler => $ya,
                                         dbh => $dbh
                                        );

my $style =<<EOFOO;
<?xml version="1.0"?>
<xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" versi
+on="1.0">
<xsl:output encoding = "iso-8859-1"/>
 <xsl:template match = "select">
   <html>
     <head>
       <title>
         Test
       </title>
     </head>
     <body>
       <table>
         <xsl:for-each select="row">
           <tr>
             <td>
               <xsl:value-of select="prefix" /> 
             </td>
             <td>
               <xsl:value-of select="code" /> 
             </td>
             <td>
               <xsl:value-of select="ctext" /> 
             </td>
           </tr>
         </xsl:for-each>
       </table>
     </body> 
   </html>
 </xsl:template>
</xsl:stylesheet>
EOFOO

my $stylesheet = XML::XSLT->new($style);

my $sql = 'select * from text_codes';

my $dom = $generator->execute($sql);

$stylesheet->transform(Source => $dom);

$stylesheet->toString();
[download]

You will obviously want to use your own database and an appropriate stylesheet :)

/J\