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

Откуда:
Сообщений: 300
есть хранимая процедура которая выводит список выпускников.
запрос в ней час данных берет из прилинкованного сервера, а другую из локальных таблиц
и надо сказать, что выполняется он порядка 6 минут, хочу слегка ускорить его выполнение...
анализируя запрос мне удалось выделить ту часть, которая и тормозит

	select 
		s056.number
		, d_vipis
		, not_ready
		, works
		, num_tarif
		, remark 
	from s056 -- это представление которое тянет данные с прилинкованного сервера
	right join (
			select 
				number
				, min(itog_ocenk) as itog 
			from z056 -- это таблица которая физически находится на этом сервере
			group by number
			having min(itog_ocenk) > 0
	) b056 on s056.number=b056.number	

можно ли как-нибудь попробовать ускорить выполнения запроса?
31 окт 11, 14:52    [11526899]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
SanyL
Member

Откуда: Москва
Сообщений: 4540
Azomorph,

а так бесполезно ускорять запросы :)

надо хотябы знать версию сервера, планы выполнения, структуры данных, распределение статистик
31 окт 11, 14:54    [11526915]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
Azomorph
Member

Откуда:
Сообщений: 300
ах да... забыл :)

сейчас:
версия:
Microsoft SQL Server 2005 - 9.00.5254.00 (Intel X86)   Dec 18 2010 23:05:34   Copyright (c) 1988-2005 Microsoft Corporation  Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2) 
план выполнения:
+

|--Nested Loops(Left Outer Join, OUTER REFERENCES:([TantalM_311011].[dbo].[Z056].[number]))
|--Filter(WHERE:([Expr1037]>(0)))
| |--Stream Aggregate(GROUP BY:([TantalM_311011].[dbo].[Z056].[number]) DEFINE:([Expr1037]=MIN([TantalM_311011].[dbo].[Z056].[itog_ocenk])))
| |--Index Scan(OBJECT:([TantalM_311011].[dbo].[Z056].[_dta_index_Z056_6_1271362177__K2_8]), ORDERED FORWARD)
|--Compute Scalar(DEFINE:([Expr1010]=CONVERT(varchar(9),[ibs].[Tantal1].[dbo].[TStudent].[StudentNumber] as [a].[StudentNumber],0), [Expr1011]=CONVERT(varchar(100),[ibs].[Tantal1].[dbo].[TStudent].[WorkInformation] as [a].[WorkInformation],0), [Expr1024]=isnull(CONVERT(varchar(1024),[ibs].[Tantal1].[dbo].[TComments].[Value] as [f].[Value],0),'')))
|--Nested Loops(Left Outer Join, OUTER REFERENCES:([ibs].[Tantal1].[dbo].[TStudent].[StudentID]))
|--Compute Scalar(DEFINE:([ibs].[Tantal1].[dbo].[TStudent].[StudentID]=[ibs].[Tantal1].[dbo].[TStudent].[StudentID] as [a].[StudentID], [ibs].[Tantal1].[dbo].[TStudent].[StudentNumber]=[ibs].[Tantal1].[dbo].[TStudent].[StudentNumber] as [a].[StudentNumber], [ibs].[Tantal1].[dbo].[TStudent].[DischargeDate]=[ibs].[Tantal1].[dbo].[TStudent].[DischargeDate] as [a].[DischargeDate], [ibs].[Tantal1].[dbo].[TStudent].[WorkInformation]=[ibs].[Tantal1].[dbo].[TStudent].[WorkInformation] as [a].[WorkInformation], [ibs].[Tantal1].[dbo].[TStudent].[not_ready]=[ibs].[Tantal1].[dbo].[TStudent].[not_ready] as [a].[not_ready], [ibs].[Tantal1].[dbo].[TRates].[RateNumber]=[ibs].[Tantal1].[dbo].[TRates].[RateNumber] as [b].[RateNumber]))
| |--Remote Query(SOURCE:(ibs), QUERY:(SELECT "Col1106","Col1108","Col1113","Col1114","Col1129","Tbl1003"."RateNumber" "Col1103" FROM "Tantal1"."dbo"."TEmails" "Tbl1009" RIGHT OUTER JOIN "Tantal1"."dbo"."TPhones" "Tbl1005" RIGHT OUTER JOIN "Tantal1"."dbo"."TPhones" "Tbl1007" RIGHT OUTER JOIN "Tantal1"."dbo"."TRates" "Tbl1003" RIGHT OUTER JOIN (SELECT "Tbl1001"."StudentID" "Col1106","Tbl1001"."Birthdate" "Col1107","Tbl1001"."StudentNumber" "Col1108","Tbl1001"."LanguageID" "Col1109","Tbl1001"."EducationLevelID" "Col1110","Tbl1001"."RateID" "Col1111","Tbl1001"."Comment2" "Col1112","Tbl1001"."DischargeDate" "Col1113","Tbl1001"."WorkInformation" "Col1114","Tbl1001"."Rating" "Col1115","Tbl1001"."DateRating" "Col1116","Tbl1001"."DedicateType" "Col1117","Tbl1001"."ind_plan" "Col1118","Tbl1001"."F1_plat" "Col1119","Tbl1001"."F2_bank" "Col1120","Tbl1001"."F3_bad" "Col1121","Tbl1001"."F4_tasks" "Col1122","Tbl1001"."F5_two" "Col1123","Tbl1001"."OFF_ALL" "Col1124","Tbl1001"."n_numold" "Col1125","Tbl1001"."txttarif" "Col1126","Tbl1001"."nSpecial" "Col1127","Tbl1001"."s_del" "Col1128","Tbl1001"."not_ready" "Col1129" FROM "Tantal1"."dbo"."TStudent" "Tbl1001" WHERE CONVERT(varchar(9),"Tbl1001"."StudentNumber",0)=? AND "Tbl1001"."DedicateType"=N'Mail') Qry1130 ON "Col1111"="Tbl1003"."RateID" ON "Col1106"="Tbl1007"."StudentID" AND "Tbl1007"."TypeID"=(1) ON "Col1106"="Tbl1005"."StudentID" AND "Tbl1005"."TypeID"=(2) ON "Col1106"="Tbl1009"."StudentID"))
|--Compute Scalar(DEFINE:([ibs].[Tantal1].[dbo].[TComments].[Value]=[ibs].[Tantal1].[dbo].[TComments].[Value] as [f].[Value]))
|--Remote Query(SOURCE:(ibs), QUERY:(SELECT TOP (1) "Tbl1022"."Value" "Col1042" FROM "Tantal1"."dbo"."TComments" "Tbl1022" WHERE "Tbl1022"."StudentID"=?))
31 окт 11, 15:11    [11527082]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
Azomorph
Member

