BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
mrtball
Obsidian | Level 7

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,

1 ACCEPTED SOLUTION

Accepted Solutions
LinusH
Tourmaline | Level 20
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

8 REPLIES 8
SASKiwi
PROC Star

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
Obsidian | Level 7

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
Obsidian | Level 7

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
Tourmaline | Level 20
Even if the SAD doc isn't clear to me, but perhaps
DELETE_MULT_ROWS=YES is worth a try?
Data never sleeps
mrtball
Obsidian | Level 7

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
PROC Star

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
Tourmaline | Level 20
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
mrtball
Obsidian | Level 7

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

suga badge.PNGThe SAS Users Group for Administrators (SUGA) is open to all SAS administrators and architects who install, update, manage or maintain a SAS deployment. 

Join SUGA 

CLI in SAS Viya

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.

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