topic SQL passthrough in SAS Programming

SQL passthrough

JHE — Fri, 17 Apr 2020 19:26:50 GMT

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

Re: SQL passthrough

Shmuel — Fri, 17 Apr 2020 20:37:06 GMT

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.

Re: SQL passthrough

JHE — Fri, 17 Apr 2020 20:48:32 GMT

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

Re: SQL passthrough

SASKiwi — Fri, 17 Apr 2020 21:33:48 GMT

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.

Re: SQL passthrough

JHE — Sat, 18 Apr 2020 00:52:36 GMT

Oh, good point .

Re: SQL passthrough

Kurt_Bremser — Sat, 18 Apr 2020 06:19:23 GMT

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.

Re: SQL passthrough

JHE — Sat, 18 Apr 2020 16:27:36 GMT

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

Re: SQL passthrough

Kurt_Bremser — Sat, 18 Apr 2020 17:54:39 GMT

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).

Re: SQL passthrough

ChrisNZ — Sun, 19 Apr 2020 04:04:49 GMT

> 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).