[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Efficiently Output query results into two adjacent columns in Excel spreadsheet

Posted on 2012-08-12
23
Medium Priority
?
847 Views
Last Modified: 2012-08-24
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
0
Comment
Question by:lostinspace9
  • 11
  • 7
  • 4
  • +1
23 Comments
 
LVL 35

Accepted Solution

by:
Norie earned 750 total points
ID: 38285291
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).
0
 

Author Comment

by:lostinspace9
ID: 38285318
Would this take care of the second column too?
0
 
LVL 35

Expert Comment

by:Norie
ID: 38285357
It will dump the entire recordset, which would include the second field if there was one.
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 

Author Comment

by:lostinspace9
ID: 38285474
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.
0
 
LVL 35

Expert Comment

by:Norie
ID: 38285488
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;
0
 

Author Comment

by:lostinspace9
ID: 38285556
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.
0
 
LVL 35

Expert Comment

by:Norie
ID: 38285663
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).
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38287178
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

0
 

Author Comment

by:lostinspace9
ID: 38287358
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;
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38287409
>>"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?
0
 

Author Comment

by:lostinspace9
ID: 38287443
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
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38287543
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.
0
 

Author Comment

by:lostinspace9
ID: 38287609
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38287656
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.
0
 

Author Comment

by:lostinspace9
ID: 38287788
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.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38287915
Can you create a table of the relevant values used in the IN clause? And, if possible, make the query into a stored procedure.
0
 

Author Comment

by:lostinspace9
ID: 38287934
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?
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 38288199
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.
0
 

Author Comment

by:lostinspace9
ID: 38302498
I have a table set up with the keys.  Please give me an example of what you mean.  Thanks.
0
 
LVL 32

Expert Comment

by:awking00
ID: 38306409
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?
0
 

Author Comment

by:lostinspace9
ID: 38306434
Yes, I have tried that.  I am using a VB script to do the extraction.
0
 
LVL 85

Assisted Solution

by:Rory Archibald
Rory Archibald earned 750 total points
ID: 38310827
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.
0
 

Author Closing Comment

by:lostinspace9
ID: 38331260
Thanks for your help.  It is much appreciated.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Suggested Courses

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question