Откуда:
Сообщений: 300
+

  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([TantalM_311011].[dbo].[Z056].[number]))
       |--Filter(WHERE:([Expr1037]>(0)))
       |    |--Stream Aggregate(GROUP BY:([TantalM_311011].[dbo].[Z056].[number]) DEFINE:([Expr1037]=MIN([TantalM_311011].[dbo].[Z056].[itog_ocenk])))
       |         |--Index Scan(OBJECT:([TantalM_311011].[dbo].[Z056].[_dta_index_Z056_6_1271362177__K2_8]), ORDERED FORWARD)
       |--Compute Scalar(DEFINE:([Expr1010]=CONVERT(varchar(9),[ibs].[Tantal1].[dbo].[TStudent].[StudentNumber] as [a].[StudentNumber],0), [Expr1011]=CONVERT(varchar(100),[ibs].[Tantal1].[dbo].[TStudent].[WorkInformation] as [a].[WorkInformation],0), [Expr1024]=isnull(CONVERT(varchar(1024),[ibs].[Tantal1].[dbo].[TComments].[Value] as [f].[Value],0),'')))
            |--Nested Loops(Left Outer Join, OUTER REFERENCES:([ibs].[Tantal1].[dbo].[TStudent].[StudentID]))
                 |--Compute Scalar(DEFINE:([ibs].[Tantal1].[dbo].[TStudent].[StudentID]=[ibs].[Tantal1].[dbo].[TStudent].[StudentID] as [a].[StudentID], [ibs].[Tantal1].[dbo].[TStudent].[StudentNumber]=[ibs].[Tantal1].[dbo].[TStudent].[StudentNumber] as [a].[StudentNumber], [ibs].[Tantal1].[dbo].[TStudent].[DischargeDate]=[ibs].[Tantal1].[dbo].[TStudent].[DischargeDate] as [a].[DischargeDate], [ibs].[Tantal1].[dbo].[TStudent].[WorkInformation]=[ibs].[Tantal1].[dbo].[TStudent].[WorkInformation] as [a].[WorkInformation], [ibs].[Tantal1].[dbo].[TStudent].[not_ready]=[ibs].[Tantal1].[dbo].[TStudent].[not_ready] as [a].[not_ready], [ibs].[Tantal1].[dbo].[TRates].[RateNumber]=[ibs].[Tantal1].[dbo].[TRates].[RateNumber] as [b].[RateNumber]))
                 |    |--Remote Query(SOURCE:(ibs), QUERY:(SELECT "Col1106","Col1108","Col1113","Col1114","Col1129","Tbl1003"."RateNumber" "Col1103" FROM "Tantal1"."dbo"."TEmails" "Tbl1009" RIGHT OUTER JOIN "Tantal1"."dbo"."TPhones" "Tbl1005" RIGHT OUTER JOIN "Tantal1"."dbo"."TPhones" "Tbl1007" RIGHT OUTER JOIN "Tantal1"."dbo"."TRates" "Tbl1003" RIGHT OUTER JOIN (SELECT "Tbl1001"."StudentID" "Col1106","Tbl1001"."Birthdate" "Col1107","Tbl1001"."StudentNumber" "Col1108","Tbl1001"."LanguageID" "Col1109","Tbl1001"."EducationLevelID" "Col1110","Tbl1001"."RateID" "Col1111","Tbl1001"."Comment2" "Col1112","Tbl1001"."DischargeDate" "Col1113","Tbl1001"."WorkInformation" "Col1114","Tbl1001"."Rating" "Col1115","Tbl1001"."DateRating" "Col1116","Tbl1001"."DedicateType" "Col1117","Tbl1001"."ind_plan" "Col1118","Tbl1001"."F1_plat" "Col1119","Tbl1001"."F2_bank" "Col1120","Tbl1001"."F3_bad" "Col1121","Tbl1001"."F4_tasks" "Col1122","Tbl1001"."F5_two" "Col1123","Tbl1001"."OFF_ALL" "Col1124","Tbl1001"."n_numold" "Col1125","Tbl1001"."txttarif" "Col1126","Tbl1001"."nSpecial" "Col1127","Tbl1001"."s_del" "Col1128","Tbl1001"."not_ready" "Col1129" FROM "Tantal1"."dbo"."TStudent" "Tbl1001" WHERE CONVERT(varchar(9),"Tbl1001"."StudentNumber",0)=? AND "Tbl1001"."DedicateType"=N'Mail') Qry1130 ON "Col1111"="Tbl1003"."RateID" ON "Col1106"="Tbl1007"."StudentID" AND "Tbl1007"."TypeID"=(1) ON "Col1106"="Tbl1005"."StudentID" AND "Tbl1005"."TypeID"=(2) ON "Col1106"="Tbl1009"."StudentID"))
                 |--Compute Scalar(DEFINE:([ibs].[Tantal1].[dbo].[TComments].[Value]=[ibs].[Tantal1].[dbo].[TComments].[Value] as [f].[Value]))
                      |--Remote Query(SOURCE:(ibs), QUERY:(SELECT TOP (1) "Tbl1022"."Value" "Col1042" FROM "Tantal1"."dbo"."TComments" "Tbl1022" WHERE "Tbl1022"."StudentID"=?))
