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

Greetings, all.

I've written an application for my company that takes a batch schedule as represented in a database and converts it to a postscript file via GraphViz. The problem that I'd like to fix is that some of our databases reside in other parts of the world (I'm in Minnesota, some of the databases are in Sydney, Australia), thus making the application run very slow. Running the whole application on the remote server is not an option, as GraphViz is not installed on the remote machine. In your experiences, how have you handled running a lot of database queries across a slow line?

thanks,
thor

Replies are listed 'Best First'.
Re: Remote SQL queries
by dws (Chancellor) on Jun 25, 2003 at 19:53 UTC
    In your experiences, how have you handled running a lot of database queries across a slow line?

    It really depends on how timely you want the report to be. Your options are basically:

    • Query on Demand. Go after the data whenever you need it. This guarantees fresh data, at the expense of a slow running application.
    • Schedule the Query. Arrange to run the query in advance of your application, on some set schedule. Your data won't be as fresh, but the application will run quicker since it only has to deal with cached data.
    • Schedule a Remote Query. Run a scheduled query, but do it remotely (on the database server). This is a win if you have a slow line, but have to process a lot of data to get the summary that your application will use. The results of the remote query can be retrieved on demand, or pre-sent so that your application doesn't have to suffer retrieval time.

[reply]
Re: Remote SQL queries
by adrianh (Chancellor) on Jun 25, 2003 at 19:52 UTC

    Either move the renderer to the database machine, or move the data to the renderer. Since the former is out of the question you'll have to do the latter ;-)

    Get a bulk download of the necessary data, then use a local copy of MySQL or DBD::SQLite and run your renderer off that.

[reply]
Re: Remote SQL queries
by cbro (Pilgrim) on Jun 25, 2003 at 19:55 UTC
    You didn't specify which SQL client you use, but I know MySQL allows the user to 'mirror' databases. Maybe you should set something like that up. That way, you'd be able to run everything local (e.g. your queries and your GraphViz program).

    HTH,
    Chris
[reply]
Re: Remote SQL queries
by husker (Chaplain) on Jun 25, 2003 at 20:31 UTC
    Stored procedures, especially if you think the data returned by the query will be very small compared to all the data in the tables.
[reply]

Back to Seekers of Perl Wisdom