Splunk DB Connect 1: How to improve my dbquery search to speed up my dashboard load time?

[crossap]

Hi,

I am looking for some help on the best way to speed up my dashboard load time.

Currently, the searches I have enabled on my desktop seem to take a long time to load and makes showing the system frustrating

My Overview Dashboard has 20 traffic lights with each one loading data from dbquery

| dbquery "DATABASE" "SELECT * FROM SANS20Score" | WHERE SANSID = "1.1" | fields "COMPLIANCE%"| rename COMPLIANCE% as SANS1.1 | join [ | dbquery "DATABASE" "SELECT * FROM SANS20Score" | WHERE SANSID = "1.2" | fields "COMPLIANCE%"| rename COMPLIANCE% as SANS1.2] | join [ | dbquery "DATABASE" "SELECT * FROM SANS20Score" | WHERE SANSID = "1.3" | fields "COMPLIANCE%"| rename COMPLIANCE% as SANS1.3] | join [ | dbquery "DATABASE" "SELECT * FROM SANS20Score" | WHERE SANSID = "1.6" | fields "COMPLIANCE%"| rename COMPLIANCE% as SANS1.6] | eval SANS1=('SANS1.1'+'SANS1.2'+'SANS1.3'+'SANS1.6')/4 | fields SANS1 | rangemap field=SANS1 elevated=51-84 low=85-100 severe=0-50 default=none

Is the issue my searches? or can you recommend anything to improve the slow load time.

thanks

[richgalloway]

Yes, I think the issue is your searches. Four DB queries are likely to be slow. It would be best to combine them into a single query, but if you can't do that at least reduce the amount of data returned by each one. For example,

| dbquery "DATABASE" "SELECT COMPLIANCE% FROM SANS20Score WHERE SANSID = '1.1'"|  rename COMPLIANCE% as SANS1.1 | join [ | dbquery "DATABASE" "SELECT COMPLIANCE% FROM SANS20Score WHERE SANSID = '1.2'" | rename COMPLIANCE% as SANS1.2] | join [ | dbquery "DATABASE" "SELECT COMPLIANCE% FROM SANS20Score WHERE SANSID = '1.3'" | rename COMPLIANCE% as SANS1.3] | join [ | dbquery "DATABASE" "SELECT COMPLIANCE% FROM SANS20Score WHERE SANSID = '1.6'" | rename COMPLIANCE% as SANS1.6] | eval SANS1=('SANS1.1'+'SANS1.2'+'SANS1.3'+'SANS1.6')/4 | fields SANS1 | rangemap field=SANS1 elevated=51-84 low=85-100 severe=0-50 default=none