Asynchronous output from CLR function

Question

I have create a CLR function like this

public class GETJSONFROMURL
{
   [Microsoft.SqlServer.Server.SqlFunction]
    public static SqlString GETData(SqlString URL) 
    {
        SqlString Data = "N/A";


        using (var webClient = new System.Net.WebClient())
        {

            var json = webClient.DownloadString(new Uri(URL.ToString()));
            // Now parse with JSON.Net
            JObject o = JObject.Parse(json);
            Data = (string)o["Data_results"]["MyData"];


        }
        return Data;


    }
}

and made a function in Sql Like this

CREATE ASSEMBLY GetDataURL
FROM 'C:\GETJSONFROMURL.dll'
WITH PERMISSION_SET = EXTERNAL_ACCESS

CREATE FUNCTION GetDataURL (@URL NVARCHAR(MAX))
RETURNS NVARCHAR(MAX) WITH EXECUTE AS CALLER
AS 
EXTERNAL NAME GetDataURL.GETJSONFROMURL.GETData

when I select this function in query window I get result very fast under 0.03 sec

SELECT [dbo].[GetDataURL] ('http://www.Testserver.com/123')

but when I call this function as multiple call , like inside Triggers after insert something call that select I get delay for get result .

can you please help me to create an asynchronous function which is when I call inside sql as multiple call didn't get delay for returning result .

thanks

Oh my. Your CLR SQL function makes an HTTP call on every row. That will, obviously, be exquisitely slow. Needless to say, you can't possibly take advantage of any async pattern since the function must return a value immediately. (custom CLR functions in SQL server are supposed to be fast -- excessive calculations are bad, let alone making a network call to some other server) I am about 100% certain that this notion of baking this logic into the SQL tier is completely inappropriate and should be handled in the calling code. — Kirk Woll
– Kirk Woll, Commented Oct 16, 2013 at 1:48
I did It before but get result inside sql is very slow 4.6 sec because of that I create CLR — akaco
– akaco, Commented Oct 16, 2013 at 1:57
Why would you think it would be better to make the database server do this rather than the application? — Andrew Barber
– Andrew Barber, Commented Oct 16, 2013 at 2:06
To further @Andrew's point, you should always keep in mind that, generally speaking, SQL tiers can only scale vertically. Clearly, most people who enjoy relational databases also ensure at least a minimum of database checks (FK/UK integrity, etc.), but the underlying goal of ensuring the DB doesn't do too much work should always be considered. In other words, you want the DB to help ensure it maintains only valid data, but extracting JSON from a web service just so the client (presumably one that can scale horizontally) doesn't have to do that work doesn't even begin to qualify. — Kirk Woll
– Kirk Woll, Commented Oct 16, 2013 at 2:31

Hannah Vernon · Accepted Answer · 2013-10-16 02:31:40Z

2

Instead of doing this inside a trigger, you could create a SQL Server Agent job that updates the table every 5 minutes (or more or less often depending on your requirements).

The job could call your CLR function once for every row that doesn't have the data inserted yet.

However, I would strongly suggest you make the client-side software get the data from the URL and insert it into the table.

You could consider having a separate machine get the json data for newly inserted rows; this takes the load off the SQL Server, and allows the client to not worry about getting the data.

answered Oct 16, 2013 at 2:31

Hannah Vernon

3,4711 gold badge34 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

akaco Over a year ago

Yes it's good Idea But the problem is we need live data and that data check by customers take time ... and also we need this happening in our end because we calculate some result over live data and can not wait for Agent Job to done the process .

Hannah Vernon Over a year ago

if you need the data live, you need to have the client-end get the data and insert it with the rest of the row.

akaco Over a year ago

Ok I did It Here ????

Collectives™ on Stack Overflow

Asynchronous output from CLR function

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related