When I click on a screen on my vb application, it takes approx 6/7 seconds to display the results. When I click off the screen and click back again into the screen, it returns immediately.
I ran profiler, and can see there are multiple queries running when clicking on this screen. There wasn't anything glaring obvious to fix on these queries; when run one by one in SQL Server, they ran within 1 second. The execution plan didn't recommend any missing indexes (I did add 2 indexes myself to prevent some key look ups but that was it).
My last test was to update statistics. I did this, updated all statistics on our local copy, but this still doesn't return the results on the application any faster.
Any advice on how to return these results immediately on the application? Perhaps statistics are missing from these queries? Or is there a way to auto update stats or similar for these specific queries?
SQL Server Settings:
Auto Create Stats - True
Auto Update Status - True
Auto Create Incremental Stats - False
Auto Update Stats Async - False