I'm not sure I want to spell this out too much here, it is only vicariously relevant since I am running the code from Foundation SAS, not from E.G.
The intention is to migrate the code to E.G. when it is running more effectively, but we aren't there yet.
I'm reading SQL Server directly, using a SQL Server function to subset little table to littler table, and then joining on a matching key to very big table. Since it is written as passthrough, I don't believe I am seeing data drawn across the network and then matched. The original SAS code offered did that, and to its shame, it took much more than an hour to run.
The original SAS code replaced a tangled process where MS Access was an intermediate step and put the query to SQL Server. That ran in under 2 minutes, but I can't get anywhere near that performance, even after providing explicit index helper statements in the Passthrough code. (Why I should have to tell SQL Server about its own indexes I cannot fathom. However, it did improve the outcome to a small extent.)
I am using all the right syntax with 'Connection to ...", and not seeing the network traffic or process activity I would expect if it were running in SAS instead.
Kind regards
David
... View more