Re: Fetchrow question

[mje]

in reply to Fetchrow question

I see others have answered your question but I'm not sure I agree with them all. When you say "Do the $Sth->execute() read the entire table and put it into an array in memory?" if you are talking about what happens in Perl or the XS code DBD::ODBC uses the answer is no. In ODBC terms what happens (in this simplified pseudo code is like this):

  you call $dbh->prepare:
  SQLPrepare(select whatever from whatever);

  you call $sth->execute
  SQLDescribeParam(called per parameter and parameters bound with SQLB
+indParameter) - does not happen in your case as you haven't got any
  SQLExecute
  SQLNumResultCols(to find if there is a result-set and how many colum
+ns)
  various calls to SQLDescribeCol and SQLColAttributes to find out abo
+ut the columns

  you call $sth->fetchrow;
  If the columns are not already bound:
    SQLBindCol for each column
  SQLFetch (one row requested from ODBC driver)

  you call $sth->fetchrow;
  SQLFetch (one row requested from ODBC driver)

What happens in the database and between the database and the ODBC driver is difficult to answer as it depends on the driver and database. For instance, with MS SQL Server using the TDS protocol then unless you've changed the default cursor, the database gathers all the rows and sends them down the socket and the driver reads whatever it decides (could be 1 row, could be multiple rows).

When you use fetchall_* DBI calls fetch repeatedly in the DBD and stores the rows.

[alainb]

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.

[Corion]

I would expect manually iterating through a Win32::OLE object to be slower than having a C library do it. Each method call and each property access to a Win32::OLE object has to cross process boundaries.

If you can use ADODB directly, maybe it is faster than ODBC, but I expect database queries to be mainly limited by the speed of the network and the database, not by the local database driver, at least if it has a way of bulk fetching result rows. Maybe the GetRows() method of the ADODB recordset helps you a bit, but I would do this over native DBI instead of trying ADODB.

[roboticus]

alainb:

I generally don't find much difference in speed between using the ODBC drivers and the native Oracle and MS SQL drivers, so I generally use DBD::ODBC.

What strikes me when reading your node is that you're reading some 100,000 records. Is that just for a performance test, or are you typically doing such large reads? I'll read entire tables when I'm creating data dumps for third party vendors, but generally my queries only send/retrieve a couple hundred rows at most. If you're frequently pulling multiple thousands of records from the database, you might be doing too much work in perl and not enough in SQL. Doing work in SQL can be a little tricky, but you can save quite a good bit of I/O that way, and possibly solve any performance bottlenecks.

Update: If you give a bit more detail about the task you're performing, and the types of queries you're doing, we might be able to give more specific advice.

...roboticus

When your only tool is a hammer, all problems look like your thumb.

[Anonymous Monk]

While I cannot comment on either ODBC or ADODB, I heartily recommend the Advanced Perl DBI talk, which discusses squeezing the most performance out on the Perl side, and a lot more.

(Hint: fetchrow_array() copies the values practically for naught. You are better off using fetchrow_arrayref(), especially with the magic added by bind_columns() -- see slides 22-23)

[alainb]

Hi yes the 100000 read was only to do the test and to be able to evaluate the difference in time from one method to another.

The real thing dosent read so much record but its so creazy that we have to do a lot of work.

First the is a keyid who is keep in a table
There also is a table for the header and another table for the details.

To add an header I have to read the keyid table, get the keyid then update the table with keyid +1.

Then I can add the header with the new keyid.

To add a detail I have to do the same:
read the keyid table, get the keyid then update the table with keyid +1
then add the detail with the new keyid.

I know this probably look weird but its a package that we cannot modyfy so we have to work has they define their database.

So Now to gain performance since I know Ho many details i have to add, instead of getting the keyid and updating it by 1 each times I update it once by number of detail line + 1 (header).

Ie : if I have 10 details line + 1 header
I Read the keyId and add 12 to it then update the table.

I add the header with keyid-11
I add the detail line1 with keyid-10
...
I add the detail line10 with keyid