PROC SQL DELETE on seletive columns taking forever.

[Dob4Die]

Hi ,

I am working on simple PROC SQL DELETE step with WHERE clause on one of the columns . Looks like its taking forever to run

Example code:

libname sqldsn sqlsvr dsn=SQLserverDSN user=username password='password' INSERT_SQL=YES INSERTBUFF=1000 schema=dbo;

PROC SQL;

  DELETE FROM SQLDSN.table_name 

    WHERE column1 >= value (this datetime value)

  ;

QUIT;

without WHERE clause its running in fraction of second and same WHERE clause working quick in SELECT * query but DELETE from table taking forever . Looking for thoughts what causing PROC SQL DELETE taking forever.

Tried direct_exe=DELETE in LIBNAME statement but not improvement.

Thanks for your help .

SAS version . 9.4 on SAS GRID.

[DBailey]

Since you're using an external dbms, you might try a pass through query.

proc sql;

connect using sqldsn as csql;

execute (

     delete from dbname.schemaowner.table_name

     where column1 >= value

) by csql;

disconnect from csql;

quit;

[Dob4Die]

Thanks DBiley for quick response.

Can I SAS macro variable as Value in WHERE clause in pass thru? Thanks .

[SASKiwi]

Yes you can. Make sure you use double quotes so the value resolves: where column1 = "&MyDatetime"

[Dob4Die]

I am doing something wrong here ...I am keep getting below error:

ERROR: CLI execute error: [SAS/ACCESS to SQL Server][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax

       near 'dt'.

%let value1 = "06FEB2015:00:00:00.000"dt;

PROC SQL ;

  CONNECT TO SQLSVR AS sqldsn(datasrc=SQLDBDSN user=USERNAME password='PASSWORD');

  EXECUTE(DELETE FROM DATBASE.DBO.table_name

         WHERE column1>= "&value1") by sqldsn;

  DISCONNECT FROM SQLDSN;

  QUIT;

[DBailey]

SqlServer expects dates in a different format.

%let value1='2015-02-06';

You can't use double quotes.  If this is in a macro, you'd need to use %bquote or some other function to force the evaluation of the macro variable.

[LinusH]

Doing explicit pas thru is making it DBMS dependant and unnecessary complicated.

SAS/ACCESS is capable of transforming date and date-time constants on the fly.

[DBailey]

Don't disagree...but the original request was to improve performance on the approach you mentioned.  This is the most common way of doing that.

[LinusH]

OP was in pseudo-code, so it's hard to see what was causing the initial slow performance.

Adding OPTIONS SASTRACE=',,,d' SASTRACELOC=SASLOG; might give some clues on what gets pushed down and how.

Also, see the DIRECT_EXE libname option.

[RW9]

Just out of interest, why are you performing admin tasks on a database from SAS?  Would you not be better served addressing DB admin tasks via the DB itself?