Otimizar UPDATE à base de dados.

[Seabra]

Boas

Tenho este código que funciona, mas só executa cerca de 2 registos por segundo.

Estou a ler um ficheiro de texto e a gravar na base de dados. á leitura de 25000 registos no ficheiro de texto demora cerca de 20s. Se eu ligar a esta função de escrito é que fica muito lento, pelo que o problema está neste código.

Porque é que cada insert está tão lento?

Private Sub Gava_Reg_DB(Linha As String)
 Dim Array_Dados() As String
 Dim Data As Date
 Dim da As New OleDb.OleDbDataAdapter
 Dim ds As New DataSet
 Dim dt As New DataTable
 Dim Data_Txt As String
 If conn.State = ConnectionState.Closed Then conn.Open()
 Array_Dados = Linha.Split(";")
 If Not IsDate(Array_Dados(0)) Then
	 MessageBox.Show("Registo com data não válida!!!", "Mensagem de erro...", MessageBoxButtons.OK, MessageBoxIcon.Error)
	 Exit Sub
 End If
 Data_Txt = DateTime.Parse(Array_Dados(0) & " " & Array_Dados(1))
 Data = Convert.ToDateTime(Data_Txt)



 '*****************************************Verificar se já existe a data.
 cmd = conn.CreateCommand
 cmd.CommandText = "SELECT * FROM Registos WHERE DataHora = @Campo1"
 cmd.Parameters.AddWithValue("@Campo1", SqlDbType.VarChar).Value = Data
 da.SelectCommand = cmd
 da.Fill(ds, "Registos")
 dt = ds.Tables("Registos")

 '**********************************Dados a inserir
 TextBox11.Text = Data
 TextBox12.Text = Array_Dados(2)
 TextBox13.Text = Array_Dados(3)
 TextBox14.Text = Array_Dados(4)
 TextBox15.Text = CSng(Array_Dados(5))
 TextBox16.Text = CSng(Array_Dados(6))
 TextBox17.Text = Array_Dados(7)

 If (dt.Rows.Count = 0) Then
	 '***************************************Gavar dados na base de dados
	 cmd = conn.CreateCommand
	 cmd.CommandText = "INSERT INTO Registos( DataHora, Localizacao, Produto, Tipo, Humidade, Gordura, Obs) Values(@Campo1,@Campo2,@Campo3,@Campo4,@Campo5,@Campo6,@Campo7)"
	 cmd.Parameters.AddWithValue("@Campo1", SqlDbType.DateTime).Value = Data
	 cmd.Parameters.AddWithValue("@Campo2", SqlDbType.VarChar).Value = Array_Dados(2)
	 cmd.Parameters.AddWithValue("@Campo3", SqlDbType.VarChar).Value = Array_Dados(3)
	 cmd.Parameters.AddWithValue("@Campo4", SqlDbType.VarChar).Value = Array_Dados(4)
	 cmd.Parameters.AddWithValue("@Campo5", SqlDbType.Decimal).Value = CSng(Array_Dados(5))
	 cmd.Parameters.AddWithValue("@Campo6", SqlDbType.Decimal).Value = CSng(Array_Dados(6))
	 cmd.Parameters.AddWithValue("@Campo7", SqlDbType.VarChar).Value = Array_Dados(7)
	 check = cmd.ExecuteReader.RecordsAffected()
 Else 'Se já existirem dados com esta data.
	 '**********************************Atualiza dados
	 TextBox9.Text = dt.Rows(0)(0).ToString
	 TextBox2.Text = dt.Rows(0)(1).ToString
	 TextBox3.Text = dt.Rows(0)(2).ToString
	 TextBox4.Text = dt.Rows(0)(3).ToString
	 TextBox5.Text = dt.Rows(0)(4).ToString
	 TextBox6.Text = dt.Rows(0)(5).ToString
	 TextBox7.Text = dt.Rows(0)(6).ToString
	 TextBox8.Text = dt.Rows(0)(7).ToString
	 If RadioButton3.Checked Then 'Perguntar para sobrepor
		 Dim dlgRes As DialogResult = MessageBox.Show("Registo com a data: " & Trim(TextBox2.Text) & " já existe na base de dados. Pretende atualizar com novos dados?", "Atualização de dados...", MessageBoxButtons.YesNoCancel, MessageBoxIcon.Question)
		 If dlgRes = DialogResult.No Then
			 Exit Sub
		 End If
		 If dlgRes = DialogResult.Cancel Then
			 Saida = True
			 Exit Sub
		 End If
	 End If

	 If RadioButton2.Checked Then 'Não sobrepor
		 Exit Sub
	 End If
	 cmd = conn.CreateCommand
	 cmd.CommandText = "UPDATE Registos SET DataHora=@Campo1, Localizacao=@Campo2, Produto=@Campo3, Tipo=@Campo4, Humidade=@Campo5, Gordura=@Campo6, Obs=@Campo7 WHERE @Campo1=DataHora"
	 cmd.Parameters.AddWithValue("@Campo1", SqlDbType.DateTime).Value = Data
	 cmd.Parameters.AddWithValue("@Campo2", SqlDbType.VarChar).Value = Array_Dados(2)
	 cmd.Parameters.AddWithValue("@Campo3", SqlDbType.VarChar).Value = Array_Dados(3)
	 cmd.Parameters.AddWithValue("@Campo4", SqlDbType.VarChar).Value = Array_Dados(4)
	 cmd.Parameters.AddWithValue("@Campo5", SqlDbType.Decimal).Value = CSng(Array_Dados(5))
	 cmd.Parameters.AddWithValue("@Campo6", SqlDbType.Decimal).Value = CSng(Array_Dados(6))
	 cmd.Parameters.AddWithValue("@Campo7", SqlDbType.VarChar).Value = Array_Dados(7)
	 check = cmd.ExecuteReader.RecordsAffected()
 End If
End Sub

Cumprimentos

[FreiNando]

O campo DataHora está indexado? Se não está, deves indexá-lo para diminuir o tempo de pesquisa.

Penso que iria correr mais rápido se tudo ficasse no mesmo procedimento e usasses um OleDbCommand para a pesquisa, outro para o INSERT e outro para o UPDATE, declarados no inicio do procedimento e reutilizados a cada linha, que seria lida do ficheiro dentro de um ciclo. Assim só terias de adicionar os parâmetros no inicio, bastando actualizar o valor de cada parâmetro na leitura de cada linha.

Também é mais rápido, utilizares um OleDbDataReader para a pesquisa e leitura de registos existentes. Só tens de o fechar após a leitura de cada registo.

Edited July 2, 2015 at 08:09 PM by FreiNando

[Seabra]

O campo DataHora está indexado? Se não está, deves indexá-lo para diminuir o tempo de pesquisa.

Penso que iria correr mais rápido se tudo ficasse no mesmo procedimento e usasses um OleDbCommand para a pesquisa, outro para o INSERT e outro para o UPDATE, declarados no inicio do procedimento e reutilizados a cada linha, que seria lida do ficheiro dentro de um ciclo. Assim só terias de adicionar os parâmetros no inicio, bastando actualizar o valor de cada parâmetro na leitura de cada linha.

Também é mais rápido, utilizares um OleDbDataReader para a pesquisa e leitura de registos existentes. Só tens de o fechar após a leitura de cada registo.

Vou refazer o código e vereificar.

Obrigado pela tua opinião.