I am executing several queries to MongoDB through DBConnect (v3.1.4) and UnityJDBC driver. I'm experiencing issues getting results for anything but the simplest SQL queries. All queries take a very long amount of time compared to when I run the same SQL queries through a UnityJDBC client.
Here's an example of the kinds of queries that are able to return results
| Query | Splunk Exec Time | UnityJDBC Client Exec Time |
| SELECT COUNT(_id) FROM docs | 8 - 40 sec | 0.1 sec |
| SELECT * FROM docs WHERE status='ERROR' | 20 - 40 sec | 0.06 sec |
Now if I try to use dbxquery to execute any join queries or anything slightly more complex than the above I get the following error:
com.mongodb.MongoTimeoutException: Timed out after 30000 ms while waiting to connect. Client view of cluster state is {type=UNKNOWN, servers=[{address=/dev-db:27017, type=UNKNOWN, state=CONNECTING, exception={com.mongodb.MongoSocketException: /dev-db}, caused by {java.net.UnknownHostException: /dev-db}}]
Here's a couple of examples of queries which work in UnityJDBC's client, but fail in splunk after roughly 30 seconds yielding the above error.
SELECT * FROM StatusHistory WHERE CAST(ModifiedOn, 'DATE') > '2020-08-07'
SELECT * FROM StatusHistory sh JOIN docs d ON sh.doc_id = d.id
SELECT Status, COUNT(Status) FROM StatusHistory GROUP BY Status HAVING COUNT(Status) > 6
I don't believe there is an intermittent network issue causing this because the queries that fail always fail in the same way and the queries that produce results always produce results (sometimes after greater than 30 seconds if there are enough concurrent dbxqueries on the system).
What could be causing the timeout?
In the error log it looks like the host address is missing some data, like an IP address or a hostname. It's strange this is only happening for the more complex queries and not for everything.
Why are dbxqueries so slow compared to a JDBC Client connected to the same database?
