batch query ?!

[Lloyd Dupont]

I just watch an overview of ADO.NET 2 on MSDN TV.
In that demo the SqlDataAdapter featured a batch mode where you cold submit
update of many line with one query.
(instead of the line by line update as now)
I thought it was a great feature.

Now in my own PocketPC database application (www.ihookdb.com I have my
own "data access layer" and I thought it would be nice to use batch update
too. (and because it's my code I could do it today

And there a few question came to my mind (on about how to do that)

1. I was thinking, oh well man, it's simple, just queue the query with ';'
in the middle.
but there is a problem
if I write
UPDATE MyTable (field1, field2) VALUES (@field1_1, @field2_1) WHERE
ID=@field3_1; UPDATE MyTable (field1, field2) VALUES (@field1_2, @field2_2)
WHERE ID=@field3_2;..... N times (where N is my batch size)

(notice I write @field_1 for 1st query, @field1_2 for the second, etc ...)

but what if I use a batch size of 100 and they submit only 3 line changes ?
should I create the query depending on the number of changes ?
how to do that cleanly ?

2. but there is worst !
lest say I do an INSERT with an ID Identity column, I should have a SELECT
@@IDENTITY after my insert. but what happened if I do many insert at a time
?!....


Any SQL Admin to rescue me ?!!

 

[Pablo Castro [MS]]

1. Here you can do the same we do in ADO.NET 2.0: we iterate over the change
list (in our case the modified rows in a DataTable) and extend the DML
statement in each iteration. We stop either when we reach the
BatchUpdateSize limit, or when we have no more changes.

2. In SQL Server you can send a batch with multiple inserts/updates/deletes
and intermixed selects, you can't map-back the result-sets comming from the
select statements to their corresponding insert/update/delete statement
(because it might not be a 1-1 ratio between select and dml). So there are
two options I can think of:
a. Return identities and any value you want to refresh in your client cache
through output parameters instead of a resultset. This is what we do in
ADO.NET 2.0.
b. Return a "marker" resultset at the end of each individual change. For
example, just do "SELECT 'marker' my_special_marker". So when you see the
marker you know that you can move to the next resultset. This is certainly
less elegant (and slower), but it can help if option a. is not good for some
reason.


BTW - there are many other aspects that make batching kind of tricky. If you
plan to support arbitrary SQL statements you have to be careful to consider
variable scoping and name clashes, failure cases in the middle of the batch,
etc.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.

 

[Lloyd Dupont]

Thanks for this answer Pablo !

--
ihookdb
Get your data mobile
http://www.ihookdb.com