Next time a mistake like that happens, it's OK to just edit the post. 🙂

Also, it's much easier to read a query when it's posted inside a sql code block....

What I do for my Rebuild Index maintenance tasks is that I perform the following steps:

1. Backup the transaction log

2. Change the recovery model to Bulk-logged

3. Execute the index Rebuild...

How about using RANK() OVER?

See how this performs:

WITH FullResults (RecordsDate, RecordsJoinValue, MoreRecordsDate, MoreRecordsJoinValue,MoreRecordsValue, MoreRecordsRank)

AS

(

SELECT #Records.[Date] AS RecordsDate

, #Records.JoinValue AS RecordsJoinValue

, #MoreRecords.[Date] AS MoreRecordsDate

, #MoreRecords.JoinValue AS MoreRecordsJoinValue

, #MoreRecords.Value AS MoreRecordsValue

, RANK()...

The OP made no mention of sending an email per each result, which is why a set based solution was recommended.

Unfortunately, I don't think there currently is a purely set-based...

Thanks for all your replies and help everyone!

@mister.magoo

Ah, I see your point now. This is not a RBAR problem. I'm grateful for your input. The reason that I'm so...

@mister.magoo

In this case, each select statement is returning a single row, instead of a set. While the UNION method works, in the long-term it will simply become another method that...

I'm simply trying to figure out why the RBAR solution to this problem is orders of magnitude faster than the obvious set-based solution. I'd like to figure out what exactly...

I haven't even attempted it. To do so would be to execute 3600 select statements connected by UNION ALL. I cannot imagine that that would do anything good for performance.

@jeff

Here is how the devices are distributed.

Server_Id, Device_Id

1 has 1540 Device_Id's

2 has 742 Device_Id's

3 has 357 Device_Id's

4 has 26 Device_Id's

5 has 917 Device_Id's

It had no effect sadly.

There is a Fifth ServerId, but it only has 160,000 rows of the 18 million. There are no other server ID's.

*Edit* I Added the fifth and final ServerId to my...

The index already exists.

Also, keep in mind that the MAX() Method is only taking 650ms, so it's not taking an excessively long time. It's just that the UNION ALL method...

Fairly slow actually. 🙂 Average for two runs is 19906.0

I should also mention that as of this writing, this table contains over 18 million rows, and that is only expected...

Yes and no. I believe that the book is dated a bit though. SQL Server Express 2005, the database limit was 4G. That limit was increased to 10G in SQL...

JB, I would recommend starting your own thread to ask for help, instead of posting in a 2 year old thread.