I think a lot of people know that in Oracle, stored procedures can be passed in an array (such as int[]), that is, you can pass multiple records to the data to update together. Reduce the number of requests to the database.
But what about SQL Server? Bulk INSERT This is a lot of people know, I also know, but unfortunately, I have never used, only to guide the data will be considered, but the Guide data DTS is not more convenient?
An item on hand, there are several features, each need to update the N (n<1000) record, the record is not much, but if you only update one at a time, loop insert, that each function requires N times request database, if there are 1000 concurrent, that database in addition to do you this thing, other work do not have to do. So, you need to minimize database requests and do all the records at once.
Fortunately, SQL Server provides us with a new feature that uses XML (2000 seems to have no function).
Let's assume one such requirement: The user updates a book and needs to update n chapters.
The general idea is to update the book first, and then loop through the chapters and N times to update the chapter table of the data. You can look at this performance.
Let's try it in XML.
Stored procedures that take advantage of XML updates
Copy Code code as follows:
Create PROCEDURE Up_book_insert
(
@BookId INT,
@ChapterXml XML
)
As
BEGIN
CREATE TABLE #table
(
Chapterid INT,
Chaptername VARCHAR (255),
Price INT
);
INSERT #table
SELECT *
From (
SELECT x.c.value (' id[1] ', ' int ') as Chapterid,
X.c.value (' name[1] ', ' varchar (255) ') as Chaptername,
X.c.value (' price[1] ', ' int ') as Price
From @ChapterXml. Nodes (' Chapter ') as X (c)--note: the X (c) namespace here is required
) T;
INSERT into Tbchapter (Bookid,chapterid,chaptername,price)
SELECT @BookId, chapterid,chaptername,price from #table;
End
In fact, the temporary table can be removed from the stored procedure.
And then we'll run the next look
Executing stored procedures
Copy Code code as follows:
EXEC up_book_insert 10000, ' <Chapter><Id>268</Id><Name> No. 268 Chapter </NAME><PRICE>100 </Price></Chapter><Chapter><Id>273</Id><Name> No. 273 Chapter </name><price >100</Price></Chapter><Chapter><Id>275</Id><Name> No. 275 Chapter </Name>< Price>100</price></chapter> '
What do you think? It's not bad. You only need to parse the XML format in the stored procedure.
In C #, the XML format can be passed into the dbtype.string type.
Write a function to generate an XML-formatted string
Generate XML-formatted functions
Copy Code code as follows:
public static string Formatxmlinfo (List<chapterinfo> List)
{
if (list==null| | List. count<=0)
{
return String.Empty;
}
StringBuilder sb = new StringBuilder ();
foreach (chapterinfo info in list)
{
Sb. AppendFormat ("<chapter><id>{0}</id><name>{1}</name><price>{2}</price ></Chapter> ", info. Chapterid, Info. Chaptername, Info. Price);
}
Return SB. ToString ();
}
Well, it's done.
Performance specific How, has not been tested, but the point is that, than multiple requests to the database, or in the stored procedure to loop the string of the efficiency of the split.
