http://qs1969.pair.com?node_id=164483

Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:

Monks My project is to write a daemon which will process a long list of SQL process against an Oracle DB.I need to run a defined set of SQL's paralelly to achieve efficiency.(Note: all the SQL's runs against the same DB). For this , I would like to have a DB connection pool thereby avoiding multiple connections to the DB.My questions is, how would I do it in perl or is there any modules whichis available to write a DB connection pool. FYI, this has nothing to do with mod_perl or Apache.Thanks for your help

Replies are listed 'Best First'.
Re: DB Connection pool
by gav^ (Curate) on May 07, 2002 at 00:42 UTC
    You might want to look at http://www.firstworks.com/sqlrelay.html.

    To quote:

    SQL Relay is a persistent database connection pooling, proxying and load balancing system for Unix and Linux supporting ODBC, Oracle, MySQL, mSQL, PostgreSQL, Sybase, MS SQL Server, IBM DB2, Interbase, Lago and SQLite with APIs for C, C++, Perl, Perl-DBD, Python, Python-DB, Zope, PHP, Ruby, Ruby-DBD and Java, command line clients, a GUI configuration tool and extensive documentation. The APIs support advanced database operations such as bind variables, multi-row fetches, client side result set caching and suspended transactions. It is ideal for speeding up database-driven web-based applications, accessing databases from unsupported platforms, migrating between databases, distributing access to replicated databases and throttling database access.
    Sounds interesting, thought I haven't had time to play with it yet.

    gav^

[reply]
Re: DB Connection pool
by perrin (Chancellor) on May 07, 2002 at 04:47 UTC
    First, connection pools do not avoid multiple connections to a database. What they do is limit the overall number of connections open at any given time and re-use them between parallel threads or processes. They may reduce the number of connections needed at one time, but only if some of the threads aren't doing any database work.

    Second, you don't need one. Just fork processes for the number of connections you want, connect separately in each (after the fork) and go. That's the most efficient you can get with this.

[reply]
Re: DB Connection pool
by samtregar (Abbot) on May 07, 2002 at 01:01 UTC
    Are you sure this will help? Unless your database server has multiple processors it is unlikely that running multiple queries in parallel will be substantially faster than running the same queries serially.

    If you do have multiple processors on your database server, then my suggestion would be to fork() N processes to each do 1/N of the work (N = number of available processors). Each process can then establish a private database connection, saving you the trouble of dealing with connection pools.

    Of course, more complicated schemes are possible but they are unlikely to be worth the effort.

    -sam

[reply]
      Unless your database server has multiple processors it is unlikely that running multiple queries in parallel will be substantially faster than running the same queries serially.

      Not necessarily true. Compiling a large project (say, a linux kernel) on a single CPU machine completes substantially faster with two processes (make -j2 ...) than it does in a single process, as this allows the CPU to keep compiling in one process while the other waits for disk access.

      While I haven't tested whether this is also true of databases, I suspect that it is likely to be.

[reply]
[d/l]
        My experience with databases is that they are far less I/O bound than many other tasks. As long as your machine has sufficient free RAM and your database is properly tuned, your queries should not be waiting for disk access often enough to matter.

        Of course, it's worth testing. It might be that the proper setting for N is actually 2 * number of available CPUs.

        -sam

[reply]
Re: DB Connection pool
by lachoy (Parson) on May 07, 2002 at 01:03 UTC

    The module ResourcePool might be useful here.

    Chris
    M-x auto-bs-mode

[reply]
Re: DB Connection pool
by samgold (Scribe) on May 07, 2002 at 01:38 UTC
    What do you mean by a defined set of SQL's paralelly? Are you talking about more than one SELECT statement or are you talking about parallel query? I am an Oracle DBA and would like to help you out if I can. I try to use perl as much as I can and this sounds very interesting.

    Sam
[reply]
Re: DB Connection pool
by rdfield (Priest) on May 07, 2002 at 12:17 UTC
    Why not use PL/SQL and call the stored package from you daemon? Sounds like a job for DBMS_JOB and DBMS_PIPE to me.

    rdfield

[reply]
Re: DB Connection pool
by Anonymous Monk on May 07, 2002 at 14:42 UTC
    Go to :
    http://www.theperlreview.com/

    and dload issue-01 and read :

    Design patterns : Singeltons from brian d foy ...
    i think this is exaclty what u want ...:")

    hth

    (in the past i wanted to do a similar CGI-thing and are blaming now that i did't know that)
[reply]

Back to Seekers of Perl Wisdom