I'm optimizing a Firebird 2.5 database of work tickets. They're stored in a table declared as such:
CREATE TABLE TICKETS ( TICKET_ID id PRIMARY KEY, JOB_ID id, ACTION_ID id, STATUS str256 DEFAULT 'Pending' );
I generally want to find the first ticket that hasn't been processed and is in
My processing loop would be:
- Retrieve 1st Ticket where
- Do work with Ticket.
- Update Ticket Status =>
Nothing too fancy. If I'm watching the database while this loop runs I see the number of indexed reads climbs for each iteration. The performance doesn't seem to degrade terribly that I can tell, but the machine I'm testing on is pretty quick. However, I've received reports of performance degradation over time from some of my users.
I've got an index on
Status, but it still seems like it scans down the
Ticket_Id column each iteration. It seems like I'm overlooking something, but I'm not sure what. Is the climbing number of indexed reads for something like this expected, or is the index misbehaving in some way?
-- Edits for comments --
In Firebird you limit row retrieval like:
Select First 1 Job_ID, Ticket_Id From Tickets Where Status = 'Pending'
So when I say "first", I'm just asking it for a limited record set where
Status = 'Pending'.
ticket_id, you probbaly need an index on
ticket_idactually performed worse than just having Status indexed.
id(the data type) a domain you defined?