I'm using SAS/SHARE to establish an ODBC connection to a SAS Server to read and write data. Read queries are fine and work at an acceptable speed. Writing data back however is slow.
At the moment my solution is doing a loop executing a SQL statement of the form "INSERT INTO tbl_X VALUES (1,2, ... , n)" for every record. Depending on the way the application is run it may be necessary to write 250k+ rows.
In the past using other databases I've usually got around this by doing Bulk Inserts where an array of values can be passed in a single statement, rather than executing line by line INSERT VALUES statements. Is this possible with the SAS ODBC driver? An example would be very useful.
Any suggestions / guidance to speed up the insert process will be happily received!
Note: Its not an acceptable solution to save the data to a local text file and then manually import via the SAS client interface!
The main purpose of using SHARE is to handle concurrent update to the same table. Beacuse it is a server, it became also a ODBC server. It does not offer any bulk-load functionality. The performance of standard SQL INSERT INTO table query-expression in SAS are comparable to bulk-load in regular RDBMS.
The most common way to load SAS tables are to use SAS programs. So if you can have your transactions in your ODBC source database, then have SAS to read them via ODBC and insert it into SAS tables. If you don't have the SAS/ACCESS to ODBC product, you can export your values to a flat file, and then have SAS read it.
The Table Server in SAS 9.2 may offer better performance. Another option would be using SPD server, if you contrary to expectation have that licensed.
I'm not using SAS/SHARE to talk between databases. In this case a .NET application is reading data that has been processed in the SAS environment, doing additional modelling, and then inserting new results back into the SAS environment. The application's calculations are complicated, so its not possible / feasible to move all the logic onto SAS.
As a result, its not possible to connect to the application from SAS and "read" the data back into SAS as you suggest in your response. We only have SAS/SHARE at our disposal to connect between the application and SAS. We are running SPDS, however in our setup its not visible from our office network, and we cannot practically connect to it for our purposes.
I would disagree that SQL INSERT INTO has an equivalent performance to a bulk-load in this case, because each ODBC command execution of .ExecuteNonQuery has signficant overhead as it must process one execution for each insert. On an Oracle or SQL Server DB for example a significant performance improvement would be gained using Bulk Insert.
Are you aware of any other workarounds to bulk load via an ODBC connection? I really don't want to have to write data to a text file and then have people manually import the data into SAS.
Sorry if I was unclear. I meant that SAS SQL INSERT INTO has a comparable performance to RDBMS bulk-loads. Since there is no real overhead when adding data to a SAS table (if you don't have loads of indexes...), I would consider this the SAS "bulk-load".
My experience is mostly based on loading data from SAS through ODBC, where it's possible to load data based on query result. I'm no .net programmer, but is that really so that you can't use insert into with a query expression?
If that's not possible, I think the least bad solution is to read data from SAS. It should be no problem if you just could save your result set (leaving the modeling programming outside SAS) in a text file or in any other ODBC source.