07-04-2016 11:48 AM - edited 07-04-2016 12:10 PM
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)
07-05-2016 02:30 PM
07-04-2016 03:41 PM
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.
07-05-2016 05:16 AM
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.
07-05-2016 05:52 AM
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.
07-05-2016 08:10 AM - edited 07-05-2016 08:12 AM
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?
07-05-2016 03:52 PM
I'd recommend trying PASSTHRU as @LinusH has already mentioned. This will get you closer to what SQL Server Studio does. I'd also try using UPDATEBUFF= on the LIBNAME statement. Try setting it to say 1000.
07-05-2016 02:30 PM
07-06-2016 05:20 AM
It looks like UPDATEBUFF= is limited to Oracle data sources. So the SQL Server passthrough is the most performant way forward for us.
Thank you both for your repsonses.
Need further help from the community? Please ask a new question.