SQL passthrough

[JHE]

Good afternoon, I need advice, please see two attach log files; first one o not know how to resolve on this. second one, only 10 records took over an hour to run is the  problem, how to resolved. 

Thank you

[Shmuel]

For log1 - there is a disk space issue writing to a temporary sybase table. Contact your sybase DBA.

As for log2 - your query is the select DISTINCT with OUTOBS=10.

To get that amount of output observation you probably need read much more observations,

depending on data, amount of duplicates and of variables, beyond the overhead of the connection to sybase.

[JHE]

log 1, I am going to contact SAS admin,

log 2, might be the disk space issue, only 10 records but took over an hour to complete.

Thank you 

Joanne 

[SASKiwi]

Not sure why you use DISTINCT in all of your queries. These will definitely slow them down and cause Sybase to do extra sorting to confirm the uniqueness of your result set and use more database temporary space. I try to avoid using that in any SQL I write. There are other ways to ensure uniqueness by refining your queries or doing summarising. I suggest you explore each of the tables you are reading to see if DISTINCT is necessary or not.

[JHE]

Oh, good point .

[Kurt_Bremser]

outobs= is a SAS option. When using explicit passthrough, the SQL code in there is viewed as a "black box" by SAS, and it does not change anything in there, like setting output options. So the whole query will be executed, including all joins and sorts for distinct. SAS then returns only 10 observations from what it gets.

Regarding DISTINCT: use it only when it is needed. It forces a sort on ALL columns in the select, and you might imagine what that costs.

[JHE]

Is there a way I can clean up my tempDB . Our SAS admin not reply to me yet

[Kurt_Bremser]

Your storage problems do not happen on the SAS side, they happen in the database, so you need to get in touch with the DataBase Administrator (DBA).

[ChrisNZ]

> only 10 records took over an hour to run

You ran the whole query. That took an hour. Your only keeping 10 records of that result will not change the passthrough query.

I can't tell you more since I will not open untrusted DOCX files, but you may be able to limit the number of rows inside the pass-through code, for example by adding a condition on ROWNUM or similar (ROWNUM is for Oracle).