Glad you like the approach Matt :) Actually anothe...

2011-12-23T20:55:50.462+01:00

Glad you like the approach Matt :) Actually another point that I wanted to add is that this optimization will also be pushed to non-RDBMS sources, eg. CSV files, since the DC query layer is programmatic, not SQL-based. So the materialization of the actual SQL query or the instructions to the CSV reader happens at a later stage (using MetaModel).

Obviously not all filters can use this approach. We also have some filters like the JavaScript filter which are completely scriptable by the end user and that is not translatable to a query. In those cases we simply process the filter in the engine.

You're right in that often time it's not even optimal to push transformations to the query. My general rule of thumb is: If it decreases the amount of data being transmitted (I/O), then do the optimization, if not, then do it in the row processing engine instead. I think you have the same strategy in Kettle? That's why we so far we don't have a "push down" optimization technique for transformers in DC, since these will grow the size (add columns) of the dataset and that will increase I/O between DB and client. And besides, DC's (and Kettle's) engine is multi-threaded, while most RDBMS'es dispatch every query in a single thread.

Nice! For filters it makes sense in most but not ...

2011-12-23T17:47:22.563+01:00

Nice! For filters it makes sense in most but not all cases to filter in the database. However, making this optional seems like the thing to do since there are a lot of RDBMS situations out there, indexes, nework speed and so on need to be considered.
The thing is, I have unfortunately seen a lot of cases where the database filtered, summed, grouped and counted slower than the data could be read over JDBC. That in turn allows tools like DataCleaner and Kettle to work faster on the raw data in those cases... ;-)

Comments on kasper's source: Push down query optimization in DataCleaner

Glad you like the approach Matt :) Actually anothe...

Nice! For filters it makes sense in most but not ...