CODEWITHSUNDEEP
sql
ediblecode
ediblecode

Reputation: 11971

SQL restricting by foreign key.

I have a royalties frame and a batches frame. Batches consist of a bunch of royalties batched together.

Basically when in the batch frame, it updates the unallocated royalties after an allocation has been done. However, it tries to match by ROYALTY_ID, which takes forever because there are millions of them. I want to restrict the function by BATCH_ID so it'll massively reduce the search time, but I'm not quite sure how.

Thanks Dan

Current query:

UPDATE ROYALTIES 
SET ALLOCATION_STATUS_ID = pRoyaltyStatusId 
WHERE ROYALTIES.RIGHT_TYPE_ID = pRightTypeId 
AND ROYALTIES.WORK_ID = pWorkId 
AND ROYALTIES.TERRITORY_ID = pTerritoryId 
AND ROYALTIES.ROYALTY_PERIOD_END_DATE = UTILS.FIX_DATE(pEndDate);

Upvotes: 0

Views: 101

Answers (1)

heximal
heximal

Reputation: 10517

No, foreign key constraint does not connect with performance issue. You should look at the INDEX technique. The index for the field BATCH_ID (details table) will improve select query performance.

Upvotes: 1

Related Questions