1

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

  • 1
    Check value of page life expectancy, may be its too low and your query plan is not able to accommodate in the cache. – Learning_DBAdmin Apr 16 at 9:44
  • It's set to 17028, that's on a local copy, but the issue still occurs locally too. – Paul S Apr 16 at 9:51
  • 1
    Check next options on your database: Auto Update Statistics and Auto Update Statistics Asynchronously. – Denis Rubashkin Apr 16 at 10:30
  • Forgot to post those, sorry. All defaults are set. So, Auto Update Statistics = [TRUE] and Auto Update Statistics Asynchronously = [FALSE] – Paul S Apr 16 at 10:34
  • SQL Server needs data in the buffer cache before it can be used. The first time you run the queries, pages not already in cache are read from storage, which can take some time depending on your storage speed. Subsequent queries are faster because data are already in cache. If your queries are tuned, the solution is faster storage and/or more memory. – Dan Guzman Apr 16 at 10:44
2

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.

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.

  • It's the latter. @DanGuzman mentions the following solutions 1) tune the queries, if possible, 2) Faster Storage and 3) More memory. I will continue to work step 1. Step 2 and 3 i will reach out to the client with. Any other thoughts for improvements? I'm guessing I should focus on improvements to areas around the caching? – Paul S Apr 16 at 11:59
  • Consider "warming" the database's Buffer Cache. After restarting the database, run some chosen queries that load some or all of the data tables into the Cache, ready for Applications to use later. It doesn't matter if those "warming" queries take a handful of seconds to run; the Users will see a faster response because the data's off disk and in memory. Not saying you should stop looking at query performance, always a Good idea, but this will you past that first "hit" after the database is restarted. – Phill W. Apr 16 at 13:41
  • When you say "After restarting the database, run some chosen queries" - do you mean have a task to run these queries, say daily or weekly? I'm sure it would help but also not entirely sure it's the cleanest way to fix the issue. – Paul S Apr 16 at 13:49
  • Database queries will /always/ run slower the very first time they run. This is because databases serve data from their in-memory caches, not from the raw data files, and they have to get the data into Cache before they can hand it back to the client. There is NO WAY around this. In a similar fashion, you can't just "jump" into the cockpit of a commercial airliner, push the "Start" button and fly away. It can take several minutes to spin up a modern turbofan engine. Obviously, well-tuned queries will /help/, but this initial delay is fundamentally inherent to the technology. – Phill W. Apr 17 at 11:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.