Any answer to performance questions has to consider the architecture of the SAS host, the architecture of the database, and the speed of the network connections. Also you need to look at the type of data, and how much of that data has variable length character fields.
Systems and databases can be grid or MPP, which means that they have many processors running in a massively parallel manner. These systems increase performance by 10 to 100 or more times, compared with single processing systems. Also the design of the database affects performance, so it's difficult to compare one database in third normal form and to another may non-normal database. But again, it depends on the queries that run on those databases, since most all databases have an issue where a few bad queries can slow down everyone.
Character data is a potential problem since SAS does not have true variable length data (maybe DS2 will make a liar out of me), but databases do have efficient storage for character data. Also character keys are slower and less efficient than numeric keys, which makes a difference when you are deciding to store BIGINT data as characters in a SAS dataset.
Getting down to performance in general, a SAS grid or an MPP is always better if you can afford it. Whether you have a grid or not, you still need to be concerned about data movement over any network, which is why some programs may take a day or so to finish. Your best bet for performance is to return relatively small result sets from the database, and have all the processing occur in-database, which means only the results are returned and no files are transferred across a network to compute those results.
You will really see the performance difference with big data. But to analyze performance, you must consider all the other relevant factors that affect performance. Oracle and SAS can run in a grid or MPP, and Oracle queries can be designed so they run completely in-database, which is generally going to be faster than any number of single processors on one computer. When you consider all of the factors, then you can make better decisions about performance issues and trade offs.
If you supply some more details about your environment and your budget, then performance recommendations are easier to recommend. Even without a budget or an MPP, you can make tweaks to improve elapsed time by a factor of 2 to 5 times. How much improvement do you need, and how would you measure it?
... View more
