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

Откуда:
Сообщений: 111
Товарищи.... как можно оптимизировать эту штуку? А то на тестовой базе работает 22 сек боюсь на боевой будет работать по нескольку минут(((( MS SQL 2005

select distinct
EO.ShortName as 'Сокращенное наименование', 
EO.FullName as 'Полное наименование',
(select count (*) from PrimaryEducationDocuments where EduOrgId = EO.Id) as 'Количество документов, связанных с ОУ',
(select count (*) from PrimaryEducationDocuments where EduOrgId = EO.Id and EducationOrganizationName = EO.FullName) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием ОУ', 
(select count (*) from PrimaryEducationDocuments where EduOrgId = EO.Id and EducationOrganizationName = EO.ShortName) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с сокращенным наименованием ОУ',
(select count (*) from PrimaryEducationDocuments where EduOrgId = EO.Id and EducationOrganizationName in (select FullName from EducationOrganization where Id != EO.Id)) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием других ОУ',
(select count (*) from PrimaryEducationDocuments where EduOrgId = EO.Id and EducationOrganizationName in (select ShortName from EducationOrganization where Id != EO.Id)) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с сокращенным наименованием других ОУ',
(select count (*) from EducationOrganization where Id != EO.ID and FullName in (
select EducationOrganizationName from PrimaryEducationDocuments where EduOrgId = EO.ID))as 'Количество других ОУ, с полными наименованиями которых совпадает наименование ОУ, выдавшего связанные документы',
(select count (*) from EducationOrganization where Id != EO.ID and ShortName in (
select EducationOrganizationName from PrimaryEducationDocuments where EduOrgId = EO.ID))
as 'Количество других ОУ, с сокращенными наименованиями которых совпадает наименование ОУ, выдавшего связанные документы'
from EducationOrganization EO
join dbo.PrimaryEducationDocuments PED on EO.Id = PED.EduOrgId
11 сен 13, 18:15    [14827923]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
aleks2
Guest
Захерачить каждый подзапрос как Indexed View и иметь искомое щастье...
11 сен 13, 18:19    [14827942]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
lynatik50
Member

Откуда:
Сообщений: 111
aleks2
Захерачить каждый подзапрос как Indexed View и иметь искомое щастье...


А в коде самом как нибудь можно оптимизировать?
11 сен 13, 18:25    [14827968]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
lynatik50
Member

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

Индексы есть
11 сен 13, 18:30    [14828004]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
Гость333
Member

Откуда:
Сообщений: 3683
lynatik50
А в коде самом как нибудь можно оптимизировать?

В качестве затравки: вот этот ужас
select distinct
EO.ShortName as 'Сокращенное наименование', 
EO.FullName as 'Полное наименование',
(select count (*) from PrimaryEducationDocuments where EduOrgId = EO.Id) as 'Количество документов, связанных с ОУ',
(select count (*) from PrimaryEducationDocuments where EduOrgId = EO.Id and EducationOrganizationName = EO.FullName) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием ОУ', 
...
from EducationOrganization EO
join dbo.PrimaryEducationDocuments PED on EO.Id = PED.EduOrgId

заменяем таким:
select
   EO.ShortName as 'Сокращенное наименование', 
   EO.FullName as 'Полное наименование',
   count(*) as 'Количество документов, связанных с ОУ',
   sum(case when PED.EducationOrganizationName = EO.FullName then 1 else 0 end) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием ОУ', 
   ...
from EducationOrganization EO
     join dbo.PrimaryEducationDocuments PED on EO.Id = PED.EduOrgId
group by
   EO.ShortName,
   EO.FullName
11 сен 13, 18:31    [14828012]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
lynatik50
Member

Откуда:
Сообщений: 111
Гость333,

ок, спасибо, а с остальныб безобразием что сделать?
11 сен 13, 18:51    [14828086]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
Алексей Куренков
Member [заблокирован]

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

outer apply + count(case EducationOrganizationName ...... end) поможет Вам ))
12 сен 13, 09:55    [14829430]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
Glory
Member

Откуда:
Сообщений: 104760
lynatik50
а с остальныб безобразием что сделать?

Добавить по образу и подобию
 sum(case when PED.EducationOrganizationName = EO.FullName then 1 else 0 end) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием ОУ', 
12 сен 13, 09:56    [14829435]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
Glory
Member

Откуда:
Сообщений: 104760
Алексей Куренков
outer apply + count(case EducationOrganizationName ...... end) поможет Вам ))

