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

Hi Monks

Need some suggestions on best way to solve this problem:

I have a perl program which can connect to any two different databases and do a comparison of two tables.

I have close to 200+ database tables to be compared.I also have option to compare different objects in different servers (parallel). Now the issue is that opening 200+ connections to database. I would like to implement a connection pool here to reduce the number of active connections at any point of time.

These are the few options i can think of:

1. Use Apache::DBI 2. Use DBIx pool 3. Use ResourcePool

New modules/approaches apart from the above are also welcome.

If any one has come across this scenario, could you please let me know which one is the best approach...

Thanks

Replies are listed 'Best First'.
Re: Connection Pooling
by Laurent_R (Canon) on Jun 10, 2013 at 11:15 UTC

    I don't know if this will help in your case, but I had a few years ago a somewhat similar case: a euro migration of a relatively large database with about 120 or 130 tables having monetary values to be converted to the new currency. My responsibility was to validate the data quality after the migration. What we did is simply to dump the tables (or the relevant fields of the tables) into flat CSV files before and after the migration and then just read the files in parallel to compare the monetary values and check their conversion. This turned out to be more practical and much faster than using database indexes to access the data. But, because of the nature of the migration, we were guaranteed to have the data dumped in the same order into the files, making further processing very easy. This might or might not apply to your case.

[reply]
    A reply falls below the community's threshold of quality. You may see it by logging in.
Re: Connection Pooling
by erix (Prior) on Jun 10, 2013 at 10:44 UTC

    In case the DBMS is postgres, I would try pgBouncer.

    (Having said that, one wonders if 200 connections is really so much that the database cannot handle them together, especially when your application seems to be a migration (one-of). But I suppose you've tried it :))

[reply]

      Thanks for your respose. As of now, we are using oracle,sybase and MS-Sqlserver.

[reply]
Re: Connection Pooling
by Happy-the-monk (Canon) on Jun 10, 2013 at 10:39 UTC

    Now the issue is that opening 200+ connections to database.

    You can do any number of operations with an (any one) open database connection.

    Compare all the tables you like, just don't close the connection in-between table comparisons.

    Cheers, Sören

    (hooked on the Perl Programming language)

[reply]

      Yes we can do all the table validations using one connection.

      Since to reduce the comparison time, i am runnig this script on different servers for different tables. Now i cannot have a database connection persistent across servers.

      At the maximum, i can have one open connection per server. But again controlling the opening and closing of connections on different servers would be difficult unless you have a control mechanism.

      This is the reason why we are trying to implement connection pooling

[reply]
Re: Connection Pooling
by space_monk (Chaplain) on Jun 10, 2013 at 09:56 UTC

    Just in case this is an XY Problem (see XY Problem), perhaps you could explain why you are comparing 200 database tables in the first place and what you are trying to get out of that comparison?

    If you spot any bugs in my solutions, it's because I've deliberately left them in as an exercise for the reader! :-)
[reply]

      We have the tool in production. We are actually trying to do some enhancements to improve performance. To your question of why we need to compare 200+ database tables, this is part of data validation during database migration from one database to another.

[reply]

Back to Seekers of Perl Wisdom