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

Откуда: Latvija
Сообщений: 145
Задача следующая (SQL 2008) : есть 3 таблицы (PAYMENTS_ECOMM_TRANSACTION_REVERSAL, PAYMENTS_ECOMM и PAYMENTS_bank_request). Все они соединены с одной таблицей PAYMENTS_bank_card_payment по ключу payment_id.
Необходимо выдать отчет о платежах (основные данные хранятся в PAYMENTS_bank_card_payment, в остальных таблицах - дополнительные данные по разным платежным системам), отсортированный по payment_id (именно по этому я использую еще одну временную таблицу) + идет постраничный ввод.
В каждой из таблиц не так уж и много записей -
PAYMENTS_bank_request - 7000 с копейками
PAYMENTS_ECOMM - 300
PAYMENTS_ECOMM_TRANSACTION_REVERSAL - вообще 29
PAYMENTS_bank_card_payment - около 4500

До прошлой недели все нормально работало. И вдруг отчет стал выпадать с ошибкой TimeOut
тут задавала вопрос

Сегодня выяснилось, что при выполнении идет обработка аж 4 млн. строк... (через Profiler). К сожалению, у меня нет прав на использование Profiler - это надо обращаться с просьбой к конкретному человеку, который ОЧЕНЬ редко бывает на месте. Поэтому проследить изменение количества обрабатываемых строк при изменении запроса - сложно.


Буду благодарна за советы по оптимизации запроса.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

create PROCEDURE [dbo].[sp_ReportBankPayment1] 

	@a_Mode tinyint,
	@date_from datetime = null,
	@date_to datetime = null,
	@pf_id int = null,
	@sch_id int = null,
	@bank_name varchar(10) = null,
	@school nvarchar(80) = null,
	@person_first_name nvarchar(80) = null,
	@record_from int = null,
	@record_to int = null,
	@our_payment nvarchar(20) = null,
	@bank_payment nvarchar(5) = null


AS
BEGIN
SET NOCOUNT ON;

IF @pf_id = 0 SET @pf_id = null
IF @sch_id = 0 SET @sch_id = null

DECLARE @visi varchar(1)

DECLARE @bank_count int
SET @bank_count = (SELECT count(*) FROM dbo.PAYMENTS_bank_card_name)

DECLARE @bank_help nvarchar(50)
DECLARE @bank_one varchar(1)


DECLARE @Payments_Temp TABLE
(	
	payment_bank_date varchar(14),
	our_payment_date datetime,
	child_name nvarchar(125),
	person_type nvarchar(20),
	payment_id int,
	our_payment_number varchar(10),
	payment_bank_number varchar(10),
	summa_bez_PVN decimal(18,2),
	PVN decimal(18,2),
	summa_ar_PVN decimal(18,2),
	payment_details nvarchar(125),
	school nvarchar(100),
	bank_name nvarchar(50),
	ecomm_category int,
	success_payment bit	
)

DECLARE @Payments_Temp1 TABLE
(	
	id int identity(1,1),
	payment_bank_date varchar(14),
	our_payment_date datetime,
	child_name nvarchar(125),
	person_type nvarchar(20),
	payment_id int,
	our_payment_number varchar(10),
	payment_bank_number varchar(10),
	summa_bez_PVN decimal(18,2),
	PVN decimal(18,2),
	summa_ar_PVN decimal(18,2),
	payment_details nvarchar(125),
	school nvarchar(100),
	bank_name nvarchar(50),
	ecomm_category int,
	count_record int,
	all_bez_PVN decimal (18,2),
	all_pvn decimal(18,2),
	all_ar_pvn decimal(18,2),
	success_payment bit	
)
           INSERT INTO @Payments_Temp (payment_bank_date, our_payment_date, child_name, person_type, payment_id,
		our_payment_number, payment_bank_number, summa_bez_PVN, PVN, summa_ar_PVN, payment_details,
		school, bank_name, ecomm_category, success_payment)
	SELECT
		CASE	WHEN pay.success_payment = 1 and req1.request_statuss  = 'MANUAL' 
				THEN convert(varchar, pay.payment_date, 104) + ' (m)'
				ELSE convert(varchar, pay.payment_date, 104) END as payment_bank_date,
		req.request_date as our_payment_date, 
		child.pf_first_name + ' ' + child.pf_last_name + ' (p/k ' + person.person_identification_number + ')'  as child_name, 
		CASE 
			WHEN parent.cpt_id = 4 THEN 'Father'
			WHEN parent.cpt_id = 5 THEN 'Mother'
			WHEN parent.cpt_id = 6 THEN 'Other person'
		END as person_type, 
		pay.payment_id as payment_id,		
		convert(varchar, pay.payment_id) + '/ek' as our_payment_number,
		CASE WHEN payment_bank_number = '' THEN '-' 
