topic Re: PROC SQL DELETE on seletive columns taking forever. in SAS Programming

PROC SQL DELETE on seletive columns taking forever.

Dob4Die — Thu, 05 Feb 2015 22:14:37 GMT

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.

Re: PROC SQL DELETE on seletive columns taking forever.

DBailey — Thu, 05 Feb 2015 22:36:28 GMT

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;

Re: PROC SQL DELETE on seletive columns taking forever.

Dob4Die — Thu, 05 Feb 2015 23:48:21 GMT

Thanks DBiley for quick response.

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

Re: PROC SQL DELETE on seletive columns taking forever.

SASKiwi — Fri, 06 Feb 2015 02:42:41 GMT

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

Re: PROC SQL DELETE on seletive columns taking forever.

Dob4Die — Fri, 06 Feb 2015 05:05:56 GMT

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;

Re: PROC SQL DELETE on seletive columns taking forever.

DBailey — Fri, 06 Feb 2015 14:00:38 GMT

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.

Re: PROC SQL DELETE on seletive columns taking forever.

LinusH — Fri, 06 Feb 2015 15:03:45 GMT

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.

Re: PROC SQL DELETE on seletive columns taking forever.

RW9 — Fri, 06 Feb 2015 15:06:58 GMT

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?

Re: PROC SQL DELETE on seletive columns taking forever.

DBailey — Tue, 10 Feb 2015 14:57:04 GMT

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.

Re: PROC SQL DELETE on seletive columns taking forever.

LinusH — Tue, 10 Feb 2015 16:25:14 GMT

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.