It is always best to do most of the processing on a server and only return the filtered results back to the requesting client. In this case I need two columns (state and county) otherwise I might have simply created a macro variable if only a single column were required.
The LIBNAME statement is used to bulk copy (BCP) 8 rows from DEV into the global (##) temp table. Those 8 rows are then joined to an 8 million row table and the entire process takes less than one second.
/* store data into a global (##) temporary table on SQL Server */ libname sqltemp odbc noprompt = "Driver={SQL Server};Server=devserver;Trusted_Connection=Yes;" bcp = yes connection = global ; proc sql ; connect to odbc as prod ( "Driver={SQL Server};Server=prodserver;Trusted_Connection=Yes;" ) ; /* populate data from prod into a dev SQL Server global temporary table */ create table sqltemp.'##counties'n as select * from connection to prod ( select * from db.schema.table where statecode = &state. and countycode = &county. ) ; disconnect from prod ; connect to odbc as dev ( "Driver={SQL Server};Server=devserver;Trusted_Connection=Yes;" ) ; create table work.results as select * from connection to dev ( select a.* from db.schema.table a inner join ##counties b /* SQL Server temp table */ on a.state = b.state and a.county = b.county ) ; disconnect from dev ; quit ; libname sqltemp clear ;
No comments:
Post a Comment