Select matching database rows seems to be slow

Support
1

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

2

Hi Nikolaas,

How much time does the action takes?

And how many columns are matched?

3

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

4

So you’re matching 500 EasyMorph rows with a DB table that has 2800 rows?

5

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.

6

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.