title: Sql server query with lots of substrings performance
body: <p>I have stored procedure in which i perform a bulk insert into a temp table and perform substring on its field to get the different row required for the main table.
  The no. of columns for the main table is 66 and the row added after each run of the sp is approx 5500. 
  Code for bulk insert part:</p>
  
  <pre><code>    CREATE TABLE [dbo].[#TestData] (
      logdate DATETIME,
      id CHAR(15),
      value VARCHAR(max)
      )
  
  BEGIN TRANSACTION
  
  DECLARE @sql VARCHAR(max)
  
  SET @sql = 'BULK INSERT [dbo].[#TestData] FROM ''' + @pfile + ''' WITH (
      firstrow = 2,
      fieldterminator = ''\t'',
      rowterminator = ''\n''
      )'
  
  EXEC(@sql)
  
  IF (@@ERROR &lt;&gt; 0)
  BEGIN
      ROLLBACK TRANSACTION
  
      RETURN 1
  END
  
  COMMIT TRANSACTION
  </code></pre>
  
  <p>Code for substring part :</p>
  
  <pre><code>CASE 
          WHEN (PATINDEX('%status="%', value) &gt; 0)
              THEN (nullif(SUBSTRING(value, (PATINDEX('%status="%', value) + 8), (CHARINDEX('"', value, (PATINDEX('%status="%', value) + 8)) - (PATINDEX('%status="%', value) + 8))), ''))
          ELSE NULL
          END,
  </code></pre>
  
  <p>This substring code is used in insert into and is similar for all the 66 columns.
  It takes around 20-25 sec for the sp to execute. I have tried indexing on temp table,droped foreign keys,droped all indexes,droped primary key but still it takes the same time.
  So my question is can the performance be improved?</p>
  
  <p><strong>Edit:</strong> The application for interface is visual foxpro 6.0.
  As sql server is slow with string manipulation and doing all the string manipulations on foxpro now. New to foxpro Any suggestions how to send null from foxpro to sqlserver?
  Never worked with null in foxpro 6.0.</p>