Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have an application that consists of Web-services that make calls to a sharded SQL Server database. Each database node has a SQL CLR stored procedures, which given some number of records to process, retrieves data, performs some computation and returns the results for each record to the Web server. As there are several nodes I use asynchronous execution to start the execution of each procedure and then get the results. Below is what I have now.

The web-services do something like the following:

InsertIntoInputTable(int server);
IAsyncResult[] asyncRes = ExecuteProcedure();    
GetResults(asyncRes);

Where InsertIntoInputTable()is :

private InsertIntoInputTable(int server)
{
    using (SqlCommand sqlcmd = 
        new SqlCommand("INSERT INTO InputTable VALUES (@X, @Y, @Z)", 
        connection[server]))
    {
        sqcmd.Parameters.AddWithValue("@X", x_value);
        sqcmd.Parameters.AddWithValue("@Y", y_value);
        sqcmd.Parameters.AddWithValue("@Z", z_value);
    }
}

ExecuteProcedure() is:

private IAsyncResult[] ExecuteProcedure()
{
    // initiate reader requests
    IAsyncResult[] asyncRes = new IAsyncResult[serverCount];
    for (int s = 0; s < serverCount; s++)
    {
        sqlcmds[s] = connections[s].CreateCommand();
        sqlcmds[s].CommandText = 
            String.Format("EXEC [DB].[dbo].[StoredProc] @tableName");
        sqlcmds[s].Parameters.AddWithValue("@tableName", inputTableName);
        asyncRes[s] = sqlcmds[s].BeginExecuteReader(null, sqlcmds[s]);
    }
    return asyncRes;
}

and GetResults() is:

private int GetResults(IAsyncResult[] asyncRes)
{
    WaitHandle[] waitHandles = new WaitHandle[asyncRes.Length];
    for (int i = 0; i < waitHandles.Length; i++)
        waitHandles[i] = asyncRes[i].AsyncWaitHandle;
    for (int numWaits = 0; numWaits < waitHandles.Length; numWaits++)
    {
        int index = WaitHandle.WaitAny(waitHandles, -1);
        SqlDataReader reader = sqlcmds[index].EndExecuteReader(asyncRes[index]);
        while (reader.Read())
        {
            // process records ...
        }
        reader.Close();              
    }
}

The SQL CLR stored procedure:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProc(string tableName)
{
    // record to be sent back 
    record = new SqlDataRecord(GetRecordMetaData());
    SqlContext.Pipe.SendResultsStart(record);
    // read input records from input table
    SqlConnection conn = new SqlConnection("context connection=true");
    SqlCommand cmd;
    cmd = new SqlCommand(String.Format("SELECT X, Y, Z from {0}", tableName), conn);
    using (SqlDataReader reader = cmd.ExecuteReader())
    {
        while (reader.Read())
        {
            // retrieve data from database
            // perform computation associated with each record (X, Y, Z)
            // set return record value
            SqlContext.Pipe.SendResultsRow(record);
        }
    }
    SqlContext.Pipe.SendResultsEnd();
}

This works fine, but what I will need is to be able to send some records back to one of the servers for further processing. So inside the GetResults() function as I get each record I will have to check some criteria and possibly send back the record for further processing. I have been thinking about something along the following lines:

  • make the SQL CLR stored procedure run in a while(true) loop
  • after a record is processed remove it from the input table
  • at the end of each iteration check a special table in the DB that indicates if the processing is done and if so break out of the while(true) loop
  • in the GetResults() function check the criteria for each record returned
  • if it needs further processing insert into the input table for the appropriate server
  • if no records need further processing update the special status table to indicate that all is done

So the SQL CLR procedure might look like:

[Microsoft.SqlServer.Server.SqlProcedure]
public static void StoredProc(string tableName)
{
    // record to be sent back 
    record = new SqlDataRecord(GetRecordMetaData());
    SqlContext.Pipe.SendResultsStart(record);
    while(true)
    {
        // read input records from input table
        SqlConnection conn = new SqlConnection("context connection=true");
        SqlCommand cmd;
        cmd = new SqlCommand(String.Format(
                "SELECT X, Y, Z from {0}", tableName), conn);
        using (SqlDataReader reader = cmd.ExecuteReader())
        {
            while (reader.Read())
            {
                // retrieve data from database
                // perform computation associated with each record (X, Y, Z)
                // set return record value
                SqlContext.Pipe.SendResultsRow(record);

                // remove processed input record from input table

            }
        }
        // check status table
        if (done)
            break;
    }
    SqlContext.Pipe.SendResultsEnd();
}

