Skip to Content
0

SAP SDI flow graph Batch Task-- Improve Performence

May 01, 2017 at 03:45 PM

255

avatar image
Former Member

Hi,

We are trying de-normalize the data using SDI flow graph. We created a flow graph(Batch Task) which joins around 7 tables and inserts into a one flat table.

Flow graph is running perfect with out any issues, Further we want to know the bottle necks and improve the performance of the flow graph.

If there is any SQL/Stored procedure generated for the flow graph then where can i see the SQL/Stored Procedure (location path)?

We are on SAP HANA 1.0 SP12

Thanks

Srini

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Best Answer
Timo Wagner
May 01, 2017 at 04:42 PM
0

Hello Srini,

the Flowgraph in Batch Task mode is generating a Taskplan which will then be executed.

One way to look into the performance of those operators is to enable the "Just in time" preview on the last node of the flowgraph and activate the flowgraph again. You can now click on a small preview icon next to that node and you can then execute a select query on a generated calc scenario for the flowgraph including this operation. This Query can now be analyzed by right clicking on the query and analyze query.

Kind regards,

Timo Wagner

Show 7 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Hi TImo,

I have a 'data sink' with a DB table as my last node and when I do preview, the select is just a SELECT top 100 from <DB Table>. Am I missing something here? If I want to analyze all the filters and joins performance, shouldn't I get a more detailed query with all the joins and filters?

0

Hi Benedict,

with the last node I meant the last transformation.

If we have a flowgraph like this:

Source1 . ---- Join --- Aggregation -- Data Sink
Source 2 ----/

You can apply the "Just in Time" Preview on the Aggregation Node and this will then create a calc scenario that should be equivalent to the flowgraph output of the aggregation.

You can use the regular sql analyzer capabilities to look into the different operations and e.g. see how a certain Join condition impacts your overall performance.

Kind Regards,

Timo Wagner

0
Former Member
Timo Wagner

Ok, that works. I am able to use the 'just in time' to look at inter mediate results just like a data preview on nodes in a calculation view.

0
Former Member

HI Timo,

Thanks for ur response, we have variables defined in flow graph and the "Just in Time" preview doesn't work when we defined any variables.I'm not sure if this a bug in 1.0 SP 12?

Is there a way to know what SQL the flow graph is executing?

Thanks

Srini

0
Former Member
Former Member

Hi Srinivas,

Create a copy of the flowgraph and set it to 'procedure' instead of task. Now you will see a stored proc created under the same schema the flowgraph was defines in. If you look inside the proc you can see the entire SQL generated for the flowgraph. You can take this and execute it in a sql console and look at the plan, filters etc.

Goodluck.

0

Hi Srini,

if you've set default variables you should still be able to access the JIT.

If you can't can you try to manually access the generated Calc Scenario?

It should be visible in the webide catalog in the Column Views Section of your Target Schema following this pattern:

"SYSTEM"."public.sap::varJITTest_Filter1_CV"

With SYSTEM being the Target Schema, public.sap the package, varJITTest is the flowgraph name and Filter1 is the name of the node with the JIT flag set.

Converting it into a procedure is not a fit all solution, as the task framework allows additional operations to be performed that are not available in the procedure target mode.

Thanks,

Timo

0
Former Member

Thanks Timo and Benedict for you valuable suggenstions.

Thanks

Srini

0
avatar image
Former Member May 03, 2017 at 02:42 AM
0

Thanks Timo and Benedict for you valuable suggenstions.

Thanks

Srini

Share
10 |10000 characters needed characters left characters exceeded