Optimizer in WRS

[Ian]

I wish the SAS optimizer in WRS would not tamper with SQL (submitted to a third-party database) that contains a count(distinct). Reading all the rows back to SAS and then doing the distinct count in SAS is so expensive in terms of I/O. Why can't it just send the SQL as-is?

[LinusH]

What do you mean that WRS is tampering the SQL?

Which version are you using?

Do go through an Information Map?

Please attach the SQL gfenerated by WRS/Information Map, and the SQl that is executed by the RDBMS.

Maybe you mean that the SQL sent by the SAS/ACCESS engine is not implicitly sending the whle query?

[Ian]

We use SAS 9.2, Oracle 11g and WRS 4.3. The SAS access engine will not pass any sql with count(distinct) down to Oracle. I've been wondering for years why not.

I don't see why I need to prove this behaviour - it is common knowledge.

[Quentin]

I don't know much about WRS.  But assuming you are actually writing the query yourself (rather than relying on a query generator in WRS or some such), since you have SAS/ACCESS to Oracle (or ODBC or whatever), why not just write an explicit pass-through query? That's probably the easiest (only?) way to guarantee that a query will be executed by Oracle.

[LinusH]

Hope I didin't offended you. I was a little bit confused by the post title, and the fact that problem was in the ACCESS engine. But if this is just a frustration overflow, (and not a question to the community), I suggest that you direct directly SAS, or in a ballot item.

I have been reading the documentation and tech papers, and concluded from what is not described there, that count(distinct) construct is not supported by implicit pass-thru. Bur very clear, no.

If this is critical to, you probably need to move your WRS data to SAS. Perhaps store the data in SAS cubes, which usually are excellent in calculating this.

If all reports will have this measure, perhaps it's possible to create as SQL pass-thru view to the underlying RDBMS.

And I totally agree with you, SAS/ACCESS should definitely support implicit pass-thru for count distinct.

[Quentin]

As a work around, isn't it possible to just code an explicit pass-through query (in the stored process, or when you register a table, or in infomap studio, or whatever)?

I haven't used WRS / infomaps much.  But if I were writing a stored process to munch data from Oracle, I could certainly code the data extraction to be done with an explicit pass-through PROC SQL step.  Could be much easier than pulling all the data down into SAS.

[Ian]

Thanks for your reply. We will try a stored process and then wrap that inside an information map. We have plenty of infomaps - so that might be a tedious exercise. The improved performance would be worth it.

In the meantime, I'll keep bothering SAS to tell me why the Access engine does not support passing count distinct down to the database.

[Ian]

On second thought, the stored process would need to be different for each query. The beauty of information maps is that they are dynamic and allow an endless variety of queries (selects and where clauses). The stored process will effectively tie our hands to a particular predefined set of parameters that may not suit all uses.

[jakarman]

Do not know what you mean with common knowledge with the distinct keyword see: http://support.sas.com/techsup/technote/ts661.pdf

And this is and very old paper about (sugi29) about optimizing SQL and implict pass thru

SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition

Suggestion is carefull review the SAS docs like: SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition

It is changing all the time. The best thing is to test the query and the behavior with eg eguide. Change the options and settings.

Look at some settings like SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition

You need to know a lot of oracle interfacing and all options. No easy job with all those possibilities. 

Even the bulkoader is the same as native SQl-ldr, but where is the intermediate file?

SAS/ACCESS(R) 9.3 for Relational Databases: Reference, Second Edition 

.

[jakarman]

I missed the point of the issue .. my failure...

(Thx Linus), and I agree it should done as much of the work in database from the easiest SAS approach usage.

Rereading and looking (some of the same challenges).

Is it the count distinct agregate fucntion where it is about?

SAS(R) 9.4 SQL Procedure User's Guide  (Performs statistical summary calculations) 

Is it not a format date or other issue causing the pass-thru to fail, eg this note:

Note: When more than one argument is used within an SQL aggregate function, the function is no longer considered to be an SQL aggregate or summary function.

I have the experience that using the tools like query-builder is generating SQL not very suitable for pass-thru processing.

There are new DBMS interfaces coming like fed-sql and proc ds2.

At the same time all is focussing to SAS-VA and could be SAS-portal WRS is suffering from that    

The reason as far  i can guess: The agreggate functions are becoming implemented but they are not always standard SQL.

It could be SAS is far behind following this evolution or missing the implemtation not wanting to support every type variation of SQL