By using this application i have inserted data to SQL Server table. I have inserted test data to table by using to two methods .
- Conventional Parameter passing to SQL Server stored-procedure.
1: public static void AddTestDataByObjectList(List<TestData> dataList)
2: {
3: SqlConnection connection = new SqlConnection(DBConnectionReader.GetDBConnection());
4: connection.Open();
5: try
6: {
7: foreach (var data in dataList)
8: {
9: SqlCommand command = new SqlCommand("dbo.AddTestData", connection);
10: command.CommandType = CommandType.StoredProcedure;
11: SqlParameter parameter1 = new SqlParameter("@Col1", SqlDbType.VarChar, 20);
12:
13: parameter1.Value = data.Col1;
14: command.Parameters.Add(parameter1);
15: SqlParameter parameter2 = new SqlParameter("@Col2", SqlDbType.Int);
16:
17: parameter2.Value = data.Col2;
18: command.Parameters.Add(parameter2);
19: SqlParameter parameter3= new SqlParameter("@Col3", SqlDbType.DateTime);
20:
21: parameter3.Value = data.Col3;
22: command.Parameters.Add(parameter3);
23: SqlParameter parameter4= new SqlParameter("@Col4", SqlDbType.VarChar, 50);
24:
25: parameter4.Value = data.Col4;
26: command.Parameters.Add(parameter4);
27: SqlParameter parameter5= new SqlParameter("@Col5", SqlDbType.VarChar, 8000);
28:
29: parameter5.Value = data.Col5;
30: command.Parameters.Add(parameter5);
31: SqlParameter parameter6= new SqlParameter("@Col6", SqlDbType.VarChar, 200);
32:
33: parameter6.Value = data.Col6;
34: command.Parameters.Add(parameter6);
35: SqlParameter parameter7= new SqlParameter("@Col7", SqlDbType.Decimal);
36:
37: parameter7.Value = data.Col7;
38: command.Parameters.Add(parameter7);
39: command.ExecuteNonQuery();
40: command.Parameters.Clear();
41: }
42: }
43: catch (Exception)
44: {
45: throw;
46: }
47: finally
48: {
49: connection.Close();
50: }
51: }
- Table Value parameter passing to SQL Server stored-procedure.
1: public static void AddTestDataByDataTable(DataTable dataTable)
2: {
3: SqlConnection connection = new SqlConnection(DBConnectionReader.GetDBConnection());
4: connection.Open();
5: try
6: {
7: SqlCommand command = new SqlCommand("dbo.AddTestDataByTableType", connection);
8: command.CommandType = CommandType.StoredProcedure;
9: SqlParameter parameter1 = new SqlParameter("@TestData", SqlDbType.Structured);
10:
11: parameter1.Value = dataTable;
12: command.Parameters.Add(parameter1);
13: command.ExecuteNonQuery();
14: }
15: catch (Exception)
16: {
17: throw;
18: }
19: finally
20: {
21: connection.Close();
22: }
23: }
I have inserted 10000 records to table by using above two methods. And measured the time take to each method. To get better result i have inserted 10000 records 20 times for each method. Following are the results
| Attempt |
DataTable(ms)
|
ObjectList(ms)
|
1
|
206
|
4651
|
2
|
187
|
4484
|
3
|
288
|
5412
|
4
|
325
|
4341
|
5
|
222
|
4718
|
6
|
239
|
4636
|
7
|
210
|
4556
|
8
|
216
|
4269
|
9
|
201
|
4576
|
10
|
990
|
5105
|
11
|
178
|
5066
|
12
|
220
|
4021
|
13
|
241
|
4300
|
14
|
162
|
4561
|
15
|
274
|
4892
|
16
|
217
|
4747
|
17
|
223
|
4236
|
18
|
400
|
3966
|
19
|
869
|
4800
|
20
|
562
|
4322
|
milliseconds(take to insert 10000 records) VS no of attempts
Pros :
- Faster data throughput.
- Less codding.
Cons :
- Hard to debug.
- May be need more network bandwidth.
Data base script that i have use to analysts table value parameter
1: USE tempdb;
2:
3: -- This table is use to measure time taken to each method
4: CREATE TABLE dbo.TestPerformance
5: (
6: ID INT IDENTITY(1,1) primary key
7: ,TType VARCHAR(50)
8: ,TIteration INT
9: ,StartTime DATETIME2
10: ,EndTime DATETIME2
11: )
12: GO
13: -- Test Table with different data typs
14: CREATE TABLE dbo.TestData
15: ( ID INT IDENTITY(1,1) Primary KEY
16: ,Col1 varchar(20)
17: ,Col2 INT
18: ,Col3 Datetime
19: ,Col4 varchar(50)
20: ,Col5 varchar(max)
21: ,Col6 varchar(200)
22: ,Col7 Decimal(20,2))
23:
24: GO
25:
26: -- Add test performance with start time
27: CREATE proc dbo.AddTestPerformance
28: @Type VARCHAR(50)
29: ,@Iteration INT
30: ,@OutId INT =NULL OUTPUT
31: AS
32: BEGIN
33: INSERT INTO dbo.TestPerformance (TType,TIteration,StartTime)
34: VALUES(@Type,@Iteration,SYSUTCDATETIME())
35: SET @OutId=@@IDENTITY
36:
37: END
38: GO
39:
40: -- Update the end time
41: create proc dbo.UpdateTestPerformance
42: @Id INT
43: AS
44: BEGIN
45: UPDATE dbo.TestPerformance SET EndTime=SYSUTCDATETIME() where ID=@Id
46: END
47: GO
48: --- Add test data using conventional parameters
49: create proc dbo.AddTestData
50: @Col1 varchar(20)
51: ,@Col2 INT
52: ,@Col3 Datetime
53: ,@Col4 varchar(50)
54: ,@Col5 varchar(max)
55: ,@Col6 varchar(200)
56: ,@Col7 Decimal(20,2)
57: AS
58: BEGIN
59:
60: INSERT INTO dbo.TestData(Col1,Col2,Col3,Col4,Col5,Col6,Col7)
61: VALUES (@Col1,@Col2,@Col3,@Col4,@Col5,@Col6,@Col7)
62:
63: END
64: GO
65:
66: -- Create a table type
67: CREATE TYPE dbo.TestType AS TABLE
68: (Col1 varchar(20)
69: ,Col2 INT
70: ,Col3 Datetime
71: ,Col4 varchar(50)
72: ,Col5 varchar(max)
73: ,Col6 varchar(200)
74: ,Col7 Decimal(20,2))
75:
76: GO
77: -- add values using table type
78: create proc dbo.AddTestDataByTableType
79: @TestData dbo.TestType ReadOnly
80: AS
81: BEGIN
82:
83: INSERT INTO dbo.TestData(Col1,Col2,Col3,Col4,Col5,Col6,Col7)
84: SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7 FROM @TestData
85: END
86:
1: public static List<TestData> GetTestDataList(int NoOfRows)
2: {
3: List<TestData> testDataList = new List<TestData>();
4: for (int i = 0; i < NoOfRows; i++)
5: {
6: TestData testData = new TestData();
7: testData.Col1 = Convert.ToString(Guid.NewGuid()).Substring(1,20);
8: testData.Col2 = i;
9: testData.Col3 = DateTime.Now.AddDays(i);
10: testData.Col4 = Guid.NewGuid().ToString();
11: testData.Col5 = Guid.NewGuid().ToString() +
12: Guid.NewGuid().ToString() +
13: Guid.NewGuid().ToString() +
14: Guid.NewGuid().ToString();
15: testData.Col6 = Guid.NewGuid().ToString() + Guid.NewGuid().ToString();
16: testData.Col7 = 1000000 + i;
17: testDataList.Add(testData);
18: }
19: return testDataList;
20: }
21:
22: public static DataTable GetTestDataTable(int NoOfRows)
23: {
24: List<TestData> testDataList = new List<TestData>();
25: testDataList = GetTestDataList(NoOfRows);
26: DataTable dataTable = new DataTable("TestData");
27:
28: dataTable.Columns.Add("Col1", typeof(string));
29: dataTable.Columns.Add("Col2", typeof(int));
30: dataTable.Columns.Add("Col3", typeof(DateTime));
31: dataTable.Columns.Add("Col4", typeof(string));
32: dataTable.Columns.Add("Col5", typeof(string));
33: dataTable.Columns.Add("Col6", typeof(string));
34: dataTable.Columns.Add("Col7", typeof(decimal));
35: foreach (var data in testDataList)
36: {
37: dataTable.Rows.Add( data.Col1
38: ,data.Col2
39: ,data.Col3
40: ,data.Col4
41: ,data.Col5
42: ,data.Col6
43: ,data.Col7);
44: }
45: return dataTable;
46: }
No comments:
Post a Comment