Solved Why are my SQL Queries so slow? (1 Viewer)

goncalo

Member
Joined
May 23, 2023
Messages
51
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:

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 = Nothing

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!
 
Here's a starter:


ANYTHING by Allen Browne is probably going to be useful. This article might be relevant to your stated issues. It's at least a starting point.
 
while this one takes around 1 minute +

What is "this one"? You have 3 queries in that code, which one is the problem? What happens when you directly run the query in the environment of the database (SQL Server I believe)? Is it just as slow there?

Generally the way to speed up SELECT queries is by using indexes:


It essentially optimizes the retrieval of data in specific fields. You would want to apply indexes to the fields in the WHERE and ORDER BY clauses of your query.
 
What is "this one"? You have 3 queries in that code, which one is the problem? What happens when you directly run the query in the environment of the database (SQL Server I believe)? Is it just as slow there?

Generally the way to speed up SELECT queries is by using indexes:


It essentially optimizes the retrieval of data in specific fields. You would want to apply indexes to the fields in the WHERE and ORDER BY clauses of your query.
by "this one" i meant the entire code block in general, not any specific query
i was also comparing this code block to another block of code that i have in another workbook since the code that i have in the other workbook is also similar to this once although it retrieves information from another database and is much much faster than the database im trying to retrieve information from with the code i provided

And yes its just as slow there which makes me believe that this could be an hardware issue (?)
 
If they are slow in SQL Server then that makes me think it's a query issue and not VBA and not hardware.
 
If they are slow in SQL Server then that makes me think it's a query issue and not VBA and not hardware.
it could very well be a query issue but like, on that other workbook that im using to retrieve information i use almost the exact same queries (sometimes even more complex ones) and it takes around 2-3 seconds for the information to appear
that database doesn't have as much information it probably has like -1 or 2 million rows compared to the one that is giving me issues
 
Last edited:
If the PROCESS_LABEL and ST400_LEAK_VALUE fields aren't indexed and you are querying a large table (100k records or more), then I would expect those queries to run quite slowly.

Run the exact same query in SSMS and make sure you turn on the Show Execution plan option, it will show you how it is working and highlight any missing indexes that would improve the performance.
 
If the PROCESS_LABEL and ST400_LEAK_VALUE fields aren't indexed and you are querying a large table (100k records or more), then I would expect those queries to run quite slowly.

Run the exact same query in SSMS and make sure you turn on the Show Execution plan option, it will show you how it is working and highlight any missing indexes that would improve the performance.
I ended up indexing alot of the fields and that fixed it!
it went from needing to wait more than a minute to it basically being done instantly
Thanks Minty,plog and Doc!
 
Be aware that randomly adding unnecessary indexes can significantly slow down other operations, as the indexes need to be updated when records are added or deleted.

The execution plan will normally show exactly what needs adding as a bare minimum to improve performance.
 

Users who are viewing this thread

Top Bottom