try this index on your heap table......not quite sure why you are worried about data load speeds...seems you are already deleting data over three days old and adding new. Therefore I am going to assume you have a period at some time to perform this task. You could drop the index first and then recreate once loaded...that may help...but an index on your date may also assist the delete.

anyways...food for thought...here is some test data to paly with...its not a replica of your system ...but it does have 5M rows and some hotels/sites.

kind regards

USE [tempdb]

GO

DROP TABLE [dbo].[Hotel]

GO

DROP TABLE [dbo].[Site]

GO

DROP TABLE [dbo].[TransData]

GO

SELECT TOP 5000000 ---- NOTE 5 MILLION rows

TranID = IDENTITY(INT, 1, 1),

SiteID = 1 + CAST(Abs(Checksum(Newid()) % 10000) AS INT),

HotelID = 1 + CAST(Abs(Checksum(Newid()) % 1000 ) AS INT),

SalesAmount = CAST(Rand(Checksum(Newid())) * 99 + 1 AS DECIMAL(5, 2)),

TransDate = Dateadd(dd, Abs(Checksum(Newid()))%Datediff(dd, '2010', '2013'), '2010')

INTO TransData

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

CROSS JOIN sys.all_columns ac3

SELECT TOP 500

SiteID = 1 + CAST(Abs(Checksum(Newid()) % 10000) AS INT)

INTO Site

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

GO

SELECT TOP 50

HotelID = 1 + CAST(Abs(Checksum(Newid()) % 1000) AS INT)

INTO Hotel

FROM sys.all_columns ac1

CROSS JOIN sys.all_columns ac2

GO

set statistics time, IO on

declare @jobdate as datetime

set @jobdate = '2011-07-09'

SELECT Hotel.HotelID, Site.SiteID, TransData.TransDate

FROM TransData INNER JOIN

Site ON TransData.SiteID = Site.SiteID INNER JOIN

Hotel ON TransData.HotelID = Hotel.HotelID

WHERE (TransData.TransDate = @jobdate)

set statistics time, IO OFF

/*create index*/

CREATE NONCLUSTERED INDEX [NIX_transdata] ON [dbo].[TransData]

(

[TransDate] ASC,

[HotelID] ASC,

[SiteID] ASC

) ON [PRIMARY]

GO

set statistics time, IO on

declare @jobdate as datetime

set @jobdate = '2011-07-09'

SELECT Hotel.HotelID, Site.SiteID, TransData.TransDate

FROM TransData INNER JOIN

Site ON TransData.SiteID = Site.SiteID INNER JOIN

Hotel ON TransData.HotelID = Hotel.HotelID

WHERE (TransData.TransDate = @jobdate)

set statistics time, IO OFF