巧用链接服务器同步数据

roych

有时候我们可能需要某个数据库的个别表，但同步数据往往会遇到一些问题。例如，由于权限问题无法发布订阅远程数据库。这时候链接服务器几乎成为不二选择了。但全量更新通常也会遇到一些问题，例如，当数据超过1000万行的时候。
最初我的思路是每天只更新不一样的数据。当时打算通过主键来匹配，新增的就更新，原表不存在的则删除。后来发现，还存在更改的很难判断。主键不变，如果每个字段去检查是否变更，相当繁琐。

所以打算不再同步数据表，而改为将所需基础数据追加到本地。如此一来，将不必更新多个表，而改为若干个字段就足矣。

在实际操作过程中，一开始是打算按查询的思路来完成，把所需的表进行整合，一次性把所需数据提取出来，发现需要运行很久。于是决定拆开多个查询，单表追加基础数据后，再逐步更新，应该会好些。
经测试，查询数据是比较快的，但追加到本地表则很慢，即便只有几万行。因此开始怀疑可能是运行脚本时需要调用大量资源，便思考是否可以考虑临时表来处理。于是就有了以下脚本：

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. --追加前一天数据
    
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 通话时长,'
    
25. +' count(SalesStaff) 通话次数, sum(case when BillableSeconds>=1 then 1 else 0 end) 接通次数,'
    
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,工号,最近接通日期, 通话时长,通话次数,接通次数,外呼号码)
    
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,最后沟通时间=s.LastFollowDate,沟通结果=s.LastFollowContent,
    
59. 沟通状态=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

复制代码

值得注意的是，双井号（##）才可以多次使用。用单井号（#）则运行完第一个语句后，临时表将消失（即第二个语句将出现错误）。拼接SQL语句比较麻烦，但考虑到需要按日期来执行语句，而且涉及到链接服务器（openquery），那也是必不可免的事情了。在拼接过程中，大家需要注意单引号的使用。

roych

注意：第三句应改为：
set @startDate=DATEADD(day,-1,GETDATE())
估计输入参数"2018-12-14"之类会被默认为文本。因此，需要用一个dateadd转为日期吧。

zpy2

否可以考虑临时表来处理。
为什么用临时表会快点？感觉插入了二次，应该慢才对。

roych

zpy2 发表于 2019-3-29 16:16
否可以考虑临时表来处理。
为什么用临时表会快点？感觉插入了二次，应该慢才对。

直接处理的话，相当于调用CPU来计算。而临时表会先把数据调入缓存，只要缓存足够大，通常比直接处理要快的。这就类似于读取内存数据和读取硬盘数据的意思。

zpy2

roych 发表于 2019-3-29 16:47
直接处理的话，相当于调用CPU来计算。而临时表会先把数据调入缓存，只要缓存足够大，通常比直接处理要快 ...

哦，难不是还有这样子啊，长知识了。