Originally posted by: MansiP

Hi,

My map uses a Sink card which has a rule to Truncate a table. This table has about 1.5 million records and my the sink card takes about 2 hours to truncate the data. But when I directly run the truncate query in Database it takes only 3 seconds to truncate 1.5 million records. Is someone aware of the latency with Sink card?? I am using Mercator 6.7.

Thanks for your help!!
-Mansi
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

the Sink adapter doesn't do anything with the data so there is no latency.
I'm guessing that you're instead using the DBLOOKUP function in that card and calling it for each record you process?

if you run truncate in the database it's going to be a lot faster as all of the data is local - if you process the data record by record remotely it will be a lot slower.

Typically a DBLOOKUP will take 10ms per call - this is roughly in line with your figures.

You could also try profiling your map to check where any delays are occurring.
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: MansiP

Hi Oliver,

Thanks for the information. We are using DBQuery to truncate the records. So does DBQuery act same like DBlookup??

Thanks!
-Mansi
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

yes, DBQuery works in the same way as DBLookup - the only difference between the two is that one adds a new line character to the data from the database, the other does not.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: MansiP

Thank you Oliver...so using -D (delete) on Output card will be faster than this? or can you please suggest any other options.
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

No, changing the output card will not impact the performance - it's the function that needs to change.

Can you do a full profile of the map running and post it to the forum, together with a copy of the map rule?
#IBM-Websphere-Transformation-Extender
#DataExchange
#IBMSterlingTransformationExtender

Originally posted by: MansiP

We are using Mercator 6.7 and not the WTX hence I dod not have Profiler. Can you please let me know your ideas on below.

If I write the Truncate query in MDQ and use it in output card then will the performance improve?
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: SystemAdmin

Can you post the mapping rule with the DBQuery to the forum?
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: MansiP

=DBQUERY("TRUNCATE TABLE ABC"," -DBTYPE ORACLE -CONNECT "DB_Name" -USER "DB_USER" -PASSWORD "+DB_Password)
#DataExchange
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender

Originally posted by: SystemAdmin

I was under the impression that you were truncating a field for each of the 1.5 million records.

If you use the truncate table command in the database the number of records is irrelevant - 1 record will take the same time as 1 million records.

How often does this rule run?
If just once, the problem is elsewhere in your map - you need to upgrade your client and run the profiler.

Is there any other database access in your map? how many records does you map process?
Are you able to post your map and trees to the forum?
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: MansiP

I am sure that the delay is happening on Sink card which is executing Truncate query. Please find the below Audit log which shows that the second output card is taking 2 hours to execute. Second Ouput card is sink card which is using Truncate query.

<SourceReport card="9" adapter="DB" bytes="267610418" adapterreturn="0">
<Message>Success</Message>
<Settings>-VAR ID= **** -VAR TABLE_NAME=*** -DBTYPE ORACLE -MDQ ***.mdq -DBNAME *** -CONNECT *** -USER *** -PASSWORD *** -QUERY Extract_**</Settings>
<TimeStamp>*13:35:31 July 29, 2010*</TimeStamp>
</SourceReport>

<TargetReport card="1" adapter="File" bytes="0" adapterreturn="0">
<Message>Data written successfully</Message>
<Settings>/***/archive/**/Archive.txt</Settings>
<TimeStamp>*13:37:40 July 29, 2010*</TimeStamp>
</TargetReport>

<TargetReport card="2" adapter="Sink" bytes="0" adapterreturn="0">
<Message>Success</Message>
<TimeStamp>*15:17:01 July 29, 2010*</TimeStamp>
</TargetReport>
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

yes - it probably is something in that card that is causing the delay, that's why i asked you questions about that card:
=============
How often does this rule run?
If just once, the problem is elsewhere in your map - you need to upgrade your client and run the profiler.

Is there any other database access in your map? how many records does you map process?
Are you able to post your map and trees to the forum?
=============
#IBM-Websphere-Transformation-Extender
#IBMSterlingTransformationExtender
#DataExchange

Originally posted by: MansiP

This runs once in a day and it truncates this table and next card inserts about 1.5million into the same table again.
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: MansiP

insertion card is card#3

<TargetReport card="3" adapter="DB" bytes="303795306" adapterreturn="0">
<Message>Success</Message>
<Settings>-DBTYPE ORACLE -MDQ ***.mdq -DBNAME *** -CONNECT *** -USER *** -PASSWORD **** -TB ABC</Settings>
<TimeStamp>15:17:01 July 29, 2010</TimeStamp>
</TargetReport>
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: SystemAdmin

How many times does the truncate run? (3rd time I've asked this!!)
it sounds like you're running it for every record in the input file - i.e. 1.5 million times, not once.

According to your fragments of log file the insert of 1.5 million records didn't take any time at all - but the audit log file may be wrong and it's card 3 taking up the time.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: lanapoli

Run the map without the 3rd card (INSERT) and see how long it takes.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: MansiP

it runs once in a day
#IBMSterlingTransformationExtender
#DataExchange
#IBM-Websphere-Transformation-Extender

Originally posted by: SystemAdmin

>it runs once in a day
no I'm asking about the truncate in your map - does the truncate run for each input record (i.e. 1.5 million times) or just once per map run?
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange

Originally posted by: jvanboga

I truncate large tables with map rules all of the time. Unless there are keys and other tables involved the tracation is very fast in 6.7.

I agree, this looks like another issue. Try writing a separate map with nothing but the truncate. You can do this with, 1 output card and one text element, no input cards.
#DataExchange
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender

Originally posted by: TarunB

Sounds like you are truncating and then inserting into the same table. In this case you could simply use -DELETE as part of the command statement in output card 3.

You therefore lose an output card (2), making the map more efficient, and at the same time it means that the table is only truncated if the insert works correctly, i.e. more transactionally secure.
#IBMSterlingTransformationExtender
#IBM-Websphere-Transformation-Extender
#DataExchange