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
Best Answer
Does the delay reappear when you restart the Application or when you restart the Database?
If it's the former, then the Application may be doing some caching of its own and, the first time you run it, it's building that cache, which takes a while.
If it's the latter, then it's probably the data being loaded into the database's Buffer Cache from disk. Once you've loaded the data once (and assuming that nothing more important flushes it out again) then the data is simply sitting in the Buffer Cache waiting to be [very quickly] reused.