6 Replies Latest reply: Feb 8, 2013 8:23 AM by Denis Mukhin RSS

    Performance ROracle vs. RODBC


      I just set up database access for a Oracle 11g database using RODBC. However the performance is rather poor. I need to analyze several million records on a regular basis in R. However, it takes hours to get them into a data frame object (i.e. more than 15min per 1 million records).

      How does the performance of ROracle look like? Do you have any benchmarks on comparing RODBC and ROracle?

        • 1. Re: Performance ROracle vs. RODBC
          Denis Mukhin
          We don't really have a formal comparison but we do have experience using RODBC in the context of RODM package. Writes were certainly much much slower. I don't really have RODBC installed on my system now, but here are some numbers on my system using ROracle on a 220K data set. Nothing spectacular about the hardware. It's a VM.
          R> con<-dbConnect(Oracle(), "rquser", "rquser")
          R> system.time(dat<-dbGetQuery(con, "select * from ontime_s"))
             user  system elapsed 
            5.740   0.144   7.501 
          R> dim(dat)
          [1] 219932     26
          Based on this numbers I do recommend that you give ROracle a try (even though the numbers came from different machines). If you have trouble installing it post a question here. We'll be more than happy to help. Take a look at the INSTALL file. This should get you started. One thing that is not mentioned there yet is a problem setting up Instant Client SDK from a .zip file. Once unzipped it will not have libclntsh.so symbolic link. You will have to create it manually.

          Hope this helps,
          • 2. Re: Performance ROracle vs. RODBC
            Thanks for your fast reponse and sharing your experience. I give it shot.
            • 3. Re: Performance ROracle vs. RODBC
              I finally got the time to try ROracle. However, I ran into the following problem.

              I did the following steps:

              1) Open R and install R package "DBI" (i.e. "install.packages("DBI")")

              2) Set up directory "C:\instantclient\x64", i.e. unzip "Instant Client 11.2 Basic" and "Instant Client 11.2 SDK" packages to this folder

              3) Go to folder "cd C:\Program Files\R\R-2.15.1\bin"

              4) Download "ROracle_1.1-7.tar.gz" from CRAN and copy it to folder "C:\Program Files\R\R-2.15.1\bin"

              5) Run "R CMD INSTALL --build ROracle_1.1-7.tar.gz" from folder "C:\Program Files\R\R-2.15.1\bin"

              Unfortunately, I get the following error:


              Microsoft Windows [Version 6.1.7601]
              Copyright (c) 2009 Microsoft Corporation. All rights reserved.

              C:\>cd C:\Program Files\R\R-2.15.1\bin

              C:\Program Files\R\R-2.15.1\bin>R CMD INSTALL --build ROracle_1.1-7.tar.gz
              * installing to library 'C:/Program Files/R/R-2.15.1/library'
              * installing source package 'ROracle' ...
              ** Paket 'ROracle' erfolgreich entpackt und MD5 Summen überprüft
              ERROR: configuration failed for package 'ROracle'
              * removing 'C:/Program Files/R/R-2.15.1/library/ROracle'


              I have not yet created the libclntsh.so symbolic link as there is no description in the INSTALL text file how to do that.

              Any help would be greatly appreciated. Thanks.
              • 4. Re: Performance ROracle vs. RODBC
                Denis Mukhin
                Try setting <tt>OCI_LIB64</tt> before running <tt>R CMD INSTALL</tt>
                set OCI_LIB64=c:/instantclient/x64/instantclient_11_2
                • 5. Re: Performance ROracle vs. RODBC
                  Thanks for your quick reply.

                  However, it still does not work. Can I check somehow if your command actually worked?

                  Actually there is no folder "instantclient_11_2" in the folder "x64" only "sdk", "vc8" and "vc9" (I unzip in x64 the above mentioned ZIP files, which are listed in the INSTALL file.).

                  Can you think of anything else? Thanks.


                  Edited by: MMzh on 08.02.2013 06:23
                  • 6. Re: Performance ROracle vs. RODBC
                    Denis Mukhin
                    Do you have Rtools installed on your system?