topic Re: Unused index during left join in proc sql in SAS Programming

Unused index during left join in proc sql

JanKwiatkowski — Wed, 23 Aug 2023 20:46:25 GMT

Does anyone know why during left join in roc SQL T1 (medium size table around 80k rows) and T2 (very large table with 3bln rows) Sas doesn't use index?
But during inner join proc SQL it uses index and performance is great.
I've used MSGLEVEL=I option to see what's happening in log.

Re: Unused index during left join in proc sql

ballardw — Wed, 23 Aug 2023 22:11:12 GMT

It may help to answer such a question by providing the log with the code and all the notes.

And indicate which data set(s) have index on which variable(s).

Re: Unused index during left join in proc sql

LinusH — Thu, 24 Aug 2023 07:45:35 GMT

You can also add the undocumented PROC SQL options _method and _tree to get more details on how PROC SQL is evaluating the query.

The optimal would a hash join, which requires that your left table fits into memory and uses index on the right table for the join criteria. You can affect the optimizer to chose this by adjusting the BUFFERSIZE option, but you cannnot force it.