Architecting, installing and maintaining your SAS environment

SQL Server OLEDB Library - SAS Passing CursorDelete to SQL Server

Accepted Solution Solved
Reply
Contributor
Posts: 41
Accepted Solution

SQL Server OLEDB Library - SAS Passing CursorDelete to SQL Server

[ Edited ]

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,


Accepted Solutions
Solution
‎07-06-2016 05:20 AM
Super User
Posts: 5,432

Re: SQL Server OLEDB Library - SAS Passing CursorDelete to SQL Server

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".
Data never sleeps

View solution in original post


All Replies
Super User
Posts: 3,256

Re: SQL Server OLEDB Library - SAS Passing CursorDelete to SQL Server

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.

Contributor
Posts: 41

Re: SQL Server OLEDB Library - SAS Passing CursorDelete to SQL Server

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

Contributor
Posts: 41

Re: SQL Server OLEDB Library - SAS Passing CursorDelete to SQL Server

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

Super User
Posts: 5,432

Re: SQL Server OLEDB Library - SAS Passing CursorDelete to SQL Server

Even if the SAD doc isn't clear to me, but perhaps
DELETE_MULT_ROWS=YES is worth a try?
Data never sleeps
Contributor
Posts: 41

Re: SQL Server OLEDB Library - SAS Passing CursorDelete to SQL Server

[ Edited ]

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

Super User
Posts: 3,256

Re: SQL Server OLEDB Library - SAS Passing CursorDelete to SQL Server

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.

Solution
‎07-06-2016 05:20 AM
Super User
Posts: 5,432

Re: SQL Server OLEDB Library - SAS Passing CursorDelete to SQL Server

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".
Data never sleeps
Contributor
Posts: 41

Re: SQL Server OLEDB Library - SAS Passing CursorDelete to SQL Server

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 571 views
  • 2 likes
  • 3 in conversation