Improving the Maps Execution Time

[Vishal1982]

Hello All.
I am using the TX 8.0 Map Designer for data transformation from
multiple tables to one table ,but while transferring the data it is
taking more time(i.e. near about 24-30 hours to transfer
approx 230000 records)so please let me know what we can do
to improve the performance of data transformation,also i want
to know the ideal time require to transfer 250000 records.

Thanks in Advance!!!

[jvmerc]

Have you played with burst mode and page size/count settings? Have you tried writing your output to files to determine how much time is used on the output side?

Outside of that, without seeing your code, it would be difficult to give you solid answers.

I can tell you that I have a process that was pretty fast for normal files (files less than 15mb). When I needed to load the a file that was more than 60mb the db load time went up substantially. I ended up writing a sqlldr script that was executed by a map which reduced the load time by as much as 80%. If all elese fails.... try that.

[Vishal1982]

I have tried with the burst mode and page size setting but it still not improved the performance ,can you share the script of sqlldr with me so
that i will try to execute that under my maps.

[jvmerc]

This is a very simple example. I'm sure you could do this several ways. You'll need SQL Loader installed on the machine you will run the map on.

the item below is in a batch file....

sqlldr id/pw@sid control=load_data_cntl_file.CTL LOG=path\file.LOG bad=path\file.BAD

The item below is the 'ctl' file.

LOAD DATA
INFILE '.\file.ext' --name of file containing the data to load
APPEND
INTO TABLE table_to_load TRAILING NULLCOLS
(
ATTRIBUTE_1 CHAR TERMINATED BY "|" OPTIONALLY ENCLOSED BY'~',
ATTRIBUTE_2 CHAR TERMINATED BY "|" OPTIONALLY ENCLOSED BY'~',
ATTRIBUTE_3 CHAR TERMINATED BY "|" OPTIONALLY ENCLOSED BY'~',
ATTRIBUTE_4 CHAR TERMINATED BY "|" OPTIONALLY ENCLOSED BY'~',
ATTRIBUTE_5 CHAR TERMINATED BY "|" OPTIONALLY ENCLOSED BY'~',
ATTRIBUTE_6 CHAR TERMINATED BY "|" OPTIONALLY ENCLOSED BY'~',
ATTRIBUTE_7 CHAR TERMINATED BY "|" OPTIONALLY ENCLOSED BY'~',
ATTRIBUTE_8 CHAR TERMINATED BY "|" OPTIONALLY ENCLOSED BY'~',
ATTRIBUTE_9 CHAR TERMINATED BY "|" OPTIONALLY ENCLOSED BY'~',
ATTRIBUTE_10 CHAR TERMINATED BY "|" OPTIONALLY ENCLOSED BY'~',
)

[Vishal1982]

Hi ,
Apart from the SQL Loader , i want to know is there any other way to improve the map execution in Map Designer or through event server.

[jvmerc]

How fast does the map run if you just write the data to a file verses loading to DB?

Are you mapping and loading at the same time or mapping to one output card and loading from another?

Are you doing queries while mapping to the output? If, for example, you are assigning sequence numbers for keys as you map the data and each sequence requires a statement you could attempt to determine how many sequence numbers you'll need up front. Then perform one query that returns all the numbers you require to a separate card. That would speed things up.

Have you left trace files on? Both the map and dblookup?

Otherwise I'd have to see the map. There are all sorts of ways to build speed. Is there a reason you don't want to use sqlldr? I was able to load data for over 100,000 members into up to 13 tables in under 10 minutes.

[maltu]

I would start breaking down the processes, time taken for each input card to read, time taken to translate and write to target. Check how it pans out.

Generally, reading data could be tme consuming while extracting from diff DBs or source tables. See if you can fine tune the SQL that is being used..For instance or you extractiong only the required columns or extracting using a generic SQL statement. Also check if you could write a proc with the DB and invoke the proc from the map, that helps in most situations.

Next would be how you writing to the target, again a proc would certainly speed things up, fine tune the proc to be as specific as possible. You could go further and analyse how many function calls are being used with the mapcode..optimize them if possible.

Without looking at the actual code, all the above are guesstimates, however customised specific solutions are better once the root cause is found for the performance problem.