Bulk delete doesn't work with DQL???

Bongsub_You

Dear everyone,

Today, I tested the HVS batch with 100,000 objects.

After testing, I tried to delete entire objects with DQL.

But, It doesn't work. The Content Server didn't response on "delete emc_payment_check_batch objects".

Anybody knows the way on bulk delete objects?

-- Bongsub

Find more posts tagged with

Comments

bacham

Not a good idea to delete 100'000 in a single DQL statement. Your typical database will probably not be able to take it. You would need to adjust your rollback segments.

Hicham

Bongsub_You

Wow! Thanks for your quick response.

Is there another way to delete bulk objects with DQL or API?

I agree with your ajusting the rollback segment will be work. however, I don't like the ajust the db side configuration.

Best regards...

-- Bongsub

Chris_Campbell

It's going to take a long time to complete that statement because it's not just deleting 100,000 rows. It's actually removing at least a million rows in the background. That's taking into consideration all the related tables, repeated rows, etc.

You're also not invoking the ALL clause, which means that if those objects are versioned then you are only deleting the CURRENT version of that object. So the system would also have to go and perform updates to all the previous versions and make those objects CURRENT.

Most of the performance is going to depend on your DB settings (like the rollbacks mentioned above), the state of the database, etc. For bulk removals, there isn't much more than you can do than what you're already doing. Other than going to the database directly (which I do not recommend), you're performing a removal at pretty much the lowest level.

One thing you might want to consider is also doing a BEGIN TRAN , then your delete statement followed by COMMIT ... just in case you end up deleting too much.

DCTM_Guru

Can you partition your bulk delete by adding addition criterion? For example, we normally delete by dates. This gives a logical divide between batches from both a process perspective and the amount you try to delete in a single transaction.