CODEWITHSUNDEEP
sqlpostgresql
cblanto7
cblanto7

Reputation: 169

How can I speed up this update and set?

I have two tables, x and y

I have created a query that updates x.col_p with the sum of column y.col_p where they both match a particular date and ID.

UPDATE x 
SET col_p = (
    SELECT sum(col_p) 
    FROM y 
    WHERE concat(x.mmonth, x.dday, x.yyear) = (concat(y.mmonth, y.dday, y.yyear))
        AND x.ID = y.ID
    )

This successfully updates the colums with the correct values but it takes 15 minutes to execute and I have ~40 more similar queries to run like this... So how could I go about speeding this up? I think the functions inside the set and where are killing me.

Upvotes: 2

Views: 59

Answers (2)

Mehmet Kaplan
Mehmet Kaplan

Reputation: 2342

The things you can do are:

  1. Assure the table yis reached by a primary key (of which column y.id is the first column).
  2. If you don't have any null columns, it seems you may not need concat. That is why rather than concat(x.mmonth, x.dday, x.yyear) = (concat(y.mmonth, y.dday, y.yyear)), try (x.mmonth, x.dday, x.yyear) = (y.mmonth, y.dday, y.yyear).
  3. After you are sure, y uses a proper primary key, you can make the update by giving a parallel hint to x. For example, in order to use 4 parallel processes, update /*+ PARALLEL(x 4) */ x ... For this step be careful to use system resources properly.

So to wrap the things up, what I am suggesting is;

  • reach to the table you go "a lot of times", i.e. table y via primary key
  • reach to the table you make full scan, i.e. table x via parallel processes.

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269953

I would suggest writing the query as:

UPDATE x 
    SET col_p = (SELECT sum(col_p) 
                 FROM y 
                 WHERE x.yyear = y.yyear AND x.mmonth = y.mmonth AND
                       x.dday = y.dday AND x.ID = y.ID
                );

Then be sure that you have an index on y(ID, yyear, mmonth, dday, col_p).

Upvotes: 3

Related Questions