FirebirdSQL/問答篇/Performance with joins and order by
出自VFP Wiki
小 |
小 (revert) |
||
| 第1行： | 第1行： | ||
| - | + | === 如何改善 joins and order by 的執行效率 === | |
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | ||
| - | + | *問題描述 | |
| - | + | <code> | |
| - | + | 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 | ||
| + | </code> | ||
| + | |||
| + | *解決方式 | ||
| + | <code> | ||
| + | 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 | ||
| + | </code> | ||
在2005年4月3日 (日) 08:02所做的修訂版本
如何改善 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
