Sorry for the delay in my response to this thread. There are a lot of angles to this that I wanted to make sure we address. First, the original issue reported here. To summarize, Andrew Howell's comment concerns the "etls_recordCheck" macro - he notes that DI Studio always seems to insert this macro into the code, which passes a :select count(*) statement to SQL Server to count the rows in a table. This statement takes a very long time to return. From what I can tell, Andrew would ultimately like a way to control whether the "etls_recordCheck" macro" is inserted into the code. This can be done by turning off the option that enables it, which is "Collect Table Statistics". This option is enabled by default but can be disabled for a given job in DI Studio by right-clicking within the job diagram and unchecking the option "Collect Table Statistics". This can also be turned off for any job in the preferences for DI Studio by going to the "Tools" menu, selecting "Options", clicking on the "Job Editor" tab, and in the middle of the dialog box, unchecking the option "Collect Table Statistics". A secondary questions concerns how the code that relates to this option is generated. The code for this must be capable of running against multiple data sources, so it needs to be generic for any data source. We could enhance the option to first query to determine the data source, and then pass a more data source-specific query to collect these table statistics, but understand that we would be making one or more roundtrips to the database to run a macro that can easily be disabled when it is not desirable and those roundtrips impact performance as well. Also, take into account that if we do this for SQL Server, Oracle, etc. customers will expect us to make and maintain these database-specific changes for this setting for each and every data source they could be accessing, and keep them current for database changes into the future. Because of this, we think it is preferable for those customers who want to have more fine-grained controls over this table statistics code to make code changes in it themselves to suit the unique needs of their own environments. Finally, LinusH's issue concerns the use of the ACCESS engine for OLEDB, which, as he correctly notes, is caused by is the use of OpenRowset () against the views. The reason why this is not a bug is that OpenRowset () will perform this way when you run this query natively - in other words, the query runs as fast it would outside of SAS code. One way to make this query faster, however, would be to set the property DBPROP_MAXROWS and specify an upper limit of the number of rows returned to something manageable, like 100, 1000, etc. We do not expose a way in the ACCESS engine to set this property, thus the "enhancement" nature of this, an enhancement that we have thus far elected not to implement. I hope that provides some clarification. Please post any additional questions about these issues. Mike F.
... View more