ELSE payment_bank_number END as payment_bank_number,
		(convert(decimal(18,2), pay.payment_summa) / 1.21) as summa_bez_pvn, 
		(convert(decimal(18,2), pay.payment_summa) / 1.21) * 0.21 as PVN,
		convert(decimal(18,4), pay.payment_summa) as summa_ar_pvn,
		CASE WHEN LEN(payment_details) = 0 THEN '-' ELSE SUBSTRING(payment_details, CHARINDEX(',', payment_details) + 2, 
LEN(payment_details)-CHARINDEX(',', payment_details)) END as payment_details,
		school.sch_name as school,
		pay.bank_full_name as bank_name,
		0,
		pay.success_payment
	FROM PAYMENTS_bank_card_payment pay 
	inner join PAYMENTS_bank_request req on req.Payment_Id = pay.Payment_Id and (req.request_type = '1002' or req.request_type = '0002')
	left outer join PAYMENTS_bank_request req1 on req1.Payment_Id = pay.Payment_Id and (req.request_type = '1101' or req.request_type = '0004')
	inner join personal_file parent on parent.pf_id = req.pf_id
	inner join personal_file child on child.pf_id = parent.pf_id_child
	inner join school school on school.sch_id = child.sch_id
	inner join person person on person.person_id = child.person_id
	WHERE 
		@date_from >= pay.payment_date and pay.payment_date < (@date_to+1)
		and
		(@pf_id is null or child.pf_id = @pf_id)
		and
		(@sch_id is null or school.sch_id = @sch_id)
		and
		(@our_payment is null or convert(nvarchar, pay.payment_id) = @our_payment)
		and 
		(@bank_payment is null or pay.payment_bank_number = @bank_payment)

	INSERT INTO @Payments_Temp (payment_bank_date, our_payment_date, child_name, person_type, payment_id,
		our_payment_number, payment_bank_number, summa_bez_PVN, PVN, summa_ar_PVN, payment_details,
		school, bank_name, ecomm_category, success_payment)
	SELECT 
		CASE WHEN ecomm.cpets_id = 5 THEN convert(varchar(10), payment_date, 104) + ' (m)'
		ELSE convert(varchar(10), payment_date, 104) END as payment_bank_date, 
		ecomm.ec_date as our_payment_date, 
		child.pf_first_name + ' ' + child.pf_last_name + ' (p/k ' + person.person_identification_number + ')'  as child_name, 
		CASE 
			WHEN parent.cpt_id = 4 THEN 'Father'
			WHEN parent.cpt_id = 5 THEN 'Mother'
			WHEN parent.cpt_id = 6 THEN 'Other person'
		END as person_type, 
		pay.payment_id as payment_id,
		convert(varchar, pay.payment_id) + '/ek' as our_payment_number, 
		'-' as payment_bank_number,
		(convert(decimal(18,2), pay.payment_summa) / 1.21) as summa_bez_pvn, 
		(convert(decimal(18,2), pay.payment_summa) / 1.21) * 0.21 as PVN,
		convert(decimal(18,4), pay.payment_summa) as summa_ar_pvn,
		CASE
			WHEN LEN(payment_details) = 0 THEN '-'
			WHEN ecomm.cpets_id <> 1 and LEN(payment_details) > 0 THEN payment_details
			WHEN ecomm.cpets_id = 1 and LEN(payment_details) > 0 
