topic Re: Why doesn't SAS use index on nested query, or is it another problem? in SAS Procedures

Why doesn't SAS use index on nested query, or is it another problem?

kooliskool — Thu, 07 Feb 2013 23:48:46 GMT

I have used nested query to query on a variable that is already indexed, but SAS returns the following message:

INFO: Index BaseCardNumber not used. Sorting into index order may help.

The following is the code:

proc sql;

create table CCF_GDN as

select *

from fad.CCF_GDN

where BaseCardNumber not in (select distinct ACCT_NBR

from FALT2)

and EffectiveDate between '02oct2012'd and '02jan2013'd

;quit;

So I'm wondering how should I write this in another way to optimise the search by using the index on the variable BaseCardNumber.

Thanks in advance guys.

Re: Why doesn't SAS use index on nested query, or is it another problem?

DBailey — Fri, 08 Feb 2013 00:43:09 GMT

might try...

create table ccf_gdn as

select f.*

from

fad.ccf_gdn f

left outer join falt2 f2

on f.basecardnumber = f2.acct_nbr

where

f.effecdtivedate between '02oct2012'd and '02jan2013'd

and f2.acct_nbr is null;

Re: Why doesn't SAS use index on nested query, or is it another problem?

kooliskool — Fri, 08 Feb 2013 04:11:48 GMT

Hi,

Thanks on the reply, I guess it is a possible solution, because I tried and the joining does use the index. However I'm interested in querying BaseCardNumber NOT in acct_nbrs, so can't really do that with joins right.....?

And the answer I was really after is how to still do the nested query, but using the index to make it faster, because the error message says order of the observation is not the same as in the index, how can I fix that problem?

Thanks in advance.

Re: Why doesn't SAS use index on nested query, or is it another problem?

Tom — Fri, 08 Feb 2013 05:04:16 GMT

How would it use an index for NOT in query? I can see if you have a list of IDs you can look them up in the index. But how could you use the index to look up a the ones that aren't in your list?

Re: Why doesn't SAS use index on nested query, or is it another problem?

DBailey — Fri, 08 Feb 2013 13:25:23 GMT

that's the purpose of doing a left join and then requiring the value to be null. It actually accomplishes the same things as a "not in"...

select

from t1 left outer join t2 on t1.col1 = t2.col1

where t2.col1 is null

is functionally equivalent to

select * from t1 where col1 not in (select col1 from t2);

I'm a bit surprised that the not exists doesn't use the index on t2.

select * from t1 where not exists (select * from t2 where col1=t1.col1)

I would have thought that it did.

select * from