Daniel Noll wrote:
I created bogus test data where jobid was always 0 and state was cycled between 0,1,2. Query: SELECT COUNT(*) FROM jobitems WHERE jobid = 0 AND state = 0
Given this particular query pattern I would say that increased times for larger tables makes sense. Since jobid is always 0 and state has one of three values, if you increase the number of rows from 100 to 1 million it stands to reason that there are going to be a lot more rows matching the WHERE criteria, and so it will take longer to count those rows.
For the record, when I changed the data and the WHERE clause so that COUNT(*) only returns a single row, the times I saw were constant regardless of how many rows were in the table--which is what I would expect since we're using the index.
If I log the query plan it confirms what's happening. It estimates the number of rows which will come back and then says it's iterating through each row to determine the count.
Okay, I think that matches what I mentioned above...
Problem is that two users adding or removing items at the same time would be updating the same table and I'm not sure how clever Derby is if two users do an UPDATE at the same time which adds or subtracts from the existing value.
Unless I'm missing the heart of your question, I think this is a fairly normal transactional task for Derby: two users trying to update the same row at the same time. Derby should use transaction locking to ensure that both users' updates are correctly reflected in the table.
If it merged them together as expected then I'm guessing would be a viable solution.
I agree, it seems like that should work (assuming "merge" here means "correctly apply both users' changes to the table"). If you try it out and run into problems, though, you can always post back with a follow-up mail...
Army
