title: Efficiently load large JSON file into SQL Server
body: <p>I have been attempting to identify what the most effective way to load a large JSON file into SQL Server is.</p>
  
  <p>I have a rather primitive API that most helpfully returns me a 150+ MB JSON string with ~450k rows and 12 columns. Ultimately I want to be able to use this in power-query, I figure the simplest way to store, index, then query it efficiently will be in SQL Server. </p>
  
  <p>I have asked regarding filtering the data from the source (the most logical solution, but for the purposes of this question, count it out).</p>
  
  <p>I have attempted code like the following to prove it will work</p>
  
  <pre><code>DECLARE @JSON VARCHAR(MAX)
  
  select @JSON = BulkColumn
  from OPENROWSET 
  (BULK 'C:\temp\test.txt', SINGLE_CLOB) 
  AS j
  
  Select * FROM OPENJSON (@JSON)
  </code></pre>
  
  <p>The issue I have is that after 7 minutes query time on a laptop with fast SSD, 16gb ram and i7-7800hq I decided there must be a better way of going about this.</p>
  
  <p>I'm happy to try any other language (say python, r, C# clr) </p>
  
  <p>Security isn't my primary concern, I think I should be able to refresh the data in a few seconds rather than waiting several minutes.</p>