Hi,
I use select matching database rows on about 2800 rows to match and it is very slow.
All fields on which I match have indexes in the database.
What could be the problem ?
Thanks
Nikolaas
Hi,
I use select matching database rows on about 2800 rows to match and it is very slow.
All fields on which I match have indexes in the database.
What could be the problem ?
Thanks
Nikolaas
Hi Nikolaas,
How much time does the action takes?
And how many columns are matched?
Hi Andrew,
As far as I remember it was about 90 seconds to match 500 records and we match on 4 fields.
About 4 or 5 columns are retrieved from the database table.
I also noticed a large increase of the CPU occupation of the database instance at runtime.
Kind regards,
Nikolaas
So you’re matching 500 EasyMorph rows with a DB table that has 2800 rows?
Hi Andrew,
I shall do another test later. I have tested to match 500 rows in EasyMorph with a table in the database containing about 1.8 million rows.
Looks like an expected performance to me. The action exports EasyMorph data to a temporary table and then uses SELECT … FROM target_table WHERE EXISTS( ) query to match values in the target and the temporary tables.
You can try to log the actual query, executed by EasyMorph, and then use EXPLAIN or a similar feature to check if all the indexes are being used.
As a workaround, you can try to export your matched values to a temporary table with the “Export to database” action and then use the “Custom SQL” mode of the “Import from database” action with a SELECT query with an appropriate JOIN clause. But I’m not sure if the query execution time will be smaller.