巧用鏈接服務(wù)器同步數(shù)據(jù)

roych

有時(shí)候我們可能需要某個(gè)數(shù)據(jù)庫的個(gè)別表，但同步數(shù)據(jù)往往會遇到一些問題。例如，由于權(quán)限問題無法發(fā)布訂閱遠(yuǎn)程數(shù)據(jù)庫。這時(shí)候鏈接服務(wù)器幾乎成為不二選擇了。但全量更新通常也會遇到一些問題，例如，當(dāng)數(shù)據(jù)超過1000萬行的時(shí)候。
最初我的思路是每天只更新不一樣的數(shù)據(jù)。當(dāng)時(shí)打算通過主鍵來匹配，新增的就更新，原表不存在的則刪除。后來發(fā)現(xiàn)，還存在更改的很難判斷。主鍵不變，如果每個(gè)字段去檢查是否變更，相當(dāng)繁瑣。

所以打算不再同步數(shù)據(jù)表，而改為將所需基礎(chǔ)數(shù)據(jù)追加到本地。如此一來，將不必更新多個(gè)表，而改為若干個(gè)字段就足矣。

在實(shí)際操作過程中，一開始是打算按查詢的思路來完成，把所需的表進(jìn)行整合，一次性把所需數(shù)據(jù)提取出來，發(fā)現(xiàn)需要運(yùn)行很久。于是決定拆開多個(gè)查詢，單表追加基礎(chǔ)數(shù)據(jù)后，再逐步更新，應(yīng)該會好些。
經(jīng)測試，查詢數(shù)據(jù)是比較快的，但追加到本地表則很慢，即便只有幾萬行。因此開始懷疑可能是運(yùn)行腳本時(shí)需要調(diào)用大量資源，便思考是否可以考慮臨時(shí)表來處理。于是就有了以下腳本：

1. CREATE PROCEDURE [dbo].[A9_Sametime](@startDate date = null) as
   
2. if @startDate is null
   
3. set @startDate=GETDATE()
   
4. 
   
5. declare
   
6. --長日期
   
7. @startDateTime datetime,
   
8. @firstDayDateTime datetime,
   
9. 
   
10. @endDateTime datetime,
    
11. @sql1 varchar(8000),
    
12. @sql2 varchar(1000),
    
13. @sql3 varchar(1000)
    
14. 
    
15. 
    
16. set @startDateTime=DATEADD(day,-1,@startDate)
    
17. set @firstDayDateTime=DATEADD(day,1-day(@startDate),@startDate)
    
18. set @endDateTime=DATEADD(MILLISECOND,-3,convert(datetime,@startdate))
    
19. 
    
20. 
    
21. 
    
22. --追加前一天數(shù)據(jù)
    
23. set @sql1 = 'select * into ##temp from openquery(A9Server, ''select CustomerID 客服ID,SalesStaff 工號,max(StartTime) 最近接通日期,'
    
24. +' CONVERT(VARCHAR(8),DATEADD(ss,sum(BillableSeconds),''''1900-01-01 00:00:00''''),108) as 通話時(shí)長,'
    
25. +' count(SalesStaff) 通話次數(shù), sum(case when BillableSeconds>=1 then 1 else 0 end) 接通次數(shù),'
    
26. +' Destination 外呼號碼 from [A9SERVER].[S60623].[dbo].[bmdcallcenterrecord] '
    
27. +' where StartTime between '''''+convert(varchar(23),@startDateTime,21)+''''' and '''''
    
28. +convert(varchar(23),@endDateTime,21)+''''' group by CustomerID,SalesStaff,Destination'')'
    
29. 
    
30. 
    
31. --更新訂單號信息
    
32. set @sql2 = 'select * into ##temp from openquery(A9Server,''select a.CustomerID,a.ID,a.LastFollowDate,'
    
33. +' a.LastFollowContent,cs.CommunicateStatus from [A9SERVER].[S60623].[dbo].[bdCustomerAllocate] a,'
    
