Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 Pending status.

My processing loop would be:

  1. Retrieve 1st Ticket where Pending
  2. Do work with Ticket.
  3. Update Ticket Status => Complete
  4. Repeat.

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'.

share|improve this question
    
What do you mean with "first" in "Retrieve 1st Ticket where 'Pending'"? –  ypercube Sep 24 '12 at 23:55
    
If "first" mean smallest ticket_id, you probbaly need an index on (status, ticket_id) –  ypercube Sep 24 '12 at 23:56
    
And how sure are you that the performance degradation is caused by this procedure and not by other queries/statements? –  ypercube Sep 24 '12 at 23:58
    
@ypercube - No, I'm not certain that's where the performance degradation is. That's why my question was "do I need to be concerned with this, or is it normal behavior of an index?". It's something I noticed while monitoring the database, and I considered it unexpected. I would not expect it to continue to scan the preceding rows when I provide a where clause against an indexed column. FWIW, modifying the index to include ticket_id actually performed worse than just having Status indexed. –  g.d.d.c Sep 25 '12 at 0:12
    
Is id (the data type) a domain you defined? –  a_horse_with_no_name Jun 26 '13 at 21:25

2 Answers 2

you can try Filter index for same or a normal non - clustered index on status field.

As you said that your first query is always find Pending activities you can try for the Filter index.

CREATE NONCLUSTERED INDEX Index_name ON Database.Table_name(ticket_id) WHERE status = 'pending'

Points to remember when creating Filtered Index:

  • They can be created only as Non clustered Index
  • They can be used on Views only if they are persisted views.
  • They cannot be created on full-text Indexes.

I hope it will help you.

Thank you, Sam

share|improve this answer
    
I don't think Firebird has filtered indexes. –  ypercube Sep 17 '13 at 9:56
    
And indexes cannot be declared as CLUSTERED or NONCLUSTERED either. –  ypercube Sep 17 '13 at 10:02
    
My apologies. i just forgot that you are not using MS SQL server. But if there is any thing similar to Filter index it may be helps you. –  Sagar Makhesana Sep 17 '13 at 10:55

Make sure "status" is the first column, then use Ticket_ID as an INCLUDE. Typically I would say it is pointless to put an INCLUDE on the primary key, but you may as well try it.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.