How-To
Troubleshoot slow database read / write speed.
Generally configuring data sources would be outside of Support scope. Drivers interact with Designer and with the data source. While they are neither developed nor maintained by Alteryx there are still a few troubleshooting steps that we can offer the customer struggling with database slowness. Of course the best and first internal contact for the user is the DBA.
Environment
- Alteryx Designer
- Supported database and database driver
Procedure
-
General checks
- Exclude network issue
- Check the network connectivity to DB server. The customer's Network team with DBA will be a good resource for that.
- Are database Server and client machine they in the same network? Are they regionally far away? Behind Azure or AWS? This might impact write speed.
- Exclude driver issue
- To exclude a driver issue check whether the same connection to the data source is also slow connecting from a different application for instance using Microsoft Access.
- Generally our experience shows that OLE DB is faster than ODBC, but there might be exceptions
-
Optimizing write speed
-
Optimize record size
- Generally speaking, the smaller the records are the faster they will load. Record size is determined by field size. Therefore, you should check for large fields. The default size for fields in the Formula tool is often too large and you can save some space using the Auto Field tool before connecting to the database to write. Additionally, BLOBs and spatial data will take a lot of space and can slow down writing.
-
Optimize transaction size
- The transaction size is set in the Output tool. It is the number of records sent in one transaction. In the extreme case of it being set to 1 it will send one record at a time.
- For slow client machine speeds reading records one by one instead of writing a big batch can be faster.
- However, for a faster machine increasing the transaction size might increase the write speed. Please note that finding the right value for the transaction size might require some experimenting with it.
-
Bulk write
- Is a bulk write option available for the data source? If yes, it should be chosen for writing large data as it will be faster, but generally rollback to committed inserts will not be supported.
-
Hive ODBC
- Hive ODBC can be slow when loading data to tables. If you are looking for a faster option to write to Hive and want to create a new table or overwrite an existing table refer to the following article.
-
Optimizing read speed
-
Declare / fetch driver options
- Some drivers offer the option of enabling Declare / Fetch mode. Depending on the driver naming might differ. The default mode is generally not to use Declare Fetch. Then the entire result from the cursor is pushed to client memory. This will obviously increase client RAM usage.
- On the other hand if RAM is limited Declare Fetch might be an option to consider. If enabled a specific number of rows will be returned at a time. However, note that this might lead to network latency.
- Please note that often drivers will often offer additionally customization of buffers, caches sizes and other settings that will directly affect read speed.
-
Limit data brought into Designer
- In the Visual Query Builder or in the SQL Editor select only the columns that need to be brought into Designer. Additionally, in the SQL Editor use - if feasible SELECT TOP, LIMIT or ROWNUM depending on database to limit the number of results.
-
Do not show % complete
- In the Input Data tool check Do Not Show % Complete. This will stop Designer from reporting on the status of reading the data and will speed up reading time.
-
For Oracle
- If Visual Query Builder is loading very slowly, this might be related to an issue with Oracle DB when users do not have the “select any table” privilege. When users have only limited access to database objects, queries against ALL_OBJECTS will be slow.