Poor SAS View performance

[tansel]

We have a problem about SAS view performance when its getting a query from multiple tables -that contains reasonable large data- from database. We have a simple loan portfolio data that has nearly 600k rows and 40 columns for a day and we have it for 2 years period. It has some inner joins from differnt tables also (2-3 more tables) IT department create views for us and we find that its working so slowly. When you try to drag it to the process flow, it takes 30 minutes to have a view. 

I read that (i m not sure its true or not)  if you r getting a view that contains multiple join queries from different table, SAS is getting all data inside and make it ready for you for view usage. Is it true? as i mentioned above, i have to wait for neasrly 30 minutes to have a view from SAS and the data is not so large.

When we try it with a view that is getting one table query the perfomance is great. Also we put the data to sas table and try to view it. performance is great als this time. But i dont want to create a new copy database that contains same tables in SAS server.

Is anybody knows a solution to handle this poor performans of a view that contains multiple queries joined by a query?

p.s. we also try proc sql for just one day data. It is not also working properly.

Thanks.

[Tom]

The details matter. 

My guess is that you are using SAS code to merge multiple tables from an external database.  If SAS cannot figure out how to push the query into the database then it will be forced to pull all of the data out of the data base and then do the view. 

If you can configure the views/joins to be done in the database then you should see better performance as there should be less data transferred from the database to SAS.  Of course then you will need to take steps to optimize the performance of those queries in the external database.

[tansel]

Sorry for insufficent details. i ve just talked to IT dept. they said that thay are running query at the orjinal database. but i dont understand why we are not able to see it in seconds while it is running at orijinal database in seconds. we call the local support. and they said that when it becomes complicated with somequeries, even if its runnig on database, use the table option instead of view option i SAS. But, It doesnt sound reasonable to me.

[JackHamilton]

I agree with Tom's diagnosis and also with his statement that you didn't provide enough information.

Two things you might try:

- Add " OPTIONS SASTRACE=',,,DB' SASTRACELOC=SASLOG NOSTSUFFIX; " to see what's getting passed to the database. Is an optimized query with joins being passed, or are you getting back every single record in every table?

- Run a PROC CONTENTS , or something else that reads only the structure without reading any records. Is that also slow?

[tansel]

thanks for your reply. I will forward t to IT dept very soon. Now we r talking about creating the table at orijinal database then get a single view at SAS or directly create a table at SAS instead of view. But im not sure because of memory issues.It should be resolved with view option.

[Patrick]

---

@tansel wrote:
thanks for your reply. I will forward t to IT dept very soon. Now we r talking about creating the table at orijinal database then get a single view at SAS or directly create a table at SAS instead of view. But im not sure because of memory issues.It should be resolved with view option.

---

A view is sort of encapsulated SQL code that gets executed when you're using the view. As such a physical table will consume less resources during run-time than a view because with a table no such extra processing needs to happen.

Very often the bottleneck is the data transfer from the database to SAS. For this reason you want to reduced data volumes on the database first before moving the data to SAS for further processing.

When joining tables from a database then SAS will try to push the whole query to the database for processing and only return the result set (=minimize the data transfer between the DB and SAS).

If you add options sastrace=',,,d' sastraceloc=saslog nostsuffix; at the beginning of your code then the SAS log will show you which part of a query SAS could actually push to the database and which part got executed on the SAS side. Using this option and analysing the log is an important step in performance analysis. Sometimes a few changes to a query will allow SAS to push everything to a DB. Also: Be aware that SAS can't push all functions to the database so as soon as you use one of these non-compatible functions data will get pulled into SAS.

For the actual data transfer: There is a libname option readbuff=<some value> with a default which is almost always too low. Setting this option to a higher value can increase data transfer significantly.

And last but not least: 

If possible then create the views on the database and not within SAS. This guarantees that view execution will always happen on the database side and it also allows to use database specific SQL syntax for query optimisation.

[JackHamilton]

Could it be a bandwidth problem? If you don't need all the columns in the source tables, you could use SASTRACE to find out what's being brought back.

Also, if you have any ORDER BY clauses, you could try removing them, and then doing a PROC SORT in SAS. I wouldn't have thought that would matter, but it does appear to.

[JackHamilton · Accepted solution]

Can you create a single view on the database side that does all of the processing there, with no joins or wheres on the SAS side? You might have to upload some of your selection tables from SAS to the database to make this work. SASTRACE can be your friend here.

[tansel]

thanks to everyone. we did it finally. we created a temporary table on db side (includes joins etc) and executed query with it. Then we took the view from it with a simple query without joins. It worked in seconds.