topic Re: Exploit Index with Large List in SAS Programming

Exploit Index with Large List

DanZ — Wed, 10 Feb 2016 20:29:47 GMT

Is there an efficient way to exploit an index (45k unique) using a list of ~1k values without entering them into the WHERE? Is there some method of loading the list into a hash and pulling it into the where instead of into a subsetting if?

Re: Exploit Index with Large List

LinusH — Wed, 10 Feb 2016 20:54:00 GMT

Not sure what you are asking. What is your "problem"?
How do you select your 1k values?
If you have your 1k values in a lookup table you could either fill them into a macro variable to be used in a WHERE statement.
Or a simple inner join, if you are lucky SQL will use hash join as it's internal plan.

Using a data step hash makes no sense (if I ubders5your inquiry correctly ), it will require a table scan, and will be able to use the index.

Re: Exploit Index with Large List

DanZ — Wed, 10 Feb 2016 21:28:38 GMT

I guess another way to state "Is there a way to exploit an index outside of a WHERE"?

I'm not finding anything.

Background is that we need 6 tables (and counting...) attached to a table with 1.9 billion rows on a persistent basis. A full table scan is not an option, and SQL gets messy really quickly. There is an indexed field in common with one of the attached tables that needs to be filtered on.

The best I can think of is what you said, to populate a macro variable and load that into the WHERE.

Re: Exploit Index with Large List

SASKiwi — Wed, 10 Feb 2016 21:50:01 GMT

It would help if you could post your SQL so what you are trying to achieve is clearer to everyone.

Re: Exploit Index with Large List

LinusH — Wed, 10 Feb 2016 22:22:23 GMT

Still understand the "outside where" part.
Sounds you need to optimize joins between lookup tables and a large master table. So the best you can come with is either a where in () or SQL with indexed or hash join.

1.9 billion rows, in a Base SAS libname?
You could exploit SPDE, it may not optimize joins, but deals with indexes (update, where evaluation) better than Base.

If this is a really important use case, consider invest in SPD Server which has an index type specific for join optimization, and a star schema planner (which builds this in () clause automatically behind the scenes).