We help IT Professionals succeed at work.

Efficiently Output query results into two adjacent columns in Excel spreadsheet

I have written an SQL query from an Oracle database to extract 48 values (24 per column) and place them into an Excel spreadsheet into 2 adjacent columns.  It is working.  However, it is slow.  When I run the script in debug mode, it slows down when I begin to output the data (takes approximately 30 seconds).  I am a novice SQLPlus and VBA user.  Any suggestions on how to speed up the output up would be appreciated.  I have attached a snippet of the code and indicated where the slow down begins.  Also, if you provide a suggestion involving the query, please provide an explanation.  I tried an Inner join but it gives me an Oracle  00923 error.  The Oracle version is 11g.  Thanks for your help.
eequest.txt
Comment
Watch Question

Analyst Assistant
CERTIFIED EXPERT
Commented:
You can output the whole recordset in one go using CopyFromRecordSet.
Worksheets("Sheet1").Cells(8,15).CopyFromRecordSet rcdSet

Open in new window

That would output all the fields of all the records starting at Cells(8,15).

Author

Commented:
Would this take care of the second column too?
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
It will dump the entire recordset, which would include the second field if there was one.

Author

Commented:
Thanks for the help, but since the query does not load the results into two columns, this did not help.  Any solutions on improving the efficiency of the query would be appreciated.  Thanks.
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
What does the query load the results into?

You seem to be referring to fields(0) and fields(1) which would suggest that you have at least 2 fields/columns in the query.

Even if it's not 2 columns isn't dumping the whole recordset more efficient than looping through the records and writing them field by field.

By the way, which query is is you are referring to?

Is it qryString, which you use in the code, or this?

  1 SELECT C1.Value, C2.Value
  2   From
  3   (SELECT ROWNUM,RECNUM, ¿SCADA:ANALOG¿.Value from ¿MYTABLE:TABLE¿ "T"
  4  WHERE T.Key IN
  5  ( 'AA', 'BB', 'EE', 'CC', 'DD')
  6  ORDER BY T.Key)
  7  C1
  8   INNER JOIN
  9   ( SELECT ROWNUM,RECNUM,T.Value from ¿MYTABLE:TABLE¿ "T"
10  WHERE T.Key IN
11   ('EE', 'FF', 'GG', 'HH', 'II')
12   ORDER BY T.Key)
13   C2
14   ON C1.RECNUM = C2.RECNUM;

Author

Commented:
I initially loaded the results into qryString.  I was trying to improve the efficiency with the qryString identified in your post above.  However, it does not work.  When I run it, it issues an Oracle 00923 error (missing or misplaced from).  Yes, I agree it is more efficient to dump all the data at once,  but in order to use the CopyRecordSet, the query needs to store the data into two columns.  Thanks for the tip.
NorieAnalyst Assistant
CERTIFIED EXPERT

Commented:
So what query are you using and how does it get it's results?

As far as I know a query usually returns columns/fields of data from a query (or queries).
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Perhaps you mean:

  qryString = "SELECT " & Chr(34) & "MYTABLE:TABLE" & Chr(34) & ".Value FROM " & Chr(34) & "MYTABLE:TABLE" & Chr(34) & _
                Chr(34) & "MYTABLE:TABLE" & Chr(34) & " WHERE (" & Chr(34) & "MYTABLE:TABLE" & Chr(34) & _
                ".Key IN ('A','B','C','D','E','F','Y','W','G','H','I','J','Z','X','K','L','M','N','O','P','AA'," & _
                "'V','Q','R','S','T','U','BB','CC','DD','EE','TTR','TTU','FF','GG','RR','SS','HH','II','KK','LL'," & _
                "'MM','NN','OO','PP','TT','UU','QQ') ORDER BY " & Chr(34) & "MYTABLE:TABLE" & Chr(34) & ".Key"

 'Execute the query string
    Set rcdSet = conn.Execute(qryString)

    'Set Starting row to dump data into the worksheet
    Row = 8
    mcolumn = 15
    'Dump Data into worksheet, If rcdSet.EOF is True, there is no more data that was returned  (Slow DOWN)
    Do While Not rcdSet.EOF
        'Dump Data into worksheet - column O or 15 and P Column 16; If have multiple items in query, each would have a field assigne to it via the rcdSet command
        Worksheets("Sheet1").Cells(Row, mcolumn).CopyFromRecordset rcdSet, 24
        mcolumn = mcolumn + 1
    Loop
    rcdSet.Close

    'Close ODBC connection
    conn.Close
End Sub

Open in new window

Author

Commented:
Yes, that is what is working.  I was trying to improve the efficiency using this query, but as I stated it gives me an ORA-00923 error.  

 SELECT C1.Value, C2.Value
  2   From
  3   (SELECT ROWNUM,RECNUM, ¿SCADA:ANALOG¿.Value from ¿MYTABLE:TABLE¿ "T"
  4  WHERE T.Key IN
  5  ( 'AA', 'BB', 'EE', 'CC', 'DD')
  6  ORDER BY T.Key)
  7  C1
  8   INNER JOIN
  9   ( SELECT ROWNUM,RECNUM,T.Value from ¿MYTABLE:TABLE¿ "T"
10  WHERE T.Key IN
11   ('EE', 'FF', 'GG', 'HH', 'II')
12   ORDER BY T.Key)
13   C2
14   ON C1.RECNUM = C2.RECNUM;
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
>>"Yes, that is what is working."

