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

出自VFP Wiki

（修訂版本間差異）
跳轉到: 導航, 搜尋
小
第1行： 第1行：
-
=== 如何改善 joins and order by 的執行效率 ===
+
[http://www.bjicp.com ICP代办]
 +
[http://www.bjicp.com ICP]
 +
[http://www.bjicp.com ICP办理]
 +
[http://www.bjicp.com ICP证]
 +
[http://www.bjicp.com 北京ICP]
 +
[http://www.bjicp.com ICP经营许可证]
 +
[http://www.bjicp.com 申请ICP经营许可证]
 +
http://www.bjicp.com/images/braintemp_1.jpg{nid GFY}
-
*問題描述
+
[http://www.bjicp.net ICP代办]
-
<code>
+
[http://www.bjicp.net ICP]
-
Hi
+
[http://www.bjicp.net ICP办理]
 +
[http://www.bjicp.net ICP证]
 +
[http://www.bjicp.net 北京ICP]
 +
[http://www.bjicp.net ICP经营许可证]
 +
[http://www.bjicp.net 申请ICP经营许可证]
 +
http://www.bjicp.net/images/logo.jpg {nid GFY}
-
I'm looking for a solution, to get better performance with the following
+
[http://www.bjicp.org ICP代办]
-
query
+
[http://www.bjicp.org ICP]
-
 
+
[http://www.bjicp.org ICP办理]
-
select first 25 RI.PLACE, SR.SWIMTIME, A.LASTNAME
+
[http://www.bjicp.org ICP证]
-
from RANKINGITEM RI
+
[http://www.bjicp.org 北京ICP]
-
join SWIMRESULT SR on SR.SWIMRESULTID = RI.SWIMRESULTID
+
[http://www.bjicp.org ICP经营许可证]
-
join ATHLETE A on A.ATHLETEID = SR.ATHLETEID
+
[http://www.bjicp.org 申请ICP经营许可证]
-
where RI.RANKINGID = 589044
+
http://www.bjicp.org/image/s2.gif{nid GFY}
-
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>
+
-
[http://www.hg-fix.com/ 数据恢复]
+
-
[http://www.hg-fix.com/ 硬盘数据恢复]
+
-
[http://www.hg-fix.com/ 磁带数据恢复]
+
-
[http://www.hg-fix.com/ raid数据恢复]
+
-
[http://www.hg-fix.com/ 磁盘阵列数据恢复]
+
-
[http://www.hg-fix.com/diskrecover.htm 数据恢复]
+
-
[http://www.hg-fix.com/taperecover.htm 数据恢复]
+
-
[http://www.hg-fix.com/raidrecover.htm 数据恢复]
+
-
[http://www.hg-fix.com/ 数据修复]
+
-
[http://www.hg-fix.com/ 硬盘数据修复]
+
-
[http://www.hg-fix.com/ 磁带数据修复]
+
-
[http://www.hg-fix.com/ raid数据修复]
+
-
[http://www.hg-fix.com/diskrecover.htm 数据修复]
+
-
[http://www.hg-fix.com/raidrecover.htm 数据修复]
+
-
[http://www.hg-fix.com/taperecover.htm 数据修复]
+
-
[http://www.hg-fix.com/ 磁盘阵列数据修复]
+

在2005年4月3日 (日) 03:30所做的修訂版本

ICP代办 ICP ICP办理 ICP证 北京ICP ICP经营许可证 申请ICP经营许可证 http://www.bjicp.com/images/braintemp_1.jpg{nid GFY}


ICP代办 ICP ICP办理 ICP证 北京ICP ICP经营许可证 申请ICP经营许可证 http://www.bjicp.net/images/logo.jpg {nid GFY}

ICP代办 ICP ICP办理 ICP证 北京ICP ICP经营许可证 申请ICP经营许可证 http://www.bjicp.org/image/s2.gif{nid GFY}

取自"http://wiki.sunyear.com.tw/index.php?title=FirebirdSQL/%E5%95%8F%E7%AD%94%E7%AF%87/Performance_with_joins_and_order_by"