Hi @cgates
I'm surprised none of the replies commented on the READBUFF=1000 clause!
When I look at improving Database extraction queries, these are the things I look at:
Can I increase the READBUFF= value?
Typically, increasing this value causes faster data transfer, but it will depend on how much memory you have allocated to your SAS session.
%put %sysfunc(getoption(memsize));
and whether or not you can increase it!?
How many of the actual table columns are really being used in subsequent steps -- if certain columns being dropping later, then I exclude them from extraction query at the first place. Only get the rows and columns that you actually need.
Is any of the table columns defined as BLOB or CLOB, if yes, these columns would have maximum value length of 32767 when moved over to SAS data set! Now you better ask yourself, could that cause data truncation? if, yes, then the original query (Select * from ....) would needs to be rewritten to properly handle such columns and ensure data integrity when moved to SAS.
Does the SAS code perform additional filtering in subsequent steps that could be applied to the query to start with? If yes, then add these filtering conditions to your query to reduce the number of extracted records. Only get the rows and columns that you actually need.
I hope this provides you with preliminary pre-requisites that you can incorporate with the rest of the solutions and suggestions you have already received,
Ahmed
... View more