My code is not the same as yours at all, so I'm not sure what you mean by that comment?

Author

Commented:
The following code does not work.  It gives me an ORA-00923 error.  It is an initial attempt to improve the code's efficiency.

 SELECT C1.Value, C2.Value
  2   From
  3   (SELECT ROWNUM,RECNUM, 'MYTABLE:TABLE'.Value from 'MYTABLE:TABLE' "T"
  4  WHERE T.Key IN
  5  ( 'AA', 'BB', 'EE', 'CC', 'DD')
  6  ORDER BY T.Key)
  7  C1
  8   INNER JOIN
  9   ( SELECT ROWNUM,RECNUM,T.Value from 'MYTABLE:TABLE' "T"
10  WHERE T.Key IN
11   ('EE', 'FF', 'GG', 'HH', 'II')
12   ORDER BY T.Key)
13   C2
14   ON C1.RECNUM = C2.RECNUM; Accept Multiple

The following code does work, but it is slow.  I would like to know if there is a way to make it more efficient.  It would be nice to be able to put the results into two columns within the query so I can use the CopyRecordSet command.  

qryString = "SELECT " & Chr(34) & "MYTABLE:TABLE" & Chr(34) & ".Value FROM " & Chr(34) & "MYTABLE:TABLE" & Chr(34) & _
                Chr(34) & "MYTABLE:TABLE" & Chr(34) & " WHERE (" & Chr(34) & "MYTABLE:TABLE" & Chr(34) & _
                ".Key IN ('A','B','C','D','E','F','Y','W','G','H','I','J','Z','X','K','L','M','N','O','P','AA'," & _
                "'V','Q','R','S','T','U','BB','CC','DD','EE','TTR','TTU','FF','GG','RR','SS','HH','II','KK','LL'," & _
                "'MM','NN','OO','PP','TT','UU','QQ') ORDER BY " & Chr(34) & "MYTABLE:TABLE" & Chr(34) & ".Key"

 'Execute the query string
    Set rcdSet = conn.Execute(qryString)

    'Set Starting row to dump data into the worksheet
    Row = 8
    mcolumn = 15
    'Dump Data into worksheet, If rcdSet.EOF is True, there is no more data that was returned  (Slow DOWN)
    Do While Not rcdSet.EOF
        'Dump Data into worksheet - column O or 15 and P Column 16; If have multiple items in query, each would have a field assigne to it via the rcdSet command
        Worksheets("Sheet1").Cells(Row, mcolumn).CopyFromRecordset rcdSet, 24
        mcolumn = mcolumn + 1
    Loop
    rcdSet.Close

    'Close ODBC connection
    conn.Close
End Sub
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
If you read the code you will see that it does use the copyfromrecordset command so I'm not sure what you are hoping for. Frankly, copying 48 records to a sheet should not take a long time unless you have a lot of calculations happening, in which case you should set calculation to manual, dump the data into the sheet and then set it back to automatic.

Author

Commented:
Presently, the query reads 48 values with one command and is slow.  What I would like is to be able to perform the query such that it is faster and stores its results into 2 columns each containing 24 values, allowing me to use the copyrecordset command.  Since the query does not store the results into 2 columns, the copyrecordset command is not doing what I need it to do.  It is outputting the results into 1 column.   There are no calculations going on.  Thanks.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
The code as written will output 24 records in one column and 24 in the next and so on until there are no more. Your original post said that the query itself was fast but populating the sheet is slow. if you have no calculations going on (and no event code running in response) then I don't see how it could be that slow to populate 48 cells.

Author

Commented:
I reran the query and noticed it is also slow performing the query.  The Copyrecordset is much faster and I would like to use that.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Can you create a table of the relevant values used in the IN clause? And, if possible, make the query into a stored procedure.

Author

Commented:
Are you asking me to create a table on the Oracle server with the values assigned to the keys and then execute the query on the Oracle server?  If so, what would be gained from that?
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011

Commented:
Running a query on the server should always be faster. If you have a table set up with the keys you are interested in then you can perform a straight join on that table which will be faster than an IN clause as well.

Author

Commented:
I have a table set up with the keys.  Please give me an example of what you mean.  Thanks.
awking00Information Technology Specialist
CERTIFIED EXPERT

Commented:
I have often found it quicker (and easier) to import a query from Oracle into Excel using the Import External Data, New Database Query wizard rather than export from the database to the spreadsheet. Have you tried that option?

Author

Commented:
Yes, I have tried that.  I am using a VB script to do the extraction.
CERTIFIED EXPERT
Most Valuable Expert 2011
Top Expert 2011
Commented:
If you have the keys in a table then it should be a simple join:


qryString = "SELECT " & Chr(34) & "MYTABLE:TABLE" & Chr(34) & ".Value FROM " & Chr(34) & "MYTABLE:TABLE" & Chr(34) & _
    " INNER JOIN KeyTable On " & Chr(34) & "MYTABLE:TABLE" & Chr(34) & ".Key = KeyTable.Key"

for example.

Author

Commented:
Thanks for your help.  It is much appreciated.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.