34. +' [A9SERVER].[S60623].[dbo].[mdcommunicatestatus] cs where len(a.LastCommunicateStatus)>0  '
    
35. +' and LastFollowDate between '''''+convert(varchar(23),@startDateTime,21)+''''' and '''''
    
36. +convert(varchar(23),@endDateTime,21)+''''' and a.LastCommunicateStatus=convert(varchar(50),cs.ID)'')'
    
37. 
    
38. 
    
39. --更新提醒信息
    
40. set @sql3 = 'select * into ##temp from openquery(A9Server,'
    
41. +' ''select u.UserName,d.DepartName,u.WorkNo from [A9SERVER].[S60623].[dbo].[frmuser] u,'
    
42. +' [A9SERVER].[S60623].[dbo].[mdDepartment] d where u.DepartmentID=d.id'')'
    
43. 
    
44. 
    
45. BEGIN
    
46. --set nocount on
    
47. --set nocount off
    
48. exec(@sql1)
    
49. 
    
50. insert into tblWidth(客戶ID,工號,最近接通日期, 通話時(shí)長,通話次數(shù),接通次數(shù),外呼號碼)
    
51. select * from ##temp
    
52. 
    
53. if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..##temp') and type='U')
    
54. drop table ##temp
    
55. --
    
56. exec(@sql2)
    
57. 
    
58. update tblWidth  set 分配ID=s.ID,最后溝通時(shí)間=s.LastFollowDate,溝通結(jié)果=s.LastFollowContent,
    
59. 溝通狀態(tài)=s.CommunicateStatus from ##temp s where
    
60. s.CustomerID=tblWidth.客戶ID and tblWidth.分配ID is null
    
61. 
    
62. if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..##temp') and type='U')
    
63. drop table ##temp
    
64. 
    
65. exec(@sql3)
    
66. 
    
67. update tblWidth  set 客服=UserName,部門=DepartName from ##temp s
    
68. where s.WorkNo=工號 and 客服 is null
    
69. 
    
70. if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb..##temp') and type='U')
    
71. drop table ##temp
    
72. END

復(fù)制代碼

值得注意的是，雙井號（##）才可以多次使用。用單井號（#）則運(yùn)行完第一個(gè)語句后，臨時(shí)表將消失（即第二個(gè)語句將出現(xiàn)錯(cuò)誤）。拼接SQL語句比較麻煩，但考慮到需要按日期來執(zhí)行語句，而且涉及到鏈接服務(wù)器（openquery），那也是必不可免的事情了。在拼接過程中，大家需要注意單引號的使用。

roych

注意：第三句應(yīng)改為：
set @startDate=DATEADD(day,-1,GETDATE())
估計(jì)輸入?yún)��?shù)"2018-12-14"之類會被默認(rèn)為文本。因此，需要用一個(gè)dateadd轉(zhuǎn)為日期吧。

zpy2

否可以考慮臨時(shí)表來處理。
為什么用臨時(shí)表會快點(diǎn)？感覺插入了二次，應(yīng)該慢才對。

roych

zpy2 發(fā)表于 2019-3-29 16:16
否可以考慮臨時(shí)表來處理。
為什么用臨時(shí)表會快點(diǎn)？感覺插入了二次，應(yīng)該慢才對。

直接處理的話，相當(dāng)于調(diào)用CPU來計(jì)算。而臨時(shí)表會先把數(shù)據(jù)調(diào)入緩存，只要緩存足夠大，通常比直接處理要快的。這就類似于讀取內(nèi)存數(shù)據(jù)和讀取硬盤數(shù)據(jù)的意思。

zpy2

roych 發(fā)表于 2019-3-29 16:47
直接處理的話，相當(dāng)于調(diào)用CPU來計(jì)算。而臨時(shí)表會先把數(shù)據(jù)調(diào)入緩存，只要緩存足夠大，通常比直接處理要快 ...

哦，難不是還有這樣子啊，長知識了。