Subject | Performance - Querying newly inserted records for processing - which better? |
---|---|
Author | Kenneth Foo |
Post date | 2004-03-10T09:43:42Z |
Which is faster to query for newly inserted records for some task processing?
METHOD 1 - Use a composite key with a field denoting 'N' for newly inserted rows, 'P' for processed rows. Once processed, update this indexed field from 'N' to 'P'.
METHOD 2 - Use another table to keep the IDs of newly inserted rows
Here's my case.
CREATE TABLE BCAST_MESSAGE_RESPONSES (
PROCESSED_FLAG CHAR(1) CHARACTER SET ASCII NOT NULL,
MESSAGE_ID BIGINT NOT NULL,
RESPONSE_TYPE VARCHAR(10) CHARACTER SET ASCII NOT NULL,
RID INTEGER NOT NULL,
RESPONSE_DATA BLOB
SUB_TYPE TEXT
SEGMENT SIZE 80
CHARACTER SET ASCII,
RESPONSE_TIME TIMESTAMP DEFAULT 'NOW' NOT NULL
);
CREATE UNIQUE INDEX IDX_BCAST_MSGRESP ON BCAST_MESSAGE_RESPONSES(PROCESSED_FLAG,MESSAGE_ID,RESPONSE_TYPE,RID);
Newly inserted items have PROCESSED_FLAG='N'.
A singleton task runs in the background, looks for records where PROCESSED_FLAG='N',
processes them (to increment some summary count), and updates the value to 'P'.
To quicken the search for unprocessed items, PROCESSED_FLAG is part of an index.
Thus what is being done is ...
loop {
INSERT INTO BCAST_MESSAGE_RESPONSES (PROCESSED_FLAG,MESSAGE_ID,RID,...) VALUES ('N',?,?,...);
}
Singleton task would...
SELECT * FROM BCAST_MESSAGE_RESPONSES WHERE PROCESSED_FLAG='N';
loop {
UPDATE BCAST_MESSAGE_RESPONSES SET PROCESSED_FLAG='P' WHERE MESSAGE_ID=? AND RID=?;
}
If I'm not mistaken, updating an index would involve a delete and a reinsertion in the index right? (2 ops)
My question is...would it be faster or slower if instead of having a combind index as above, I create another
table keep track of newly inserted items instead? (See below)
CREATE TABLE NEWITEMS (
MESSAGE_ID BIGINT NOT NULL,
RID INTEGER NOT NULL,
PRIMARY KEY(MESSAGE_ID,RID)
);
Thus , my sequence of operations would be
loop {
INSERT INTO BCAST_MESSAGE_RESPONSES (MESSAGE_ID,RID) VALUES (?,?);
INSERT INTO NEWITEMS(MESSAGE_ID,RID) VALUES (?,?);
}
Singleton task would...
SELECT * FROM NEWITEMS;
loop {
SELECT * FROM BCAST_MESSAGE_RESPONSES WHERE MESSAGE_ID=? AND RID=?
//Delete record in NEWITEMS to mark that they have been processed
DELETE FROM NEWITEMS WHERE MESSAGE_ID=? AND RID=?
}
Thanks!
Regards
Kenneth
[Non-text portions of this message have been removed]
METHOD 1 - Use a composite key with a field denoting 'N' for newly inserted rows, 'P' for processed rows. Once processed, update this indexed field from 'N' to 'P'.
METHOD 2 - Use another table to keep the IDs of newly inserted rows
Here's my case.
CREATE TABLE BCAST_MESSAGE_RESPONSES (
PROCESSED_FLAG CHAR(1) CHARACTER SET ASCII NOT NULL,
MESSAGE_ID BIGINT NOT NULL,
RESPONSE_TYPE VARCHAR(10) CHARACTER SET ASCII NOT NULL,
RID INTEGER NOT NULL,
RESPONSE_DATA BLOB
SUB_TYPE TEXT
SEGMENT SIZE 80
CHARACTER SET ASCII,
RESPONSE_TIME TIMESTAMP DEFAULT 'NOW' NOT NULL
);
CREATE UNIQUE INDEX IDX_BCAST_MSGRESP ON BCAST_MESSAGE_RESPONSES(PROCESSED_FLAG,MESSAGE_ID,RESPONSE_TYPE,RID);
Newly inserted items have PROCESSED_FLAG='N'.
A singleton task runs in the background, looks for records where PROCESSED_FLAG='N',
processes them (to increment some summary count), and updates the value to 'P'.
To quicken the search for unprocessed items, PROCESSED_FLAG is part of an index.
Thus what is being done is ...
loop {
INSERT INTO BCAST_MESSAGE_RESPONSES (PROCESSED_FLAG,MESSAGE_ID,RID,...) VALUES ('N',?,?,...);
}
Singleton task would...
SELECT * FROM BCAST_MESSAGE_RESPONSES WHERE PROCESSED_FLAG='N';
loop {
UPDATE BCAST_MESSAGE_RESPONSES SET PROCESSED_FLAG='P' WHERE MESSAGE_ID=? AND RID=?;
}
If I'm not mistaken, updating an index would involve a delete and a reinsertion in the index right? (2 ops)
My question is...would it be faster or slower if instead of having a combind index as above, I create another
table keep track of newly inserted items instead? (See below)
CREATE TABLE NEWITEMS (
MESSAGE_ID BIGINT NOT NULL,
RID INTEGER NOT NULL,
PRIMARY KEY(MESSAGE_ID,RID)
);
Thus , my sequence of operations would be
loop {
INSERT INTO BCAST_MESSAGE_RESPONSES (MESSAGE_ID,RID) VALUES (?,?);
INSERT INTO NEWITEMS(MESSAGE_ID,RID) VALUES (?,?);
}
Singleton task would...
SELECT * FROM NEWITEMS;
loop {
SELECT * FROM BCAST_MESSAGE_RESPONSES WHERE MESSAGE_ID=? AND RID=?
//Delete record in NEWITEMS to mark that they have been processed
DELETE FROM NEWITEMS WHERE MESSAGE_ID=? AND RID=?
}
Thanks!
Regards
Kenneth
[Non-text portions of this message have been removed]