--游标在处理较小数据量时性能很好，但对于大数据量，游标很慢，以下示例中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:UsersAheading-222AppDataLocalTemp~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