Consequential inserts into large fact table performing very slow inside stored Procedure

Database
Enthusiast

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

Hi,

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?

4 REPLIES
Teradata Employee

Re: Consequential inserts into large fact table performing very slow inside stored Procedure

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.

Enthusiast

Re: Consequential inserts into large fact table performing very slow inside stored Procedure

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?

Senior Apprentice

Re: Consequential inserts into large fact table performing very slow inside stored Procedure

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.

Enthusiast

Re: Consequential inserts into large fact table performing very slow inside stored Procedure

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.