How we can customised number of thread for select query which using JAVA UDX?
Background :
We have JAVA UDX which is performing better for external table compare to internal table.
He are the observation :
- select query on external table created on s3 location creates 100+ threads
- select query on managed tabled (data is storage on communal storages) creates 65 threads
**Question : **
Its possible to set no of thread manually or increase?
Note:
- We are not using any resource pool to limit the resources and running query using dbadmin.
0
Answers
The number of threads likely correlates to the number of files being read. For the external table, it's possible that many threads are used to scan all files in the external table path. For managed tables, the number of threads likely correlates to the number of ROS files that make up the table, as shown in STORAGE_CONTAINERS system table, where each sal_storage_id is a file on a node (or Eon storage).
Fewer threads is not necessarily a bad thing: for both external tables and managed tables, the optimizer should prune out files that don't contain data needed for the query, so Vertica should read with fewer threads. If the managed table seems slower, you should consider a projection design that is better suited for the query so fewer threads can read the ROS files more efficiently.