THEN SUBSTRING(payment_details, CHARINDEX(',', payment_details) + 2, LEN(payment_details)-CHARINDEX(',', payment_details)) 
END as payment_details,
		school.sch_name as school,
		pay.bank_full_name as bank_name,
		ecomm_category.cpets_id as ecomm_category,
		pay.success_payment
	FROM PAYMENTS_bank_card_payment pay 
	inner join PAYMENTS_ECOMM ecomm on ecomm.payment_Id = pay.payment_Id
	inner join personal_file parent on parent.pf_id = ecomm.pf_id
	inner join personal_file child on child.pf_id = parent.pf_id_child
	inner join school school on school.sch_id = child.sch_id
	inner join person person on person.person_id = child.person_id
	inner join dbo.CATEGORY_PAYMENTS_ECOMM_TRANSACTION_STATUS ecomm_category ON ecomm_category.cpets_id = ecomm.cpets_id

	WHERE 
		@date_from >= pay.payment_date and pay.payment_date < (@date_to+1)
		and
		(@pf_id is null or child.pf_id = @pf_id)
		and
		(@sch_id is null or school.sch_id = @sch_id)
		and
		(@our_payment is null or convert(nvarchar, pay.payment_id) = @our_payment)
		and 
		(@bank_payment is null or pay.payment_bank_number = @bank_payment)


	INSERT INTO @Payments_Temp (payment_bank_date, our_payment_date, child_name, person_type, payment_id,
		our_payment_number, payment_bank_number, summa_bez_PVN, PVN, summa_ar_PVN, payment_details,
		school, bank_name, ecomm_category)
	SELECT
		convert(varchar(10), pay.payment_date, 104) as payment_bank_date, 
		ecomm_reversal.pert_datetime as our_payment_date, 
		child.pf_first_name + ' ' + child.pf_last_name + ' (p/k ' + person.person_identification_number + ')'  as child_name, 
		CASE 
			WHEN parent.cpt_id = 4 THEN 'Father'
			WHEN parent.cpt_id = 5 THEN 'Mother'
			WHEN parent.cpt_id = 6 THEN 'Other person'
		END as person_type, 
		pay.payment_id as payment_id,
		'rev/' + convert(varchar, old_pay.payment_id) as our_payment_number, 	
		'-' as payment_bank_number,
		(convert(decimal(18,2), pay.payment_summa) / 1.21) as summa_bez_pvn, 
		(convert(decimal(18,2), pay.payment_summa) / 1.21) * 0.21 as PVN,
		convert(decimal(18,4), pay.payment_summa) as summa_ar_pvn,
		CASE WHEN LEN(pay.payment_details) = 0 THEN '-' ELSE pay.payment_details END as payment_details,
		school.sch_name as school,
		pay.bank_full_name as bank_name,
		0
	FROM PAYMENTS_bank_card_payment pay 
	inner join PAYMENTS_ECOMM_TRANSACTION_REVERSAL ecomm_reversal on ecomm_reversal.payment_id = pay.payment_id
	inner join PAYMENTS_ECOMM ecomm on ecomm.petr_id = ecomm_reversal.petr_id
	inner join PAYMENTS_bank_card_payment old_pay on old_pay.payment_id = ecomm.payment_id
	inner join personal_file parent on parent.pf_id = ecomm.pf_id
	inner join personal_file child on child.pf_id = parent.pf_id_child
	inner join school school on school.sch_id = child.sch_id
	inner join person person on person.person_id = child.person_id

	WHERE 
		@date_from >= pay.payment_date and pay.payment_date < (@date_to+1)
		and
		(@pf_id is null or child.pf_id = @pf_id)
		and
		(@sch_id is null or school.sch_id = @sch_id)
		and pay.success_payment = 1
		and
		(@our_payment is null or convert(nvarchar, pay.payment_id) = @our_payment)
		and 
		(@bank_payment is null or pay.payment_bank_number = @bank_payment)

DECLARE @count_record int
SET @count_record = (select COUNT(*) from @Payments_Temp)

DECLARE @all_bez_pvn decimal(18,2)
DECLARE @all_pvn decimal(18,2)
DECLARE @all_ar_pvn decimal(18,2)

SET @all_bez_pvn = (SELECT SUM(summa_bez_PVN) from @Payments_Temp where success_payment = 1)
SET @all_pvn = (SELECT SUM(PVN) from @Payments_Temp where success_payment = 1)
SET @all_ar_pvn = (SELECT SUM(summa_ar_PVN) from @Payments_Temp where success_payment = 1)


INSERT INTO @Payments_Temp1 (payment_bank_date, our_payment_date, child_name, person_type, payment_id,
			our_payment_number, payment_bank_number, summa_bez_PVN, PVN, summa_ar_PVN, payment_details,
			school, bank_name, ecomm_category, success_payment)
SELECT payment_bank_date, our_payment_date, child_name, person_type, payment_id,
			our_payment_number, payment_bank_number, summa_bez_PVN, PVN, summa_ar_PVN, payment_details,
			school, bank_name, ecomm_category, success_payment FROM @Payments_Temp order by payment_id

