0

When i try to capture Incremental Load in One SQL transaction. Update is not working. Basically, It Keeps on Executing for infinite time for 90k rows.

Input SQL transaction

BEGIN;
INSERT INTO IncrementalLoad_Dest.dbo.tblDest
(ColID, ColA, ColB, ColC)
SELECT s.ColID, s.ColA, s.ColB, s.ColC 
FROM IncrementalLoad_Source.dbo.tblSource s
LEFT JOIN IncrementalLoad_Dest.dbo.tblDest d ON d.ColID = s.ColID
WHERE d.ColID IS NULL ;
UPDATE d
SET
d.ColA = s.ColA
,d.ColB = s.ColB
,d.ColC = s.ColC
FROM IncrementalLoad_Dest.dbo.tblDest d
INNER JOIN IncrementalLoad_Source.dbo.tblSource s ON s.ColID = d.ColID
WHERE (
(d.ColA != s.ColA)
OR (d.ColB != s.ColB) 
OR (d.ColC != s.ColC)
);
END;

Update statement is not executing from psql utility. Why it keeps on executing not even throwing the error.

Also, tried checking Active lock on target file. No lock exists.

Please consider column in double quote around each column and SQL transaction written in greenplum SQL syntax.

Any help on it would be much appreciated .

NEO
  • 389
  • 8
  • 31

1 Answers1

1

Try this:

UPDATE IncrementalLoad_Dest_dbo.tblDest d
SET d.ColA = s.ColA,
    d.ColB = s.ColB,
    d.ColC = s.ColC
FROM IncrementalLoad_Source_dbo.tblSource s 
WHERE s.ColID = d.ColID
and ((d.ColA != s.ColA) OR (d.ColB != s.ColB) OR (d.ColC != s.ColC));

You basically have to change your SQL Server syntax to be more ANSI compliant so it works in Greenplum.

Also, you may want to avoid UPDATE statements. You'll definitely want to VACUUM before you execute an UPDATE. You'll probably want to make the tables Append Optimized (appendonly=true) when you create the table.

Jon Roberts
  • 2,068
  • 1
  • 9
  • 11
  • Is that any future possibility of getting UPSERT action in greenplum database ? – NEO Feb 26 '17 at 21:54
  • I did same way above mentioned. situation same it keeps on executing. can we discuss on chat ? – NEO Feb 26 '17 at 21:55
  • Also., checked just after canceling the update statement found locks in file.:https://paste.ofcode.org/g36qTr32RvxRUsSgAyAdeT – NEO Feb 26 '17 at 22:01
  • "not executing" and "not completing" are two different things. You should make sure both tables are distributed by the same key. Make sure you have analyzed and vacuumed the tables. If you have already deleted or updated data, you may have a very bloated table which is just adding to how long it takes to run. You might need to rebuild the table(s). – Jon Roberts Feb 27 '17 at 14:51
  • In this case "Not completing. Source table is External table. so, it is not using distributed clause while destination table is regular table. vaccum and analyzed already ran on it. and i also did rebuilding the regular table. – NEO Feb 27 '17 at 18:17
  • Sorry. it is my mistake. I corrected it and ran again it is working fine I just Updated (~90k rows) – NEO Mar 01 '17 at 13:46
  • Thanks a ton Jon. it is working for me. can You please suggest - How do i use this transaction to perform Insert and update in one shot. can i use vaccum and analyze syntax between them then keep everything in Begin and end block – NEO Mar 01 '17 at 13:49
  • delete and update would be two statements but you can do it in one transaction. After that transaction, then you vacuum. I recommend using analyzedb to keep stats up to date. – Jon Roberts Mar 01 '17 at 13:52