How effecient are correlated sub-squeries for large datasets (Proc SQL)? Is it a common practice among sas users?

[sachin01663]

My coding style in SAS and SQL is writing step wise code with comments rather than complex sub-squeries. I prefer doing multiple joins rather than one complex join within a subquery.

Recently  I have seen some code with massive Sub-queries and multiple joins which honestly I found hard to follow. I must mention it was by SQL coders and not SAS users.

My question is - Are correlated sub -queries very commonly used in SAS - specially with large tables ( 100k+ rows)? Do you guys come across this quite often?

I understand there is nothing wrong or right in this but my aim is to know what is the best practice

Thanks, as always.

Sachin   

[LinusH]

Can't tell if my experience applies only to SAS. In general, I think correlated sub-queries can be complex, and for large tables, hard to optimize.

Indexing on the key columns may help to make speed it up.

Often these queries are used for updating target data with a transaction table. Even if I generally like SQL, I think that in these cases, a data step approach (modify by) is often much more efficient.  

[Ksharp]

My question is - Are correlated sub -queries very commonly used in SAS - specially with large tables ( 100k+ rows)?

No. SQL is not suited for large tables, and even it is not as efficient as cartesian product.

It is advantage for SAS to process large table.

Ksharp

[esjackso]

I dont have hard evidence for this and it might also fall under the category of "your mileage might vary", but given the gains in processing power and memory, SAS is almost always bottle necked at I/O (ie writing info to disk, temporary or permanently). Given this the less writing to disk you do the better, so sub queries should reduce some of the I/O. I have seen this in my case (complex queries from relational databases) but as mention by it probably depends on what the task is. Some of our more complex queries are made because we are writing / verify queries for a SQL Server Application our Developers are making, which means the query needs to be as platform independent as possible (ie no data steps or procs).

I would say even after 10 years of writing SQL code in SAS, I rarely start off building a complex query directly but build the pieces and see if it makes sense to combine, just because I find it easier to track down where I have gone wrong that way. I do believe there is a trade off between readablilty of the code and using highly complex SQL queries (4 or more levels deep).

I would be interested in hearing others comments.

EJ

[SASKiwi]

My impression is that SAS's SQL optimiser has improved a lot over recent SAS versions, that is between 9.1 and 9.3 (and way better than Version 8). I found 9.1 to be not so good with complex SQL especially sub-queries, but recently I have tried writing steps with 5 or 6 sub-queries in SAS 9.3 and found performance to be very good. But as always only testing with your specific requirements will prove it one way or the other. I follow the EJ approach also!

[sachin01663]

Thank you guys.

I agree with esjackso1 and LinusH. I hardly use complex SQl queries so wanted to understand what am I missing on.