Linker IT Software
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Performance Issue in 2nd Execute Query (Read 3585 times)
YaBB Newbies


Posts: 2
Performance Issue in 2nd Execute Query
14.11.14 at 04:36:35
Hi, I have a performance in the query.  Following with the code. Have any idea on it ?  Thanks !!
Sub Refresh_DATA()
   SQLXL.Database.Connect ..........
   Count = 1
   For Each key In Worksheets("SHEET_LIST").Range("A1:A100").Cells
        ' - Run 1st time in looping, it was fast and normal  
           ' - Run 2nd time in looping, it was very slow and show message in excel "Processing Query (Press any key to cancel)"  
        ' - However when run debugger in step by step, nO performance issue find.  
        ' - I also try add a code to sleep 10 seconds for each select, no any improve
       Call Select_Data(num, StartDate, EndDate, key.Value)  
End Sub
Sub Select_Data(num As String, StartDate As Date, EndDate As Date, key As String)
    SQLXL.Sql.setText "SELECT col1, col2, col3 FROM table1 WHERE date > TO_DATE('20140101', 'yyyymmdd')"
    Set SQLXL.Sql.Statements(1).Target = SQLXL.Targets(litExcel)
    SQLXL.Sql.Statements(1).OptimiseForLargeQuery = False
    With SQLXL.Targets(litExcel)
        .AutoFilter = False
        .AutoFit = True
        .Headings = True
        .Sort = False
        .StartFromCell = "$A$2"
        .Transpose = False
        .SQLInNote = True
        .ShowNote = True
        .FormatData = True
        .FreezePanes = False
        .PasteInsert = False
    End With
    With SQLXL.Sql.Statements(1)
        .ShowParametersDlg = False
        .ShowResultsetDlg = False
    End With
End Sub
Back to top
« Last Edit: 14.11.14 at 04:38:00 by allanyaucl »  
  IP Logged
Gerrit-Jan Linker
YaBB Administrator

Posts: 75
Re: Performance Issue in 2nd Execute Query
Reply #1 - 14.11.14 at 08:16:10
It is difficult to say what caused the slowless. You loop over 100 values and run essentially a select statement and dumping the values in A2. Why A2 all the time? It will overwrite that was already there. Have you tried to use the SQLQueryValue Excel formula that SQL*XL adds? Perhaps that is a more elegant way to do it. It allows you to run SQL from an Excel formula. When you want to bring more that one row or column back you need to enter it as an array forumla. There are examples on the website about it.
For your performance problems I would recommend to test it when there are no other documents open in Excel. Slowness can be caused by recalculations by Excel. You can set Excel's calculation method to manual to avoid unnecessary automatic calculations. One thing to try on the SQL*XL side is the output target litNone. That will not generate output but still the SQL will be executed. In your example that will show whether the slowness is due to Excel or SQL*XL.
Good luck!
Back to top

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
YaBB Newbies


Posts: 2
Re: Performance Issue in 2nd Execute Query
Reply #2 - 14.11.14 at 10:11:36
It don't put the result set in same place, It have Sheets(key.Value).Select  before call the sub Select_Data.  That mean it put each result let in difference sheet. Follow is my desgin.  
Sheet  : Sheet_list,
            Cell : Sheet_name, <-- Excel Sheet name for keep the result set
                    item_code,   <-- Where condition  
                     startdate,    <-- Where condition  
                     enddate      <-- Where condition  
Sheets : item_1, item_2 .... item_n  
The vba will read the rows in sheet_list using  for loop, then SELECT the rows base on item_code, startdate, enddate then put the result in difference sheets.  
Given more detail in my testing:
               1st Query    2nd Query
Run VBA      2 secs      Over 2 mins  
Debugger    2 secs          2 secs  
In my vba calculation method is disable, I think the performance issue not cause by this. I will try your 2nd suggestion litNone in this weekend.  
Back to top
« Last Edit: 14.11.14 at 10:13:48 by allanyaucl »  
  IP Logged
Pages: 1