Am 19.04.2006 um 14:08 schrieb Igor Tandetnik:
Kai Wu <[EMAIL PROTECTED]> wrote:
2. The table schema is simple
TABLE MID(id VARCHAR(8),scts VARCHAR(12),daddr VARCHAR(20))
the scenario is it needs first select the "id" by
select id from MID where scts= .. AND daddr=...
after the id field is retrieved, this entry can be deleted by
delete from MID where id=..
or delete from MID where scts=.. AND daddr=..
Apparently "delete" does some repeated work, ie, to locate the
matching entry from the table
(it might be helpful to set id as PRIMARY KEY, but since
insert/delete dominates, its overhead might be higher)
Try
select rowid from MID where scts= .. AND daddr=...
delete from MID where rowid=...
ROWID is a column representing internal B-Tree key. Every table has
it, and every table is "indexed" by it, by nature of the internal
data representation.
Well, no matter whether indexed by ROWID or not, skipping the SELECT
certainly avoids the overhead of returning the ROWID to the caller:
DELETE FROM MID WHERE scts=.. AND daddr=..
However, from the OP, I take it that extracting the ID column is a
requirement before deleting the record. In this case, there's no way
around the SELECT query, but fetching both rowID and ID column in one
query may speed up things:
SELECT rowID, ID FROM MID WHERE scts= .. AND daddr=..
DELETE FROM MID WHERE rowID=..
You're still running each delete query separately, though. If you're
looping over records, maybe you can gain a lot of speed by using
batch delete queries (i.e. delete all records that match certain
criteria in one shot)?
Also, if you search by scts and daddr columns - do you have an index
on one or both of them?
HTH,
</jum>
