Как оптимально написать T-SQL запрос для выборки настройки Table/Group/All (например, счет ГК из профиля разноски)

[mazzy]

мдя... на большой базе пришлось подбирать фильтры. в принципе там получалось минут 20 на всю выборку в 15млн результирующих записей.

вот какие запросы были:

-- наивный: 4% по плану запроса, выполнено за 1:24, выбрано 342294 записи
with trans as (
SELECT
    isnull(accTable.SUMACCOUNT, isnull(accGroup.SumAccount, accAll.SumAccount)) as pSumAccount,
    tr.*
from custtrans as tr
join custtable as tab on (tab.accountnum = tr.accountnum)
left join CUSTLEDGERACCOUNTS as accTable
    on (accTable.DATAAREAID = 'eras'
    and accTable.POSTINGPROFILE = tr.POSTINGPROFILE
    and accTable.AccountCode = 0 and accTable.NUM = tab.ACCOUNTNUM)
left join CUSTLEDGERACCOUNTS as accGroup 
    on (accGroup.DATAAREAID = 'eras'
    and accGroup.POSTINGPROFILE = tr.POSTINGPROFILE
    and accGroup.AccountCode = 1 and accGroup.NUM = tab.CUSTGROUP)
left join CUSTLEDGERACCOUNTS as accAll
    on (accAll.DATAAREAID = 'eras'
    and accAll.POSTINGPROFILE = tr.POSTINGPROFILE
    and accAll.AccountCode = 2)
where tr.DATAAREAID = '3r'
  and tab.dataAreaId = 'edat'
)
select *
from trans
where trans.pSumAccount in ('62.02.01', '62.02.02', '62.02.02', '62.01.09')

-- row_number, 87% по плану запроса, выполнился 4:39, выбрано 342294 записи
with trans as (
SELECT --top 100
    la.SUMACCOUNT as pSumAccount
    ,row_number() over (partition by tr.DataAreaId, tr.RecId, la.dataareaid, la.POSTINGPROFILE order by la.ACCOUNTCODE) as acc_rn
    ,tr.*
from custtrans as tr
join custtable as tab on (tab.accountnum = tr.accountnum)
left join CUSTLEDGERACCOUNTS as la
    on ( la.dataareaid = 'eras'
     and la.POSTINGPROFILE = tr.POSTINGPROFILE
     and la.num = (case la.AccountCode 
							when 0 then tab.AccountNum
							when 1 then tab.CustGroup
							else la.num
							end)
    )
where tr.DATAAREAID = '3r'
  and tab.dataAreaId = 'edat'
)
select *
from trans
where trans.acc_rn = 1
  and trans.pSumAccount in ('62.02.01', '62.02.02', '62.02.02', '62.01.09')

-- подзапрос: 8% по плану запроса, выполнился за 1:59, выбрано 342294 записи
with trans as (
select 
	(select top 1 SumAccount 
		from CustLedgerAccounts as la
		where la.PostingProfile = tr.postingProfile
			and la.num = (case la.AccountCode 
							when 0 then tab.AccountNum
							when 1 then tab.CustGroup
							else la.num
							end)
			and la.dataAreaId = 'eras'
		order by AccountCode  ---- <----
		) as pSumAccount

	,tr.*
from custtrans as tr
inner join custtable as tab on (tab.AccountNum = tr.AccountNum)
where tr.DATAAREAID = '3r'
  and tab.dataAreaId = 'edat'
)
select *
from trans
where trans.pSumAccount in ('62.02.01', '62.02.02', '62.02.02', '62.01.09')

прикольно.
А есть у кого-нибудь объяснение таким результатам?
И как можно сделать оптимальнее?

[gl00mie]

Copy-paste detected

Цитата:

Сообщение от mazzy

вот какие запросы были:

-- наивный: 4% по плану запроса, выполнено за 1:17, выбрано 342294 записи
with trans as (
SELECT
    isnull(accTable.SUMACCOUNT, isnull(accGroup.SumAccount, accAll.SumAccount)) as pSumAccount,
    tr.*
from custtrans as tr
join custtable as tab on (tab.accountnum = tr.accountnum)
left join CUSTLEDGERACCOUNTS as accTable
    on (accTable.DATAAREAID = 'eras'
    and accTable.POSTINGPROFILE = tr.POSTINGPROFILE
    and accTable.AccountCode = 0 and accTable.NUM = tab.ACCOUNTNUM)
left join CUSTLEDGERACCOUNTS as accGroup 
    on (accTable.DATAAREAID = 'eras'
    and accGroup.POSTINGPROFILE = tr.POSTINGPROFILE
    and accGroup.AccountCode = 1 and accGroup.NUM = tab.CUSTGROUP)

Во втором left join должно быть

on (accGroup.DATAAREAID = 'eras'

вместо

on (accTable.DATAAREAID = 'eras'

[mazzy]

Цитата:

Сообщение от gl00mie

Copy-paste detected Во втором left join должно быть

on (accGroup.DATAAREAID = 'eras'

вместо

on (accTable.DATAAREAID = 'eras'

опа! спасибо.

перезапустил запрос. принципиально не изменилось. было 1:17, стало 1:24.
исправил в сообщении, чтобы потом не путаться.