2

I have a view on a table that has 35 million rows. When I'm selecting from the view for the first time, the execution time is about 35 seconds. When I run the query again repeatedly, it is much faster, down to about 3 seconds each time.

Is there a generic explanation of why I get different execution times in scenarios like this?

3

All operating systems and all applications use a concept called "caching".

It means - when the data is first read from a slow memory device (like, a hard disk), it is saved in a fast memory device (like, RAM) for some time, to facilitate faster lookups.

The same applies to RDBMS.

First time the data blocks that build up your query results are read from disk, second time they are read from memory.

Details can be explored using OS and database tools. If you specify what RDBMS and what OS you are on, we can help you get the details. For PostgreSQL it's about EXPLAIN command.

3

An alternative solution to your issue and to filiprem's answer could also be the underlying OS and the power saving settings.

Power Saving Settings (Windows)

On Windows Server for example the Power Saving settings are set to "Balanced" as can be seen when running the following command:

C:\> powercfg /list

This will display the current power configurations/profiles available on the server and will display a asterisk * for the profile that is currently active.

Here an example output:

Existing Power Schemes (* Active)
-----------------------------------
Power Scheme GUID: 381b4222-f694-41f0-9685-ff5bb260df2e  (Balanced) *
Power Scheme GUID: 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c  (High Performance)
Power Scheme GUID: a1841308-3541-4fab-bc81-f71556f20b4a  (Power Saver)

The default setting for a freshly installed Windows Server 2012 / 2016 is Balanced as can be seen in the example output.

Impact

If a Windows Server is idling then the processor can (and will) be throttled to consume less CPU. A query requesting a lot of CPU will be slower because the processor is providing only 50% performance.

The OS will realize this and allow the CPUs to achieve 100% for subsequent requirements. This is what you might be observing when your query runs the second time.

Leave the OS alone again for another half an hour and the CPU will again be throttled to 50% CPU usage.

Reference

This was also recently observed by Paul Randall from SQLSkills in his recent Newsletter:

SQLskills Insiders #160: The silent killer and video demo on Azure VM metrics

Solution

You can change the Power Configuration settings to use the High Performance profile by issuing the following command:

powercfg /s 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c

The output of powercfg /l will then change to:

Existing Power Schemes (* Active)
-----------------------------------
Power Scheme GUID: 381b4222-f694-41f0-9685-ff5bb260df2e  (Balanced)
Power Scheme GUID: 8c5e7fda-e8bf-4a96-9a85-a6e23a8c635c  (High Performance) *
Power Scheme GUID: a1841308-3541-4fab-bc81-f71556f20b4a  (Power Saver)

Initial and subsequent identical queries should now execute inside a similar duration window.

Power Saving Settings (Unix/Linux)

This solution is possibly equally valid for Unix/Linux Operating Systems, but will depend on the distribution.

There is a question over on the Unix & Linux Stackexchange which discusses issues with Power Saving Settings on a laptop:

Is the power management on different linux distributions the same?

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.