topic Re: How to get a small set of records from a very large table in SAS Procedures

How to get a small set of records from a very large table

Karen — Tue, 17 Mar 2015 17:37:11 GMT

I have a list of of about 20,000 ids (length $20.). There is a SQL Server database table on a server somewhere with about 2 billion records. I need to get all the records from that table for my list of ids. I work on a 64-bit PC and connect to the database with an OLEDB libname A. I tried the following code:

proc sql;

create table NEW as select * from id_list i, A.bigTable t where i.id = t.id;

quit;

This took forever to run and failed because my server connection timed out at 8 hours. So I added a variable to id_list (obsnum=_n_;) and modified the syntax to:

create table NEW as select * from id_list (where=(1 le obsnum le 1000)) i, A.bigTable t where i.id = t.id;

This took 12 seconds (real time) to run. So I wrote a macro to do this and ran it 20 times, giving it a new thousand-id range each time.

Then I thought, why not try 2000 ids at a time, instead of 1000. 30 minutes later, this query was not complete so I stopped it.

My question is: what accounts for the huge increase in time when I try to get records for more than 1000 ids at a time? Is there a better method to go against a very large table to find records?

Re: How to get a small set of records from a very large table

ballardw — Tue, 17 Mar 2015 17:40:28 GMT

How you connect may be an issue but one of the first things is only select the variables you need.

The selection may be quicker as pass-through as well.

Re: How to get a small set of records from a very large table

Haikuo — Tue, 17 Mar 2015 17:51:23 GMT

Is it possible that you have missing IDs after first 1000 IDs?

Re: How to get a small set of records from a very large table

Tom — Tue, 17 Mar 2015 18:00:36 GMT

Can you create a table in the SQL database to store the list?

Otherwise you might be better off using the SAS dataset to generate WHERE clauses against the large table.

Here is an example. You might have to adjust if your ID variable is character or if it is too long to fit 1000 values into a single macro variable.

proc sql noprint ;

select distinct id into :idlist1 separated by ',' from id_list (firstobs=1 obs=1000) ;

select distinct id into :idlist2 separated by ',' from id_list (firstobs=1001 obs=2000) ;

create table NEW as select * from A.BIGTABLE

where id in (&idlist1,&idlist2)

;

quit;

Re: How to get a small set of records from a very large table

Ksharp — Wed, 18 Mar 2015 13:47:20 GMT

You'd better create a INDEX for that prime key variable for a large table .and try to use PASS-Through SQL ,let SQL Server to do such heavy work.

Re: How to get a small set of records from a very large table

Patrick — Wed, 18 Mar 2015 15:11:48 GMT

Is in your libname pointing to the SQL Server option "MULTI_DATASRC_OPT=IN_CLAUSE" defined?

Use below option in your code to see in the SAS log what code actually gets sent to the dbms

OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX;

Is the code executed for 1000 and 2000 obs at a time the same? Just thinking that may-be you're exceeding with 2000 id's some threshold where SAS needs to generate different SQL code.

Re: How to get a small set of records from a very large table

LinusH — Wed, 18 Mar 2015 15:25:54 GMT

My guess is that you are moving the whole source table to SAS for the join.

Try the DBKEY= ds option for the cross platform join.

If that doesn't work for you, consider to use a temporary table in SQLS so you are sure that the whole join will take place where the data is.

As said, be sure that the id column is indexed.