Run_Id Check_Point Check_Point_Desc Records_Processed time_elapsed
16 201,607,122,102 1 Delete From Xyz 110,013,266 0:00:28.0000
15 201,607,122,102 2 INSERT INTO Xyz FROM abc6 42,007,343 0:06:27.0000
14 201,607,122,102 3 INSERT INTO Xyz FROM abc5 66,254,366 0:21:33.0000
13 201,607,122,102 4 INSERT INTO Xyz FROM abc4 1,276,919 0:01:11.0000
12 201,607,122,102 5 INSERT INTO Xyz FROM abc3 194,723 0:00:22.0000
11 201,607,122,102 6 INSERT INTO Xyz FROM abc2 177,291 0:00:14.0000
17 201,607,122,102 6 COLLECT STATS ON xyz 0 0:00:01.0000
10 201,607,122,102 7 INSERT INTO Xyz FROM abc 102,857 0:19:00.0000
I have a stored procedure which is populating a large fact table. This fact table is sourcing data from 4 large source views. these source views are pretty complex in themselves, and the selects are also slow, but the main problem is the insert operation at the merge step. have very limited access to check most of the stuff. but what could be the work around for 4 large inserts. I thnink volatiles cannot be used in SPs. Also, since the sources for the isnerts are complex views, am not sure , how the source PI would work. Any suggestions?
1) Views will use the same PI as the base tables in joins.
2) I think it is in fact possible to use Volatile Tables in Stored Procedures.
3) For performance improvement you can have a look at the explain plan to see what is the bottleneck.
Check the performance of your complex source views, that SQL is going to be run. I'm not sure how you'd use volatile tables here. Can you post the DDL for the target table, SP and explain plans?
As the first insert is fast and the following geting slower you might have a SET table with lots of rows per NUPI.
And you seem to run those inserts sequentially, the first will be using FastPath (without journal), but the following need to maintain the Transient Journal.
Try to wrap the inserts into a one transaction or a MultiStatement Request.
Thanks Dieter. but the target table is already populated even in the 1 st insert , its not an empty table. Still I can see that the 1st insert is actually better. If I pull all the inserts into 1 transaction, I will have to do a union all between the 4 complex views, which again will be perf intensive. @vandeBegB..I have the code etc on remote machine. Will try to provide more useful info here.