any hints for improving update query performance

Moderator: NorbertKrupa

Post Reply
bhupal
Newbie
Newbie
Posts: 11
Joined: Wed Apr 03, 2013 9:30 am

any hints for improving update query performance

Post by bhupal » Wed Jun 19, 2013 3:38 am

HI all

i have two tables . both are having references.

i need to update one table bye joining those two common columns.
first table has 4crors of data and second one 1.5 crors of data

i had written a query

update table_name set column_name=(select column_name from table_name2 where table_name2.matching_column=table_name.matching_column)

for this query its taking more than 2.30 hrs time. here i canceled the query.

please any body can help me in this regard to improve query performence.

can i use /*+ direct */ command in update query.

thanks in advance

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: any hints for improving update query performance

Post by nnani » Wed Jun 19, 2013 6:56 am

Hi bhupal,

Yes you can use a direct keyword in update query. This will write all the records onto the disk instead of the memory and then to disk.
You can also try with the merge command in Vertica.

Code: Select all

update [ /*+ direct */ ] table_name set column_name=(select column_name from table_name2 where table_name2.matching_column=table_name.matching_column)
1. Make sure no other queries are running while your updates are going.
2. Try assigning a customized resource pool for your query.
3. Make sure your tables are distributed on 'matching_column' so that all reside on the same node and there is no re distribution of data.
4. Analyze the explain plan

Hope this helps
nnani........
Long way to go

You can check out my blogs at vertica-howto

Post Reply

Return to “New to Vertica”