on 05-30-2011 8:46 AM
Dears,
I developed a transaction which query database many times in a repeater loop and finally generate a SAP MII XML Output Document which I want to display in a html hyper link in MII Navigation. (Using XacuteQuery and iGrid)
I found that
1. if I execute in SAP MII Workbench, the transaction executing very slow, and also the database undo log in D:\oracle\TMI\sapdata2\undo_1\UNDO.DATA1 increasing soon.
2. If I use MII Schedule Edit to run the transaction, it executing fast.
Does any one know why?
Is there any setting can make it executing fast in a MII Workbench?
Many thanks!
Ivan
Hi,
Is there any difference between the values you are using for the transaction input parameters while using the scheduler compared to when executing from the WB?
Thanks
Udayan
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Hmmm... strange. There shouldn't really be any difference as both the calls are pointing to the same transaction on the server.
So how did you compare this I mean is it just a visual difference that you are observing or you have something quantitative in both the cases?
What version of MII are you on?
I can see the executing duration time in Transaction Detail to compare both case.
The MII version is 12.1.7.50.
Besides transaction executing time, I found that I can also see the log in MII->System Management->Transaction Manager->Transaction Detail if executing in MII Workbench.
But I can not see the log if executing in Scheduler.
Because my transaction calling another transaction in a repeater, so it writing many log, I think it takes lots of time if writing many log.
Is there any setting to disable the transaction log? Maybe it can shorten the executing time.
Thanks!
Hi Mike,
Thanks for your information.
But I've removed all Tracer actions in transaction.
The executing time should not be a big difference (2.5 minutes and 14 minutes)
The database archive log in C:\oracle\TMI\oraarch is also big difference. (under 44M and 6G)
So it really did more lots of jobs if executing in Wrokbench.
Thanks!
Hi,
Can you explain why it's different by using MII Workbench and Scheduler depending on sql joining and logic?
My transaction logical is basically as below,
1. query qualified sfc in SAPME tables
SELECT *
FROM (SELECT s.site, s.sfc, ss.operation_bo, ss.qty_in_queue,
ss.qty_in_work, s.priority, s.item_bo, s.shop_order_bo,
s.status_bo, ss.sfc_router_bo, ss.step_id, ss.step_sequence,
st.status_description, cf.ATTRIBUTE, cf.VALUE
FROM sfc_step ss,
sfc s,
sfc_router sr,
sfc_routing srt,
status st,
custom_fields cf
WHERE sr.handle = ss.sfc_router_bo
AND srt.handle = sr.sfc_routing_bo
AND s.handle = srt.sfc_bo
AND st.handle = s.status_bo
AND SUBSTR (s.status_bo, -3) IN ('402', '403', '404')
AND sr.handle = ss.sfc_router_bo
AND sr.in_use = 'true'
AND ((ss.qty_in_queue > 0) OR (ss.qty_in_work > 0))
AND s.site = '[Param.1]'
AND cf.handle(+) = s.handle
AND cf.ATTRIBUTE(+) = 'QTIMECONTROL'
[Param.2]
ORDER BY s.priority DESC, s.sfc)
WHERE (ATTRIBUTE = 'QTIMECONTROL' AND VALUE != 'N') OR VALUE IS NULL
2. use Repeater to query sfc's activity_log table
SELECT al.site, al.sfc, al.operation, al.operation_revision, op.description,
al.step_id,
TO_CHAR (NEW_TIME (date_time, 'PST', 'GMT'),
'YYYY-MM-DD'
)
|| 'T'
|| TO_CHAR (NEW_TIME (date_time, 'PST', 'GMT'), 'HH24:MI:SS')
AS date_time,
TO_CHAR (NEW_TIME (date_time, 'PST', 'GMT'),
'YYYY/MM/DD HH24:MI:SS'
) AS complete_date,
cf.VALUE AS qtime, action_code, ss.operation_bo AS current_op,
ss.step_id AS current_step_id,
ss.step_sequence AS current_setp_sequence,
al.item || ',' || al.item_revision AS item,
TO_CHAR (SYSDATE, 'YYYY/MM/DD HH24:MI:SS') AS check_time,
(sysdate-NEW_TIME (date_time, 'PST', 'GMT'))2460 as difference
FROM activity_log al,
custom_fields cf,
sfc s,
sfc_routing srg,
sfc_router sr,
sfc_step ss,
operation op
WHERE al.site = '[Param.1]'
AND (action_code IN( 'COMPLETE' , 'START' , 'SIGNOFF'))
AND al.sfc = '[Param.2]'
AND cf.handle(+) =
'OperationBO:'
|| al.site
|| ','
|| al.operation
|| ','
|| al.operation_revision
AND cf.ATTRIBUTE(+) = 'QTIME'
AND s.handle = srg.sfc_bo
AND srg.handle = sr.sfc_routing_bo
AND 'true' = sr.in_use
AND sr.handle = ss.sfc_router_bo
AND 0 < ss.qty_in_queue + ss.qty_in_work
AND s.sfc = al.sfc
AND al.operation = op.operation
AND al.operation_revision = op.revision
AND op.site= '[Param.1]'
AND al.operation NOT LIKE '%-W'
ORDER BY date_time DESC
3. call another transaction to parse input data and get output data
4. parse get back data to form a MII xml output Document.
Thanks!
Hi,
I can't urge this strongly enough!
Build a couple of custom views in the ME database (or check for ones that may already exist). Your complex queries will run so much faster than having them execute in MII as written.
Yes, MII can pass it over to the relational database, but take advantage of the power and tools of the relational database.
Regards,
Mike
Hi,
Even the SQL is a little bit complex, but actually it reply in very short time. The transaction takes long time should not because of SQL query time, it could be because of taking time to calling another transaction.
But because it run fast in MII Scheduler, I'm trying find a way to run it in MII Workbench as fast as in MII Scheduler. (such as how to disable the log or trace of transaction to database, it takes long time and generate huge archive log).
Thanks!
User | Count |
---|---|
11 | |
6 | |
2 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 | |
1 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.