All Forums Database
Soumyatg 9 posts Joined 07/08
21 Jul 2016
Consequential inserts into large fact table performing very slow inside stored Procedure
 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?

22 Jul 2016

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.

VandeBergB 182 posts Joined 09/06
22 Jul 2016

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?

Some drink from the fountain of knowledge, others just gargle.

dnoeth 4628 posts Joined 11/04
22 Jul 2016

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.


Soumyatg 9 posts Joined 07/08
24 Jul 2016

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.


You must sign in to leave a comment.