Although your program seems quite simple, it is not obvious where the performance bottleneck is and there are many possibilities within your program, the modules you use and the databases you are accessing. A good place to start might be to profile the execution of your program and then focus your attention on whatever takes the most time.

I have found Devel::NYTProf to be easy to use and very informative.

I would first try to determine whether most of the time is spent executing your program or waiting for the databases to respond. As your program is quite simple, I suspect the latter.

You may find that only the selects or only the inserts are slow. Without knowing anything about your databases or systems, it is hard to say anything other than: there are many possibilities. The underlying network or servers may be overloaded. You may have triggers on your database that are consuming time and resources. You may be querying a poorly optimized view.

Rather than trying to identify all the possibilities in advance, simply start to track down what is taking the time and follow the leads.

Eventually, you will identify something that is consuming a lot of time. You should be able to identify what is taking the most time quite quickly.

The next operation step is to try some alternative approach in hope that it will be faster. think about why that operation is slow and what changes you could make that might be make it faster. Then try some of your ideas.

You are already using Benchmark. Keep good records as you progress so you know what changes you made and what impact they had on performance.

Be prepared to back out changes. Not everything you try will be an improvement.

It is not obvious what your underlying databases are. They may have tools that will help you understand why the queries you are running take so long to execute. If they do, you could use them or get help from someone who is familiar with them.

If you find you are not making good progress, get help from others, providing the information you have gathered and the alternatives you have tried.

In reply to Re: DBI Performance help by ig
in thread DBI Performance help by cool79s

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.