We would like to move our dashboards from another tool into Splunk, so this question is quite important for us. The issue is that the dbxquery seems to be very slow and our dashboards base on the online DB queries.
Following query takes 49 ms when executed directly on the database:
| noop searchoptimization=false| dbxquery query="select timestamp, errorid, sum(errorcnt) as errorcnt from sapiop.ZKPIDAAPPDUMP where errorid <> '%TECHNICAL ITEM%' and sysid = 'ISP' and timestamp between cast('2018-11-28 08:04:00' as timestamp) and cast('2018-11-28 12:04:00' as timestamp) and errorid = 'TIMEOUT' group by timestamp, errorid" connection="HANAMLBSO"
On Splunk, 2.745 seconds:
1.59 command.dbxquery 1 - 16 0.00 dispatch.check_disk_usage 1 - - 0.00 dispatch.createdSearchResultInfrastructure 1 - - 1.14 dispatch.evaluate 1 - - 1.14 dispatch.evaluate.dbxquery 1 - - 0.00 dispatch.evaluate.noop 2 - - 0.01 dispatch.writeStatus 7 - - 0.11 startup.configuration 1 - - 0.00 startup.handoff 1 - -
This is only one of several examples.
Now, ... we really need to get it running faster, otherwise we will not decide to use Splunk for it.
Could you please advice how to optimize it?
I have read in another question that this would be the java based DB Conn 3 causing issues and the DB Conn 1 would be way faster. Is it possible to install the DB Conn 1 still? Where would we download it?
If not, is it possible to modify the DB Conn App in the way that the normal ODBC driver is used and not the jDBC?
The fine docs say that dispatch.evaluate is "The time spent parsing the search and setting up the data structures needed to run the search." I'm not sure why that would be as high as it is in this case.
Have you tried converting the above dbxquery into a view inside your DBMS, so you then only have to have a dbxquery of something like
select timestamp, errorid, error_cnt from sapiop.view_ZKPID_for_Splunk
I'd be very interested in seeing the difference in run time.
As to going back to DBX1 ... no, don't do that if there's any other option. But I guess if you have to, it might do what you need. The thing is, DBX 1 is pretty terrible.
If I were you, I'd be raising a support ticket with Splunk. They may or may not have an answer, but even if they have no good answer it'll be one more support ticket for a performance problem out of DBX that isn't related (really) to the actual query or DB itself, but seems just a problem with the java interface they're using.