Very slow upsert operation

  • 23Views
  • Last Post 2 weeks ago
0
votes
Ben West posted this 2 weeks ago

I'm attempting to upsert Zendesk users via a CSV source.

My CSV contains a mix of existing users and new ones.

I'm looking up the user ID based on their name and another field, the last four digits of their social.

It takes about 8 minutes to do one row, 16 minutes to do two. For grins I decided to submit all 2,000 records and it's been running four days.

 

What am I doing wrong? The operation in question is 29073973

Order By: Standard | Newest | Votes
0
votes
Mariia Zaharova posted this 2 weeks ago

Hi Ben!

This package was stuck for some reason. For now, it is stopped.

Please run it again and let us know the results. Please note, the usage of lookup mapping increases execution time due to the additional requests to the lookup table. As we can see you are using two lookup mappings in your package: for Id and for OrganizationId. For OrganizationId field we recommend you to use a cached lookup - for this, open your package editor, find mapping for the OrganizationId field and select the "Use cache" checkbox in the Lookup options.

 

Looking forward to your reply.

 

0
votes
Ben West posted this 2 weeks ago

Cached or not, it doesn't seem to help. I think Skyvia's lookups are the issue, from reading other people's comments with other systems like Mailchimp.

 

Based on this, we will likely be moving to a different product, unless you can improve the speed.

0
votes
Mariia Zaharova posted this 2 weeks ago

Hi Ben!

 

We are sorry to find out that you are not satisfied with our product.

 

Using a cached lookup can significantly reduce package execution time. When the cached lookup is used, the necessary fields from all rows of the lookup object are queried and cached on Skyvia, and lookup is performed against this cache. Depending on the number of rows in the lookup object and the number of imported rows (source records), this may provide a performance gain. 

If you tried cached lookup only for the OrganizationId field, we would also recommend you to enable it for the target Id field.

 

Best regards,

Mariia

0
votes
Ben West posted this 2 weeks ago

We did try the cached option on the target id field and it made no appreciable difference.

 

When you remove lookup fields, the upsert operation only takes 22 seconds to do a great many rows.

 

For this reason, I am considering using a staging table on a local SQL server. I would use Skyvia to make a local copy of our data, then merge our local system data into that staging table, retaining the unique identifying fields on the destination server.

Close