31 окт 11, 15:12    [11527093]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104751
У вас в цикле Nested Loops идет обращение к удаленному серверу
Чем больше итераций цикла, тем дольше выполнение
31 окт 11, 15:30    [11527289]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
SanyL
Member

Откуда: Москва
Сообщений: 4540
как вариант - с удаленки перетащить все во времянку и джойнить уже ее, а дальше смотреть - может есть смысл индекс сделать... ну или не loop join а merje использовать.
31 окт 11, 16:17    [11527766]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
Azomorph
Member

Откуда:
Сообщений: 300
SanyL
как вариант - с удаленки перетащить все во времянку и джойнить уже ее, а дальше смотреть - может есть смысл индекс сделать... ну или не loop join а merje использовать.

да я в итоге так и сделал, но толку особо не дало, т.к. при джойне времянки все начинает тормозить table scan этой времянки, т.к. фильтруется порядка 10000 записей

а на временную таблицу можно индекс повесить?
1 ноя 11, 07:39    [11530291]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31978
Azomorph
а на временную таблицу можно индекс повесить?
Да
1 ноя 11, 08:27    [11530360]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
Azomorph
Member

Откуда:
Сообщений: 300
спасибо, попробую :)
1 ноя 11, 08:49    [11530387]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
Azomorph
Member

