Summary:
-Do the $Sth->execute() read the entire table and put it into an array in memory? - normally, yes
-Do the $Sth->fetchrow() read one row from the array in memory? Yes, makes copy of what the DBI already has. The DBI's array is also normally completely memory resident, so this is a memory to memory copy - which albeit fast can take "significant time" when working with large data structures. No matter how fast something is, if you do enough million's of times, it adds up!
=====
The scalar value of $Sth->execute(); is number of results. Note that this can be the value "0E0", which is Perl's way of returning a "true, but zero number result" (meaning that the execute statement worked). In order to know the number of results, the DB has already produced the complete result set (or at least knows how many rows there are) and this would normally be memory resident.
Update: I think the caveat here is "if it can do so". I think there are situations where the DB will have to "repeat work" or become less efficient if not enough memory is available to it. For example in SQLite, it is possible to dynamically increase the DB's cache size - this can help enormously when say indexing a large table. I am not sure that there is a "one size fits all" answer to your question. But for example, I think the default cache size for SQLite is 20MB. A couple of MB table is certainly memory resident after the execute.
If you are interested in highest performance, use $sth->fetchall_arrayref(); This gives you a reference to the DBI's memory with the result set. You can modify a single row and send that single row back with an SQL update. This avoids you having to make a local copy of what the DBI already has in memory - and this can be significant if you are working with large result sets.
fetchrow() gets you a copy of what the DBI already has and there are completely valid and fine reasons for doing it this way. Use more complicated mechanisms when performance matters.
If I assign and keep the ref returned by fetchall_arrayref() in scope, I can make a new query and get a new array_ref - or at least that is how some of my SQLite DB codes works. I compare these result set(s) together in ways that the DB can't easily and I can modify say row 325 of $result_ref1 and execute another SQL command to update it in the DB without even having to make a copy of it outside of the DBI's memory.
You are asking this question for some kind of reason. If you explain more about what you are doing, I'm sure some of the Monk DBI gurus will have some ideas about how to do it at least differently, if not better.
In reply to Re: Fetchrow question
by Marshall
in thread Fetchrow question
by alainb
| For: | Use: | ||
| & | & | ||
| < | < | ||
| > | > | ||
| [ | [ | ||
| ] | ] |