20-mins (190GB in 221s @ 880MB/s - my Client's most important database)

Windows Server 2008 R2 Enterprise x64 (VM 4vCPU 14GB)

SQL Server 2008 R2 Enterprise x64

Native backups via Ola Hallengren

Compression

Checksum

8-stripes

BufferCount 64

Blocksize...

/* If you're using SQL2005+ you could try something this */

;

WITH CTE ( Test )

AS ( SELECT Names

...

[font="Courier New"]/* Perhaps you could try something like this - using a Calendar table ? */

;

WITH DailyClientCount

AS ( SELECT CT.[Date]...

Just an idea, if you can live with the loss of precision, you could try dividing both @ts_now and [timestamp] by 1000 inside the [number] argument of the DATEADD function,...

/* If I understand correctly, this may produce the desired result */

UPDATE @TableA

SET Clean_City = B.output_city

FROM @TableA AS A

...