title: Recommended index and query improvement
body: <p>I have this cross server query that inserts a large amount of rows from our log database to our data warehouse.  The problem is right now the job is running over 15 hours.  Need to drastically reduce this.  Need advice on the best index for this query or if any improvements can be made on the query itself.  I'm thinking an index on URL, Timestamp for the fromDatabase and ID Timestamp for toDatabase.  But not sure on the best order of the key columns or if I should use included, etc.  All help is greatly appreciated.</p>
  
  <pre><code>  SELECT @SQL = N'
      INSERT INTO ' + @ToDatabase + '.dbo.Log
          (
          ImportDateTime
          ,ServerSource
          ,DatabaseSource
          ,Id
          ,Type
          ,UserName
          ,AppCode                        
          ,SubscriptionCode
          ,Duration
          ,ServiceNamespace
          ,ServiceName
          ,MethodName
          ,Parameters
          ,[Message]
          ,StackTrace
          ,Url
          ,UrlReferrer
          ,Browser
          ,BrowserVersion
          ,Platform
          ,Timestamp
          ,IpAddress
          ,EriAccountId
          )
          (
              SELECT
              ''' +  CONVERT(VARCHAR(50),@ImportDateTime) + '''
              ,''' +  @ServerSource + '''
              ,''' +  @DatabaseSource + '''
              ,Id
              ,Type
              ,UserName
              ,AppCode
              ,SubscriptionCode
              ,Duration
              ,ServiceNamespace
              ,ServiceName
              ,MethodName
              ,Parameters
              ,Message
              ,StackTrace
              ,Url
              ,UrlReferrer
              ,Browser
              ,BrowserVersion
              ,Platform
              ,Timestamp
              ,IpAddress
              ,EriAccountId
  
           FROM (
                  select
                  Id
                  ,Type
                  ,UserName
                  ,AppCode
                  ,SubscriptionCode
                  ,Duration
                  ,ServiceNamespace
                  ,ServiceName
                  ,MethodName
                  ,Parameters
                  ,Message
                  ,StackTrace
                  ,Url
                  ,UrlReferrer
                  ,Browser
                  ,BrowserVersion
                  ,Platform
                  ,Timestamp
                  ,IpAddress
                  ,EriAccountId
  
      from openquery([' + @ServerSource + '],
                      ''select 
                      Id
                      ,Type
                      ,UserName
                      ,AppCode
                      ,SubscriptionCode
                      ,Duration
                      ,ServiceNamespace
                      ,ServiceName
                      ,MethodName
                      ,[Parameters] = CONVERT(NVARCHAR(MAX),[Parameters])
                      ,[Message]
                      ,StackTrace
                      ,Url
                      ,UrlReferrer
                      ,Browser
                      ,BrowserVersion
                      ,Platform
                      ,Timestamp
                      ,IpAddress
                      ,EriAccountId           
                      FROM ' + @FromDatabase + '.dbo.[Log] WITH (NOLOCK)
                      WHERE URL LIKE ''''http://online%'''' AND CONVERT(DATETIME2, TimeStamp) &gt; ''''' + CONVERT(NVARCHAR(50),@AssessorDeploymentTimestamp) + ''''' AND CONVERT(DATETIME2,TimeStamp) &gt; ''''' +  CONVERT(NVARCHAR(50),@DateCollected) + ''''' '') o
                      WHERE NOT EXISTS 
                          (SELECT 1
                          FROM ' + @ToDatabase + '.dbo.Log b 
                          WHERE b.id = o.id
                          AND CONVERT(DATETIME2, b.TimeStamp) &gt; ''' +  CONVERT(NVARCHAR(50),@DateCollected) + '''
                          AND b.ServerSource = ''' +  @ServerSource + '''
                          )
  
                  ) a
          )'
  </code></pre>