1. Drop indexes before load and create indexes after load

When you have indexes on a table, every insertion, update or deletion (CRUD) requires the database to update the indexes as well. This will be a overhead and significantly slow down the data load. To overcome this issue, select the checkbox for "Drop indexes before load and create indexes after load" in the "Caching" tab for the resource -> View, Table, Procedure. This helps to improve the efficiency and speed of operation, as it doesn't need to update the indexes.

Additionally, since this also rebuilds the indexes, the new indexes are optimized for the current data resulting in better performance.



2. Enable "Native Bulk Data Loading"

For the databases that supports it, enabling bulk loading helps load the data in bulk rather than individual records. This results in a fewer insert statement for the database and also reduced network congestion, including minimal transaction logging on the database end, thus help achieve better performance.



3. Optimize SQL queries:

Use Parameterized queries when its possible and simplify complex queries. Generate the "Query Plan" for the SQL queries to understand which queries when modified provide better query execution plan. Look into the "Total execute time" to decide on the right execution plan for modified SQLs.

 


Reach out to Data Virtualization support if you may require any clarifications on this article or any questions implementing this in your environments.