CODEWITHSUNDEEP
sql-server
Phate01
Phate01

Reputation: 1795

Why is GETDATE slowing down select query if I use a variable?

I'm doing a select on a table with about 6 millions records selecting GETDATE()

select getdate() as date, [...] from MyTable

I verified that the performance issue is on GETDATE(), removing all other fields the query is still slow.

I thought that putting the value of GETDATE() in a separate var would speed the query up

declare @now datetime
set @now = GETDATE()
select @now as date, [...] from MyTable

It is slow as well. Why?

Upvotes: 1

Views: 1225

Answers (2)

Reuben Sultana
Reuben Sultana

Reputation: 1

Two points.

  • ASYNC_NETWORK_IO is SQL Server saying that it is waiting for network bandwidth to be available in order to send more data down the pipe.
  • SSMS stores the output of the Results window in a temp file on your C:\ drive so will be affected by disk I/O, AV scanning, other processes, etc. running on your machine. Same concept if you use a Linux OS.

I'd experiment with limiting the size of the data being returned (10M records can hardly be analysed by a human), and using a different tool to pull the records (if you really need 10M records) for starters. Also, review the Execution Plan to find out where exactly the delay is. If it still points yo the ASYNC_NETWORK_IO wait, then your problem could be one or more of the network components between yourself and the server. Try using a wired connection instead of WiFi. Do you have a VPN? Is there anything limiting data transfer rates? Or the reason might simply be that too much data is being pulled.

Upvotes: 0

MattM
MattM

Reputation: 374

I'd never really noticed this before. But I am seeing the same thing.

Ran the following on a 10 million row table...

-- query #1
DECLARE @now AS DATETIME ;
SET     @now = GETDATE() ;
SELECT  @now AS [date], * FROM [MyTable] ;
-- cpu time = 2,563 ms
-- duration = 27,511 ms

-- query #2
SELECT  GETDATE() AS [date], * FROM [MyTable] ;
-- cpu time = 2,421 ms  
-- duration = 26,862 ms

-- query #3
SELECT  * FROM [MyTable] ;
-- cpu time = 1,969 ms  
-- duration = 23,149 ms

And the cpu times and durations are showing a difference.

All three query plans are more or less the same, with negligible difference between estimated costs for the queries.

The only differences I could see between the plans were the wait stats...

Query #1

  • WaitType = ASYNC_NETWORK_IO
  • WaitCount = 77,716
  • WaitTimeMs = 24,234

Query #2

  • WaitType = ASYNC_NETWORK_IO
  • WaitCount = 75,261
  • WaitTimeMs = 23,662

Query #3

  • WaitType = ASYNC_NETWORK_IO
  • WaitCount = 55,434
  • WaitTimeMs = 20,280

That's an extra 3-4 seconds, between including and not including the GETDATE() column in the result set, just waiting for whatever's running the query to acknowledge it has consumed the data and is ready for more.

In my case, I was using SSMS to execute the queries. So, I can only put it down to SSMS dragging its heels to render that extra column, which amounted to about 75 MB (10M x 8 bytes).

Having said that, the bulk of the time is obviously taken up with scanning all 10 million rows.

Unfortunately, I think the extra execution time to include your GETDATE() column is unavoidable.

Upvotes: 2

Related Questions