Here i am trying to update application with new set of QVDs. Script execution works fine, if i limit the load for 2 million records by using debug wizard. When i applied full data reload script Execution freezes at one particular table and at particular record point as shown in below pic. can any one help me how to overcome this issue. PFA Image.
Note: I checked Keep progress open after reload form user preferences
Here Freeze occurs in the middle of the script, As i mentioned in screen shot it stops at temp_Days_Worked table. Here freeze means it is not showing any process running, i waited for 20 mins but process is at same place where it is before, i have gone through the RAM usage it is 4.5 GB at that time . I am using task manager to come out from this loop.
Perhaps you simply have to have a little more patience than 20 minutes. But you could also be doing something in the script that could be done in a less costly way. Can you post the script? And how much RAM is still available (free) at that time?
Well, you're creating a lot of temporary tables to create new fields and then join that table to the original source table. That is expensive in terms of calculation time and memory usages. You should try to create these fields in preceding loads. If that's not possible you'll simply have to accept the calculation time penalty. Ram usage doesn't seem to be a problem on your machine.
I found the problem where the script freezes. On the script i am using subfield function in nested if clause to perform transformations. Here subfield function won't work for large data sets. if i use Distinct keyword in front of date field it works fine.