12

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

6
  • What do you mean with "first" in "Retrieve 1st Ticket where 'Pending'"? Sep 24, 2012 at 23:55
  • If "first" mean smallest ticket_id, you probbaly need an index on (status, ticket_id) Sep 24, 2012 at 23:56
  • And how sure are you that the performance degradation is caused by this procedure and not by other queries/statements? Sep 24, 2012 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, 2012 at 0:12
  • Is id (the data type) a domain you defined?
    – user1822
    Jun 26, 2013 at 21:25

2 Answers 2

1

The degradation over time occurs due to the increased number of items that are in the "Complete" status. Think about this for a second - you won't get any performance degradation when testing as you probably have a small number of rows with the status as "Complete". But in production, they may have millions of rows with the "Complete" status and this number will increase over time. This, essentially, makes your index on Status less and less useful over time. As such, the database probably just decides that because Status almost always has the value 'Complete', it will just scan the table instead of using the index.

In SQL Server (and maybe other RDBMS's?), this can be worked around using Filtered Indexes. In SQL Server you would add a WHERE condition onto the end of your index definition to say "apply this index only to records with a Status <> 'Complete' ". Then any query using this predicate will most likely use the index on the small amount of records not set to 'Complete'. However, based on the documentation here: http://www.firebirdsql.org/refdocs/langrefupd25-ddl-index.html, it does not look like Firebird supports filtered indexes.

A workaround is to put 'Complete' records in an ArchiveTickets table. Create a table with the exact same definition (though without any auto generated ID) as your Tickets table and maintain rows between them by pushing 'Complete' records to the ArchiveTickets table. The Index on your Tickets table will then be over a much smaller number of records and be much higher performance. This will likely mean you will need to change any reports etc that reference 'Complete' tickets to point to the Archive table or perform a UNION across both Tickets and ArchiveTickets. This will have the advantage of not only being fast, but will also mean that you can create specific indexes for the ArchiveTickets table to make it perform better for other queries (for instance: Give me the average time to completion for complete tickets) which are not needed on the Tickets table.

You should be concerned with this if your production is going to go into the thousands of rows. Performance will degrade over time and negatively impact your user experience.

0

Whether or not performance is affected will be a function of data volume and machine capacity. Given the capacity of modern hardware, it's hard to imagine ticket sales volume that couldn't be handled by the design you describe. However, there are changes I would recommend for correctness, and might improve performance as a secondary benefit.

Your get first pending query is non-deterministic. First according to what order? An SQL table has no intrinsic order; the First 1 hack is just giving you some arbitrary first one. To make it deterministic, why not process pending jobs in Job_ID order?

If you have two indexes {Job_ID} and {Status,Job_ID}, this query will return one row predictably and efficiently:

Select Job_ID, Ticket_Id
From   Tickets
Where Job_ID = ( 
  select min(Job_ID) from Tickets 
  where Status = 'Pending'
);

I'm not a Firebird user, so you'll have to check the query plan, but it should be efficient because the subquery references only the second index, produces a value for the first one. (There may be other efficiency tricks available to you. You might be able to organize the physical table as a B+ tree, or have access to a hidden row_id, for example.)

The other change I would make for correctness is to make Status a single, constrained byte, and let the application supply the "Pending" string. That will guard against erroneous Status values, and probably make the index smaller in the bargain. Something like:

CREATE TABLE TICKETS (
  TICKET_ID id PRIMARY KEY,
  JOB_ID id,
  ACTION_ID id,
  STATUS char(1) not NULL 
     DEFAULT 'P'
     CHECK( STATUS in ('P', 'C', 'X') ) -- whatever the domain is
);

Of course, you can use a view (or maybe a derived column) to supply the canonical strings for Status.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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