select 
	@count_record as count_record,
	CASE WHEN @all_bez_pvn is null THEN 0 ELSE @all_bez_pvn END as all_bez_pvn,
	CASE WHEN @all_pvn is null THEN 0 ELSE @all_pvn END as all_pvn,
	CASE WHEN @all_ar_pvn is null THEN 0 ELSE @all_ar_pvn END as all_ar_pvn
	 
select * from @Payments_Temp1 
where  (@record_from is null and @record_to is null) or (id between @record_from and @record_to)

END
21 дек 09, 15:44    [8096424]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
Sergey S
Member

Откуда: Киев
Сообщений: 215
Может ошибаюсь, но попробуй использовать на своих объемах данных не переменные табличные а временные таблицы, и без всяких лишних варчаровских полей, по возможности флагами, и тока в конце обработай кэйсами в последнем селекте эти все флаги
21 дек 09, 16:00    [8096587]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
iljy
Member

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

планы покажите. И про временные таблицы мысль хорошая, но зависит от количества реально выбираемых в них записей.
21 дек 09, 16:03    [8096617]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
Sergey S
не переменные табличные а временные таблицы

Немного не поняла этот момент. Можно пояснить?
21 дек 09, 16:03    [8096623]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
iljy
Naile,
планы покажите.


Дело в том, что работаем в тестовой системе, а спец. человечек после тщательной проверки переносит все в реальную систему. Так вот -в тестовой системе все работает, но там записей на порядок меньше. И план я могу предоставить только из тестовой системы. Подойдет? Или будет неинформативно?
21 дек 09, 16:15    [8096742]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
Sergey S
Member

Откуда: Киев
Сообщений: 215
НЕ
DECLARE @Payments_Temp TABLE
(
...
)

А
CREATE TABLE #Payments_Temp (...)
...
DROP TABLE #Payments_Temp

вроде-бы на больших объемах таблицы работают быстрее
21 дек 09, 16:31    [8096886]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
Sergey S
Member

Откуда: Киев
Сообщений: 215
Sergey S,

ну и с индексами потом пожно поэксперементировать
21 дек 09, 16:33    [8096902]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
Sergey S
А, спасибо. Поняла...

Ну а откуда может быть такое мега-количество обрабатываемых строк при запросе?
21 дек 09, 16:42    [8096974]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
Вернее не количество обрабатываемых строк, а количество Read операций при выполнении запроса.
21 дек 09, 16:45    [8096998]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
Glory
Member

Откуда:
Сообщений: 104760
Naile

Сегодня выяснилось, что при выполнении идет обработка аж 4 млн. строк... (через Profiler).

Profiler показывает число обращений на чтений/запись, а не число обрабатываемых строк
21 дек 09, 16:46    [8097007]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
Glory,
Да, прощу прощения. Я уже поправилась в предыдущей записи.
21 дек 09, 16:49    [8097031]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
Glory
Member

Откуда:
Сообщений: 104760
Naile
Glory,
Да, прощу прощения. Я уже поправилась в предыдущей записи.

Увеличение числа Чтений может говорить об изменении плана запроса
А план может поменяться по многим причинам.
21 дек 09, 16:53    [8097064]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
aleks2
Guest
За одно это
 convert(nvarchar, pay.payment_id) = @our_payment)
надо бессрочно ссылать в уборщицы.

1. Уменьшите количество таблиц в соединении до АБСОЛЮТНО НЕОБХОДИМОГО минимума при вставке во временную таблицу, только шоб фильтры применить.
2. Прочие атрибуты из ОСТАЛЬНЫХ таблиц - добавите UPDATE-ом.
3. Сделайте кластерный индекс по payment_id.
4. Нахрен при нумерации строк копировать таблицу целиком?

5. ГЛАВНОЕ: выборка миллиона строк ради десятка нужных - существует ли больший идиотизм?
(id between @record_from and @record_to)
21 дек 09, 18:08    [8097646]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
aleks2
Огромное спасибо за конкретные указания. Всю критику принимаю. :)

aleks2
5. ГЛАВНОЕ: выборка миллиона строк ради десятка нужных - существует ли больший идиотизм?

