--游标在处理较小数据量时性能很好，但对于大数据量，游标很慢，以下示例中sqlstr查询到的数据大约五六万条，使用游标需执行一小时左右，改用临时表处理，需要六分钟左右
1 USE [PMDB_IndexingSystem]
2 GO
3 /****** Object: StoredProcedure [dbo].[V3] Script Date: 2018/4/28 22:30:19 ******/
4 SET ANSI_NULLS ON
5 GO
6 SET QUOTED_IDENTIFIER ON
7 GO
8 -- Batch submitted through debugger: SQLQuery1.sql|7|0|C:\Users\Aheading-222\AppData\Local\Temp\~vs1F11.sql
9 -- =============================================
10 -- Author: <Author,,Name>
11 -- Create date: <Create Date,,>
12 -- Description: <Description,,>
13 -- =============================================
14 ALTER PROCEDURE [dbo].[P_AutoIndexReporter_Add]
15
16 AS
17 BEGIN
18 DECLARE @QueryDate NVARCHAR(8),@TxtNumber INT,@DbName NVARCHAR(20),@Year NVARCHAR(10) ,@Month NVARCHAR(10),@tableName NVARCHAR(50),@sqlStr NVARCHAR(500)
19 SET @TxtNumber = 100
20 SET @QueryDate = CONVERT (NVARCHAR(8),DATEADD(DAY,-1,GETDATE()),112)
21 SET @Year = DATENAME(YEAR,GETDATE())
22 SET @Month = DATENAME(MONTH,GETDATE())
23 SET @DbName = CASE
24 WHEN @Month = '01' or @Month = '02' or @Month = '03' THEN 'DB'+@Year+'A'
25 WHEN @Month = '04' or @Month = '05' or @Month = '06' THEN 'DB'+@Year+'B'
26 WHEN @Month = '07' or @Month = '08' or @Month = '09' THEN 'DB'+@Year+'C'
27 WHEN @Month = '10' or @Month = '11' or @Month = '12' THEN 'DB'+@Year+'D'
28 ELSE 'DB2018A'
29 END
30 SET @tableName = '[192.168.1.31].['+@DbName+'].[dbo].[ArticleInfoB]'
31 --创建临时表
32 CREATE TABLE #tempTable(REFID INT IDENTITY(1,1), KID BIGINT,paperID BIGINT,BT NVARCHAR(200),TX NVARCHAR(MAX),TXZS INT,ZZ NVARCHAR(100),PaperName NVARCHAR(50),BC VARCHAR(20),RQ date,CT NVARCHAR(MAX),TS NVARCHAR(MAX))
33
34 SET @sqlStr = 'SELECT KID,paperID,RTRIM(LTRIM(BT)),TX,TXZS,RTRIM(LTRIM(ZZ)),RTRIM(LTRIM(MC)),RTRIM(LTRIM(BC)) ,CONVERT(date,CAST(RQ AS varchar)),CT,TS FROM '+ @tableName + ' WHERE RQ = '+@QueryDate
35 --SET @sqlStr = 'SELECT KID,paperID,RTRIM(LTRIM(BT)),TX,TXZS,RTRIM(LTRIM(ZZ)),RTRIM(LTRIM(MC)),RTRIM(LTRIM(BC)) ,CONVERT(date,CAST(RQ AS varchar)),CT,TS FROM '+ @tableName + ' WHERE RQ = 20180426'
36
37 --向临时表插入当天原始数据
38 INSERT INTO #tempTable(KID,paperID,BT,TX,TXZS,ZZ,PaperName,BC,RQ,CT,TS)
39 Exec(@sqlStr)
40
41 --获取当天文章信息 @paperID 报社ID @TX内容 @ZZ作者 @BT标题 @KID主键ID
42 DECLARE @paperID BIGINT,@TX NVARCHAR(MAX),@TXZS INT,@ZZ NVARCHAR(100),@BT NVARCHAR(200),@KID BIGINT,@PaperName VARCHAR(100),@BC VARCHAR(5),@RQ date,@CT NVARCHAR(MAX),@TS NVARCHAR(MAX)
43 --标记当前临时表最小行号、最大行号
44 DECLARE @MINID INT,@MAXID INT
45 --获取临时表数据的最小行号、最大行号
46 SELECT @MINID = MIN(REFID),@MAXID = MAX(REFID) FROM #tempTable
47 --循环处理临时表
48 WHILE @MINID <= @MAXID
49 BEGIN
50 --获取当前处理行的信息
51 SELECT @paperID = paperID,@TX = TX,@TXZS = TXZS,@ZZ = ZZ,@BT = BT,@KID = KID,@PaperName = PaperName,@BC = BC,@RQ = RQ,@CT = CT,@TS = TS FROM #tempTable WHERE REFID = @MINID
52
53 --业务处理begin
54 SET @TX = RTRIM(LTRIM(@TX))
55
56 IF ISNULL(@ZZ,'')<>'' --当文章作者不为空的时候直接插入数据
57 BEGIN
58 INSERT INTO [dbo].[T_AutoIndexReporter]
59 ([AIReptID]
60 ,[ArticleID]
61 ,[ArticleTitle]
62 ,[ArticleHeadSubset]
63 ,[ArticleEndSubset]
64 ,[AIReptName]
65 ,[IsConfirm]
66 ,[CreateTime]
67 ,[PaperID]
68 ,[PaperName]
69 ,[BC]
70 ,[ContentText]
71 ,[TextNumber]
72 ,RQ
73 ,[CT]
74 ,[TS])
75 VALUES
76 (NEWID()
77 ,@KID
78 ,@BT
79 ,LEFT(@TX , @TxtNumber)
80 ,RIGHT(@TX,@TxtNumber)
81 ,@ZZ
82 ,0
83 ,GETDATE()
84 ,@paperID
85 ,@PaperName
86 ,@BC
87 ,@TX
88 ,@TXZS
89 ,@RQ
90 ,@CT
91 ,@TS)
92 END
93 ELSE --获取根据文章中的报纸ID获取正则表达式并匹配数据
94 BEGIN
95 DECLARE @matchFlag INT
96 SET @matchFlag = 0
97
98 --正则匹配匹配出数据后修改插入表
99 DECLARE @Rule VARCHAR(500),@RangeMatching int
100 DECLARE Cursor_Rule CURSOR FOR (
101 SELECT T2.[RangeMatching],T2.[Rule]
102 FROM [RulesGroup_Paper] T
103 INNER JOIN [dbo].[Rules_Group] T1 ON T.GroupID = T1.GroupID
104 INNER JOIN [dbo].[T_ReporterMatchingRules] T2 ON T1.RuleID = T2.ReptMachRuleID
105 WHERE T.PaperID = @paperID)
106 OPEN Cursor_Rule
107 FETCH NEXT FROM Cursor_Rule INTO @RangeMatching,@Rule
108 WHILE (@@FETCH_STATUS = 0)
109 BEGIN
110 IF dbo.[Regex.IsMatch](@Rule,@TX)=1--正则表达式匹配数据并插入表
111 BEGIN
112 INSERT INTO [dbo].[T_AutoIndexReporter]
113 ([AIReptID]
114 ,[ArticleID]
115 ,[ArticleTitle]
116 ,[ArticleHeadSubset]
117 ,[ArticleEndSubset]
118 ,[AIReptName]
119 ,[IsConfirm]
120 ,[CreateTime]
121 ,[PaperID]
122 ,[PaperName]
123 ,[BC]
124 ,[ContentText]
125 ,[TextNumber]
126 ,RQ
127 ,[CT]
128 ,[TS])
129 VALUES
130 (NEWID()
131 ,@KID
132 ,@BT
133 ,LEFT(@TX , @RangeMatching)
134 ,RIGHT(@TX,@RangeMatching)
135 ,SUBSTRING(RTRIM(LTRIM(dbo.[Regex.Match](@Rule,@TX))),1,100)
136 ,0
137 ,GETDATE()
138 ,@paperID
139 ,@PaperName
140 ,@BC
141 ,@TX
142 ,@TXZS
143 ,@RQ
144 ,@CT
145 ,@TS)
146
147 SET @matchFlag = 1
148 BREAK
149 END
150 FETCH NEXT FROM Cursor_Rule INTO @RangeMatching,@Rule
151 END
152 CLOSE Cursor_Rule
153 DEALLOCATE Cursor_Rule
154 IF @matchFlag = 0
155 BEGIN
156 INSERT INTO [dbo].[T_AutoIndexReporter]
157 ([AIReptID]
158 ,[ArticleID]
159 ,[ArticleTitle]
160 ,[ArticleHeadSubset]
161 ,[ArticleEndSubset]
162 ,[AIReptName]
163 ,[IsConfirm]
164 ,[CreateTime]
165 ,[PaperID]
166 ,[PaperName]
167 ,[BC]
168 ,[ContentText]
169 ,[TextNumber]
170 ,RQ
171 ,[CT]
172 ,[TS])
173 VALUES
174 (NEWID()
175 ,@KID
176 ,@BT
177 ,LEFT(@TX , @TxtNumber)
178 ,RIGHT(@TX,@TxtNumber)
179 ,''
180 ,0
181 ,GETDATE()
182 ,@paperID
183 ,@PaperName
184 ,@BC
185 ,@TX
186 ,@TXZS
187 ,@RQ
188 ,@CT
189 ,@TS)
190 END
191 END
192 --业务处理end
193
194 SET @MINID = @MINID + 1
195 END
196
197
198 END
浙公网安备 33010602011771号