Hi in fact the mais reason for this question is because we are experiencing performance issue, not only in speed but also the sql database who goes at 90-100% frequently and for a long time.

We searched on the web and there where people saying using Win32::ole will improve performance.

So I did a test on a test database on another server

The database is Microsoft SQL

The scripts start by using dbi:ODBC
here what it does

select var1,var2,var3 from table
then
loop until var1 is empty
($var1,$var2,$var3) = $Sth1->fetchrow();

For 100000 records it took less than 2 seconds

I did the same test but using Win32::OLE->new('ADODB.Connection');

I used the same select
select var1,var2,var3 from table
then i did the same loop and used
$var1 = $RS->Fields(0)->value;
$var2 = $RS->Fields(1)->value;
$var3 = $RS->Fields(2)->value;
$RS->MoveNext;

for the same 100000 records it took 16 seconds this time

So I suspected that something was related to having too much memory usage so I did in the same script

test1 using dbi:ODBC:test
test2 using Win32::OLE->new('ADODB.Connection');
test3 using dbi:ODBC:test

and the result was identical both test1 and test3 took
less than 2 sec and test2 still took 16 secs

So I wondered if everything is in memory why so many difference.

In reply to Re^2: Fetchrow question by alainb
in thread Fetchrow question by alainb

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.