Performance Optimization in SAP DS - Huyhh
description
Transcript of Performance Optimization in SAP DS - Huyhh
www.fssc.com.vn
Page 1Committed – Professional - Understanding
SAP Data ServicePerformance Optimization
08/2015
CÔNG TY GIẢI PHÁP PHẦN MỀM TÀI CHÍNH
www.fssc.com.vn
Page 2Committed – Professional - Understanding
ContentsQuick Tips for Job Performance
Optimization in BODSPush downData TransferBulk loadingNote
www.fssc.com.vn
Page 3Committed – Professional - Understanding
Quick Tips- Đảm bảo rằng hầu hết tất cả các Dataflows
được tối ưu hóa. Tối đa hóa việc đẩy hoạt động (push-down operations) xuống database càng nhiều càng tốt. Có thể kiểm tra việc tối ưu hóa bằng cách sử dụng tùy chọn “Display Optimized SQl…” bên trong Dataflow. SQL nên bắt đầu bằng INSERT INTO…SELECT statements…
www.fssc.com.vn
Page 4Committed – Professional - Understanding
Quick Tips- Chia nhỏ Dataflow thành nhiều Dataflow nếu
có thể. Điều này giúp cho việc bảo trì hoặc tái sử dụng trong tương lai được dễ dàng hơn; cũng như đảm bảo việc hầu hết các Dataflow có thể được “push down”.
- Nếu không thể thực hiện pushdown trong 1 Dataflow thì có thể kích hoạt tính năng Bulk Loader ở target table. Click đúp vô target table, chọn Bulk Loader Options. Tuy nhiên chỉ kích hoạt được trong trường hợp target table không phải là bảng tạm.
- Select Distinct: Trong BODS ‘Select -> Distinct Row’ không được push down. Nó chỉ được push down trong trường hợp khi query nằm trước target table. Vì vậy nếu phải sử dụng thì nên đặt ở query transform cuối cùng.
- Order By và Group By không được push down, ngoại trừ trong Data flow chỉ có duy nhất 1 Query Transform.
www.fssc.com.vn
Page 5Committed – Professional - Understanding
Quick Tips- Lookup: Sử dụng Lookup table như là
source table. Kỹ thuật này có lợi thế hơn việc sử dụng lookup function vi nó đẩy hoạt động thực thi join xuống bên dưới database. Ngoài ra nó sẽ dễ dàng cho việc bảo trì, sửa chữa dataflow. Thêm nữa lookup sẽ truy xuất vào bảng, đến từng record làm tăng hoạt động I/O trong DS.
- Sử dụng Query Transform để chia dữ liệu thay vì sử dụng Case transforms vì nó tốn tài nguyên chuyển đổi trong BODS.
- Hạn chế sử dụng các tính năng nâng cao như Run Distinct as Separate process, v.v
- Sử dụng Data Transfer (type = ‘TABLE’) để pushdown các logic phức tạp trong DF xuống database.
- …
www.fssc.com.vn
Page 6Committed – Professional - Understanding
Push down- To Before runnoptimize performance, the
software pushes down as many SELECT operations as possible to the source database and combines as many operations as possible into one request to the database.
- Before runing a job, you can view the SQL that is generated and adjust your design to maximize the SQL that is pushed down toimprove performance.
- Use database links and the Data_Transfer transform to push down more operations.
www.fssc.com.vn
Page 7Committed – Professional - Understanding
Full push-down operations- The Optimizer always first tries to do a full
push-down operation.- A full push-down operation is when all
transform operations can be pushed down to the databases.
- SAP Data Services sends SQL INSERT INTO... SELECT statements to the target database where SELECT retrieves data from the source
www.fssc.com.vn
Page 8Committed – Professional - Understanding
Full push-down Conditions- All of the operations between the source
table and target table can be pushed down.- The source and target tables are from the
same datastore, they are in datastores that have a database link defined between them, or if the datastore has linked remote servers.
- If the source and target are not in the same datastore, the software can also do a full push-down operation if you use one of the following features:● Add a Data _Transfer transform before the target.● Define a database link between the two datastores.
www.fssc.com.vn
Page 9Committed – Professional - Understanding
Full push-down Ways- To enable a full push-down from the source
to the target, you can also use the following features:● Data_Transfer transform● Database links ?● Use Linked Remote Servers option ?
www.fssc.com.vn
Page 10Committed – Professional - Understanding
Partial push-down operations- When a full push-down operation is not
possible, SAP Data Services still pushes down the SELECT statement to the source database.
- Operations within the SELECT statement that the software can push to the database include:
● Aggregations: GROUP BY statement,…
● Distinct rows ● Filtering● Joins ● Ordering● Functions
www.fssc.com.vn
Page 11Committed – Professional - Understanding
Operations that cannot be pushed down
- Conditional, Merge, Table Comparison, Pivot, UnPivot, History Preservation, and Hierarchy Flattening will not push down.
- Data Quality transforms won’t push down.- Many built-in functions, like decode, will
push down properly. Custom functions will not push down.
- Joins between sources that are on different database servers that do not have database links defined between them.
www.fssc.com.vn
Page 12Committed – Professional - Understanding
Operations that cannot be pushed down- Job_NO_SQL: Full Push Down
Tất cả operations đều được xử lý dưới datasource
www.fssc.com.vn
Page 13Committed – Professional - Understanding
Data Transfer• Data transfer transformation is used
extensively to optimize the performance of the job by pushing down operations to the Database level.
• Data transfer transformation insures the operations for ‘push down’.
• Data transfer transformation creates a temporary Database table that is used as staging table for operation
• By pushing down operations to the source database, Data services reduce the no. of rows and operations that the engine must retrieve and process, which improves the performance.
• A Data transfer transform automatically divides the data flow into sub data flows and executes serially.
www.fssc.com.vn
Page 14Committed – Professional - Understanding
- Job_NO_TR: Join 2 bảng khác datastore(1 bảng ở Sybase, 1 bảng oracle)
Data Transfer - Exemple
www.fssc.com.vn
Page 15Committed – Professional - Understanding
Data Transfer - Exemple- Job_TR_BL: Sử dụng Data Transfer
www.fssc.com.vn
Page 16Committed – Professional - Understanding
Bulk Loading (for Sybase IQ)- Bulk loading is the fastest way to insert large
numbers of rows into a Sybase IQ table
1. First, Data Services loads the data into its memory. In this step it will also perform all kind of error handling that is specified at the level of the file format!
2. Second, Data Services writes the just loaded data to another file into its bulk loader directory (option Bulk loader directory in the advanced datastore settings - if not specified it is the ~/BusinessObjects/DataServices/log/bulkloader directory).
3. Third it sends the LOAD TABLE ... USING FILE ...command to IQ to initiate the bulk loading process within IQ.
www.fssc.com.vn
Page 17Committed – Professional - Understanding
Bulk Loading (for Sybase IQ)JOB Thông tin Thời gian thực thi (s)
Job_BL Không Bulk Load
Fetch: 5000
Row per commit: 1.000 389
Row per commit: 5.000 163
Row per commit: 50.000 167
Row per commit: 100.000 169
Row per commit: 500.000 157
Job_BL Bulk Load
Fetch: 5000
Chọn Binary Format
Bỏ chọn Ignore conversion error 16
Chọn Ignore conversion error 14
Bỏ chọn Binary Format
Chọn Ignore conversion error 14
Bỏ chọn Ignore conversion error 17
So sánh thời gian thực thi khi sử dụng Bulk Load
www.fssc.com.vn
Page 18Committed – Professional - Understanding
Bulk Loading (for Sybase IQ)
So sánh thời gian thực thi khi sử dụng Bulk Load
JOB Thông tin Thời gian thực thi (s)
Job_BL_DDFT Bulk Load
Fetch: 5000
Chọn Binary Format
Chọn Ignore conversion error 43
Bỏ chọn Ignore conversion error 43
Job_BL_DDFT Không Bulk Load
Fetch: 5000
Row per commit: 1.000.000 249
www.fssc.com.vn
Page 19Committed – Professional - Understanding
Note- Trong 1 DF, không được set 2 bulk load.
www.fssc.com.vn
Page 20Committed – Professional - Understanding
NoteTrường hợp chọn 2 bulk load trong cùng 1 DF
www.fssc.com.vn
Page 21Committed – Professional - Understanding
NoteTrường hợp chọn duy nhất 1 bulk load trong cùng 1 DF