Reduce number of database calls

Question

I have a stored-procedure which accepts five parameters and performing a update on a table

Update Table
Set field = @Field
Where col1= @Para1 and Col2=@Para and Col3=@Para3 and col4 =@aPara4

From the user prospective you can select multiple values for all the condition parameters. For example you can select 2 options which needs to match Col1 in database table (which need to pass as @Para1)

So I am storing all the selected values in separates lists.

At the moment I am using foreach loop to do the update

  foreach (var g in _list1)
            {
                foreach (var o in _list2)
                {
                    foreach (var l in _list3)
                    {
                        foreach (var a in _list4)
                        {
                           UpdateData(g, o, l,a);
                        }
                    }
                }
            }

I am sure this is not a good way of doing this since this will call number of database call. Is there any way I can ignore the loop and do a minimum number of db calls to achieve the same result?

Update

I am looking for some other approach than Table-Valued Parameters

Is that all that SP is doing? Are you free to perform this update without the SP? — Sergey Kalinichenko, Sep 16 '13 at 15:55
Are you in control of the DB schema? Specifically, are you allowed to create user-defined types? — Sergey Kalinichenko, Sep 16 '13 at 15:56
Then you cannot take the Table-Valued parameters approach :-( — Sergey Kalinichenko, Sep 16 '13 at 16:02
@dasblinkenlight: Yes I am looking for some other posibilities — huMpty duMpty, Sep 16 '13 at 16:04
Are you at least in control of the SP? Can you change this specific SP, or define a new SP of your own, or is the schema completely off limits to you? — Sergey Kalinichenko, Sep 16 '13 at 16:11
@dasblinkenlight: Yes, I can change this sp or add new one to change the parameters — huMpty duMpty, Sep 16 '13 at 16:13

score 1 · Answer 1 · answered Sep 16 '13 at 15:56

One possible way would be to use Table-Valued Parameters to pass the multiple values per condition to the stored procedure. This would reduce the loops in your code and should still provide the functionality that you are looking for.

If I am not mistaken they were introduced in SQL Server 2008, so as long as you don't have to support 2005 or earlier they should be fine to use.

score 1 · Answer 2 · answered Sep 16 '13 at 16:10

Consider using the MS Data Access Application Block from the Enterprise Library for the UpdateDataSet command.

Essentially, you would build a datatable where each row is a parameter set, then you execute the "batch" of parameter sets against the open connection.

You can do the same without that of course, by building a string that has several update commands in it and executing it against the DB.

score 1 · Answer 3 · answered Sep 16 '13 at 16:19

1

Since table-valued parameters are off limits to you, you may consider an XML-based approach:

Build an XML document containing the four columns that you would like to pass.
Change the signature of your stored procedure to accept a single XML-valued parameter instead of four scalar parameters
Change the code of your stored procedure to perform the updates based on the XML that you get
Call your new stored procedure once with the XML that you constructed in memory using the four nested loops.

This should reduce the number of round-trips, and speed up the overall execution time. Here is a link to an article explaining how inserting many rows can be done at once using XML; your situation is somewhat similar, so you should be able to use the approach outlined in that article.

answered Sep 16 '13 at 16:19

Sergey Kalinichenko

714,442
84
1,110
1,523

This is really interesting, will try. – huMpty duMpty Sep 16 '13 at 16:21
One more this, I am not sure how we can associate multiple parameters to where condition – huMpty duMpty Sep 16 '13 at 16:27
@huMptyduMpty You need to run an [update from a join](http://stackoverflow.com/a/1293347/335858), and join to the XML data. – Sergey Kalinichenko Sep 16 '13 at 16:29

score 1 · Accepted Answer · edited May 23 '17 at 10:25

1

You can bring query to this form:

Update Table Set field = @Field Where col1 IN {} and Col2 IN {} and Col3 IN {} and col4 IN {}

and pass parameters this way: https://stackoverflow.com/a/337792/580053

edited May 23 '17 at 10:25

Community

1
1

answered Sep 16 '13 at 16:53

Dennis

37,026
10
82
150

score 0 · Answer 5 · answered Sep 16 '13 at 15:59

So long as you have the freedom to update the structure of the stored procedure; the method I would suggest for this would be to use a table value parameter instead of the multiple parameters.

A good example which goes into both server and database code for this can be found at: http://www.codeproject.com/Articles/39161/C-and-Table-Value-Parameters

score 0 · Answer 6 · answered Sep 16 '13 at 16:16

0

Why are you using a stored procedure for this? In my opinion you shouldn't use SP to do simple CRUD operations. The real power of stored procedures is for heavy calculations and things like that.

Table-valued parameters would be my choice, but since you are looking for other approach why don't you go the simpler way and just dynamically construct a bulk/mass update query on your server side code and run it against the DB?

answered Sep 16 '13 at 16:16

Who told you this ***The real power of stored procedures is for heavy calculations and things like that*** ?? – huMpty duMpty Sep 16 '13 at 16:18
There are some people I know that think this. I agree that you can use SPs to do whatever you want, but in my opinion there are some situations where you really don't gain that much by using SP. But if we talk about heavy calculations against data that is stored in the DB, its definitely better to use SP than try to do it other way. – Sep 16 '13 at 20:15

Reduce number of database calls

6 Answers6