SQL Server OLEDB Library - SAS Passing CursorDelete to SQL Server

[mrtball]

Hello All,

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)

Many Thanks,

[SASKiwi]

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.

[mrtball]

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*/
proc sql;
delete
from Table1.Ad_Hoc_Info
where Field5 in ("L YEAR 1","L YEAR 2","L MISSING");
Quit;

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.

Many Thanks,
Tom

[mrtball]

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.

Many Thanks,
Tom

[LinusH]

Even if the SAD doc isn't clear to me, but perhaps
DELETE_MULT_ROWS=YES is worth a try?

[mrtball]

Hello Linus,

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?

Thanks,
Tom

[SASKiwi]

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.

[LinusH · Accepted solution]

If your can't improve SAS SQL via libname performance, pass through should definitely work with same performance that you experience in SQL Server. Essantially that is the equivalent to executing it on SQL Server console, just another "user interface".

[mrtball]

Hello Both,

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.

Many Thanks,

Tom