CODEWITHSUNDEEP
c#sqlstored-procedures
Madeleine
Madeleine

Reputation: 2182

C# - batch execute stored procedures

I currently have a WinForms application written using C#.

All our updates are done through executing stored procedures. We have a situation on one screen where there are up to 60 updates called on that screen. At the moment each update call is processed one by one, and some clients who have slow connectivity between the client and the server are experiencing speed issues. I'm pretty sure that if I can send all these calls to the database at once, that it would save a lot of wait time on the client side (currently for each of those 60 calls, ExecuteNonQuery is called and the client waits for a true / false result before continuing and this is causing a delay).

What would be the best way to bundle all these calls into one SQL call? Was thinking of putting all the code into a transaction but need to avoid locks as much as possible as there are many concurrent users using the system at any given point.

EDIT: The purpose of the stored procs is literally to update records on the server. We don't even require any response from the stored procs that are being executed. In essence, I want to bundle a bunch of calls the code below into one call and wondering what the best way to do so is

        public virtual bool Update(DataRow dataRow, Guid userId)
    {
        SqlCommand cm = null;
        bool ret = true;
        try
        {
            cm = Utilities.GetSqlUpdateCommand(dataRow, userId);
            ExecuteNonQuery(cm);              
        }
        catch (SqlException ex)
        {
            LogDataAccessBaseError(ex);
            ret = false;
            throw;
        }
        finally
        {
            if (cm != null)
            {
                cm.Dispose();
            }
        }

        return ret;
    }

Upvotes: 2

Views: 2385

Answers (4)

Bogdan M.
Bogdan M.

Reputation: 1128

Half sync-half async pattern might help. Basically you put all the request into a queue then the items are extracted by the clients.

See http://www.cs.wustl.edu/~schmidt/PDF/PLoP-95.pdf for the abstract concept.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239684

Are you using SQL Server 2008 or later, and are all of the stored procedure calls identical (other than individual parameters)?

If so, you might look into creating a version of the stored procedure that takes a Table-Valued Parameter (ADO). This would allow you to pass all of the rows across to SQL server as a single table.

If not, then I assume that GetSqlUpdateCommand is currently constructing an SqlCommand with the stored procedure name and adding appropriate parameters. A different approach would be:

Change GetSqlUpdateCommand so that it can accept an existing SqlCommand object. It then appends a new call of exec StoredProcedure @Parm1,@Parm2,@Parm2, and adds those parameters. It would need to ensure that it uses new names for the parameters it's adding so that it doesn't conflict with other exec calls. You may also need to adjust this function so that it can detect when you're nearing the limit of the number of parameters you can pass, at which point it should execute this command, then create a new one.

E.g. it builds up a text command like:

exec MegaProcedure @Parm1,@Parm2,@Parm3
exec MegaProcedure @Parm4,@Parm5,@Parm6
exec MegaProcedure @Parm7,@Parm8,@Parm9

Finally, I think you don't understand what transactions are/do - they don't magically bind multiple calls together so they execute with less overhead/roundtrip times. They are purely and simply a mechanism by which work can be rolled back, so they wouldn't help here.

(And, in fact, every statement in SQL runs inside a transaction anyway - it's just that, by default, that transaction is opened automatically when a statement starts, and is committed when the statement completes successfully)

Upvotes: 1

Brandon Moore
Brandon Moore

Reputation: 8780

Do the calls need to be made in any particular order? Multi-threading could help if not.

The best answer to your question depends on how much control you have over each end of the process. Ideally, you would just make one call that returned one big chunk of data containing everthing you need. Remember that when returning data from stored procedures, the stored procedure can return multiple select statements and you can loop through the returned recordsets. So one stored proc could potentially return everything you need. Maybe it doesn't make sense to wrap everything up in a single stored proc depending on what parameters a query needs, but you could probably wrap it up into just a few calls at least.

Upvotes: 0

Alexander Galkin
Alexander Galkin

Reputation: 12534

A straightforward solution could be to implement a new stored procedure that takes necessary parameters and executes all necessary stored procedures on the server.

Another way to have it completely on client side is, as you point out yourself, to use transactions. Since I know nothing about what your stored procedures are supposed to do, I can't tell noting about how to avoid locks here.

Upvotes: 0

Related Questions