Re: fetch row or fetchall

I've got the same problem, and I can just say that it depends all on the data and how complicated the where clause is.
my select statement could return 200_000 rows as maximum (maybe more), so in my case I don't have an option; I have to use LIMIT (or do a count first) otherwise these rows will cause the memory to grow too high (even before $sth->fetch* the rows take memory).

In your case, if the where clause is complicated, the query will be slow, so doing

select count(*) from table where ...;
select bla from table where ... LIMIT 100;
[download]

will be slower than select bla from table where ...;

It's probably the best to benchmark it on your table as results highly depend on your table size, data and oher things.

Comment on Re: fetch row or fetchall Select or Download Code

Replies are listed 'Best First'.
Re^2: fetch row or fetchall by steves (Curate) on Nov 10, 2004 at 13:37 UTC
Note that the DBI RowCacheSize database handle attribute can often help you balance your memory/speed tradeoff for large queries. But it depends on the underlying database -- not all can make use of it. In practice, I have found some significant differences in query time/memory when changing this for queries that return large numbers of rows. For significant database applications I find it nearly impossible to write SQL that works across all. I always end up having to make use of database-specific functions or other constructs. If your application is only going to run on one database it's probably fine to make use of LIMIT, TOP, or Oracle's ROWNUM. It depends on whether your priority is speed and function or portability across databases.	[reply]

Replies are listed 'Best First'.

Re^2: fetch row or fetchall
by steves (Curate) on Nov 10, 2004 at 13:37 UTC

Note that the DBI RowCacheSize database handle attribute can often help you balance your memory/speed tradeoff for large queries. But it depends on the underlying database -- not all can make use of it. In practice, I have found some significant differences in query time/memory when changing this for queries that return large numbers of rows.

For significant database applications I find it nearly impossible to write SQL that works across all. I always end up having to make use of database-specific functions or other constructs. If your application is only going to run on one database it's probably fine to make use of LIMIT, TOP, or Oracle's ROWNUM. It depends on whether your priority is speed and function or portability across databases.

[reply]