Самой это очень не нравится. Но... - y меня идет постраничный вывод данных из запроса. Но, так как, данные сначало надо отсортировать по payment_id (поскольку туда идут данные из трех таблиц, и запросто сначало могут идти 3 записи из первой, потом 1 запись из второй, 1 запись из третьей и опять 5 записей из первой таблицы - это уже в отсотрованном виде), я их ВСЕ закидываю во временную таблицу, попутно делаю order by и делаю такую выборку.
Буду благодарна за подсказку более умного решения данной проблемы.
21 дек 09, 18:28    [8097772]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
iljy
Member

Откуда:
Сообщений: 8711
Naile

Дело в том, что работаем в тестовой системе, а спец. человечек после тщательной проверки переносит все в реальную систему. Так вот -в тестовой системе все работает, но там записей на порядок меньше. И план я могу предоставить только из тестовой системы. Подойдет? Или будет неинформативно?

Будет неинформативно. Из общих соображений могу посоветовать примерно так:
1. Раз у вас идет постраничный вывод - лучше разбить процедуру на 2 части: формирование выборки и собственно ее постраничная выдача. Т.е. процедур получается 2, перед входом создаете временную таблицу (обязательно создайте на ней кластерный индекс по id!), которую заполняете в первой процедуре, а затем выдергиваете из нее записи в нужном диапазоне с помощью второй.
2. Временную таблицу заполняйте только необходимым минимумом полей, остальные присоедините непосредственно при постраничной выборке.
3. По мелочи:
3.1
SET @count_record = (select COUNT(*) from @Payments_Temp)
SET @all_bez_pvn = (SELECT SUM(summa_bez_PVN) from @Payments_Temp where success_payment = 1)
SET @all_pvn = (SELECT SUM(PVN) from @Payments_Temp where success_payment = 1)
SET @all_ar_pvn = (SELECT SUM(summa_ar_PVN) from @Payments_Temp where success_payment = 1)
это жестоко по отношению к серверу!
select @count_record = COUNT(*),
      @all_bez_pvn = SUM(case success_payment when 1 then summa_bez_PVN end),
      @all_pvn = SUM(case success_payment when 1 then PVN end),
      @all_ar_pvn = SUM(case success_payment when 1 then summa_ar_PVN end)
from @Payments_Temp
3.2
select 
	@count_record as count_record,
	CASE WHEN @all_bez_pvn is null THEN 0 ELSE @all_bez_pvn END as all_bez_pvn,
	CASE WHEN @all_pvn is null THEN 0 ELSE @all_pvn END as all_pvn,
	CASE WHEN @all_ar_pvn is null THEN 0 ELSE @all_ar_pvn END as all_ar_pvn
а это жестоко по отношению к себе
select isnull(@count_record, 0) as count_record,
	isnull(@all_bez_pvn,0) as all_bez_pvn,
	isnull(@all_pvn,0) as all_pvn,
	isnull(@all_ar_pvn,0) as all_ar_pvn
[/SRC]
21 дек 09, 19:11    [8097942]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
iljy
Member

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

хотя вообще я думаю будет быстрее сделать ОДНУ временную таблицу (вне процедуры), с кластерным индексом по id (причем identity его делать не надо!), и в процедуре вставлять в нее данные запросом
insert into #Payments_Temp (id,....)
select ROW_NUMBER() over(order by payments_id), ... from
(
   первый селект
     union all
   второй селект
)
возможностей для оптимизации такая схема оставляет гораздо больше.
И если честно - мне вообще непонятно, откуда у вас проблемы при таком количестве записей.
Naile

PAYMENTS_bank_request - 7000 с копейками
PAYMENTS_ECOMM - 300
PAYMENTS_ECOMM_TRANSACTION_REVERSAL - вообще 29
PAYMENTS_bank_card_payment - около 4500

Если метод пристального взгляда не сработает - надо таки смотреть планы на рабочей базе и разбираться уже предметно.
21 дек 09, 19:42    [8098033]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
iljy
Member

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

ну и так, еще мысль вслух. Так
select * from @Payments_Temp1 
where  (@record_from is null and @record_to is null) or (id between @record_from and @record_to)
делать совсем не нужно. Лучше как-то так
select * from @Payments_Temp1 
where  id between isnull(@record_from,1) and isnull(@record_to, @count_record)
первый вариант напрочь лишает оптимизатор возможности применить индекс по id. К тому же второй вариант дает возможность указывать только одну границу диапазона если вдруг это интересно:)
21 дек 09, 20:02    [8098083]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
Naile
Member

