Following a user query linked to the user trying to run a delete on a SQL server source table/dataset and performing DBA analysis into how SAS is interacting with SQL Server - it appears that SAS is running the less performant CursorDelete against the table rather than a Delete and appears to be deleting data on a row by row basis.
I'm just wondering if anybody knows how OLEDB delete performance can be improved when running the delete from SAS? (I've been looking at lib settings but haven't yet found anything that has dramatically improved performance)
Please post the query doing the delete. Also how long is the delete step running and how many rows are being deleted? Most databases contain a row by row delete function with roll back and recovery and also a table truncation for deleting all rows. I'm assuming here you are just deleting some rows.
Hello SAS Kiwi,
Thanks for your reply, please see code below...
/*APPEND TO AD_HOC DATA TO EXCLUDE FROM CI JOBS*/
LIBNAME Table1 OLEDB BULKLOAD=YES PROPERTIES=('Persist Security Info'=True 'Integrated Security'=SSPI 'Initial Catalog'=DBMart)
OLEDB_SERVICES=NO PROMPT=NO DATASOURCE='DB1' PROVIDER=SQLOLEDB.1 SCHEMA=dbo ;
/*REMOVE DATA FROM THE AD-HOC FILE TO CORRECT MISTAKES*/
where Field5 in ("L YEAR 1","L YEAR 2","L MISSING");
Yes you are right the user is looking at just deleting rows, We deleted approx 3million records for this user via SQL Server Management Studio in the end, this took a few minutes to complete.
Through my testing I've not yet run the SAS delete to completion however through testing i've identified about 1,200,000 records for deletion and via monitoring this is deleting roughly 20,000 every 60 seconds so would take 1 hour to complete.
Also i've just had a quick look at how DI studio would handle a delete within a table loader transformation, and this uses a SQL pass through.
I've amended my test code to a SQL server pass through and this deleted the 1.2 million records in 35 seconds.
I tried DELETE_MULT_ROWS=YES before and have just re-tried with it and it didn't offer any substational improvements. I think I maybe thought similarily to you that this LIB settings would offer improvements.
I wonder if it's another LIB setting is cancelling this out or maybe a system option is specified which is meaning that this isn't working?
Would you expect the Proc SQL delete to work as well as a SQL pass through?
The SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment.
Learn how to install the SAS Viya CLI and a few commands you may find useful in this video by SAS’ Darrell Barton.
Find more tutorials on the SAS Users YouTube channel.