Firebird 2.1 + EXISTS = query bug?

Question

Using Delphi 2009 + Firebird 2.1.3.

Database is ODS 11.1, default char set is UTF8.

My prepared query is as follows:

SELECT 
  a.po_id, a.po_no 
FROM 
  purchase_order a
WHERE EXISTS 
  (SELECT 1 
   FROM 
     sales_order_item z1
   JOIN 
     purchase_order_item z2 
   ON 
     z2.so_item_id = z1.so_item_id
   AND 
     z2.po_id = a.po_id
   WHERE z1.so_id = :soid)
ORDER BY a.po_no

Now when I loop this say 1000 times because I have 1000 x so_id, the CPU usage get at 100% for FBSERVER.EXE

Anyone encountered this problem?

Answer 1

Try this instead:

  SELECT po.po_id, 
         po.po_no 
    FROM PURCHASE_ORDER po
    JOIN PURCHASE_ORDER_ITEM poi ON poi.po_id = po.po_id
    JOIN SALES_ORDER_ITEM soi ON soi.so_item_id = poi.so_item_id
                             AND soi.so_id = :soid
ORDER BY po.po_no

Answer 2

Do a gstat -h from your databes and see the difference between Oldest Transaction and Next Transaction.

The difference between this two numbers is haw many transacrtions are open.

• If you see to many, your problem could be than you are not commiting them.

• It could also be than you open one transaction and it interferes with the other ones.

Finally, could you do this SELECT in a read-only transaction.