Hello everyone
I've been recently using this code to get information from some of the databases at the company im interning at.
This is the code im currently using:
There is more code inside that event but its nothing that has to do with the SQL Query so i don't think its necessarily important to post here.
Im pretty new to SQL and i only know how to do basic query's so i have no idea why this is so slow,could it be because of the hardware of the server?
i ask that because there is also another server that i've been pulling information from (with similiar queries) and sure, that one has much less information than the one im trying to pull information from but it only takes like 2-3 seconds tops
while this one takes around 1 minute +
Any kind of help would be appreciated,thank you!
I've been recently using this code to get information from some of the databases at the company im interning at.
This is the code im currently using:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim userInput As String
Dim conn As Object
Dim rs As Object
Dim strSQL As String
Dim cell As Range
Set cell = Sheet1.Range("H19:I19")
Application.ScreenUpdating = False
If Not Intersect(Target, cell) Is Nothing Then
Application.EnableEvents = False
Application.Calculation = xlCalculationManual
Application.DisplayAlerts = False
If cell.Cells(1, 1).value <> "" Then ' Verifica a célula para procurar por um valor
If Not IsValidFormat(cell.Cells(1, 1).value) Then
Application.EnableEvents = True ' Re-enable events before showing the message box
MsgBox "Formato de texto errado! Por favor use uma letra e até 3 números (e.x., X111).", vbExclamation, "Erro: Formato de texto errado"
UnmergeAndClear cell ' Faz o split e limpa os conteúdos
Exit Sub
End If
End If
Application.EnableEvents = True ' Liga os eventos do excel outra vez
End If
If Not Target Is Nothing And Not Intersect(Target, Me.Range("I5")) Is Nothing Then
If Target.value <> "" Then
userInput = Target.value
' Faz a ligação com a base de dados
Set conn = CreateObject("ADODB.Connection")
conn.ConnectionTimeout = 0
conn.CommandTimeout = 0
conn.Open "Provider=SQLOLEDB;Data Source=IP;Initial Catalog=DB;User ID=. ;Password=PASSWORD;"
' Query do valor da fuga,pressão do hélio e data de produção da peça
strSQL = "SELECT MOTOR_HOUSING_LABEL, REAR_HEAD_LABEL FROM dbo.LineA WHERE PROCESS_LABEL = '" & userInput & "' ORDER BY TIME_STAMP DESC;"
Debug.Print "Executing SQL Query: " & strSQL
Set rs = conn.Execute(strSQL)
If Not rs.EOF Then
Dim motor As Variant
Dim rear As Variant
motor = rs.Fields("MOTOR_HOUSING_LABEL").value
rear = rs.Fields("REAR_HEAD_LABEL").value
Debug.Print "Motor encontrado na base de dados : " & motor
Debug.Print "Rear encontrado na base de dados : " & rear
ThisWorkbook.Sheets("Sheet1").Range("K5").value = motor
ThisWorkbook.Sheets("Sheet1").Range("M5").value = rear
Else
Debug.Print "Não existe qualquer match na base de dados."
End If
strSQL = "SELECT ST300_TORQUE, ST400_LEAK_VALUE, ST390_HEPRESS_TEST FROM dbo.LineB WHERE PROCESS_LABEL = '" & userInput & "' ORDER BY TIME_STAMP DESC;"
Debug.Print "Executing SQL Query: " & strSQL
Set rs = conn.Execute(strSQL)
countSQL = "SELECT COUNT(ST400_LEAK_VALUE) AS LeakCount FROM dbo.LineB WHERE PROCESS_LABEL = '" & userInput & "' AND ST400_LEAK_VALUE IS NOT NULL ;"
Set countRS = conn.Execute(countSQL)
If Not rs.EOF Then
Dim torqueparafusos As Variant
Dim leakvalue As Variant
Dim heliumpress As Variant
heliumpress = rs.Fields("ST390_HEPRESS_TEST").value
torqueparafusos = rs.Fields("ST300_TORQUE").value
leakvalue = rs.Fields("ST400_LEAK_VALUE").value
Debug.Print "Valor de fuga encontrado na base de dados : " & leakvalue
Debug.Print "Torque encontrado na base de dados : " & torqueparafusos
Debug.Print "Valor da pressão do hélio encontrada na base de dados : "
ThisWorkbook.Sheets("Sheet1").Range("T10").value = torqueparafusos
ThisWorkbook.Sheets("Sheet1").Range("S10").value = leakvalue
ThisWorkbook.Sheets("Sheet1").Range("U10").value = heliumpress
Dim leakCount As Long
leakCount = countRS.Fields("LeakCount").value
ThisWorkbook.Sheets("Sheet1").Range("R10").value = leakCount
Else
Debug.Print "Não existe qualquer match na base de dados."
End If
rs.Close
conn.Close
Set rs = Nothing
Set conn = NothingThere is more code inside that event but its nothing that has to do with the SQL Query so i don't think its necessarily important to post here.
Im pretty new to SQL and i only know how to do basic query's so i have no idea why this is so slow,could it be because of the hardware of the server?
i ask that because there is also another server that i've been pulling information from (with similiar queries) and sure, that one has much less information than the one im trying to pull information from but it only takes like 2-3 seconds tops
while this one takes around 1 minute +
Any kind of help would be appreciated,thank you!
