Selectively deleting large amounts data from Sybase table

Question

Using Sybase ASE 15 for this - I have about a large amount of rows (up to 10 mil) to delete from a table on a regular basis, but I want to keep a selection of the latest added data to the table, so that rules out using truncate directly on the table.

delete from master_table where...

Using the above delete is very slow, so my strategy is to move the data I want to keep into a temp table, truncate the master table and move the data back in again from the temp table i.e.

1) select * into #temp_table from master_table where date_updated > dateadd(mi, -15, getdate()) and node_type != 'X'
2) truncate table master_table
3) insert into master_table select * from #temp_table

This is almost good enough - 1 & 2 have great performance, but the insert back into the master is too slow.

So my question really boils down to whether there a quick way of doing either of:

delete from master_table where...
insert into xyz select * from...

Or I'm open to alternative approaches!

Answer 1

Probably your best solution is to use partitioning.

I do not know the details of partitioning on Sybase, however, if you can create time-based partitions, you can potentially drop them by altering partitions.

You will however, need something which creates future partitions and drops old ones - this is a piece of software which you have to maintain (it may be a stored procedure or script which runs on the database server or elsewhere on a "cron" job etc).

You'll also need to ensure that those with node_type='X' are deleted correctly.

Maybe you can create two sets of daily partitions, one for node_type='X' and another for other node_types, and create new partitions each day (for tomorrow, and maybe the day after) and drop the old ones you don't need, or merge them if you do need the data.

Answer 2

Depending on your circumstances, fast bcp could work for making the insert run quickly. That will change your overall design to need a shell script (or batch file), but it could work (if your table is designed to allow fast BCP)

The other thing to look at is why the insert is slow. Is it disk issues? Too many indexes needing to be updated? It might be the case that certain tweaking of your database structure could speed it up.