Re: Performance of large queries

[prev] [thread] [next] [Date index for 2004/07/15]

On Thu, Jul 15, 2004 at 07:00:36AM +0000, cdbi-talk@xxxxxxxxx.xx wrote:
> Hi,
> 
> I just started to use Class::DBI. It is a very nice tool and makes database-handling quite easy. But I have a little performance-problem when I use a 'search' that returns a large set of data (e.g. 2000 objects). In this case the use of Class::DBI slows down the program by a factor of 3-4.
> 
> But in this case I don't need objects to be returned as I don't want to modify them; I simply want to retrieve the data. Is there a way to tell Class::DBI to return either the sth-object or is there an other way to improve the performance of the search?

AIUI the issue is not that you are getting objects back but that
search() is internally implemented to only get ids and then each id has
to go back to the database to do a seperate retrieve() for the
'Essential columns'.  It may very well be that this is no longer true, this
is just what it seemed to do last time I looked.  I've often
wondered if there was a way I was unaware of to (as an option) inject all the 'Essential'
data directly into the objects or if there was any big reason this would
be bad - can anyone shed any light on this?

Anyway, the general answer to your query is that you can use set_sql to
write whatever SQL you like or a more general solution would be to write a custom 
version of search() (though call it something else, e.g. search_raw) for your
own Class::DBI wrapper that gives you back the results of our search as
some useful (with an eye towards forwards compatibility) data structure.

Colm