И чем это будет отличаться от начального варианта ?
12 сен 13, 09:56    [14829436]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
Алексей Куренков
Member [заблокирован]

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

Ну я вижу двигаться можно например в таком направлении:

select distinct
EO.ShortName as 'Сокращенное наименование', 
EO.FullName as 'Полное наименование',
'Количество документов, связанных с ОУ',
'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием ОУ',
'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с сокращенным наименованием ОУ',
'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием других ОУ',
'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с сокращенным наименованием других ОУ',

(select count (*) from EducationOrganization where Id != EO.ID and FullName in (
select EducationOrganizationName from PrimaryEducationDocuments where EduOrgId = EO.ID))as 'Количество других ОУ, с полными наименованиями которых совпадает наименование ОУ, выдавшего связанные документы',
(select count (*) from EducationOrganization where Id != EO.ID and ShortName in (
select EducationOrganizationName from PrimaryEducationDocuments where EduOrgId = EO.ID))
as 'Количество других ОУ, с сокращенными наименованиями которых совпадает наименование ОУ, выдавшего связанные документы'
from EducationOrganization EO
join dbo.PrimaryEducationDocuments PED on EO.Id = PED.EduOrgId
outer apply
(
	select
		count(*) as 'Количество документов, связанных с ОУ',
		count(case when EducationOrganizationName = EO.FullName then '*' else null) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием ОУ',
		count(case when EducationOrganizationName = EO.ShortName then '*' else null) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с сокращенным наименованием ОУ',
		count(case when EducationOrganizationName in (select FullName from EducationOrganization where Id != EO.Id)else null) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием других ОУ',
		count(case when EducationOrganizationName in (select ShortName from EducationOrganization where Id != EO.Id) then '*' else null) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с сокращенным наименованием других ОУ',
		count(case when EducationOrganizationName = EO.FullName then '*' else null)  as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием ОУ',
	from PrimaryEducationDocuments
	where EduOrgId = EO.Id
) oa
12 сен 13, 10:06    [14829481]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
Алексей Куренков
Member [заблокирован]

Откуда: Москва
Сообщений: 567
Алексей Куренков,

Сори за синтаксическую ошибку )))

case when EducationOrganizationName = EO.FullName then '*' else null

исправляем на
case when EducationOrganizationName = EO.FullName then '*' else null end
12 сен 13, 10:08    [14829488]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
Glory
Member

Откуда:
Сообщений: 104760
Алексей Куренков
Ну я вижу двигаться можно например в таком направлении:

Ваш вариант не сильно отличается от способа ТС.
100500 обращений к PrimaryEducationDocuments вместо 1-го
12 сен 13, 10:17    [14829555]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
Алексей Куренков
Member [заблокирован]

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

