SqlCommand \u3084 DataTable \u3092\u4f7f\u3046\u3068\u304d\u306b\u6587\u5b57\u5217\u3092\u305f\u304f\u3055\u3093\u4f7f\u3046\u306e\u3067\u3059\u304c\u3001\u679c\u305f\u3057\u3066\u4e16\u9593\u4e00\u822c\uff08\uff1f\uff09\u3067\u8a00\u308f\u308c\u3066\u3044\u308b\u307b\u3069\u3001String \u306f\u9045\u304f\u3001StringBuilder \u306f\u65e9\u3044\u306e\u3067\u3057\u3087\u3046\u304b\uff1f\u3068\u3044\u3046\u30d9\u30f3\u30c1\u30de\u30fc\u30af\u3067\u3059\u3002<\/p>\n
\u975e\u5e38\u306b\u9577\u3044\u5834\u5408\u306f StringBuilder \u3092\u4f7f\u3046\u307b\u3046\u304c\u826f\u3044\u306e\u3067\u3059\u304c\u3001SqlCommand \u306a\u3069\u306b\u6e21\u3059 SQL \u6587\u7a0b\u5ea6\uff08\u9577\u304f\u3066\u3082 5000 \u6587\u5b57\u3050\u3089\u3044\uff09\u306f\u3001\u3069\u3046\u306a\u306e\u3067\u3057\u3087\u3046\u304b\uff1f\u3068\u3044\u3046\u3053\u3068\u3067\u3059\u3002<\/p>\n
\u25a0\u7d50\u8ad6<\/p>\n
\u7d50\u8ad6\u304b\u3089\u8a00\u3048\u3070\u3001\u5927\u3057\u3066\u5909\u308f\u308a\u307e\u305b\u3093\u3002\u4ee5\u4e0b\u306f 20,000 \u4ef6\u306e\u30c7\u30fc\u30bf\u3092\u5efb\u3057\u305f\u3068\u304d\u306e\u7d50\u679c\u3067\u3059\u3002<\/p>\n
avg. Normal 7.54 sec
\navg. StirngBuilder 7.22 sec
\navg. SqlCommand 7.28 sec<\/p>\n
\u9055\u3044\u306f 2,3 % \u3050\u3089\u3044\u3057\u304b\u306a\u3044\u306e\u3067\u8aa4\u5dee\u306e\u7bc4\u56f2\u3067\u3059\u306d\u3002<\/p>\n
\u4ee5\u4e0b\u306f\u3001\u30d9\u30f3\u30c1\u30de\u30fc\u30af\u7528\u306e\u5b9f\u9a13\u30b3\u30fc\u30c9<\/p>\n
\u25a0\u901a\u5e38\u306e String \u30d1\u30bf\u30fc\u30f3<\/p>\n
\r\n''' <summary>\r\n''' 1.\u901a\u5e38\u306e string \u306e\u9023\u7d50\u3067\u4f5c\u6210\r\n''' <\/summary>\r\n''' <remarks><\/remarks>\r\nPublic Sub TestNormal()\r\n Dim s As String = toMD5(DateTime.Now.ToString())\r\n\r\n Dim cn As New SqlConnection("")\r\n For j As Integer = 0 To MAX - 1\r\n Dim sql As String = ""\r\n ' \u3088\u304f\u3042\u308b string.Format \u3092\u4f7f\u3063\u305f\u4f5c\u308a\u65b9\r\n sql += "SELECT * FROM DUAL "\r\n sql += " WHERE 1 = 1 "\r\n sql += String.Format(" AND col0 = '{0}' ", s) : s = toMD5(s)\r\n sql += String.Format(" AND col1 = '{0}' ", s) : s = toMD5(s)\r\n sql += String.Format(" AND col2 = '{0}' ", s) : s = toMD5(s)\r\n sql += String.Format(" AND col3 = '{0}' ", s) : s = toMD5(s)\r\n sql += String.Format(" AND col4 = '{0}' ", s) : s = toMD5(s)\r\n sql += String.Format(" AND col5 = '{0}' ", s) : s = toMD5(s)\r\n sql += String.Format(" AND col6 = '{0}' ", s) : s = toMD5(s)\r\n sql += String.Format(" AND col7 = '{0}' ", s) : s = toMD5(s)\r\n sql += String.Format(" AND col8 = '{0}' ", s) : s = toMD5(s)\r\n sql += String.Format(" AND col9 = '{0}' ", s) : s = toMD5(s)\r\n\r\n Dim dt As New DataTable\r\n Dim da As New SqlDataAdapter(sql, cn) 'dummy\r\n Next\r\nEnd Sub\r\n<\/pre>\nstring.Format \u3092\u4f7f\u3063\u3066\u3001\u307d\u3061\u307d\u3061\u3068\u6587\u5b57\u5217\u3092\u30d5\u30a9\u30fc\u30de\u30c3\u30c8\u3057\u3066\u3044\u304d\u307e\u3059\u3002<\/p>\n
\u25a0StringBuilder \u3092\u4f7f\u3063\u305f\u30d1\u30bf\u30fc\u30f3<\/p>\n
\u3088\u304f\u3042\u308b\u3088\u3046\u306b\u3001\u6587\u5b57\u5217\u306e\u9023\u7d50\u306f StringBuilder \u3092\u4f7f\u3048\u30c3!!! \u3063\u3066\u3053\u3068\u306a\u306e\u3067\u3001\u4f7f\u3063\u3066\u307f\u305f\u306e\u3067\u3059\u304c\u3001\u5927\u3057\u3066\u5909\u308f\u308a\u307e\u305b\u3093\u3002\u3082\u3063\u3068\u9577\u3044 SQL \u306e\u5834\u5408\u3001\u52b9\u679c\u304c\u3042\u308b\u3093\u3067\u3057\u3087\u3046\u304c…\u305d\u3093\u306a\u306b\u9577\u3044 SQL \u3092\u66f8\u304f\u306e\u306f\u3069\u3046\u306a\u306e???\u3063\u3066\u3053\u3068\u306a\u306e\u3067\u3059\u3002<\/p>\n
\r\n''' <summary>\r\n''' 2.StringBuilder \u3092\u4f7f\u3046\r\n''' <\/summary>\r\n''' <remarks><\/remarks>\r\nPublic Sub TestStringBuilder()\r\n Dim s As String = toMD5(DateTime.Now.ToString())\r\n\r\n Dim cn As New SqlConnection("")\r\n For j As Integer = 0 To MAX - 1\r\n Dim sql As String = ""\r\n ' \u3088\u304f\u3042\u308b StringBuilder \u3092\u4f7f\u3063\u305f\u4f5c\u308a\u65b9\r\n Dim sb As New System.Text.StringBuilder("")\r\n sb.Append("SELECT * FROM DUAL ")\r\n sb.Append(" WHERE 1 = 1 ")\r\n sb.AppendFormat(" AND col0 = '{0}' ", s) : s = toMD5(s)\r\n sb.AppendFormat(" AND col1 = '{0}' ", s) : s = toMD5(s)\r\n sb.AppendFormat(" AND col2 = '{0}' ", s) : s = toMD5(s)\r\n sb.AppendFormat(" AND col3 = '{0}' ", s) : s = toMD5(s)\r\n sb.AppendFormat(" AND col4 = '{0}' ", s) : s = toMD5(s)\r\n sb.AppendFormat(" AND col5 = '{0}' ", s) : s = toMD5(s)\r\n sb.AppendFormat(" AND col6 = '{0}' ", s) : s = toMD5(s)\r\n sb.AppendFormat(" AND col7 = '{0}' ", s) : s = toMD5(s)\r\n sb.AppendFormat(" AND col8 = '{0}' ", s) : s = toMD5(s)\r\n sb.AppendFormat(" AND col9 = '{0}' ", s) : s = toMD5(s)\r\n\r\n Dim dt As New DataTable\r\n Dim da As New SqlDataAdapter(sql, cn) 'dummy\r\n Next\r\nEnd Sub\r\n<\/pre>\n\u25a0SqlCommand \u3092\u4f7f\u3046<\/p>\n
SqlCommand \u3092\u4f7f\u3046\u3068\u3001\u6700\u521d\u306b SQL \u6587\u3092\u4f7f\u3046\u306e\u3067\u6587\u5b57\u5217\u7de8\u96c6\u90e8\u5206\u304c\u6975\u7aef\u306b\u6e1b\u308a\u307e\u3059\u3002
\n\u3053\u308c\u306e\u5834\u5408\u306f\u300120000 \u56de\u306e\u7de8\u96c6\u304c 1 \u56de\u306b\u306a\u308b\u308f\u3051\u3067\u3059\u304c\u3001\u5287\u7684\u306b\u65e9\u304f\u306a\u308b…\u306f\u305a\u306a\u3093\u3067\u3059\u3051\u3069\u3001\u7d50\u679c\u306f\u5909\u308f\u308a\u307e\u305b\u3093\u3002<\/p>\n
\r\n''' <summary>\r\n''' SqlCommand \u3067 Const\/Dim \u3092\u4f7f\u3046\r\n''' <\/summary>\r\n''' <remarks><\/remarks>\r\nPublic Sub TestConst()\r\n Dim s As String = toMD5(DateTime.Now.ToString())\r\n Dim cn As New SqlConnection("")\r\n\r\n ' \u3053\u3053\u306f dim \u3067\u3082\u540c\u3058\r\n Const sql As String = _\r\n "SELECT * FROM DUAL0 " + _\r\n " WHERE 1 = 1 " + _\r\n " AND col0 = @col0 " + _\r\n " AND col1 = @col1 " + _\r\n " AND col2 = @col2 " + _\r\n " AND col3 = @col3 " + _\r\n " AND col4 = @col4 " + _\r\n " AND col5 = @col5 " + _\r\n " AND col6 = @col6 " + _\r\n " AND col7 = @col7 " + _\r\n " AND col8 = @col8 " + _\r\n " AND col9 = @col9 ; "\r\n\r\n Dim cmd As New SqlCommand(sql, cn)\r\n cmd.Parameters.Add(New SqlParameter("@col0", Nothing))\r\n cmd.Parameters.Add(New SqlParameter("@col1", Nothing))\r\n cmd.Parameters.Add(New SqlParameter("@col2", Nothing))\r\n cmd.Parameters.Add(New SqlParameter("@col3", Nothing))\r\n cmd.Parameters.Add(New SqlParameter("@col4", Nothing))\r\n cmd.Parameters.Add(New SqlParameter("@col5", Nothing))\r\n cmd.Parameters.Add(New SqlParameter("@col6", Nothing))\r\n cmd.Parameters.Add(New SqlParameter("@col7", Nothing))\r\n cmd.Parameters.Add(New SqlParameter("@col8", Nothing))\r\n cmd.Parameters.Add(New SqlParameter("@col9", Nothing))\r\n\r\n For j As Integer = 0 To max - 1\r\n cmd.Parameters("@col0").Value = s : s = toMD5(s)\r\n cmd.Parameters("@col1").Value = s : s = toMD5(s)\r\n cmd.Parameters("@col2").Value = s : s = toMD5(s)\r\n cmd.Parameters("@col3").Value = s : s = toMD5(s)\r\n cmd.Parameters("@col4").Value = s : s = toMD5(s)\r\n cmd.Parameters("@col5").Value = s : s = toMD5(s)\r\n cmd.Parameters("@col6").Value = s : s = toMD5(s)\r\n cmd.Parameters("@col7").Value = s : s = toMD5(s)\r\n cmd.Parameters("@col8").Value = s : s = toMD5(s)\r\n cmd.Parameters("@col9").Value = s : s = toMD5(s)\r\n Dim dt As New DataTable\r\n Dim da As New SqlDataAdapter(cmd) 'dummy\r\n Next\r\nEnd Sub\r\n<\/pre>\n\u4ee5\u4e0a\u3001\u3053\u3093\u306a\u98a8\u306b\u5b9f\u9a13\u3057\u3066\u307f\u308b\u3068\u666e\u901a\u306b string \u3092\u4f7f\u3063\u3066\u3044\u308b\u9650\u308a\u306f\u30b9\u30d4\u30fc\u30c9\u306b\u5909\u5316\u306f\u3042\u308a\u307e\u305b\u3093\u3001\u3063\u3066\u3053\u3068\u3067\u3059\u3002
\n\u305f\u3060\u3057\u3001\u30c7\u30fc\u30bf\u30d9\u30fc\u30b9\u30a2\u30af\u30bb\u30b9\u306b\u95a2\u3057\u3066\u306f\u3001\u5358\u7d14\u306a SqlDataAdapter \u306e\u547c\u3073\u51fa\u3057\u3088\u308a\u3082\u3001SqlCommand \u3067\u30d7\u30ea\u30b3\u30f3\u30d1\u30a4\u30eb SQL \u3092\u4f7f\u3063\u305f\u307b\u3046\u304c\u300110 \u500d\u4ee5\u4e0a\u65e9\u304f\u306a\u308b\u306e\u3067\u3001\u4ef6\u6570\u304c\u591a\u3044\u5834\u5408\u306f\u30d1\u30d5\u30a9\u30fc\u30de\u30f3\u30b9\u306b\u6ce8\u610f\u304c\u5fc5\u8981\u3067\u3059\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"
SqlCommand \u3084 DataTable \u3092\u4f7f\u3046\u3068\u304d\u306b\u6587\u5b57\u5217\u3092\u305f\u304f\u3055\u3093\u4f7f\u3046\u306e\u3067\u3059\u304c\u3001\u679c\u305f\u3057\u3066\u4e16\u9593\u4e00\u822c\uff08\uff1f\uff09\u3067\u8a00\u308f\u308c\u3066\u3044\u308b\u307b\u3069\u3001String \u306f\u9045\u304f\u3001StringBuilder \u306f\u65e9\u3044\u306e\u3067\u3057\u3087\u3046\u304b\uff1f\u3068\u3044\u3046\u30d9\u30f3\u30c1\u30de\u30fc\u30af\u3067 … \u7d9a\u304d\u3092\u8aad\u3080