Откуда: Latvija
Сообщений: 145
iljy,
Огромнейшее спасибо!!!
Завтра на работе все перепробую.
22 дек 09, 00:11    [8098809]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
aleks2
Guest
iljy
Naile,

ну и так, еще мысль вслух. Так
select * from @Payments_Temp1 
where  (@record_from is null and @record_to is null) or (id between @record_from and @record_to)
делать совсем не нужно. Лучше как-то так
select * from @Payments_Temp1 
where  id between isnull(@record_from,1) and isnull(@record_to, @count_record)
первый вариант напрочь лишает оптимизатор возможности применить индекс по id. К тому же второй вариант дает возможность указывать только одну границу диапазона если вдруг это интересно:)


Не фантазируй. Лучше сникерсни... тьфу... попробуй.
Тута все хорошо.
22 дек 09, 06:36    [8099034]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
aleks2
Guest
iljy
Naile,

хотя вообще я думаю будет быстрее сделать ОДНУ временную таблицу (вне процедуры), с кластерным индексом по id (причем identity его делать не надо!), и в процедуре вставлять в нее данные запросом
insert into #Payments_Temp (id,....)
select ROW_NUMBER() over(order by payments_id), ... from
(
   первый селект
     union all
   второй селект
)
возможностей для оптимизации такая схема оставляет гораздо больше.
И если честно - мне вообще непонятно, откуда у вас проблемы при таком количестве записей.

UNION не даст НИКАКОГО выыгрыша в сравнении с двумя инсертами. Можно даж не париться.
22 дек 09, 06:37    [8099035]     Ответить | Цитировать Сообщить модератору
 Re: Нужна помощь по оптимизации запроса  [new]
iljy
Member

Откуда:
Сообщений: 8711
aleks2

Не фантазируй. Лучше сникерсни... тьфу... попробуй.
Тута все хорошо.

Вот хоть раз сам бы попробовал...
+
create table T (id int primary key)
insert into T
select top 1000 ROW_NUMBER() over(order by (select 1))
from master..spt_values

declare @a int, @b int
select * from T
where (@a is null and @b is null) or id between @a and @b

select * from T
where id between isnull(@a,1) and isnull(@b, 2000)

select * from T
where (@a is null and @b is null) or id between @a and @b
--------------------------------------------------------------------------------------------------
|--Clustered Index Scan(OBJECT:([tempdb].[dbo].[T].[PK__T__3213E83F0425A276]), WHERE:([@a]
IS NULL AND [@b] IS NULL OR [tempdb].[dbo].[T].[id]>=[@a] AND [tempdb].[dbo].[T].[id]<=[@b]))

select * from T
where id between isnull(@a,1) and isnull(@b, 2000)
----------------------------------------------------------------------------------------------------
|--Clustered Index Seek(OBJECT:([tempdb].[dbo].[T].[PK__T__3213E83F0425A276]), SEEK:([tempdb].
[dbo].[T].[id] >= isnull([@a],(1)) AND [tempdb].[dbo].[T].[id] <= isnull([@b],(2000))) ORDERED FORWARD)
aleks2
UNION не даст НИКАКОГО выыгрыша в сравнении с двумя инсертами. Можно даж не париться.

В случае 2х инсертов:
1. Вставка в таблицу раз (N записей)
2. Вставка в таблицу два (M записей)
3. Выборка из таблицы с сортировкой N+M записей и вставка
3.1 Вообще говоря операция INSERT ... SELECT ... ORDER BY нигде не документирована (гуру, поправьте меня если ошибаюсь, но я нигде в БОЛ не нашел описания, что она работает именно так, как ожидается).
В случае с UNION:
1. Поскольку связь между таблицами идет по полю payment_id - высока вероятность, что по нему есть индекс, и соответственно для каждой выборки отдельно сортировка не нужна, а если и нужна - сортируются M и N записей отдельно.
2. Отсортированные наборы сливаются через Merge Concat и для них генерируются номера ROW_NUMBER. Этот способ 100% документированный.
И я не говорил, что этот способ заведомо выигрышнее, но уж не проигрышнее точно, а потенциал оптимизации гораздо больше. Так что единственное, ради чего тут может понадобиться промежуточная временная таблица - если оптимизатор не прожует объединенный запрос.
Так что поздравляю Вас, гражданин, в очередной раз соврамши
22 дек 09, 10:10    [8099446]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить