调整查询 [英] Tune query
问题描述
以下查询需要25分钟才能完成,
你能和我分享一下如何减少这段时间吗?
查询1:(20分钟)
;以prev为(
选择t.ledger_id,d.account,d.description,t.transaction_group,t.transaction_party,d.account_details,sum(d.amount) amt
来自ledger.dbo.ledger_transactions_details d
加入ledger.dbo.ledger_transactions t on t.id = d.transaction_id
group by ledger_id, account,description,transaction_group,transaction_party,d.account_details
)
从上一页选择*进入Transprocess.dbo.phy_prev¥b $ b
查询2:(25分钟)
; curr为(
选择来自Transprocess的ledger_id,account,description,transaction_group,transaction_party,account_details,max(tran_oid)tran_oid,sum(amt)amt
。 dbo.phy_acc
group by ledger_id,account,description,transaction_group,transaction_party,account_details
)
从curr中选择*进入Transprocess.dbo.phy_curr
表& count:
phy_prev - 130100236 (行数)
phy_acc   - 119943234 (行数)
phy_curr - 68188115(行数)
ledger_transactions - 217941528(行数)
ledger_transactions_details   - 417143969(行数)
不知道表&索引设计无法帮助你。
检查查询的执行计划,看看是否使用了合适的索引。
hi ,
below query taking 25min to complete,
can you pls share me how to reduce this time ?
Query1: (20min)
;with prev as (
select t.ledger_id,d.account,d.description,t.transaction_group,t.transaction_party,d.account_details,sum(d.amount) amt
from ledger.dbo.ledger_transactions_details d
join ledger.dbo.ledger_transactions t on t.id = d.transaction_id
group by ledger_id,account,description,transaction_group,transaction_party,d.account_details
)
select * into Transprocess.dbo.phy_prev from prev
Query2: (25min)
;with curr as (
select ledger_id,account,description,transaction_group,transaction_party,account_details,max(tran_oid) tran_oid,sum(amt) amt
from Transprocess.dbo.phy_acc
group by ledger_id,account,description,transaction_group,transaction_party,account_details
)
select * into Transprocess.dbo.phy_curr from curr
Tables & count:
phy_prev -- 130100236 (row count)
phy_acc -- 119943234 (row count)
phy_curr -- 68188115 (row count)
ledger_transactions -- 217941528 (row count)
ledger_transactions_details -- 417143969 (row count)
Without knowing table & index design it's impossible to assist you.
Check the execution plan of the queries to see if suitable indexes are used.
这篇关于调整查询的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!