Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Помогите оптимизировать запрос...  [new]
igoro1975
Member

Откуда: Израиль / Москва
Сообщений: 295
У меня есть запрос:
select telefon.ms_telefon, telefon.id_pirtey_gorem,
		telefon.sug_telefon, telefon.mispar_telefon, telefon.kod_kidomet,
		telefon.telefon_rashi, telefon.mispar_ratz,
		telefon.kod_kidomet_hadash, 
case cnt 
	when 0 then sug_rishon 
	when 1 then sug_sheni
	else null -- more than 2 phones
end as sug_hadash
from
(
	select t.*, 
			-- Get the number of phones of the same type that comes
			-- before the current phone
			(select count(*)
			from mp_v_hpl_pirtey_gorem_telefon t1, mp_tx_hpl_sug_telefon sug_telefon1
			where  sug_telefon1.sug_telefon = t1.sug_telefon
				and t1.id_pirtey_gorem = t.id_pirtey_gorem
				-- This is the sort order of the phones
				-- telefon_rashi desc, mispart_rats
				and ((t1.telefon_rashi > t.telefon_rashi)
					or ((t1.telefon_rashi = t.telefon_rashi)
						and (t1.mispar_ratz < t.mispar_ratz)
						)
					)
				-- This will make sure we only count the
				-- phones which are of the same type
				and sug_telefon1.sug_rishon = sug_telefon.sug_rishon				
				-- Exclude error rows				
				and kod_kidomet_hadash > 0 and ms_telefon > 0 
				) cnt, sug_rishon, sug_sheni
	from mp_v_hpl_pirtey_gorem_telefon t, 	mp_tx_hpl_sug_telefon sug_telefon
	where sug_telefon.sug_telefon = t.sug_telefon
) telefon

Результат я получаю через 45-50 минут.
94% от этого я вижу через Execution planи вот оттуда картинки:
http://www.ljplus.ru/img/g/o/gone_gone/29.JPG
http://www.ljplus.ru/img/g/o/gone_gone/30.JPG
http://www.ljplus.ru/img/g/o/gone_gone/35.JPG

Возможно ли, что вы сможете мне помочь по этим картинкам и запросу?
20 ноя 05, 13:46    [2087468]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос...  [new]
Glory
Member

Откуда:
Сообщений: 104760
igoro1975
94% от этого я вижу через Execution planи вот оттуда картинки:
http://www.ljplus.ru/img/g/o/gone_gone/29.JPG
http://www.ljplus.ru/img/g/o/gone_gone/30.JPG
http://www.ljplus.ru/img/g/o/gone_gone/35.JPG

Возможно ли, что вы сможете мне помочь по этим картинкам и запросу?

https://www.sql.ru/faq/faq_topic.aspx?fid=393
20 ноя 05, 14:27    [2087521]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос...  [new]
igoro1975
Member

Откуда: Израиль / Москва
Сообщений: 295
Glory
https://www.sql.ru/faq/faq_topic.aspx?fid=393

  |--Compute Scalar(DEFINE:([Expr1042]=If ([Expr1039]=0) then [sug_telefon].[sug_rishon] else If ([Expr1039]=1) then [sug_telefon].[sug_sheni] else NULL))
       |--Compute Scalar(DEFINE:([Expr1018]=isnull([mp_tt_kidomet_telefon].[kod_kidomet], -1), [Expr1019]=If (isnumeric([Expr1015])=0) then '-1' else Convert([Expr1015]), [Expr1020]=If ([pll_t_telefonim].[sug_telefon]=7) then 2 else Convert([pll_t_telefoni
            |--Nested Loops(Inner Join, OUTER REFERENCES:([sug_telefon].[sug_rishon], [mp_t_pirtey_gorem].[id_pirtey_gorem], [pll_t_telefonim].[mispar_ratz], [pll_t_telefonim].[telefon_rashi]))
                 |--Hash Match(Right Outer Join, HASH:([mp_tt_kidomet_telefon].[teur_kidomet])=([Expr1014]), RESIDUAL:([Expr1014]=[mp_tt_kidomet_telefon].[teur_kidomet]))
                 |    |--Clustered Index Scan(OBJECT:([db125_t].[dbo].[mp_tt_kidomet_telefon].[mp_tt_kidomet_telefon_PK]))
                 |    |--Hash Match(Inner Join, HASH:([sug_telefon].[sug_telefon])=([Expr1096]), RESIDUAL:([sug_telefon].[sug_telefon]=[Expr1096]))
                 |         |--Table Scan(OBJECT:([db125_t].[dbo].[mp_tx_hpl_sug_telefon] AS [sug_telefon]))
                 |         |--Compute Scalar(DEFINE:([Expr1096]=If ([pll_t_telefonim].[sug_telefon]=7) then 2 else Convert([pll_t_telefonim].[sug_telefon])))
                 |              |--Sort(DISTINCT ORDER BY:([mp_t_pirtey_gorem].[id_pirtey_gorem] ASC, [pll_t_telefonim].[sug_telefon] ASC, [Expr1011] ASC, [Expr1014] ASC, [Expr1015] ASC, [pll_t_telefonim].[telefon_rashi] ASC, [pll_t_telefonim].[mispar_ratz
                 |                   |--Compute Scalar(DEFINE:([Expr1014]=If (Convert(substring([Expr1011], 1, 1))<>0) then '03' else If (charindex('-', [Expr1011], NULL)>0) then Convert(substring([Expr1011], 1, charindex('-', [Expr1011], NULL)-1)) else If
                 |                        |--Merge Join(Inner Join, MERGE:([mp_t_pirtey_gorem].[id_pirtey_gorem])=([mp_t_gorem_be_tik].[id_pirtey_gorem]), RESIDUAL:([mp_t_pirtey_gorem].[id_pirtey_gorem]=[mp_t_gorem_be_tik].[id_pirtey_gorem]))
                 |                             |--Clustered Index Scan(OBJECT:([db125_t].[dbo].[mp_t_pirtey_gorem].[mp_t_pirtey_gorem_PK]), ORDERED FORWARD)
                 |                             |--Sort(ORDER BY:([mp_t_gorem_be_tik].[id_pirtey_gorem] ASC))
                 |                                  |--Hash Match(Inner Join, HASH:([mp_t_tik].[id_tik], [Expr1099])=([mp_t_gorem_be_tik].[id_tik], [mp_t_gorem_be_tik].[id_gorem_chitzoni]), RESIDUAL:([mp_t_gorem_be_tik].[id_tik]=[mp_t_tik].[id_tik] AND [mp
                 |                                       |--Compute Scalar(DEFINE:([Expr1099]=Convert([pll_t_grm].[grm_id])))
                 |                                       |    |--Hash Match(Inner Join, HASH:([pll_t_grm].[tik_id])=([Expr1098]), RESIDUAL:([Expr1098]=[pll_t_grm].[tik_id]))
                 |                                       |         |--Hash Match(Right Semi Join, HASH:([mp_tt_sug_telefon].[kod_sug_telefon])=([Expr1100]), RESIDUAL:([Expr1100]=[mp_tt_sug_telefon].[kod_sug_telefon]))
                 |                                       |         |    |--Clustered Index Scan(OBJECT:([db125_t].[dbo].[mp_tt_sug_telefon].[mp_tt_sug_telefon_PK]))
                 |                                       |         |    |--Compute Scalar(DEFINE:([Expr1100]=Convert([pll_t_telefonim].[sug_telefon])))
                 |                                       |         |         |--Compute Scalar(DEFINE:([Expr1011]=ltrim(rtrim(Convert([pll_t_telefonim].[mispar_telefon])))))
                 |                                       |         |              |--Hash Match(Inner Join, HASH:([pll_t_telefonim].[sug_mezahe], [pll_t_telefonim].[mispar_mezahe])=([pll_t_grm].[sug_mezahe], [pll_t_grm].[mezahe_id]), RESIDUAL:([pll_t_grm].
                 |                                       |         |                   |--Table Scan(OBJECT:([db121].[dbo].[pll_t_telefonim]))
                 |                                       |         |                   |--Table Scan(OBJECT:([db121].[dbo].[pll_t_grm]))
                 |                                       |         |--Compute Scalar(DEFINE:([Expr1098]=Convert([mp_t_tik].[ms_tik])))
                 |                                       |              |--Index Scan(OBJECT:([db125_t].[dbo].[mp_t_tik].[mp_i1_t_tik]))
                 |                                       |--Clustered Index Scan(OBJECT:([db125_t].[dbo].[mp_t_gorem_be_tik].[mp_t_gorem_be_tik_PK]))
                 |--Hash Match(Cache, HASH:([sug_telefon].[sug_rishon], [mp_t_pirtey_gorem].[id_pirtey_gorem], [pll_t_telefonim].[mispar_ratz], [pll_t_telefonim].[telefon_rashi]), RESIDUAL:((([sug_telefon].[sug_rishon]=[sug_telefon].[sug_rishon] AND [mp_t_
                      |--Compute Scalar(DEFINE:([Expr1039]=Convert([Expr1108])))
                           |--Stream Aggregate(DEFINE:([Expr1108]=Count(*)))
                                |--Filter(WHERE:(isnull([mp_tt_kidomet_telefon].[kod_kidomet], -1)>0))
                                     |--Merge Join(Left Outer Join, MANY-TO-MANY MERGE:([Expr1035])=([mp_tt_kidomet_telefon].[teur_kidomet]), RESIDUAL:([Expr1035]=[mp_tt_kidomet_telefon].[teur_kidomet]))
                                          |--Nested Loops(Inner Join, WHERE:([sug_telefon1].[sug_telefon]=If ([pll_t_telefonim].[sug_telefon]=7) then 2 else Convert([pll_t_telefonim].[sug_telefon])))
                                          |    |--Sort(DISTINCT ORDER BY:([Expr1035] ASC, [pll_t_telefonim].[sug_telefon] ASC, [Expr1032] ASC, [Expr1036] ASC, [pll_t_telefonim].[telefon_rashi] ASC, [pll_t_telefonim].[mispar_ratz] ASC))
                                          |    |    |--Filter(WHERE:(Convert(If (isnumeric([Expr1036])=0) then '-1' else Convert([Expr1036]))>0))
                                          |    |         |--Compute Scalar(DEFINE:([Expr1035]=If (Convert(substring([Expr1032], 1, 1))<>0) then '03' else If (charindex('-', [Expr1032], NULL)>0) then Convert(substring([Expr1032], 1, charindex('-', [Expr1032
                                          |    |              |--Nested Loops(Inner Join)
                                          |    |                   |--Clustered Index Seek(OBJECT:([db125_t].[dbo].[mp_t_pirtey_gorem].[mp_t_pirtey_gorem_PK]), SEEK:([mp_t_pirtey_gorem].[id_pirtey_gorem]=[mp_t_pirtey_gorem].[id_pirtey_gorem]) ORDERED FORWA
                                          |    |                   |--Nested Loops(Left Semi Join, WHERE:(Convert([pll_t_telefonim].[sug_telefon])=[mp_tt_sug_telefon].[kod_sug_telefon]))
                                          |    |                        |--Hash Match(Inner Join, HASH:([mp_t_gorem_be_tik].[id_gorem_chitzoni], [Expr1093])=([Expr1094], [pll_t_grm].[tik_id]), RESIDUAL:([mp_t_gorem_be_tik].[id_gorem_chitzoni]=[Expr1094] AN
                                          |    |                        |    |--Compute Scalar(DEFINE:([Expr1093]=Convert([mp_t_tik].[ms_tik])))
                                          |    |                        |    |    |--Nested Loops(Inner Join, OUTER REFERENCES:([mp_t_gorem_be_tik].[id_tik]))
                                          |    |                        |    |         |--Index Spool(SEEK:([mp_t_gorem_be_tik].[id_pirtey_gorem]=[mp_t_pirtey_gorem].[id_pirtey_gorem]))
                                          |    |                        |    |         |    |--Clustered Index Scan(OBJECT:([db125_t].[dbo].[mp_t_gorem_be_tik].[mp_t_gorem_be_tik_PK]))
                                          |    |                        |    |         |--Clustered Index Seek(OBJECT:([db125_t].[dbo].[mp_t_tik].[mp_t_tik_PK]), SEEK:([mp_t_tik].[id_tik]=[mp_t_gorem_be_tik].[id_tik]) ORDERED FORWARD)
                                          |    |                        |    |--Compute Scalar(DEFINE:([Expr1094]=Convert([pll_t_grm].[grm_id])))
                                          |    |                        |         |--Compute Scalar(DEFINE:([Expr1032]=ltrim(rtrim(Convert([pll_t_telefonim].[mispar_telefon])))))
                                          |    |                        |              |--Merge Join(Inner Join, MANY-TO-MANY MERGE:([pll_t_grm].[sug_mezahe], [pll_t_grm].[mezahe_id])=([pll_t_telefonim].[sug_mezahe], [pll_t_telefonim].[mispar_mezahe]), RES
                                          |    |                        |                   |--Bookmark Lookup(BOOKMARK:([Bmk1030]), OBJECT:([db121].[dbo].[pll_t_grm]))
                                          |    |                        |                   |    |--Index Scan(OBJECT:([db121].[dbo].[pll_t_grm].[pll_i_grm_mezahe]), ORDERED FORWARD)
                                          |    |                        |                   |--Filter(WHERE:([pll_t_telefonim].[telefon_rashi]>[pll_t_telefonim].[telefon_rashi] OR ([pll_t_telefonim].[telefon_rashi]=[pll_t_telefonim].[telefon_rashi] AND [pl
                                          |    |                        |                        |--Bookmark Lookup(BOOKMARK:([Bmk1028]), OBJECT:([db121].[dbo].[pll_t_telefonim]))
                                          |    |                        |                             |--Index Scan(OBJECT:([db121].[dbo].[pll_t_telefonim].[PK_pll_t_telefonim]), ORDERED FORWARD)
                                          |    |                        |--Table Spool
                                          |    |                             |--Clustered Index Scan(OBJECT:([db125_t].[dbo].[mp_tt_sug_telefon].[mp_tt_sug_telefon_PK]))
                                          |    |--Index Spool(SEEK:([sug_telefon1].[sug_rishon]=[sug_telefon].[sug_rishon]))
                                          |         |--Table Scan(OBJECT:([db125_t].[dbo].[mp_tx_hpl_sug_telefon] AS [sug_telefon1]))
                                          |--Sort(ORDER BY:([mp_tt_kidomet_telefon].[teur_kidomet] ASC))
                                               |--Clustered Index Scan(OBJECT:([db125_t].[dbo].[mp_tt_kidomet_telefon].[mp_tt_kidomet_telefon_PK]))

20 ноя 05, 16:11    [2087727]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос...  [new]
aleks2
Guest
? - я не знаю в какой таблице [t] или [st] находятся поля
	select ?.ms_telefon, ?.id_pirtey_gorem,
		?.sug_telefon, ?.mispar_telefon, ?.kod_kidomet,
		?.telefon_rashi, ?.mispar_ratz,
		?.kod_kidomet_hadash
		, case Count(*)
			when 0 then ?.sug_rishon 
			when 1 then ?.sug_sheni
		end as sug_hadash
	from 
	mp_v_hpl_pirtey_gorem_telefon t
	INNER JOIN
	mp_tx_hpl_sug_telefon st
	ON st.sug_telefon = t.sug_telefon
	LEFT OUTER JOIN
	(select id_pirtey_gorem, sug_rishon, telefon_rashi, mispar_ratz
		 FROM
		 mp_v_hpl_pirtey_gorem_telefon t1
		 INNER JOIN
		 mp_tx_hpl_sug_telefon st1
		 ON
		 st1.sug_telefon = t1.sug_telefon 
	 WHERE kod_kidomet_hadash > 0 and ms_telefon > 0 
        ) X
	ON
	x.id_pirtey_gorem = t.id_pirtey_gorem
	and x.sug_rishon = st.sug_rishon				
	and ((x.telefon_rashi > t.telefon_rashi)
		or ((x.telefon_rashi = t.telefon_rashi)
		and (x.mispar_ratz < t.mispar_ratz)
			)
	)
	GROUP BY ?.ms_telefon, ?.id_pirtey_gorem,
		?.sug_telefon, ?.mispar_telefon, ?.kod_kidomet,
		?.telefon_rashi, ?.mispar_ratz,
		?.kod_kidomet_hadash

21 ноя 05, 06:46    [2088346]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить