I'm trying to read JSON data from a URL in SQL Server 2008, using this code:

DECLARE @temp table (RowNum int, DATA NVARCHAR(max))
DECLARE @url VARCHAR(MAX),
@win INT,
@hr INT,
@Text VARCHAR(8000),
@RowID int,
@Status smallint,
@Accuracy tinyint

Set @url = 'http://www.jsonprovider.com/api/json/2020'
EXEC @hr = sp_OACreate 'WinHttp.WinHttpRequest.5.1', @win OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OAMethod @win, 'Open', NULL, 'GET', @url, 'false'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OAMethod @win, 'Send'
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OAGetProperty @win, 'ResponseText', @Text OUT
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win

EXEC @hr = sp_OADestroy @win
IF @hr <> 0 EXEC sp_OAGetErrorInfo @win 

select  @Text  --<< Result

AND I get my result.

The problem is the process to get this result takes 7 sec in SQL Server, but in normal call by C# application and calling in web browser I get my result in 0.06 sec.

I looking for a solution for improving my code to get a fast result.

This is an example of "just because you can, doesn't mean you should".

SQL Server is not the right tool for this job; the fact that it works at all is amazing. If you want to speed it up, put in in a c# or php program and let SQL server do that job it was intended for. Processing jSON responses is not one of them.

more