Improving PROC SQL using Oracle table performance

bbaer — Fri, 21 May 2021 16:23:11 GMT

Hello everyone,

At my current position there are a variety of Oracle tables that I have to use. For the most part I am using reading these tables in and performing analysis based on them, but on occasion I need to make some edits or create new variables in the tables. Unfortunately the performance for any writing/modifications or creation of new Oracle tables using PROC SQL seems to be very slow. I have tried making some of the modifications suggested in this article: https://www.lexjansen.com/nesug/nesug05/io/io8.pdf such as setting readbuff and insertbuff in the libname statement, but that doesn't seem to have made much of a difference. Does anyone know any other settings I could change to try to improve the performance of PROC SQL, or alternatively if I could get better performance with a Data step or another technique?

Thank you!

Re: Improving PROC SQL using Oracle table performance

SASKiwi — Fri, 21 May 2021 22:20:35 GMT

So is it only updating Oracle tables that is slow, not reading? If so READBUFF isn't going to help. In my experience there is no magic bullet to speeding up database writes, you just have to experiment with different techniques to see what works best. We update SQL Server databases a lot and do get good performance typically using table appends. The INSERTBUFF and DBCOMMIT options are helpful for this.

You would be best to post the complete SAS log of an Oracle database update program that is slow so we can get a better idea of what you are doing.

topic Re: Improving PROC SQL using Oracle table performance in SAS Programming

Improving PROC SQL using Oracle table performance

Re: Improving PROC SQL using Oracle table performance