Откуда:
Сообщений: 300
индекс помог очень даже, хранимая процедура теперь вместо 6 минут выполняется 5 секунд :)
1 ноя 11, 11:26    [11531258]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
SanyL
Member

Откуда: Москва
Сообщений: 4540
Azomorph
индекс помог очень даже, хранимая процедура теперь вместо 6 минут выполняется 5 секунд :)


вот видите как можно чтото сделать - когда есть информация от которой можно отталкиваться, а не только запрос. Другой вопрос - может и не надо перетаскивать во времянку? может на удаленном сервере индекса не хватает? И попробовать не loop а merje?

т.е. не могу сказать что оно будет лучше - но и не исключаю...
1 ноя 11, 11:39    [11531378]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
SanyL
Member

Откуда: Москва
Сообщений: 4540
Azomorph
индекс помог очень даже, хранимая процедура теперь вместо 6 минут выполняется 5 секунд :)


и новый план покажите пжста...
1 ноя 11, 11:41    [11531388]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
Azomorph
Member

Откуда:
Сообщений: 300
сейчас выложу, но предварительно поясню один момент:
такого прироста производительности мне удалось добиться за счет того, что хранимая создает вначале обычную таблицу, а после отработки запроса удаляет ее :)
скидывая данные во временную таблицу, даже индекс не дает прироста производительности
т.е. сам код хранимой процедуры выглядит так:


create procedure [dbo].[sp_GraduaterMail] -- данная хранимая процедура использует софтина написанная на фокспро и форма представления тянет из процедуры список студентов кто подался на выпуск или переводится на следующий курс
with execute as caller 
as
begin 
	if OBJECT_ID(N'dbo.Z', N'U') is not null
	begin
		drop index idx_Z_number_itog on Z
		drop table Z
	end	

	
	create table Z(number nvarchar(9), itog int)

	
	create nonclustered index idx_Z_number on Z(number)


	insert Z(number, itog) -- в эту таблицу и заносятся данные необходимые для дальнейшей фильтрации
		select
			number
			, MIN(itog_ocenk) as itog
		from sbs.TantalM.dbo.Z056
		group by number
		having MIN(itog_ocenk) > 0
		order by number

	select 
		CAST(TStudent.StudentNumber as varchar(9)) as number
		, CAST((FirstName + ' ' + PatronymicName + ' ' + SurName) as varchar(100)) as fio
		, ISNULL(CAST(dbo.f_GetStudentAddress(TStudent.StudentID) as varchar(100)), CAST(TStudent.Address as varchar(100))) as address
		, CAST((case 
				when ISNUMERIC(TStudent.PostalIndex) = 1
				then TStudent.PostalIndex
					else 0 
				 end) as decimal(6, 0)) as postind
		, TStudent.GroupCoursesID as id_cat
		, CAST(TStudent.EnteringDate as smalldatetime) as d_come
		, CAST(TStudent.EndingDate as smalldatetime) as d_out
		, CAST('056' as varchar(3)) as idx
		, CAST((case 
			when 
			 (TStudent.IsBlocked = 1) or (TStudent.IsEntrant = 1)
			then 1
		        when (TStudent.IsBlocked = 0) and (TStudent.IsEntrant = 0)
		    then 0
		  end) as bit) as s_del	
		, CAST(TStudent.DischargeDate as smalldatetime) as d_vipis
		, CAST(TStudent.not_ready as tinyint) as not_ready
		, CAST(TStudent.WorkInformation as varchar(100)) as works
		, CAST(TRates.RateNumber as int) as num_tarif
		, ISNULL(CAST((select TOP(1) Value from TComments where TComments.StudentID = TStudent.StudentID) as varchar(1024)), '') as remark
		, TSpecialty.Active as rim
		, case
			when TStudent.StudentNumber in (select number from sbs.TantalM.dbo.D056 where id_documen in (14, 16)) -- этот второй запрос тоже тянет данные с прилинкованного сервера, но на производительность не влияет
			then 1
			else 0
		end as r
	from TGroupCourses
	join TStudentGroup on TStudentGroup.StudentGroupID = TGroupCourses.StudentGroupID
		and TStudentGroup.IsVisibleMail = 1
	join TSpecialty on TSpecialty.SpecialtyID = TStudentGroup.SpecialtyID
	join TStudent on TGroupCourses.GroupCoursesID = TStudent.GroupCoursesID
	left join TRates on TStudent.RateID = TRates.RateID
	right join Z on TStudent.StudentNumber = Z.number -- ну и весь сыр-бор был в этом объединении, т.к. в среднем фильтрация порядка 10000 записей
	
	drop index idx_Z_number_itog on Z
	drop table Z
