topic Re: Writing local SAS tables from 64-bit C# process without explicit SAS references in Administration and Deployment

Writing local SAS tables from 64-bit C# process without explicit SAS references

keV — Fri, 04 Apr 2014 08:02:51 GMT

Hopefully this is in the right place as it's my first question.

We are developing an application where part of the data handling requirements is to read and write local SAS files (Server comes later).

We prefer not to have any specific SAS assembly references in our code.

Following the cookbook recipe, we can write SAS files using the IOM Provider in _Local_ mode.

However, the examples still use ADODB, which is only possible in 32-bit, as no 64-bit version will ever exist of ADODB.dll

I have tried rewriting in ADO .NET using a DataAdapter but this is doomed to failure because the IOM provider does not support parameterised queries.

My only other option appears to be to repeatedly construct and execute single row SQL insert queries.

This works, but is, not surprisingly, very slow.

Can anyone suggest a better method to achieve this?

Thanks, Kevin

Re: Writing local SAS tables from 64-bit C# process without explicit SAS references

AndreasMenrath — Sun, 06 Apr 2014 22:08:16 GMT

There is no easy way to write to SAS datasets.

You might want to try the SAS ODBC drivers, but ODBC brings a lot of pain, too.
Then there is an undocumented and strange API for the COM interface SAS.IDataSet. I would not recommend anyone to use that.
Another option would be to export your data to a CSV or XML file, transport it to your SAS server and import it with SAS code.
So OleDB is still the best way to write a SAS dataset.

I guess when you talk about "single row SQL insert queries" your programm inserts one row and waits for the OleDB provider to finish until the next row is sent. So you might get a performance boost if you insert multiple rows with a single SQL statement. You can insert multiple rows with a SET or VALUES clause (see http://support.sas.com/documentation/cdl/en/proc/61895/HTML/default/viewer.htm#a002294531.htm for example).

If you find a better way for writing a SAS dataset with OleDB please post your solution here. I'm looking for it since years. It's about time that someone writes an awesome ADO.NET Entity Framework provider for SAS tables :smileylaugh:

Re: Writing local SAS tables from 64-bit C# process without explicit SAS references

keV — Wed, 09 Apr 2014 07:37:32 GMT

Andreas, many thanks.

Using multiple VALUES sets in the insert query does give a very significant performance boost - it reduces my test write time from 8s to 2s, writing 5 rows instead of 1 row in each insert.

The next question is, does anyone know the character size limit of the query text? I could then dynamically adjust the number of rows I write for each insert to be the maximum within this limit.

Re: Writing local SAS tables from 64-bit C# process without explicit SAS references

AndreasMenrath — Mon, 21 Apr 2014 19:03:20 GMT

it reduces my test write time from 8s to 2s, writing 5 rows instead of 1 row in each insert.

That is still very slow. I guess that most of that time is used to open the dataset and initialize the driver. Can you try 1000 rows and measure the performance?

The next question is, does anyone know the character size limit of the query text?

There is no documented limit. Maybe 32K (32768) characters.

As a best practice you should try different lengths. If there is a limit it may be different in each SAS version and x86 and x64.

Re: Writing local SAS tables from 64-bit C# process without explicit SAS references

jakarman — Tue, 22 Apr 2014 07:21:08 GMT

SAS(R) 9.3 Providers for OLE DB: Cookbook (Tips for 64-bit Programming)

Re: Writing local SAS tables from 64-bit C# process without explicit SAS references

keV — Mon, 28 Apr 2014 09:15:06 GMT

I didn't say how many rows I wrote in total, only how many I did in each operation! That was for a 1000 rows.

I've now adjusted my code to dynamically adjust the number of rows per operation, based upon the query length as it is built up.

Thanks for your input.

Re: Writing local SAS tables from 64-bit C# process without explicit SAS references

keV — Mon, 28 Apr 2014 09:16:13 GMT

Sorry Jaap. I don't see how that link helps answer my question.

Re: Writing local SAS tables from 64-bit C# process without explicit SAS references

jakarman — Mon, 28 Apr 2014 10:18:36 GMT

Sorry keV as you mentioned several issues

I was focussed on you 32-bit 64-bit issue. That is more common and the approach is to have all components on the same bitness. That is what my link was for.

Rereading your question, I have more questions as answers. You are building something in .Net as Eguide AMO are also .Net applications you are building some functionality of that in the same way. The behavior on IOM and COM will be similar. I know those tools better. Builing in ADO .Net never needed to do. I know around Andreas it is/was done.

The "com" option is used for a local SAS dataset. It will start a SAS process for that, you should be able to see that running.

In your .Net coding you will see CreateWorkspaceByServer RemoveWorkspace that must be the one doing that.

As starting a sas-process will take a lot of time (many seconds) starting and stopping this will cost a lot of time.

When the sas-process or the datasets behind is badly tuned you will have a bad performance.

As far I can see you can submit SAS language code using Ado.

(SAS(R) 9.3 Integration Technologies: Windows Client Developer's Guide Using Local SAS with ADO - sample )

When you go for an IOM server. Also a SASs process need to run. I am recognizing the pooling and normal workspace servers as started by a object spawner (service).

The normal workspace server is normally run (at OS level Unix) by a personal key and needing that password/login being propagated. The start time of that process needed to add for a response. The pooledworkspace server will run by a shared account (like the STP) and by that sharing you will not see the start process overhead.