Вопрос спорный... так вместо нескольких сиков сканов делаем в аутер апплэй один... В моей практики такие решения часто помогали... Был подобный топик со схожей проблемой... помогло же ))))
12 сен 13, 11:32    [14830165]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
aleks2
Guest
lynatik50
Товарищи.... как можно оптимизировать эту штуку? А то на тестовой базе работает 22 сек боюсь на боевой будет работать по нескольку минут(((( MS SQL 2005

select distinct
EO.ShortName as 'Сокращенное наименование', 
EO.FullName as 'Полное наименование',
---
from EducationOrganization EO
join dbo.PrimaryEducationDocuments PED on EO.Id = PED.EduOrgId


О чем думают здешние знатоки?

Начнем с вопроса ребром: нафега здесь JOIN и distinct? Шоб серверу стало плохо?

select 
EO.ShortName as 'Сокращенное наименование', 
EO.FullName as 'Полное наименование',
---
from EducationOrganization EO
where exists ( select * from dbo.PrimaryEducationDocuments PED where EO.Id = PED.EduOrgId)

Причем при наличии индекса (EduOrgId) на dbo.PrimaryEducationDocuments это даст космическое ускорение... и другим подзапросам тоже.
12 сен 13, 11:41    [14830238]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
lynatik50
Member

Откуда:
Сообщений: 111
Glory
lynatik50
а с остальныб безобразием что сделать?

Добавить по образу и подобию
 sum(case when PED.EducationOrganizationName = EO.FullName then 1 else 0 end) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием ОУ', 


Да я понимаю, но в таком варианте он выдаёт ошибку

select
sum(case when PED.EducationOrganizationName in (select FullName from EducationOrganization where Id != EO.Id) then 1 else 0 end)
from EducationOrganization EO
join dbo.PrimaryEducationDocuments PED on EO.Id = PED.EduOrgId



Ошибка: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.
12 сен 13, 12:22    [14830572]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
Glory
Member

Откуда:
Сообщений: 104760
lynatik50
Да я понимаю, но в таком варианте он выдаёт ошибку

Как раз не понимаете.
Вам показали, как вынести ваши подзапросы из столбцов в join-ы
А вы опять их лепите в столбцы
12 сен 13, 12:25    [14830593]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
lynatik50
Member

Откуда:
Сообщений: 111
Glory
lynatik50
Да я понимаю, но в таком варианте он выдаёт ошибку

Как раз не понимаете.
Вам показали, как вынести ваши подзапросы из столбцов в join-ы
А вы опять их лепите в столбцы


Где они выносятся в join-ы? тут?

select distinct
EO.ShortName as 'Сокращенное наименование', 
EO.FullName as 'Полное наименование',
'Количество документов, связанных с ОУ',
'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием ОУ',
'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с сокращенным наименованием ОУ',
'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием других ОУ',
'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с сокращенным наименованием других ОУ',

(select count (*) from EducationOrganization where Id != EO.ID and FullName in (
select EducationOrganizationName from PrimaryEducationDocuments where EduOrgId = EO.ID))as 'Количество других ОУ, с полными наименованиями которых совпадает наименование ОУ, выдавшего связанные документы',
(select count (*) from EducationOrganization where Id != EO.ID and ShortName in (
select EducationOrganizationName from PrimaryEducationDocuments where EduOrgId = EO.ID))
as 'Количество других ОУ, с сокращенными наименованиями которых совпадает наименование ОУ, выдавшего связанные документы'
from EducationOrganization EO
join dbo.PrimaryEducationDocuments PED on EO.Id = PED.EduOrgId
outer apply
(
	select
		count(*) as 'Количество документов, связанных с ОУ',
		count(case when EducationOrganizationName = EO.FullName then '*' else null) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием ОУ',
		count(case when EducationOrganizationName = EO.ShortName then '*' else null) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с сокращенным наименованием ОУ',
		count(case when EducationOrganizationName in (select FullName from EducationOrganization where Id != EO.Id)else null) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием других ОУ',
		count(case when EducationOrganizationName in (select ShortName from EducationOrganization where Id != EO.Id) then '*' else null) as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с сокращенным наименованием других ОУ',
		count(case when EducationOrganizationName = EO.FullName then '*' else null)  as 'Количество документов, связанных с ОУ, у которых наименование ОУ их выдавших совпадает с полным наименованием ОУ',
	from PrimaryEducationDocuments
	where EduOrgId = EO.Id
) oa



Тут возникает такая же ошибка Ошибка: Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

и ругается он на вот этот момент

count(case when EducationOrganizationName in (select ShortName from EducationOrganization where Id != EO.Id) then '*' else null end)

ему не нравится попытка сравнить EducationOrganizationName с целой выборкой
12 сен 13, 14:04    [14831369]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
count(case when EducationOrganizationName in (select FullName from EducationOrganization where Id != EO.Id)else null)


Где вы вообще такой синтаксис то надыбали для case ?
12 сен 13, 14:34    [14831565]     Ответить | Цитировать Сообщить модератору
 Re: Опимизация  [new]
Glory
Member

Откуда:
Сообщений: 104760
lynatik50
Где они выносятся в join-ы

вот тут 14828012
12 сен 13, 14:39    [14831593]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить