Inserting 5.3M record into temp table is taking a long time

Question

I am inserting 5308194 records into this #Temp table, and it is taking about 8 seconds and was thinking that it shouldn't take that much time

Is there a better way to do this, or maybe BULK Insert, creating the temp table before the insert?

SELECT g.CustomerId, g.LogDate
INTO #Temp
FROM vwGuidelineLog g --nolock
WHERE g.LogDate >= '2017-10-01' 
  AND g.LogDate < DATEADD(DAY, 1, '2018-09-30')

DDL

CREATE VIEW [dbo].[vwGuidelineLog]
WITH SCHEMABINDING 
AS
    SELECT        
        GuidelineLogID, LogDate, FileName, CustomerID, GuidelineLinkId, CountryId
    FROM            
        dbo.GuidelineLog
    WHERE        
        (GuidelineLinkId IS NOT NULL)

Clustered index

CREATE UNIQUE CLUSTERED INDEX [IdX_vwGuidelineLog] 
ON [dbo].[vwGuidelineLog] ([GuidelineLogID] ASC)

Index for LogDate:

CREATE NONCLUSTERED INDEX [IDX_GuidelineLogDate] 
ON [dbo].[vwGuidelineLog] ([LogDate] ASC)

Answer 1

Not having an index on GuidelineLinkId isn't going to help, as that means that the Data Engine needs to scan the entire table. Considering that you have 5,308,194 rows that fulfil the requirement GuidelineLinkId IS NOT NULL AND logDate >= '2017-10-01' and LogDate < dateadd(day, 1, '2018-09-30'` I would guess you have a lot more rows than 5.3M in there. (so that's a lot to check).

I would, personally, consider adding an index on that column (probably with an INCLUDE on the other columns). Maybe:

CREATE NONCLUSTERED INDEX IDX_GuidelineLinkId
    ON dbo.GuidelineLog (GuidelineLinkId ASC)
    INCLUDE (GuidelineLogID, LogDate,LogDate, FileName, CustomerID, CountryId);

Dan's advice of altering the view IDX_GuidelineLogDate is also probably a good idea:

ALTER INDEX [IDX_GuidelineLogDate]
    ON [dbo].[vwGuidelineLog] ([LogDate] ASC)
    INCLUDE (CustomerID);