0

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)
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 2
    DDL for `vwGuidelineLog ` is going to be really important here (I assume it's a view, based on it's name) – Thom A Oct 12 '18 at 12:59
  • Well, waht hardwrawre? RAM? YOu are right, it could go faster - on a server with all SSD or something like that. Otherrwise, sorry, that is not realyl that bad, you know. – TomTom Oct 12 '18 at 13:05
  • 2
    I would expect performance to improve with `CustomerID` as an included column on the view index. – Dan Guzman Oct 12 '18 at 13:08
  • Also, you forgot to include the view's clustered index DDL. – Dan Guzman Oct 12 '18 at 13:09
  • Is there an `INDEX` on `GuidelineLinkId` on the table `GuidelineLog`? – Thom A Oct 12 '18 at 13:13
  • @Larnu No I didnt add any Index on the table itself just the view. –  Oct 12 '18 at 13:27
  • Possible duplicate of [How can i speed up this Indexed View?](https://stackoverflow.com/questions/1004944/how-can-i-speed-up-this-indexed-view) – Ivan Starostin Oct 12 '18 at 13:34

1 Answers1

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);
Thom A
  • 88,727
  • 11
  • 45
  • 75
  • would I remove the index on the view then? –  Oct 12 '18 at 13:48
  • @Jefferson `IDX_GuidelineLogDate` is one of the indexes on your view. – Thom A Oct 12 '18 at 14:07
  • Didnt think that I can have GuidelineLogID on both the table and view. So [IdX_vwGuidelineLog] ON [dbo].[vwGuidelineLog] and dbo.GuidelineLog.IDX_GuidelineLinkId. –  Oct 12 '18 at 14:13
  • OH, sorry, you mean the clustered index, I had missed, sorry. The problem is the one on the `VIEW` isn't helping, as the `VIEW` still needs to be filtered. – Thom A Oct 12 '18 at 14:16