Asked • 04/17/19

SQL/SAS: Best performance for selecting from big table (2bn rows)?

I have a non-indexed 2 billion rows table in a read-only SAS SPD server (**bigtable**). I have another 12 million rows table in my workspace (SAS_GRID) with a single column of unique ids (**idlist**). Both tables are updated constantly. I want to filter the bigtable based on idlist, something like: create table filtered_bigtable as select t1.* from bigtable t1 inner join idlist t2 on t1.id = t2.id; What's the best strategy and code (sql or sas) for doing it quickly? **Edit**: tried Robert's suggestion using a hash table and the query ran in only 40 minutes (10 times faster). Thanks for all the answers!

1 Expert Answer

By:

Still looking for help? Get the right answer, fast.

Ask a question for free

Get a free answer to a quick problem.
Most questions answered within 4 hours.

OR

Find an Online Tutor Now

Choose an expert and meet online. No packages or subscriptions, pay only for the time you need.