Hi there,

for reference you can find the whole issue here: https://community.plone.org/t/zodbconvert-incredibly-slow-solved/13606

While converting a filestorage/blobstorage to a relstorage using the oracle backend it takes a lot if time even if the database is very small.

I found the culprit in this SQL statement:

SELECT
    zoid,
    tid,
    prev_tid,
    current_object_state.state
FROM
    temp_store
        JOIN current_object_state
            USING (zoid)
WHERE (tid <> prev_tid)

It joins with the view current_object_state which in turn joins the two tables current_object and object_state using the columns ZOID and `TID':

CREATE OR REPLACE FORCE EDITIONABLE VIEW "CURRENT_OBJECT_STATE" (
	"ZOID",
	"TID",
	"STATE"
) AS 
	SELECT
		zoid,
		tid,
		state
	FROM object_state
		JOIN current_object
			USING (zoid, tid);

The table object_state already has an unique index over (ZOID, TID) but current_object has not. So I created the index by myself:

CREATE UNIQUE INDEX "CURRENT_OBJECT_UI" ON "CURRENT_OBJECT" ("ZOID", "TID")

After that the SELECT statement I mentioned above only needs 50 ms instead of 35 s to evaluate. And also the zodbconvert script got 36 times faster than before. I was able to convert the data with 1.14 transactions per second:

2021-03-22 15:48:38,689 [relstorage.storage.copy] INFO   Copied transactions: 955/955,100.00%, 63.36 KB/s   1.14 True/s, 51.62 MB

And before it was more like 0.03 transactions per second.

Please add that index to the code base so everyone can feel the difference.

Version:

• RelStorage[oracle] == 3.4
• Plone == 5.2.2
• Python = 3.8.8