这个过程执行要10分钟,其实就是两个连接,先从SqlServer的rpt_gsb表中读取计算公式,然后根据计算公式到oracle数据库中计算出数据来,最后把数据再写入到SqlServer数据库的datagrid1所绑定的表中。两个循环,第一层30个,第二层也才40不到,奇怪执行速度就是慢!我是新手水平有限,请高手不吝赐教! Sub Query_Click(ByVal sender As Object, ByVal e As EventArgs)
Dim dataR1 As OracleDataReader
Dim ds1 As DataSet
Dim cmd2 As SqlCommand
Dim cmd1 As OracleCommand
Dim str1, str3, qsrq, jzrq As String
Dim i, j, sql_exe As Integer
Dim sj(100) As Decimal For i = 0 To 99
sj(i) = 0
Next Msg.Text = ""
cnn1 = New SqlConnection(ConfigurationSettings.AppSettings("connection"))
Cnn = New OracleConnection(ConfigurationSettings.AppSettings("connection1"))
Try
cnn1.Open()
str1 = "update unit_report set sj01='',sj02='',sj03='',sj04='',sj05='',sj06='',sj07='',sj08='',sj09='',sj10='',sj11='',sj12='',sj13='',sj14='',sj15='',sj16='',sj17='',sj18='',sj19='',sj20='',sj21='',sj22='',sj23='',sj24='',sj25='',sj26='',sj27='',sj28='',sj29='',sj30=''"
cmd2 = New SqlCommand(str1, cnn1)
cmd2.ExecuteNonQuery() str1 = "select * from rpt_gsb where rpt_code='" + Drop_rpt.SelectedValue + "' order by sw"
dtcmd = New SqlDataAdapter(str1, cnn1)
ds1 = New DataSet
dtcmd.Fill(ds1, "rpt_gsb")
DataGrid1.Width = ds1.Tables("rpt_gsb").Rows.Count * 52
For j = 0 To DataGrid1.Items.Count - 1
sql_exe = 0
str1 = "update unit_report set "
For i = 1 To ds1.Tables("rpt_gsb").Rows.Count
If j = 0 Then DataGrid1.Columns(i + 2).HeaderText = ds1.Tables("rpt_gsb").Rows(i - 1).Item("title").ToString()
If ds1.Tables("rpt_gsb").Rows(i - 1).Item("js_code") = 1 Then
If ds1.Tables("rpt_gsb").Rows(i - 1).Item("history") = 1 Then
qsrq = CStr(CInt(Left(tj_qsrq.Text, 4)) - 1) + Right(tj_qsrq.Text, 4)
jzrq = CStr(CInt(Left(tj_jzrq.Text, 4)) - 1) + Right(tj_jzrq.Text, 4)
If DataGrid1.Items.Item(j).Cells(3).Text = "1" Then
str3 = ds1.Tables("rpt_gsb").Rows(i - 1).Item("sql_code") + " and sta_date>='" + qsrq + "' and sta_date<='" + jzrq + "' and unit_code='" + Trim(DataGrid1.Items.Item(j).Cells(1).Text) + "'"
Else
str3 = ds1.Tables("rpt_gsb").Rows(i - 1).Item("sql_code") + " and sta_date>='" + qsrq + "' and sta_date<='" + jzrq + "'"
End If
Else
If DataGrid1.Items.Item(j).Cells(3).Text = "1" Then
str3 = ds1.Tables("rpt_gsb").Rows(i - 1).Item("sql_code") + " and sta_date>='" + tj_qsrq.Text + "' and sta_date<='" + tj_jzrq.Text + "' and unit_code='" + Trim(DataGrid1.Items.Item(j).Cells(1).Text) + "'"
Else
str3 = ds1.Tables("rpt_gsb").Rows(i - 1).Item("sql_code") + " and sta_date>='" + tj_qsrq.Text + "' and sta_date<='" + tj_jzrq.Text + "'"
End If
End If
str3 = Left(str3, str3.IndexOf("from")) + " as nums " + Mid(str3, str3.IndexOf("from") + 1, str3.Length)
cmd1 = New OracleCommand(str3, Cnn)
Try
Cnn.Open()
dataR1 = Nothing
dataR1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection)
If dataR1.Read() Then
If IsDBNull(dataR1("nums")) Then
sj(i) = 0
Else
sj(i) = dataR1("nums")
End If
If sj(i) > 0 Then
sql_exe = 1
Select Case ds1.Tables("rpt_gsb").Rows(i - 1).Item("format")
Case 0
str1 = str1 + "sj" + i.ToString("00") + "='" + Format(sj(i), "0").ToString + "',"
Case 1
str1 = str1 + "sj" + i.ToString("00") + "='" + Format(sj(i), "###0.0").ToString + "',"
Case Else
str1 = str1 + "sj" + i.ToString("00") + "='" + Format(sj(i), "###0.00").ToString + "',"
End Select
End If
dataR1.Close()
End If
Finally
Cnn.Close()
End Try
Else
If ds1.Tables("rpt_gsb").Rows(i - 1).Item("js_code") = 2 Then
If sj(i - 2) = 0 And sj(i - 1) > 0 Then
sql_exe = 1
str1 = str1 + "sj" + i.ToString("00") + "='100.0',"
Else
If sj(i - 2) > 0 Then
sql_exe = 1
sj(i) = 100 * (sj(i - 1) - sj(i - 2)) / sj(i - 2)
str1 = str1 + "sj" + i.ToString("00") + "='" + Format(sj(i), "###0.0").ToString + "',"
End If
End If
End If
If ds1.Tables("rpt_gsb").Rows(i - 1).Item("js_code") = 3 Then
If sj(i - 2) > 0 Then
sql_exe = 1
sj(i) = sj(i - 1) / sj(i - 2)
str1 = str1 + "sj" + i.ToString("00") + "='" + Format(sj(i), "###0.0").ToString + "',"
End If
End If
End If
Next
If sql_exe = 1 Then
str1 = Mid(str1, 1, str1.Length - 1)
str1 = str1 + " where dwlh='" + Trim(DataGrid1.Items.Item(j).Cells(1).Text) + "'"
cmd2 = New SqlCommand(str1, cnn1)
cmd2.ExecuteNonQuery()
Else
DataGrid1.Items.Item(j).Visible = False
End If
Next
Finally
Cnn.Close()
cnn1.Close()
End Try
BindGrid()
End Sub
Dim dataR1 As OracleDataReader
Dim ds1 As DataSet
Dim cmd2 As SqlCommand
Dim cmd1 As OracleCommand
Dim str1, str3, qsrq, jzrq As String
Dim i, j, sql_exe As Integer
Dim sj(100) As Decimal For i = 0 To 99
sj(i) = 0
Next Msg.Text = ""
cnn1 = New SqlConnection(ConfigurationSettings.AppSettings("connection"))
Cnn = New OracleConnection(ConfigurationSettings.AppSettings("connection1"))
Try
cnn1.Open()
str1 = "update unit_report set sj01='',sj02='',sj03='',sj04='',sj05='',sj06='',sj07='',sj08='',sj09='',sj10='',sj11='',sj12='',sj13='',sj14='',sj15='',sj16='',sj17='',sj18='',sj19='',sj20='',sj21='',sj22='',sj23='',sj24='',sj25='',sj26='',sj27='',sj28='',sj29='',sj30=''"
cmd2 = New SqlCommand(str1, cnn1)
cmd2.ExecuteNonQuery() str1 = "select * from rpt_gsb where rpt_code='" + Drop_rpt.SelectedValue + "' order by sw"
dtcmd = New SqlDataAdapter(str1, cnn1)
ds1 = New DataSet
dtcmd.Fill(ds1, "rpt_gsb")
DataGrid1.Width = ds1.Tables("rpt_gsb").Rows.Count * 52
For j = 0 To DataGrid1.Items.Count - 1
sql_exe = 0
str1 = "update unit_report set "
For i = 1 To ds1.Tables("rpt_gsb").Rows.Count
If j = 0 Then DataGrid1.Columns(i + 2).HeaderText = ds1.Tables("rpt_gsb").Rows(i - 1).Item("title").ToString()
If ds1.Tables("rpt_gsb").Rows(i - 1).Item("js_code") = 1 Then
If ds1.Tables("rpt_gsb").Rows(i - 1).Item("history") = 1 Then
qsrq = CStr(CInt(Left(tj_qsrq.Text, 4)) - 1) + Right(tj_qsrq.Text, 4)
jzrq = CStr(CInt(Left(tj_jzrq.Text, 4)) - 1) + Right(tj_jzrq.Text, 4)
If DataGrid1.Items.Item(j).Cells(3).Text = "1" Then
str3 = ds1.Tables("rpt_gsb").Rows(i - 1).Item("sql_code") + " and sta_date>='" + qsrq + "' and sta_date<='" + jzrq + "' and unit_code='" + Trim(DataGrid1.Items.Item(j).Cells(1).Text) + "'"
Else
str3 = ds1.Tables("rpt_gsb").Rows(i - 1).Item("sql_code") + " and sta_date>='" + qsrq + "' and sta_date<='" + jzrq + "'"
End If
Else
If DataGrid1.Items.Item(j).Cells(3).Text = "1" Then
str3 = ds1.Tables("rpt_gsb").Rows(i - 1).Item("sql_code") + " and sta_date>='" + tj_qsrq.Text + "' and sta_date<='" + tj_jzrq.Text + "' and unit_code='" + Trim(DataGrid1.Items.Item(j).Cells(1).Text) + "'"
Else
str3 = ds1.Tables("rpt_gsb").Rows(i - 1).Item("sql_code") + " and sta_date>='" + tj_qsrq.Text + "' and sta_date<='" + tj_jzrq.Text + "'"
End If
End If
str3 = Left(str3, str3.IndexOf("from")) + " as nums " + Mid(str3, str3.IndexOf("from") + 1, str3.Length)
cmd1 = New OracleCommand(str3, Cnn)
Try
Cnn.Open()
dataR1 = Nothing
dataR1 = cmd1.ExecuteReader(CommandBehavior.CloseConnection)
If dataR1.Read() Then
If IsDBNull(dataR1("nums")) Then
sj(i) = 0
Else
sj(i) = dataR1("nums")
End If
If sj(i) > 0 Then
sql_exe = 1
Select Case ds1.Tables("rpt_gsb").Rows(i - 1).Item("format")
Case 0
str1 = str1 + "sj" + i.ToString("00") + "='" + Format(sj(i), "0").ToString + "',"
Case 1
str1 = str1 + "sj" + i.ToString("00") + "='" + Format(sj(i), "###0.0").ToString + "',"
Case Else
str1 = str1 + "sj" + i.ToString("00") + "='" + Format(sj(i), "###0.00").ToString + "',"
End Select
End If
dataR1.Close()
End If
Finally
Cnn.Close()
End Try
Else
If ds1.Tables("rpt_gsb").Rows(i - 1).Item("js_code") = 2 Then
If sj(i - 2) = 0 And sj(i - 1) > 0 Then
sql_exe = 1
str1 = str1 + "sj" + i.ToString("00") + "='100.0',"
Else
If sj(i - 2) > 0 Then
sql_exe = 1
sj(i) = 100 * (sj(i - 1) - sj(i - 2)) / sj(i - 2)
str1 = str1 + "sj" + i.ToString("00") + "='" + Format(sj(i), "###0.0").ToString + "',"
End If
End If
End If
If ds1.Tables("rpt_gsb").Rows(i - 1).Item("js_code") = 3 Then
If sj(i - 2) > 0 Then
sql_exe = 1
sj(i) = sj(i - 1) / sj(i - 2)
str1 = str1 + "sj" + i.ToString("00") + "='" + Format(sj(i), "###0.0").ToString + "',"
End If
End If
End If
Next
If sql_exe = 1 Then
str1 = Mid(str1, 1, str1.Length - 1)
str1 = str1 + " where dwlh='" + Trim(DataGrid1.Items.Item(j).Cells(1).Text) + "'"
cmd2 = New SqlCommand(str1, cnn1)
cmd2.ExecuteNonQuery()
Else
DataGrid1.Items.Item(j).Visible = False
End If
Next
Finally
Cnn.Close()
cnn1.Close()
End Try
BindGrid()
End Sub
解决方案 »
- 求推荐 ASP.NET 视频教程!!
- ObjectDataSource选择不了数据源啊... 在线急等...
- 内置对象怎么作为page类的属性出现啊
- asp.net实现透明图片的输出,图片背景变黑色问题!(C#) 特急!
- vs2005中如何提高用户控件里的某个服务端控件的访问级别?
- 发一个Web版的多层代码生成工具(基础表代码维护)
- 关于DataGrid模板列中DropDownList控件的问题
- C#的asp.net页面中,如何获取datagrid网格的第二行第二列的值 ?
- 数据库连接报错,请弟兄们帮忙看看。
- Value cannot be null.网站迁移出现这个问题。
- js在IE上可以用,在火狐上不能用,怎么解决啊?
- 不小心把vs工具箱中的TextBox控件删除了,该怎么添加?
我主要问我上述代码怎么优化?是不是用dataset的updata写数据库方式代替ExecuteNonQuery是不是会好点。
干嘛 * 扫描整个表呢
另:我运行sql 2005 profile工具,发现audit logout,然后audit Login,是不是这个原因?