And the modified GetResults:

private int GetResults(IAsyncResult[] asyncRes)
{
    WaitHandle[] waitHandles = new WaitHandle[asyncRes.Length];
    for (int i = 0; i < waitHandles.Length; i++)
        waitHandles[i] = asyncRes[i].AsyncWaitHandle;

    bool done = true;
    for (int numWaits = 0; numWaits < waitHandles.Length; numWaits++)
    {
        int index = WaitHandle.WaitAny(waitHandles, -1);
        SqlDataReader reader = sqlcmds[index].EndExecuteReader(asyncRes[index]);
        while (reader.Read())
        {
            // process records ...

            // if criteria is met send record back for further processing
            // to the appropriate server
            if (criteria)
            {
                done = false;
                InsertIntoInputTable(appropriate_server)
            }

        }
        reader.Close();              
    }
    if (done)
        // update special status table so that all stored procedures can finish
    else
       // I somehow need to process the records 
       // that were sent back for further processing
       // How???
}

My problem is the following. At the Web-server, let's say that I have finished processing all of the records returned from DB server 0. Now I move on to the server with index given by the next WaitAny(), which let's say is server 1. If a record returned from server 1 is put into the "processing queue" (input table) of server 0, how do I go back to process the record returned from that server. I also suspect but I am not sure that reader.Read() will not return false until SendResultsEnd() is executed. One possibility was to dynamically add WaitHandles, but there is a restriction of 64 WaitHandles, which will be a problem. All in all, I am not sure how this sort of functionality can be implemented and any help will be appreciated.

share|improve this question
    
Why is it important that all the stored procedure calls need to block until a single "request" is processed? This makes no sense to me. If the code is abstracted well, you should be able to input/process/gather/check either iteratively or recursively (assuming you're calling the same process but with different data? -- you didn't say). –  Jon Seigel Apr 30 '13 at 16:24
    
I do not want the stored procedure calls to block on each other. This is why I am trying to use asynchronous requests. Maybe I shouldn't be using WaitHandles and that is my problem. I basically want a Stored procedure that keeps running until it is given records to process. At the Webserver I want to be able to get the results and maybe send more records for processing to any one of the procedures by inserting into their input tables. What I am not sure about is how I should be processing the records returned from each procedure. Maybe I need to use callbacks? –  Lazar Kacho Apr 30 '13 at 16:52
1  
That's another thing. I don't understand the need for a physical table that holds the input data. Can you use a table-valued parameter instead (SQL Server 2008+)? I'm not sure it really matters if you use the asynchronous methods, or if you create your own pool of threads, but I would stay away from trying to loop-wait a CLR procedure. If you need a queue, there are other methods to do that, like Service Broker. –  Jon Seigel Apr 30 '13 at 19:46
    
My reasoning behind using a temporary table is so that I can update the input records dynamically. I am not quite sure how this would work with a table-value parameter. As for loop-waiting a CLR procedure, I don't really want to do that either, but I don't know how to make this work. Maybe I do need to look into Service Broker. How would I interact with Service Brooker at the Web server? In particular how do I get back processed records and how do I put more work into the queue if needed? –  Lazar Kacho Apr 30 '13 at 20:14
    
TVPs are read-only. But, why are the inputs being updated in the first place? Normally you would call a function with inputs, and it produces output. Am I missing something? There are so many non-standard things you're doing here that I'm getting a bit lost following the process. For Service Broker, I'll point you to the documentation because it can't be explained in a simple comment. –  Jon Seigel Apr 30 '13 at 22:16
add comment

closed as off-topic by Max Vernon, Paul White, bluefeet, RolandoMySQLDBA, Kin Sep 27 '13 at 17:15

This question appears to be off-topic. The users who voted to close gave this specific reason:

  • "Too localized - this could be because your code has a typo, basic error, or is not relevant to most of our audience. Consider revising your question so that it appeals to a broader audience. As it stands, the question is unlikely to help other users (regarding typo questions, see this meta question for background)." – Max Vernon, bluefeet, RolandoMySQLDBA, Kin
If this question can be reworded to fit the rules in the help center, please edit the question.

Browse other questions tagged or ask your own question.