end	

GO

план выполнения выходит такой:
+

exec dbo.sp_GraduaterMail
  CREATE procedure sp_GraduaterMail  with execute as caller   as  begin    if OBJECT_ID(N'dbo.Z', N'U') is not null
     begin    drop index idx_Z_number_itog on Z
     drop table Z
   end         create table Z(number nvarchar(9), itog int)
   create nonclustered index idx_Z_number_itog on Z(number)
   insert Z(number, itog)     select     number     , MIN(itog_ocenk) as itog    from sbs.TantalM.dbo.Z056    group by number    having MIN(itog_ocenk) > 0    order by number

       |--Table Insert(OBJECT:([Tantal1].[dbo].[Z]), OBJECT:([Tantal1].[dbo].[Z].[idx_Z_number_itog]), SET:([Tantal1].[dbo].[Z].[number] = [Expr1009],[Tantal1].[dbo].[Z].[itog] = [Expr1007]))
            |--Compute Scalar(DEFINE:([Expr1009]=CONVERT_IMPLICIT(nvarchar(9),[sbs].[TantalM].[dbo].[Z056].[number],0)))
                 |--Top(ROWCOUNT est 0)
                      |--Compute Scalar(DEFINE:([Expr1008]=tertiary_weights([sbs].[TantalM].[dbo].[Z056].[number])))
                           |--Sort(ORDER BY:([sbs].[TantalM].[dbo].[Z056].[number] ASC))
                                |--Compute Scalar(DEFINE:([sbs].[TantalM].[dbo].[Z056].[number]=[sbs].[TantalM].[dbo].[Z056].[number], [Expr1007]=[Expr1007]))
                                     |--Remote Query(SOURCE:(sbs), QUERY:(SELECT "Col1019","Expr1007" FROM (SELECT "Tbl1006"."number" "Col1019",MIN("Tbl1006"."itog_ocenk") "Expr1007" FROM "TantalM"."dbo"."Z056" "Tbl1006" GROUP BY "Tbl1006"."number") Qry1023 WHERE "Expr1007">(0) ORDER BY "Col1019" ASC))

   select     CAST(TStudent.StudentNumber as varchar(9)) as number    , CAST((FirstName + ' ' + PatronymicName + ' ' + SurName) as varchar(100)) as fio    , ISNULL(CAST(dbo.f_GetStudentAddress(TStudent.StudentID) as varchar(100))     , CAST(TStudent.Address as varchar(100))) as address    --, CAST(TStudent.PostalIndex as decimal(6, 0)), 0) as postind    , CAST((case        when ISNUMERIC(TStudent.PostalIndex) = 1       then TStudent.PostalIndex       else 0        end) as decimal(6, 0)) as postind    , TStudent.GroupCoursesID as id_cat    , CAST(TStudent.EnteringDate as smalldatetime) as d_come    , CAST(TStudent.EndingDate as smalldatetime) as d_out    , CAST('056' as varchar(3)) as idx    , CAST((case      when       (TStudent.IsBlocked = 1) or (TStudent.IsEntrant = 1)     then 1        when (TStudent.IsBlocked = 0) and (TStudent.IsEntrant = 0)        then 0      end) as bit) as s_del     , CAST(TStudent.DischargeDate as smalldatetime) as d_vipis    , CAST(TStudent.not_ready as tinyint) as not_ready    , CAST(TStudent.WorkInformation as varchar(100)) as works    , CAST(TRates.RateNumber as int) as num_tarif    , ISNULL(CAST((select TOP(1) Value from TComments where TComments.StudentID = TStudent.StudentID) as varchar(1024)), '') as remark    , TSpecialty.Active as rim    , case     when TStudent.StudentNumber in (select number from sbs.TantalM.dbo.D056 where id_documen in (14, 16))     then 1     else 0    end as r   from TGroupCourses   join TStudentGroup on TStudentGroup.StudentGroupID = TGroupCourses.StudentGroupID    and TStudentGroup.IsVisibleMail = 1   join TSpecialty on TSpecialty.SpecialtyID = TStudentGroup.SpecialtyID   join TStudent on TGroupCourses.GroupCoursesID = TStudent.GroupCoursesID   left join TRates on TStudent.RateID = TRates.RateID   right join Z on TStudent.StudentNumber = Z.number

       |--Compute Scalar(DEFINE:([Expr1021]=isnull([Tantal1].[dbo].[f_GetStudentAddress]([Tantal1].[dbo].[TStudent].[StudentID]),[Expr1044]), [Expr1025]='056', [Expr1038]=CASE WHEN [Expr1039] THEN (1) ELSE (0) END))
            |--Merge Join(Left Semi Join, MANY-TO-MANY MERGE:([Tantal1].[dbo].[TStudent].[StudentNumber])=([Expr1059]), RESIDUAL:(([sbs].[TantalM].[dbo].[D056].[id_documen]=(16) OR [sbs].[TantalM].[dbo].[D056].[id_documen]=(14)) AND [Tantal1].[dbo].[TStudent].[StudentNumber]=CONVERT_IMPLICIT(nvarchar(9),[sbs].[TantalM].[dbo].[D056].[number],0)))
                 |--Compute Scalar(DEFINE:([Expr1033]=isnull(CONVERT(varchar(1024),[Tantal1].[dbo].[TComments].[Value],0),'')))
                 |    |--Nested Loops(Left Outer Join, OUTER REFERENCES:([Tantal1].[dbo].[TStudent].[StudentID]))
                 |         |--Sort(ORDER BY:([Tantal1].[dbo].[TStudent].[StudentNumber] ASC))
                 |         |    |--Compute Scalar(DEFINE:([Expr1019]=CONVERT(varchar(9),[Tantal1].[dbo].[TStudent].[StudentNumber],0), [Expr1020]=CONVERT(varchar(100),((([Tantal1].[dbo].[TStudent].[FirstName]+N' ')+[Tantal1].[dbo].[TStudent].[PatronymicName])+N' ')+[Tantal1].[dbo].[TStudent].[SurName],0), [Expr1022]=CONVERT(decimal(6,0),CASE WHEN isnumeric(CONVERT_IMPLICIT(varchar(20),[Tantal1].[dbo].[TStudent].[PostalIndex],0))=(1) THEN CONVERT_IMPLICIT(int,[Tantal1].[dbo].[TStudent].[PostalIndex],0) ELSE (0) END,0), [Expr1023]=CONVERT(smalldatetime,[Tantal1].[dbo].[TStudent].[EnteringDate],0), [Expr1024]=CONVERT(smalldatetime,[Tantal1].[dbo].[TStudent].[EndingDate],0), [Expr1026]=CONVERT(bit,CASE WHEN [Tantal1].[dbo].[TStudent].[IsBlocked]=(1) OR [Tantal1].[dbo].[TStudent].[IsEntrant]=(1) THEN (1) ELSE CASE WHEN [Tantal1].[dbo].[TStudent].[IsBlocked]=(0) AND [Tantal1].[dbo].[TStudent].[IsEntrant]=(0) THEN (0) ELSE NULL END END,0), [Expr1027]=CONVERT(tinyint,[Tantal1].[dbo].[TStudent].[not_ready],0), [Expr1028]=CONVERT(varchar(100),[Tantal1].[dbo].[TStudent].[WorkInformation],0), [Expr1044]=CONVERT(varchar(100),[Tantal1].[dbo].[TStudent].[Address],0)))
                 |         |         |--Hash Match(Left Outer Join, HASH:([Tantal1].[dbo].[Z].[number])=([Tantal1].[dbo].[TStudent].[StudentNumber]), RESIDUAL:([Tantal1].[dbo].[TStudent].[StudentNumber]=[Tantal1].[dbo].[Z].[number]))
                 |         |              |--Table Scan(OBJECT:([Tantal1].[dbo].[Z]))


остальную часть плана не стал постить, т.к. он уже относится к функции вызываемой в вычисляемом поле и подзапросу, на производительность никакого влияния это не оказывает

конечно использование обычной таблицы в качестве места для временного хранения промежуточных данных может и не есть правильное решение, но это временное решение месяца на 3 - 4, т.к. по мере переноса в новую схему модулей документов студентов и зачеток механизм в том виде, что есть сейчас прекратит свое существование...

если убрать промежуточную таблицу, то выполнение этой процедуры резко увелиться до примерно 6 минут, а если использовать временную таблицу в tempdb, то что с индексом, что без, время возрастает до 7:30 примерно...

а таким образом время выполнение хранимой процедуры в среднем 5 секунд составляет...

если это можно еще улучшить, то буду рад выслушать :)
1 ноя 11, 14:53    [11533372]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104751
Azomorph
такого прироста производительности мне удалось добиться за счет того, что хранимая создает вначале обычную таблицу, а после отработки запроса удаляет ее :)
скидывая данные во временную таблицу, даже индекс не дает прироста производительности
т.е. сам код хранимой процедуры выглядит так:

Что то я не понял
Говорили вроде о временной таблицы для перекачки данных с удаленного сервера
А в вашем варианте зачем то промежуточная таблица под данные из локальной таблицы
1 ноя 11, 14:58    [11533435]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
Azomorph
Member

Откуда:
Сообщений: 300
Glory,

это уже окончательный вариант, а сначала я написал его так же, только данные с прилинкованного загонял в временную таблицу, а потом также джойнил ее с основной, тут различие только в этом было:

if OBJECT_ID(N'tempdb..#ttt) is not null
   drop table #ttt

create table  #ttt(number varchar(9), itog int)

insert #ttt
   select 
	number
	, min(itog_ocenk) as itog 
	from z056 
	group by number
	having min(itog_ocenk) > 0

create nonclustered index idx_ttt_number on #ttt(number

а остальное также, только right join с #ttt

и вот время ее выполнения почему-то оказалось даже медленней чем изначальный запрос, в среднем 7:30
поэтому решил извратиться через локальную таблицу :)
2 ноя 11, 07:36    [11536655]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
Azomorph
Member

Откуда:
Сообщений: 300
Azomorph
Glory,

это уже окончательный вариант, а сначала я написал его так же, только данные с прилинкованного загонял в временную таблицу, а потом также джойнил ее с основной, тут различие только в этом было:

if OBJECT_ID(N'tempdb..#ttt) is not null
   drop table #ttt

create table  #ttt(number varchar(9), itog int)

insert #ttt
   select 
	number
	, min(itog_ocenk) as itog 
	from sbs.TantalM.dbo.z056 
	group by number
	having min(itog_ocenk) > 0

create nonclustered index idx_ttt_number on #ttt(number

а остальное также, только right join с #ttt

и вот время ее выполнения почему-то оказалось даже медленней чем изначальный запрос, в среднем 7:30
поэтому решил извратиться через локальную таблицу :)

сравнивая планы запросов, там в обоих случая при right join идет table scan, но вот производительность разная при этом
в выборку попадает где-то в районе 10000 записей
2 ноя 11, 07:44    [11536665]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
aleks2
Guest
Если ты обучишься правильно создавать индексы - будет ишо быстрее.

Вместо этого фуфла
	create table Z(number nvarchar(9), itog int)
	create nonclustered index idx_Z_number on Z(number)

напиши лучше

declare @Z table(number nvarchar(9) primary key clustered, itog int)
2 ноя 11, 08:02    [11536686]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
Azomorph
Member

Откуда:
Сообщений: 300
aleks2
Если ты обучишься правильно создавать индексы - будет ишо быстрее.

Вместо этого фуфла
	create table Z(number nvarchar(9), itog int)
	create nonclustered index idx_Z_number on Z(number)

напиши лучше

declare @Z table(number nvarchar(9) primary key clustered, itog int)


попробую :)
2 ноя 11, 08:40    [11536776]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
aleks2
Guest
И не забудь убедиться, что типы данных
TStudent.StudentNumber = Z.number 
абсолютно одинаковы. А то неявное приведение типов способно творить чудеса.
2 ноя 11, 09:14    [11536854]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
Azomorph
Member

Откуда:
Сообщений: 300
сделал, но при джойне с основным запросом, он не видит переменную

выдает:
Msg 137, Level 16, State 1, Line 69
Must declare the scalar variable "@t1".

попробовал даже так:

declare @t1 table(number varchar(9) primary key clustered, itog int)
declare @t2 table(number varchar(9) primary key clustered, fio varchar(100), address varchar(100)
	, postind decimal(6, 0), id_cat int, d_come smalldatetime, d_out smalldatetime, idx varchar(3)
	, s_del bit, d_vipis smalldatetime, not_ready tinyint, works varchar(100), num_tarif int
	, remark varchar(1024), rim bit, r bit)
	

insert @t1(number, itog)
		select
			number
			, MIN(itog_ocenk) as itog
		from sbs.TantalM.dbo.Z056
		group by number
		having MIN(itog_ocenk) > 0
		order by number
		
--select * from @t1
		
		
insert @t2(number, fio, address, postind, id_cat, d_come, d_out, idx, s_del, d_vipis, not_ready, works
	, num_tarif, remark, rim, r)
select 
	CAST(TStudent.StudentNumber as varchar(9)) as number
	, CAST((FirstName + ' ' + PatronymicName + ' ' + SurName) as varchar(100)) as fio
	, ISNULL(CAST(dbo.f_GetStudentAddress(TStudent.StudentID) as varchar(100))
	, CAST(TStudent.Address as varchar(100))) as address
	, CAST((case 
				when ISNUMERIC(TStudent.PostalIndex) = 1
				then TStudent.PostalIndex
				else 0 
			 end) as decimal(6, 0)) as postind
	, TStudent.GroupCoursesID as id_cat
	, CAST(TStudent.EnteringDate as smalldatetime) as d_come
	, CAST(TStudent.EndingDate as smalldatetime) as d_out
	, CAST('056' as varchar(3)) as idx
	, CAST((case 
		when 
		 (TStudent.IsBlocked = 1) or (TStudent.IsEntrant = 1)
		then 1
	    when (TStudent.IsBlocked = 0) and (TStudent.IsEntrant = 0)
	    then 0
	  end) as bit) as s_del	
	, CAST(TStudent.DischargeDate as smalldatetime) as d_vipis
	, CAST(TStudent.not_ready as tinyint) as not_ready
	, CAST(TStudent.WorkInformation as varchar(100)) as works
	, CAST(TRates.RateNumber as int) as num_tarif
	, ISNULL(CAST((select TOP(1) Value from TComments where TComments.StudentID = TStudent.StudentID) as varchar(1024)), '') as remark
	, TSpecialty.Active as rim
	, case
		when TStudent.StudentNumber in (select number from sbs.TantalM.dbo.D056 where id_documen in (14, 16))
		then 1
		else 0
		end as r
from TGroupCourses
join TStudentGroup on TStudentGroup.StudentGroupID = TGroupCourses.StudentGroupID
	and TStudentGroup.IsVisibleMail = 1
join TSpecialty on TSpecialty.SpecialtyID = TStudentGroup.SpecialtyID
join TStudent on TGroupCourses.GroupCoursesID = TStudent.GroupCoursesID
left join TRates on TStudent.RateID = TRates.RateID

select * from @t2
right join @t1 on @t2.number = @t1.number
go

выдает:
Msg 137, Level 16, State 1, Line 73
Must declare the scalar variable "@t2".
Msg 137, Level 16, State 1, Line 73
Must declare the scalar variable "@t1".

может туплю с утра, но по идее же должно работать...
2 ноя 11, 09:22    [11536870]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
Azomorph
Member

Откуда:
Сообщений: 300
по отдельности селекты работают без проблем
2 ноя 11, 09:24    [11536877]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31978
Azomorph
right join @t1 on @t2.number = @t1.number
go
выдает:
Msg 137, Level 16, State 1, Line 73
Must declare the scalar variable "@t2".
Msg 137, Level 16, State 1, Line 73
Must declare the scalar variable "@t1".
может туплю с утра, но по идее же должно работать...
Нельзя писать @t1.number

Нужно указывать алиасы.
2 ноя 11, 09:34    [11536926]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
Azomorph
Member

Откуда:
Сообщений: 300
а нее... реально туплю

right join @t1 t on TStudent.StudentNumber = t.number

так работает :)
но только вот время выполнения выходит в районе 5 - 7 минут
2 ноя 11, 09:37    [11536936]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
Glory
Member

Откуда:
Сообщений: 104751
Azomorph
и вот время ее выполнения почему-то оказалось даже медленней чем изначальный запрос, в среднем 7:30
поэтому решил извратиться через локальную таблицу :)

Вы планы то сравниваете при этом?
2 ноя 11, 10:43    [11537386]     Ответить | Цитировать Сообщить модератору
 Re: медленный right join view и таблицы  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
alexeyvg
Azomorph
right join @t1 on @t2.number = @t1.number
go
выдает:
Msg 137, Level 16, State 1, Line 73
Must declare the scalar variable "@t2".
Msg 137, Level 16, State 1, Line 73
Must declare the scalar variable "@t1".
может туплю с утра, но по идее же должно работать...
Нельзя писать @t1.number

Нужно указывать алиасы.
Или
right join @t1 on [@t2].number = [@t1].number
как ни странно...
2 ноя 11, 10:49    [11537445]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить