Update Database Table is extremely slow?

1

Trying to update two columns in a simple Snowflake table with 106,000 rows took so long I abandoned it after one hour?

Doing the same with an Update statement in Snowflake took 2.2 seconds?

What am I doing wrong?

image
image853×675 69.2 KB

2

@Hendrik_Lombard what database are you working with? And how many rows does the input dataset have?

3

Snowflake table 570,000 x 20

4

Since Snowflake doesn't support queries with multiple statements by default, EasyMorph executes a separate SQL command for each unique value in the [TAX_ID] column.

I'm curious about what your UPDATE statement looks like?

5

update FINANCE_APPLICATIONS.COMMON.EM_MERCHANT_JOURNALS
set SALE_AMOUNT_MER_CCY = FINANCE_APPLICATIONS.COMMON.TEMP_STRAYS.SALE_AMOUNT_MER_CCY,
SETTLEMENT_AMOUNT = FINANCE_APPLICATIONS.COMMON.TEMP_STRAYS.SETTLEMENT_AMOUNT
from FINANCE_APPLICATIONS.COMMON.TEMP_STRAYS
where FINANCE_APPLICATIONS.COMMON.EM_MERCHANT_JOURNALS.TAX_ID = FINANCE_APPLICATIONS.COMMON.TEMP_STRAYS.TAX_ID;

6

This query is not the same the ones, executed by the "Update database table" action. The later executes a single query for each unique combination of values in the matched columns.

Also, you can execute the provided query with the "Custom command" command in the "Database command" action.

7

I now have to make a temporary table then append it.

INSERT INTO
FINANCE_APPLICATIONS.COMMON.BLACKLINE_JOURNAL_DATA
SELECT
*
FROM
FINANCE_APPLICATIONS.COMMON."temp_BL_journaldata";

8

Yes the queries are not the same.
I assumed you wanted the general gist of how an Update query works.

The second query uses a temporary table to append data very fast.

I still don't understand why you cannot make your Update Database action as fast as these two options?

9

I wanted to understand what you meant by "Doing the same" in the initial post.

We don't want to use a temporary table as the default method for the update action for two reasons:

  • A user might have privilege to update an existing table, but not to create a new table
  • Some databases might not support updating from another table

We will discuss internally if we should add that as an optional method.