本篇只是個小小紀錄文,紀錄透過SQL-XML將資料匯入資料庫.
情境:
由於小弟日前剛好負責修改公司一支程式,
目前該程式主要作用是在於,去呼叫對方的API將接回來的資料,
判斷如果已存在則更新,
不存在則新增存入DB中,
看似很單純,但只要接收回來的資料較為大量,
回應時間就會變得很慢!
仔細去看一下程式運作流程,
由於對方API回應的格式為XML的資料,
在程式中將XML格式反序列化為物件,
轉換為物件後再轉一層為欲更新MODEL物件,
好像有點繞舌簡單來說,
XML字串>物件>MODEL物件
在透過foreach的方式逐一比對,
如果DB內有資料則更新沒有則新增.
由於小弟日前剛好負責修改公司一支程式,
目前該程式主要作用是在於,去呼叫對方的API將接回來的資料,
判斷如果已存在則更新,
不存在則新增存入DB中,
看似很單純,但只要接收回來的資料較為大量,
回應時間就會變得很慢!
仔細去看一下程式運作流程,
由於對方API回應的格式為XML的資料,
在程式中將XML格式反序列化為物件,
轉換為物件後再轉一層為欲更新MODEL物件,
好像有點繞舌簡單來說,
XML字串>物件>MODEL物件
在透過foreach的方式逐一比對,
如果DB內有資料則更新沒有則新增.
實際測試
為了模擬實際上的環境如下圖片
新增
LocalDB Sampledata.mdf
XmlDto.cs產生XML :
下列為產生XML假資料:
/// <summary>產生假資料</summary>
/// <param name="recordNum">欲產生筆數</param>
/// <returns></returns>
private static string FakeData(int recordNum)
{
Console.WriteLine(string.Format("產生XML假資料開始時間{0}!", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
string Xmlstr = "";
//產生假資料
bookstory BookStoryobj = new bookstory();
BookStoryobj.books = new List<book>();
for (int i = 0; i < recordNum; i++)
{
book obj = new book()
{
BookId = Guid.NewGuid().ToString("N"),
CreateDate = "2016/06/13 12:14:38",
Amount = 100,
BookName = string.Format("［東Y］真經第{0}集", i)
};
BookStoryobj.books.Add(obj);
}
//轉換為XML
XmlSerializer xsSubmit = new XmlSerializer(typeof(bookstory));
using (StringWriter sww = new StringWriter())
using (XmlWriter writer = XmlWriter.Create(sww))
{
xsSubmit.Serialize(writer, BookStoryobj);
Xmlstr = sww.ToString();
// Console.WriteLine(string.Format("物件轉換為XML字串-開始時間{0}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
}
Console.WriteLine(string.Format("產生XML假資料完成時間{0}!,共{1}筆", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss"), recordNum));
return Xmlstr;
}
原有新增資料方法
private static void OldInsert(string XMLData, int InsertTotalCnt)
{
XmlSerializer serializer = new XmlSerializer(typeof(bookstory));
using (StringReader rdr = new StringReader(XMLData))
{
//將XML資料做反序列化
bookstory resultingMessage = (bookstory)serializer.Deserialize(rdr);
List<book_story> booksList = resultingMessage.books.Select(s => new book_story
{
Amount = s.Amount.ToString(),
BookId = s.BookId,
BookName = s.BookName,
CreateDate = Convert.ToDateTime(s.CreateDate)
}).ToList();
try
{
Console.WriteLine(string.Format("原有方法(新增資料)-開始時間{0}!", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
using (SampledataEntities db = new SampledataEntities())
{
//逐一比對物件裡面是否有相同資料,沒有則新增,有則更新
Action<book_story> RYH = (s) =>
{
if (db.book_story.Any(a => a.BookId == s.BookId))
{
var book_storyCollect = db.book_story.Where(f => f.BookId == s.BookId).ToList();
book_storyCollect.ForEach(a =>
{
a.BookName = s.BookName;
a.Amount = s.Amount;
});
}
else
{
db.book_story.Add(s);
}
};
booksList.ForEach(RYH);
db.SaveChanges();
Console.WriteLine(string.Format("原有方法(新增資料)-完成時間{0}!", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")));
}
}
catch (System.Data.Entity.Validation.DbEntityValidationException ex)
{
throw;
}
};
}
實際測試畫面
真的實際上來看花費的時間真的還滿高的,
如果是一千筆是還好,有秒殺的快感~~
目前的解決方式:
由於對方的API回傳的格式既然是XML,
又不想更動過往程式,只好把腦筋動到stored procedure身上,
透過SQL 直接讀取XML 並透過EXCEPT找出差集.
當然這有詢問過我們的DBA大大,亂搞我會被他揍的....冏
由於對方的API回傳的格式既然是XML,
又不想更動過往程式,只好把腦筋動到stored procedure身上,
透過SQL 直接讀取XML 並透過EXCEPT找出差集.
當然這有詢問過我們的DBA大大,亂搞我會被他揍的....冏
如下,直接將API回傳的XML字串帶入,並透過EXCEPT找出差集並新增
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @cnt INT
DECLARE @RevertXml XML
SET @RevertXml = @xmlStr;
WITH A
AS (SELECT Col.value('(BookId)[1]', 'nvarchar(32)') AS BookId,
Col.value('(BookName)[1]', 'nvarchar(30)') AS BookName,
Col.value('(Amount)[1]', 'nvarchar(30)') AS Amount,
CONVERT(DATETIME, Col.value('(CreateDate)[1]', 'varchar(20)')) AS CreateDate
FROM @RevertXml.nodes('/bookstory/books') Tab(Col)
EXCEPT
SELECT [BookId],
[BookName],
[Amount],
[CreateDate]
FROM [dbo].[book_story])
INSERT INTO [dbo].[book_story]
([BookId],
[BookName],
[Amount],
[CreateDate])
SELECT *
FROM A
SET @cnt =@@ROWCOUNT
SELECT @cnt AS cnt
END
接著直接在EF中呼叫此SP
/// <summary> 透過SP大量新增資料</summary>
/// <param name="XMLData">接收API回來XML資料</param>
private static void UsingXMLtoSp(string XMLData)
{
int InsertTotalCnt;
Console.WriteLine(string.Format("XML_to_sp開始時間:{0}", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff")));
try
{
using (SampledataEntities db = new SampledataEntities())
{
//直接將XML放入SP中處理
ObjectResult<SP_Storybook_Result> CallInsertSp = db.SP_Storybook(XMLData);
InsertTotalCnt = CallInsertSp.FirstOrDefault().cnt.Value;
}
}
catch (Exception ex)
{
throw ex.GetBaseException();
}
Console.WriteLine(string.Format("XML_to_sp完成時間{0},共新增{1}筆資料!", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"), InsertTotalCnt));
}
實際測試
確實有瞬秒的快感呀))))
說時遲那時快,
我們RD Team偉大的技術顧問,
跳了出來語重心長的說:就我在東Y的經驗,
這個資料如果超過十萬筆會造成還沒呼叫SP時就會造成crash,
此時的我心驚驚,膽顫顫......只好產生十萬筆資料測試一下!!!
