CODEWITHSUNDEEP
sqldelphifirebirdfirebird2.1
Atlas
Atlas

Reputation: 1363

Firebird 2.1 + EXISTS = query bug?

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?

Upvotes: 1

Views: 1061

Answers (2)

Duilio Juan Isola
Duilio Juan Isola

Reputation: 237

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.

Upvotes: 0

OMG Ponies
OMG Ponies

Reputation: 332661

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

Upvotes: 2

Related Questions