Troubleshooting initial load performance

What do we need to check when virtual table queries are slow through an SDI remote source?

1. Is the table particularly large? Create some additional task partitions to parallelize the data transfer.
2. Ensure your dpserver is configured to stream query results: both values should be true in dpserver.ini ->  [framework] > usePrefetch & useStreaming
3. Check the DP agent framework.trc log files of the DP agent installation log folder. Search for key word "Stream Complete", which should occur around the time of a remote statement query completing.
   1. "DPFramework | AsyncHandler.close  - AH(125): Stream Complete: [rows=7055742,time=32ms (rewrite=2ms,execute=12782ms,fetch=1ms,serialize=3ms, send=4ms)] "
      
      1. execute time: duration taken for the query execution in source DB
      2. fetch time: duration taken to pull the resultset from the source DB
      3. serialize time: serialize time, because there's a queue to cache the rows and send out to target HANA (DP agent will serialize the results before send to HANA)
      4. send time: real send out time

4. Based on the "execute" time above, is the query slow in your source DB? Check source DB performance by directly executing the query with a different DB client and tune it if the performance is poor in the native environment. If performance is not bad in the source DB with that test, confirm whether the filters being requested by the SDI query are being applied. If not, review the filter conditions against the list of supported features in the ADAPTER_CAPABILITIES for your given remote source's adapter: 

   Filtering capabilities SQL Expand source

   SELECT 	DESCRIPTION, 
   	IS_SDA_SUPPORTED AS SUPPORTED
   FROM ADAPTER_CAPABILITIES
   WHERE SCOPE = 'WHERE' AND ADAPTER_NAME = '<Your adapter name>';

   1. HANA side evidence for filter pushdown: M_REMOTE_STATEMENTS or M_REMOTE_QUERY_STATISTICS
   2. DP Agent side evidence for filter pushdown: <DPAgent_root>/log/stats/*sql_stats.txt
   3. Source databases likely contain some kind of audit or trace for executed SQL as well.
5. If the fetch & serialize time is high, the query may be slow in the DP agent. Check your DP agent machine to see if CPU or memory resources are stressed.
   
   1. Increase the memory available to the DP Agent processes with KBA 2737656 - How to increase DP Agent memory
   2. Double check the fetch size parameters in dpagentconfig.ini: framework.fetchSize, framework.async.fetchSize (async used for streaming queries), framework.maxDataSize. Reducing these values too much can drastically increase the required number of fetches and slow down larger queries.
6. Is the send time high?
   1. Check if there is network bandwidth issue between DP agent and HANA.
   2. Are there many simultaneous queries, such as calculation views that are built on virtual tables, or heavily parallelized initial loads? If so, it would improve performance to add additional communication channels for the agent or split your queries into multiple remote sources on multiple agents.