3
$\begingroup$

I want to use R or Python to query big structured SQL-type data, but they are very slow compared to SAS.

I tried using R and Python to return a 1.3 million record Oracle ODBC passthrough query. The query took 8-15 seconds in SAS, 20-30 seconds in Python, and 50-70 seconds in R. Does anyone know why?

R Packages Used:

First I used the RODBC package in R to query to the Oracle database. Then I tried the ROracle package, but both packages were much slower than SAS.

Python Packages Used:

For Python, I used Oracle's cx_Oracle package for the query.

Thanks a lot, Sean

$\endgroup$
  • $\begingroup$ I answered my own question, but I don't have 15 reputation points yet, so I can't officially answer it. Here's the answer: In R and Python, I was using ODBC packages that were slower than the Oracle ODBC package in SAS. I found much faster packages that could query at the same speed as SAS. For R, the package is "odbc" (lower case), not to be confused with "RODBC" (upper case). For Python, instead of using "cx_Oracle", I found "turbodbc", which is just as fast as SAS. I'm thrilled because now we can use free open source Python/R instead of very expensive SAS. $\endgroup$ – Sean McCarthy Jan 4 '18 at 15:31
3
$\begingroup$

This answer some of your questions from a Python perspective:

Is Python any faster?

This question is a bit tricky to answer, it will depend on your usage of Python, but Python is not a fast language per se. However, the pandas library in Python have been reported to handle tables of 33M-100M rows, see this. I myself have used to handle around 10M rows from a Postgres table. For a detailed experimentation using pandas, see this. In the link they apply some operations on datasets of 88M rows and 74 columns.

Do we need Hadoop or parallel processing or something else to make R/Python as fast as SAS?

Before trying using Hadoop or Spark, I recommend you to follow some optimization tricks (tips):

[1] A Beginner’s Guide to Optimizing Pandas Code for Speed

[2] Using pandas with large data

This link (Don't use Hadoop - your data isn't that big) can be useful too.

Also for a comparison of SAS and pandas you can read the pandas documentation on such comparison or this.

Is it because R and Python are matrix-based languages and SAS is more traditional-database-oriented?

Python is not a matrix-based language, as far as I know the language does not offer by default any capabilities for handling matrices. I think your referring to the numpy/scipy stack, this is a separated library.

$\endgroup$
  • $\begingroup$ Thanks a lot for your very helpful answer! Ultimately, I answered my own question. I was just using slow packages for Python and R. When I switched to the "odbc" package in R (from "RODBC" and "ROracle"), the query sped up tremendously. Same goes for Python--instead of using the slow "cx_Oracle" package, I switched to "turbodbc", which was much faster. In general, I see now that package selection is very important. For joining the tables in R, the "data.table" package was WAY faster than the "merge" function--less than a second vs. about 20 seconds for "merge". $\endgroup$ – Sean McCarthy Jan 4 '18 at 15:36

Not the answer you're looking for? Browse other questions tagged or ask your own question.