FirebirdSQL/問答篇/Performance with joins and order by

出自VFP Wiki

如何改善 joins and order by 的執行效率

• 問題描述

Hi

I'm looking for a solution, to get better performance with the following query

select first 25 RI.PLACE, SR.SWIMTIME, A.LASTNAME from RANKINGITEM RI join SWIMRESULT SR on SR.SWIMRESULTID = RI.SWIMRESULTID join ATHLETE A on A.ATHLETEID = SR.ATHLETEID where RI.RANKINGID = 589044 order by RI.PLACE

when I run this query, I have the following plan: PLAN SORT (JOIN (RI INDEX (IX_RANKINGITEM_WORLD),SR INDEX (PK_SWIMRESULT),A INDEX (PK_ATHLETE)))

when I look ath the statistics I have 33'000 indexed accesses to all three tables.

When I remove the "order by" part, the query is much faster and I have 25 indexed accesses to the three tables only.

The plan is: PLAN JOIN (RI INDEX (IX_RANKINGITEM_WORLD),SR INDEX (PK_SWIMRESULT),A INDEX (PK_ATHLETE))

What I don't understand is, why Firebird server reads all 33'000 records in all tables, even if the "order by" depends on RANKINGITEM only and there is an index for that on RANKINGITEM.

Anything I can do here with a query plan or something else ?

cu Christian

• 解決方式

The Query with the ORDER BY (SORT in PLAN) first fetch all results internally and perform a sort on the results. After that only 25 records are returned to the client. The Query without ORDER BY doesn't need to perform a sort, so it just returns the first 25 valid records fetched from disk.

If you really need a fast behaviour for this you can create a single index on RI.PLACE, but probably better is a compound index on RANKINGID, PLACE and but it in the same order in the ORDER BY clause.

Regards, Arno Brinkman ABVisie 数据恢复 硬盘数据恢复 磁带数据恢复 raid数据恢复 磁盘阵列数据恢复 数据恢复 数据恢复 数据恢复 数据修复 硬盘数据修复 磁带数据修复 raid数据修复 数据修复 数据修复 数据修复 磁盘阵列数据修复