MELHORIA DO CODIGO
Galera estou desenvolvnnndo um sistema escolar e preciso gerar as frequencias dos alunos e depois que criei a rotina abaixo o sistema ficou lento demais..alguem poderia me dar uma dica pra otimizar mais?
Private Sub Gerar_FrequenciasBoletim()
Dim sql As String
Dim sql1 As String
Dim sql2 As String
Dim sql3 As String
Dim registro As New ADODB.Recordset
Dim registro1 As New ADODB.Recordset
Dim registro2 As New ADODB.Recordset
Dim registro3 As New ADODB.Recordset
Dim datamatri As String
Dim datafinal As String
Dim Disciplina As String
Dim faltas As Variant
Dim contador As Variant
For i = 1 To BimestreAtual
DoEvents
sql1 = [Ô]Select fim from Tbl_Bimestres where ano_alu = [ô][Ô] & Anoletivo & [Ô][ô] and bimestre = [Ô] & i
registro1.Open sql1, cn, adOpenKeyset, adLockReadOnly
datafinal = Format(registro1([Ô]fim[Ô]), [Ô]mm/dd/yyyy[Ô])
sql = [Ô]Select matricula, data_matri from Aluno Where ano_alu =[ô][Ô] & Anoletivo & [Ô][ô][Ô]
registro.Open sql, cn, adOpenKeyset, adLockReadOnly
Do While registro.EOF = False
DoEvents
contador = contador + 1
datamatri = Format(registro([Ô]data_matri[Ô]), [Ô]mm/dd/yyyy[Ô])
Matricula = registro([Ô]matricula[Ô])
sql2 = [Ô]SELECT matricula, cod_disc, COUNT(frequencia) AS Total [Ô] & _
[Ô] From dbo.Tbl_Frequencia WHERE (frequencia = [ô]F[ô]) [Ô] & _
[Ô] AND (data >= [ô][Ô] & datamatri & [Ô] [ô]) AND (data <= [ô][Ô] & datafinal & [Ô][ô]) [Ô] & _
[Ô] GROUP BY matricula, cod_disc HAVING (matricula = [ô][Ô] & Matricula & [Ô][ô])[Ô]
registro2.Open sql2, cn, adOpenKeyset, adLockReadOnly
Do While registro2.EOF = False
DoEvents
Disciplina = registro2([Ô]cod_disc[Ô])
faltas = registro2([Ô]Total[Ô])
sql3 = [Ô]SELECT * FROM aluno_disciplina Where ano_alu = [ô][Ô] & Anoletivo & [Ô][ô] and matricula =[ô][Ô] & Matricula & [Ô][ô] and cod_disc = [ô][Ô] & Disciplina & [Ô][ô][Ô]
registro3.Open sql3, cn, adOpenKeyset, adLockPessimistic
If registro3.RecordCount > 0 Then
If i = 1 Then
registro3([Ô]falta1[Ô]) = faltas
registro3.Update
registro3.Close
End If
If i = 2 Then
registro3([Ô]falta2[Ô]) = faltas
registro3.Update
registro3.Close
End If
If i = 3 Then
registro3([Ô]falta3[Ô]) = faltas
registro3.Update
registro3.Close
End If
If i = 4 Then
registro3([Ô]falta4[Ô]) = faltas
registro3.Update
registro3.Close
End If
Else
registro3.Close
End If
registro2.MoveNext
Loop
registro2.Close
lbl_aluno.Caption = [Ô]Total processado: [Ô] & contador
registro.MoveNext
Loop
Next i
Call ArredondarFrequencia
End Sub
Private Sub Gerar_FrequenciasBoletim()
Dim sql As String
Dim sql1 As String
Dim sql2 As String
Dim sql3 As String
Dim registro As New ADODB.Recordset
Dim registro1 As New ADODB.Recordset
Dim registro2 As New ADODB.Recordset
Dim registro3 As New ADODB.Recordset
Dim datamatri As String
Dim datafinal As String
Dim Disciplina As String
Dim faltas As Variant
Dim contador As Variant
For i = 1 To BimestreAtual
DoEvents
sql1 = [Ô]Select fim from Tbl_Bimestres where ano_alu = [ô][Ô] & Anoletivo & [Ô][ô] and bimestre = [Ô] & i
registro1.Open sql1, cn, adOpenKeyset, adLockReadOnly
datafinal = Format(registro1([Ô]fim[Ô]), [Ô]mm/dd/yyyy[Ô])
sql = [Ô]Select matricula, data_matri from Aluno Where ano_alu =[ô][Ô] & Anoletivo & [Ô][ô][Ô]
registro.Open sql, cn, adOpenKeyset, adLockReadOnly
Do While registro.EOF = False
DoEvents
contador = contador + 1
datamatri = Format(registro([Ô]data_matri[Ô]), [Ô]mm/dd/yyyy[Ô])
Matricula = registro([Ô]matricula[Ô])
sql2 = [Ô]SELECT matricula, cod_disc, COUNT(frequencia) AS Total [Ô] & _
[Ô] From dbo.Tbl_Frequencia WHERE (frequencia = [ô]F[ô]) [Ô] & _
[Ô] AND (data >= [ô][Ô] & datamatri & [Ô] [ô]) AND (data <= [ô][Ô] & datafinal & [Ô][ô]) [Ô] & _
[Ô] GROUP BY matricula, cod_disc HAVING (matricula = [ô][Ô] & Matricula & [Ô][ô])[Ô]
registro2.Open sql2, cn, adOpenKeyset, adLockReadOnly
Do While registro2.EOF = False
DoEvents
Disciplina = registro2([Ô]cod_disc[Ô])
faltas = registro2([Ô]Total[Ô])
sql3 = [Ô]SELECT * FROM aluno_disciplina Where ano_alu = [ô][Ô] & Anoletivo & [Ô][ô] and matricula =[ô][Ô] & Matricula & [Ô][ô] and cod_disc = [ô][Ô] & Disciplina & [Ô][ô][Ô]
registro3.Open sql3, cn, adOpenKeyset, adLockPessimistic
If registro3.RecordCount > 0 Then
If i = 1 Then
registro3([Ô]falta1[Ô]) = faltas
registro3.Update
registro3.Close
End If
If i = 2 Then
registro3([Ô]falta2[Ô]) = faltas
registro3.Update
registro3.Close
End If
If i = 3 Then
registro3([Ô]falta3[Ô]) = faltas
registro3.Update
registro3.Close
End If
If i = 4 Then
registro3([Ô]falta4[Ô]) = faltas
registro3.Update
registro3.Close
End If
Else
registro3.Close
End If
registro2.MoveNext
Loop
registro2.Close
lbl_aluno.Caption = [Ô]Total processado: [Ô] & contador
registro.MoveNext
Loop
Next i
Call ArredondarFrequencia
End Sub
Private Sub Gerar_FrequenciasBoletim()
Dim SQL As String
Dim registro As New ADODB.Recordset
Dim registro1 As New ADODB.Recordset
Dim registro2 As New ADODB.Recordset
Dim registro3 As New ADODB.Recordset
Dim datamatri As String
Dim datafinal As String
Dim Disciplina As String
Dim faltas As Variant
Dim contador As Long
contador = 0
For i = 1 To BimestreAtual
DoEvents
SQL = [Ô]SELECT fim FROM Tbl_Bimestres WHERE ano_alu = [ô][Ô] & Anoletivo & [Ô][ô] and bimestre = [Ô] & i
registro1.Open SQL, cn, adOpenStatic, adLockReadOnly
datafinal = Format(registro1([Ô]fim[Ô]), [Ô]mm/dd/yyyy[Ô])
If registro1.State <> 0 Then registro1.Close: Set registro1 = Nothing
SQL = [Ô]SELECT matricula, data_matri FROM Aluno WHERE ano_alu =[ô][Ô] & Anoletivo & [Ô][ô][Ô]
registro.Open SQL, cn, adOpenStatic, adLockReadOnly
Do While Not registro.EOF
contador = contador + 1
datamatri = Format(registro([Ô]data_matri[Ô]), [Ô]mm/dd/yyyy[Ô])
Matricula = registro([Ô]matricula[Ô])
SQL = [Ô]SELECT matricula, cod_disc, COUNT(frequencia) AS Total [Ô] & _
[Ô] FROM dbo.Tbl_Frequencia WHERE (frequencia = [ô]F[ô]) [Ô] & _
[Ô] AND (data BETWEEN #[Ô] & datamatri & [Ô]# AND data #[Ô] & datafinal & [Ô]#) [Ô] & _
[Ô] GROUP BY matricula, cod_disc HAVING (matricula = [ô][Ô] & Matricula & [Ô][ô])[Ô]
registro2.Open SQL, cn, adOpenStatic, adLockReadOnly
Do While Not registro2.EOF
Disciplina = registro2([Ô]cod_disc[Ô])
faltas = registro2([Ô]Total[Ô])
SQL = [Ô]SELECT * FROM aluno_disciplina Where ano_alu = [ô][Ô] & Anoletivo & [Ô][ô] and matricula =[ô][Ô] & Matricula & [Ô][ô] and cod_disc = [ô][Ô] & Disciplina & [Ô][ô][Ô]
registro3.Open SQL, cn, adOpenKeyset, adLockPessimistic
If Not registro3.EOF Then
Select Case i
Case 1
registro3([Ô]falta1[Ô]) = faltas
registro3.Update
registro3.Close: Set registro3 = Nothing
Case 2
registro3([Ô]falta2[Ô]) = faltas
registro3.Update
registro3.Close: Set registro3 = Nothing
Case 3
registro3([Ô]falta3[Ô]) = faltas
registro3.Update
registro3.Close: Set registro3 = Nothing
Case 4
registro3([Ô]falta4[Ô]) = faltas
registro3.Update
registro3.Close: Set registro3 = Nothing
Case Else
If registro3.State <> 0 Then registro3.Close: Set registro3 = Nothing
End Select
End If
registro2.MoveNext
DoEvents
Loop
If registro2.State <> 0 Then registro2.Close: Set registro2 = Nothing
lbl_aluno.Caption = [Ô]Total processado: [Ô] & contador
registro.MoveNext
DoEvents
Loop
If registro.State <> 0 Then registro.Close: Set registro = Nothing
Next i
Call ArredondarFrequencia
End SubTenta assim mais acho que não vai resolver muito não vc esta usando qual banco
Percebi que suas tabelas não estão normalizadas tente normaliza-las e indexar os campos que você utiliza para consultar
SQL server.
a principio algo que melhorar o desempenho é colocar os campos na query então você pode fazer esta mudança na sql3:
repare que coloquei apenas os nomes dos campos usados antes do from
sql3 = [Ô]SELECT anu_alu, matricula, cod_disc, falta1, falta2, falta3, falta4 FROM aluno_disciplina Where ano_alu = [ô][Ô] & Anoletivo & [Ô][ô] and matricula =[ô][Ô] & Matricula & [Ô][ô] and cod_disc = [ô][Ô] & Disciplina & [Ô][ô][Ô]
registro3.Open sql3, cn, adOpenKeyset, adLockPessimisticrepare que coloquei apenas os nomes dos campos usados antes do from
Obrigado...será que usando recordset seria o melhor caminho?
Citação::
a principio algo que melhorar o desempenho é colocar os campos na query então você pode fazer esta mudança na sql3:
sql3 = [Ô]SELECT anu_alu, matricula, cod_disc, falta1, falta2, falta3, falta4 FROM aluno_disciplina Where ano_alu = [ô][Ô] & Anoletivo & [Ô][ô] and matricula =[ô][Ô] & Matricula & [Ô][ô] and cod_disc = [ô][Ô] & Disciplina & [Ô][ô][Ô]
registro3.Open sql3, cn, adOpenKeyset, adLockPessimistic
repare que coloquei apenas os nomes dos campos usados antes do from
Primeiro a alteração melhorou algo no desempenho?
a maneira que está utilizando é a mais rápida, porém você está fazendo consulta em 3 tabelas para atualizar 1, o ideal seria reduzir isto a apenas um update com select e inner join, meio complexo mas um pouco mais eficiente, pois não teria de abrir varias conexões para utiliza-la.
algo meio complexo porém mais eficiente
a maneira que está utilizando é a mais rápida, porém você está fazendo consulta em 3 tabelas para atualizar 1, o ideal seria reduzir isto a apenas um update com select e inner join, meio complexo mas um pouco mais eficiente, pois não teria de abrir varias conexões para utiliza-la.
algo meio complexo porém mais eficiente
Talvez seja o DoEvents! Retire-os e teste.
Outro questão está na estrutura da tabela, reparando bem na tabela bimestre você possui os bimestres ex:
bimestre
1
2
3
4
e na tabela aluno_disciplina você possui falta1, falta2... o que de deduzo ser falta no 1 bimestre, no 2 bimestre...etc, neste caso o idael seria criar um campo bimestre nesta tabela, ou seja
ao invés de
Falta1 Falta2 Falta3 Falta4
10 13 16 12
ficaria assim
Biomestre Faltas
1 10
2 13
3 16
4 12
o que dispensaria o Laço for para atualizações, exemplo
UPDATE aluno_disciplina AD SET
AD.bimestre = TB.Bimestre
FROM
aluno_disciplina AD INNER JOIN tbl_bimestre TB ON AD.ano_alu = TB.ano_alu
lógico a query acima é apenas para fim ilustrativo, porém a query funcional ficaria próximo disto
bimestre
1
2
3
4
e na tabela aluno_disciplina você possui falta1, falta2... o que de deduzo ser falta no 1 bimestre, no 2 bimestre...etc, neste caso o idael seria criar um campo bimestre nesta tabela, ou seja
ao invés de
Falta1 Falta2 Falta3 Falta4
10 13 16 12
ficaria assim
Biomestre Faltas
1 10
2 13
3 16
4 12
o que dispensaria o Laço for para atualizações, exemplo
UPDATE aluno_disciplina AD SET
AD.bimestre = TB.Bimestre
FROM
aluno_disciplina AD INNER JOIN tbl_bimestre TB ON AD.ano_alu = TB.ano_alu
lógico a query acima é apenas para fim ilustrativo, porém a query funcional ficaria próximo disto
Caro AMELORP
Todos os recordset estão sendo abertos não fechados...............
A forma como escreveu seu código nessa lógica fica difÃcil obter melhor performance pois como descreveu é uma uma estrutura PIVOT no SQL.
Nessa forma de extração de dados em um único processo em loop há sobrecarrega em memória no executável e ficas lento,
Você esta abrindo Recordset dentro de Outro em Loop.
Esse processo pode melhorar se executado integralmente no Banco em uma Procedure Referenciada ou várias procedures em cascata.
Uma melhoria seria escrever em uma procedure instanciando seus recordset para cursores dentro da procedure con certeza melhoraria sua performance
e os retornos de cada cursor para um Dataset ou Datatable, melhoraria a performance pois o processo fica no Banco...........
Uma segunda forma seria Transformar cada RecordSet em um Function ou Sub com Parametros de referencia para ser chamada em cascata ou nÃvel, melhoraria pois seria para cada processo abre e fecha a conexão.
Boa sorte.
Citação::
Dim registro As New ADODB.Recordset
Dim registro1 As New ADODB.Recordset
Dim registro2 As New ADODB.Recordset
Dim registro3 As New ADODB.Recordset
Todos os recordset estão sendo abertos não fechados...............
A forma como escreveu seu código nessa lógica fica difÃcil obter melhor performance pois como descreveu é uma uma estrutura PIVOT no SQL.
Nessa forma de extração de dados em um único processo em loop há sobrecarrega em memória no executável e ficas lento,
Você esta abrindo Recordset dentro de Outro em Loop.
Esse processo pode melhorar se executado integralmente no Banco em uma Procedure Referenciada ou várias procedures em cascata.
Uma melhoria seria escrever em uma procedure instanciando seus recordset para cursores dentro da procedure con certeza melhoraria sua performance
e os retornos de cada cursor para um Dataset ou Datatable, melhoraria a performance pois o processo fica no Banco...........
Uma segunda forma seria Transformar cada RecordSet em um Function ou Sub com Parametros de referencia para ser chamada em cascata ou nÃvel, melhoraria pois seria para cada processo abre e fecha a conexão.
Boa sorte.
Tópico encerrado , respostas não são mais permitidas