Proc SQL Delete takes WAY too long

Question

I'm running the following SAS command:

Proc SQL;
Delete From Server003.CustomerList;
Quit;

Which is taking over 8 minutes... when it takes only a few seconds to read that file. What could be cause a delete to take so long and what can I do to make it go faster?

(I do not have access to drop the table, so I can only delete all rows)

Thanks,

Dan

Edit: I also apparently cannot Truncate tables.

Answer 1

I would also mention that in general SQL commands run slower in SAS PROC SQL. Recently I did a project and moved the TRUNCATE TABLE statements into a Stored Procedure to avoid the penalty of having them inside SAS and being handled by their SQL Optimizer and surrounding execution shell. In the end this increased the performance of the TRUNCATE TABLE substantially.

Answer 2

Are there a lot of other tables which have foreign keys to this table? If those tables don't have indexes on the foreign key column(s) then it could take awhile for SQL to determine whether or not it's safe to delete the rows, even if none of the other tables actually has a value in the foreign key column(s).

Answer 3

It might be slower because disk writes are typically slower than reads.

As for a way around it without dropping/truncating, good question! :)

Answer 4

This is NOT regular SQL. SAS' Proc SQL does not support the Truncate statement. Ideally, you want to figure out what's going on with the performance of the delete from; but if what you really need is truncate functionality, you could always just use pure SAS and not mess with SQL at all.

data Server003.CustomerList;
set Server003.CustomerList (obs=0);
run;

This effectively performs and operates like a Truncate would. It maintains the dataset/table structure but fails to populate it with data (due to the OBS= option).