according to the guide Loading data from database | EasyMorph I use the select matching action when I need a subset of a table with a lot of records, usually everything run smooth but today I encountered a problem of performance that I don’t understand.
Table A contains 1 single record with ID 128;
I “Select matching database rows” on a table B with 5M rows;
Performance on EM action: 7 seconds.
Running the query "SELECT * FROM tableB WHERE MyJoinId=128: 0.00047 seconds.
Someone can explain me what is happening?
Also: if “Select matching database rows” isn’t the correct action, how can I filter a table with dynamic data from another table?
Since the “Select matching database rows” action should be able to handle a big number of matched values, it creates a temporary database table and exports all the matched values to that table. So it’s not practical to use that action when you only have a single row of values to match.
Instead, you can use the “Import from database” action where you can specify your condition with a parameter or a first column value from the input table. Like this:
thanks for the quick reply, alas we’re using v4.x and can’t switch to v5 due to the fact that we’ve to try it before put it in production (and we can’t do these test atm).
Until the update, we’ll go using custom SQL, but thanks to show us that in future we could use a correct action of EM.
In version 4.x you can already use a parameter in the query (it can be seen in @andrew.rybka's screenshot). Version 5.0 allows using first column values in addition to parameters.
To use a parameter in a query, put the action with the query in a separate module, define a parameter in the module and use the parameter in the query. Then, in the main module, use the "Iterate" action on the single value and assign it from a column to the parameter of the module with the query.
The "Select matching DB rows" action creates a temporary table in the target database, populates it with values to filter, performs a SELECT ... WHERE ... EXISTS query, and then deletes the temporary table. It looks like creating/deleting the temporary table takes most of the time, not the query itself.
