Deletes of this type are very slow:
delete from childtbl
where id1 in
(
select id1
from parenttbl
where id2 in
(
select id2
from othertbl
where condition
)
)
Like 1.5 minutes to delete a couple thousand rows.
How can I speed them up?
On 8/20/2021 1:34 PM, DFS wrote:
Deletes of this type are very slow:
delete from childtbl
where id1 in
(
select id1
from parenttbl
where id2 in
(
select id2
from othertbl
where condition
)
)
Like 1.5 minutes to delete a couple thousand rows.
How can I speed them up?
How big are your tables?
Do you have indexes on id1 and id2 in their
appropriate tables?
Deletes of this type are very slow:
delete from childtbl
where id1 in
(
select id1
from parenttbl
where id2 in
(
select id2
from othertbl
where condition
)
)
Like 1.5 minutes to delete a couple thousand rows.
How can I speed them up?
delete from childtbl
where id1 in
(
select id1
from parenttbl
where id2 in
(
select id2
from othertbl
where condition
)
)
How can I speed them up?
On 20/08/2021 19.34, DFS wrote:
Deletes of this type are very slow:
delete from childtbl
where id1 in
(
select id1
from parenttbl
where id2 in
(
select id2
from othertbl
where condition
)
)
Like 1.5 minutes to delete a couple thousand rows.
How can I speed them up?
Why not join the tables
DELETE childtbl
FROM childtbl
INNER JOIN parenttbl ON parenttbl.id1 =
childtbl.id1
INNER JOIN othertbl.id2 ON parenttbl.id2
WHERE othertbl[condition]
even
DELETE FROM childtbl
WHERE id1 IN(
SELECT id1 FROM parenttbl
INNER JOIN othertbl ON othertbl.id2 = parenttbl.id2
WHERE othertbl[condition]
)
could be faster than the original.
Sysop: | Keyop |
---|---|
Location: | Huddersfield, West Yorkshire, UK |
Users: | 293 |
Nodes: | 16 (2 / 14) |
Uptime: | 235:58:57 |
Calls: | 6,624 |
Files: | 12,172 |
Messages: | 5,319,828 |