Stories
Slash Boxes
Comments
NOTE: use Perl; is on undef hiatus. You can read content, but you can't post it. More info will be forthcoming forthcomingly.

All the Perl that's Practical to Extract and Report

The Fine Print: The following comments are owned by whoever posted them. We are not responsible for them in any way.
 Full
 Abbreviated
 Hidden
More | Login | Reply
Loading... please wait.
  • At least not against a good database. (eg Oracle, PostgreSQL, etc - but MySQL would be fine.)

    The problem is that there is a trade-off between time spend preparing a query and query performance. It would not be inappropriate to think of preparing a query as a "compile and optimize this SQL" step. MySQL spends very little energy preparing, and therefore its ability to handle complex queries suffers. Most other databases put a lot of energy into preparing, and so it is very important to try to avoid recompiling the same query over and over again.

    When you use placeholders you get the opportunity to let the database reuse query plans, which cuts query preparation costs. But your approach quotes things over and over again, which results in a lot of extra prepares, which can result in a database falling over because it runs out of CPU. I've seen it happen - it is not pretty.

    It would be good if you could build the same thing with an option to do some kind of late binding.

    Personally at work I faced a problem kind of like that, and my solution was to pass my SQL through a template engine (allowing me to do things like conditionally include complex chunks of SQL) and then pass variables in by name (I'm using DBD::Pg, which supports binding variables by name). I've been very happy with how it has worked out for me. However that suggests that you might want to think about how to set things up to expand a quasi-quoted thing multiple times...

    • Also, source filters. Yes, Module::Compile, but still, yuck.

      Personally I prefer SQL::Interp [cpan.org] (via DBIx::Simple [cpan.org] actually), which lets me write code very nearly like the quasiquoting snippet, except it’s still plain Perl and it uses placeholders and does binding under the covers. It’s a fabulous duo.

      • If my situation was simpler then that would look good to me as well. Unfortunately I have big chunks of SQL that are included or not based on one condition, and if included require specific parameters to be bound or not as well. Templating syntax works well for this, but standard template tools only return a string, so I really, really want to work with a string by itself, not a string plus parameters.

        Using :param_name isn't portable, but it is highly effective for my job.

        • That is actually exactly the situation that SQL::Interp addresses. It lets you do something like this:

          my ( $sql, @bind_value ) = sql_interp(
              'SELECT foo FROM bar'
              ( $do_include_baz
                  ? ( 'LEFT JOIN baz ON baz.foo = foo.id AND baz.quux =', \$quux )
                  : ()
              ),
              'WHERE', \%condition,
          );

          sql_interp will combine all those SQL snippets into a single string, duly putting in with placeholders for all the variables,

          • Yes, you can do that - but the result is unreadable. Which would you prefer to read?

            my ( $sql, @bind_value ) = sql_interp(
                    'SELECT foo ',
                        ( $include_bar
                                ? ', bar'
                                : ()
                        ),
                        ' , count(*) as records '
                , 'FRO

            • Neither. :-)

              But the first example shows why I specifically pointed out that the list you pass is a normal Perl list and so is amenable to all of the facilities Perl provides. You can factor it just in the same way you would factor any other part of the code:

              my $columns = join ', ', 'foo', ( $include_bar ? 'bar' : () );

              push @queries, [
                  'SELECT $columns, count(*) as records',
                  'FROM some_table',
                  'WHERE', { x => $x },
                  map { "$_ BY $columns" } qw( GROUP O

    • Actually I am going to use it on a very very busy site ;) SQL preparing does not make much sense for me since our platform will usually work in the context of PostgreSQL PL/Proxy cluster *AND* the query could be dynamic enough to defeat ordinary DBI param binding.

      Actually runtime performance is the reason to choose source-filter solutions in the first place ;)

      Also, the SQL example is, well, merely an example...Filter::QuasiQuote's power reveals in the context of true DSLs ;)

      • My experience says that with a sane design you can run one of the top couple thousand busiest websites on the internet on a handful of webservers, paying only a modest amount of attention to performance of the code on your webservers.

        That same experience says that tiny mistakes in how you handle your database can cause that same site to melt unexpectedly.

        The lesson is to not worry about webserver performance, but be paranoid about database performance. Which means use placeholders properly. If you do it d

        • I must say that all you say is indeed true for an ordinary web application :)

          But unfortunately I can't use prepare+execute in my OpenResty platform in particular. Why? Because it must scale by design to serve lots of apps here in Yahoo! China and Alibaba. So it must be a cluster or something like that.

          The PL/Proxy database server requires frontend queries to be of the following form:

                select xquery('account', 'select * from posts...', 1);

          That is, the user sql query itself must be a dynam

          • Responding out of order.

            On sending multiple insert statements at once. Yes, that can be a big win because you're cutting down on round trips to the database. Each round trip takes unavoidable resources on the client, server, and network. With network latency typically being the biggest deal. However there is an upper limit to the win from that. A compromise that works fairly well is to prepare a bulk insert that inserts multiple records, thereby bypassing the prepare and reducing round trips. YMMV. B

            • Right, preparing a bunch of insert statements first would be faster :) Merely have to deal with the last few specially :) Thanks for the tip.

              We use PgBouncer at the PL/Proxy level to cache connections to the data nodes. On the FastCGI level, a pre-forked lighttpd is used. Database connection to the PL/Proxy nodes are reused across fastcgi loops in a similar fashion as you described :)

              Well, I don't think the use of PL/Proxy necessarily means loss of relationality. Relational constraints still hold for data r

              • Tell me if a donation could help the open-sourcing decision. I'm btilly, at gmail dot com.