数据库调优过程（四）：提高数据库写入修改方案

后来发现从数据方面来解决希望不大，从程序的角度做了一下调整：

 1 /****** Object:  StoredProcedure [dbo].[GetGlobalMaxOID]    Script Date: 07/13/2016 23:50:04 ******/
 2 DROP PROCEDURE [dbo].[GetGlobalMaxOID]
 3 GO
 4 /****** Object:  Table [dbo].[GlobalMaxOID]    Script Date: 07/13/2016 23:50:03 ******/
 5 DROP TABLE [dbo].[GlobalMaxOID]
 6 GO
 7 /****** Object:  Table [dbo].[GlobalMaxOID]    Script Date: 07/13/2016 23:50:03 ******/
 8 SET ANSI_NULLS ON
 9 GO
10 SET QUOTED_IDENTIFIER ON
11 GO
12 CREATE TABLE [dbo].[GlobalMaxOID](
13     [ENodeBID] [int] NOT NULL,
14     [TableName] [nvarchar](64) NOT NULL,
15     [MaxOID] [bigint] NOT NULL,
16     [Flag] [int] NOT NULL
17 ) ON [PRIMARY]
18 GO
19 /****** Object:  StoredProcedure [dbo].[GetGlobalMaxOID]    Script Date: 07/13/2016 23:50:04 ******/
20 SET ANSI_NULLS ON
21 GO
22 SET QUOTED_IDENTIFIER ON
23 GO
24 -- =============================================
25 -- Author:        tommy duan
26 -- Create date: 2016-07-12
27 -- Description: OID 占坑
28 -- =============================================
29 CREATE PROCEDURE [dbo].[GetGlobalMaxOID]
30     @ENodeBID int,
31     @TableName nvarchar(64),
32     @TempCount int
33 AS
34 BEGIN
35     SET NOCOUNT ON;
36     
37     Declare @MaxOID bigint;
38     
39     Set XACT_ABORT ON;
40     Begin Transaction    
41         if not exists(Select ENodeBID From dbo.GlobalMaxOID Where ENodeBID=@ENodeBID and TableName=@TableName) begin
42             insert into dbo.GlobalMaxOID(ENodeBID,TableName,MaxOID,Flag)VALUES(@ENodeBID,@TableName,0,0);
43         end
44         
45         Update dbo.GlobalMaxOID Set Flag=1 Where ENodeBID=@ENodeBID and TableName=@TableName;
46     
47         Select @MaxOID=MaxOID From dbo.GlobalMaxOID Where ENodeBID=@ENodeBID and TableName=@TableName;
48                     
49         Update dbo.GlobalMaxOID Set MaxOID=(@MaxOID+@TempCount),Flag=0 Where ENodeBID=@ENodeBID and TableName=@TableName;
50     Commit Transaction;    
51     
52     Select @MaxOID;    
53 END
54 GO

使用lock：

 1 using System;
 2 using System.Collections.Generic;
 3 using System.Linq;
 4 using System.Text;
 5 using System.Data.SqlClient;
 6 using System.Threading.Tasks;
 7 using System.Configuration;
 8 
 9 namespace TestGlobalMaxOID
10 {
11     class Task
12     {
13         public int ENodeBID { get; set; }
14         public int TempCount { get; set; }
15         public string ConnectionString { get; set; }
16     }
17 
18     class Program
19     {
20         static object locker = new object();
21 
22         static void Main(string[] args)
23         {
24             string tableName = string.Empty;
25 
26             Parallel.For(10000, 10100, new ParallelOptions() { MaxDegreeOfParallelism = Environment.ProcessorCount }, (int enodebid) =>
27             {
28                 List<Task> tasks = new List<Task>();
29                 for (int i = 0; i < 100; i++)
30                 {
31                     tasks.Add(new Task() { ENodeBID = enodebid, TempCount = 1000000, ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString });
32                 }
33 
34                 Parallel.ForEach(tasks, new ParallelOptions() { MaxDegreeOfParallelism = Environment.ProcessorCount }, (Task task) =>
35                 {
36                     Console.WriteLine(GetMaxOIDByTableNameAndENodeBID(task.ConnectionString, "MRO", task.ENodeBID, task.TempCount));
37                 });
38             });
39 
40 
41             Console.WriteLine("Complete!");
42             Console.ReadKey();
43 
44         }
45 
46         static long GetMaxOIDByTableNameAndENodeBID(string connectionString, string tableName, int eNodeBId, int tempCount)
47         {
48             lock (locker)
49             {
50                 using (SqlConnection connection = new SqlConnection(connectionString))
51                 {
52                     using (SqlCommand command = new SqlCommand())
53                     {
54                         connection.Open();
55 
56                         command.Connection = connection;
57                         command.CommandText = "[dbo].[GetGlobalMaxOID]";
58                         command.CommandType = System.Data.CommandType.StoredProcedure;
59 
60                         command.Parameters.Add(new SqlParameter("@ENodeBID", eNodeBId));
61                         command.Parameters.Add(new SqlParameter("@TableName", tableName));
62                         command.Parameters.Add(new SqlParameter("@TempCount", tempCount));
63 
64                         object obj = command.ExecuteScalar();
65 
66                         return Convert.ToInt64(obj);
67                     }
68                 }
69             }
70         }
71 
72     }
73 }

数据库调优过程（四）：提高数据库写入修改方案

标签：