Welcome, Guest
Username: Password: Remember me
Data Warehousing and Data integration
  • Page:
  • 1
  • 2

TOPIC: Speed problem when updating records

Speed problem when updating records 8 months 3 weeks ago #18532

  • kp007
  • Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
  • Posts: 5
  • Thank you received: 0
Dear all,

After using the ETL for a while I'm starting to have a concern about the speed of it. I have set up a ETL transformation for my spreadsheet and it adds records in really quickly. However, when I tried to have "update record" selected in the writer it’s actually very slow. I have asked colleagues that have used this tools before, and the speed of it seems alright for them when they are doing updates. So I am posting to inquire if there are something wrong that I’m doing with my transformation, or if it is related to the source file that I have. (I am doing a 18 tabs transformation which is taking almost half an hour now)

Please see attached for a sample of my source file, ATS, and a screenshot of my access output and Update setup.
Attachments:

Please Log in or Create an account to join the conversation.

Speed problem when updating records 8 months 3 weeks ago #18533

  • admin
  • admin's Avatar
  • Away
  • Moderator
  • Moderator
  • Deeds not Words
  • Posts: 7512
  • Karma: 33
  • Thank you received: 409
Update is always slow operation especially for very large tables.

You might speed it up by adding index on update fields.
Here is the article on how it can be done for access database
support.office.com/en-us/article/create-...3a-9dda-38c6c4f1a0ce
Mike

Please Log in or Create an account to join the conversation.

Speed problem when updating records 8 months 3 weeks ago #18534

  • admin
  • admin's Avatar
  • Away
  • Moderator
  • Moderator
  • Deeds not Words
  • Posts: 7512
  • Karma: 33
  • Thank you received: 409
Please zip files before attaching them next time
Mike

Please Log in or Create an account to join the conversation.

Speed problem when updating records 8 months 3 weeks ago #18550

  • kp007
  • Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
  • Posts: 5
  • Thank you received: 0
Hi Mike,

Thanks for the response. I have tried adding index into my ETL processor but it remained relatively the same speed (still 30min total and 2 min for each table). In terms of table size, I've tried another document that have relatively the same table size and tab number but it loaded quite quickly (3 sec per tab, 54 sec in total). I've attached a sample with one tab for the faster document here.

File Attachment:

File Name: ICtesting.xlsx
File Size:54 KB


Is there any limitation that ETL have that could cause this problem?

Thanks
Attachments:

Please Log in or Create an account to join the conversation.

Speed problem when updating records 8 months 3 weeks ago #18552

  • admin
  • admin's Avatar
  • Away
  • Moderator
  • Moderator
  • Deeds not Words
  • Posts: 7512
  • Karma: 33
  • Thank you received: 409
We have created a WIKI article for you explaining how it works.

www.etl-tools.com/wiki/knowledgebase/update_is_slow
Mike

Please Log in or Create an account to join the conversation.

Speed problem when updating records 8 months 2 weeks ago #18561

  • kp007
  • Topic Author
  • Offline
  • Fresh Boarder
  • Fresh Boarder
  • Posts: 5
  • Thank you received: 0
Dear sir,
We have tried the method as suggested by you in the article - www.etl-tools.com/wiki/knowledgebase/update_is_slow

However, we have a problem. We are getting a syntax error. Attaching the SQL file for your reference. Can you please take a look at this and the ETL file and the source data file that we shared earlier. It will indeed be very helpful to us if you can run a test at your end and advise.

Additionally, our data size is not very large.We have similar tables that have the same size but run much faster ( in terms of seconds instead of minutes ).So I don't know if the size will be the cause of the issue.
Attachments:

Please Log in or Create an account to join the conversation.

  • Page:
  • 1
  • 2

We have 267 guests and 4 members online