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

Откуда:
Сообщений: 7
Задача: есть условно 3 таблицы - Document, Image, Section (Документ, Изображение, Раздел, соответственно).

Картинка с другого сайта.

Необходимо вывести количество изображений и разделов для всех документов. Что-то, типа, краткой информации о документе.
Решить задачу можно с помощью подзапросов в FROM, например, так:

SELECT  Doc.DocumentId, ImageCounter.ImageCount, SectionCounter.SectionCount
FROM Document Doc,
	(
		SELECT d.DocumentId, Count(i.ImageId) as 'ImageCount'	 
		from Document d
		LEFT JOIN Document_Image di	ON d.DocumentId = di.DocumentId
		LEFT JOIN Image i			ON di.ImageId = i.ImageId
	) ImageCounter,
	(
		SELECT d.DocumentId, Count(s.ImageId) as 'SectionCount'	 
		from Document d
		LEFT JOIN Document_Section ds	ON d.DocumentId = ds.DocumentId
		LEFT JOIN Section s				ON ds.SectionId = s.SectionId
	) SectionCounter	
where
		Doc.DocumentId = ImageCounter.DocumentId and
		Doc.DocumentId = SectionCounter.DocumentId


...но что-то подсказывает мне, что есть решение более изящное, корректное в плане производительности. Что по этому поводу думаете? Буду рад выслушать ваши предложения.
5 сен 13, 12:08    [14798555]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
Glory
Member

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

убрать Document d из подзапросов
LEFT JOIN заменить на INNER JOIN

Сообщение было отредактировано: 5 сен 13, 12:11
5 сен 13, 12:10    [14798575]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
disharmonized
Member

Откуда:
Сообщений: 7
Ах, да, забыл еще Group by добавить в подзапросы.
5 сен 13, 12:12    [14798592]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
zxc1257
Member

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

если документов много, то так

select di.documentid, count(distinct di.imageid), count(distinct ds.sectionid)
from
	document_image di
		inner join
	document_section ds on di.documentid = ds.documentid
group by di.documentid


если документов мало, то лучше так
5 сен 13, 12:33    [14798826]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
zxc1257
Member

Откуда:
Сообщений: 71
если документов мало, то лучше можно так

select t1.documentid, t1.img_cnt, t2.sec_cnt
from
(
	select di.documentid, count(1) as img_cnt
	from
		document_image di
	group by di.documentid
) t1
	inner join
(
	select ds.documentid, count(1) as sec_cnt
	from
		document_section ds
	group by ds.documentid
) t2 on t1.documentid = t2.documentid
5 сен 13, 12:39    [14798902]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
Glory
Member

Откуда:
Сообщений: 104751
zxc1257
если документов мало, то лучше можно так

В эти запросы попадут документы, у которых есть хотя бы один Image И Section
5 сен 13, 12:47    [14798974]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
zxc1257
Member

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

такое сервер сможет хорошо распараллелить

select d.documentid, z.img_cnt, q.sec_cnt
from
	document d
		outer apply
	(
		select count(1) as img_cnt
		from
			document_image di
		where di.documentid = d.documentid
	) z
		outer apply
	(
		select count(1) as sec_cnt
		from
			document_section ds
		where ds.documentid = d.documentid
	) q


а вообще если критично, можно аггрегирующий индекс попробовать
5 сен 13, 12:51    [14799006]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
zxc1257
Member

Откуда:
Сообщений: 71
Glory
zxc1257
если документов мало, то лучше можно так

В эти запросы попадут документы, у которых есть хотя бы один Image И Section


не доглядел.
5 сен 13, 12:52    [14799023]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
zxc1257
Member

Откуда:
Сообщений: 71
use db1;

create nonclustered index idx_section_docid on document_section(documentid);
create nonclustered index idx_image_docid on document_image(documentid);
create nonclustered index idx_document_docid on document(documentid);


select t.documentid, count(is_section) as sec_cnt, count(is_image) as img_cnt
from
(
	select d.documentid, null as is_section, null as is_image
	from document d -- упорядоченное чтение idx_doc_docid

	union all -- даст merge

	select di.documentid, null as is_sрection, 1 as is_image
	from document_image di -- упорядоченное чтение idx_doc_img_docid

	union all -- даст merge

	select ds.documentid, 1 as is_section, null as is_image
	from document_section ds -- упорядоченное чтение idx_doc_sect_docid
) t
group by documentid -- не даст в плане никаких сортировок все отсортировано в индексах, а merge порядок сохранил


К сообщению приложен файл. Размер - 26Kb
5 сен 13, 14:19    [14799867]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
а простой left join разве не тоже самое делает ?
select d.documentid,count(di.ImageId) as img_cnt,count(ds.SectionId) as sec_cnt
from document d
left join document_image di on di.documentid = d.documentid
left join document_section ds on ds.documentid = ds.documentid
group by d.documentid  
5 сен 13, 14:35    [14800040]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
disharmonized
Member

Откуда:
Сообщений: 7
zxc1257, большое спасибо за ответы, попробую поковыряться в базе завтра.

LexusR, оно вернет число все строк по айдишникам документов, полученных при джойнах. Т.е., если скажем, в документе 2 изображения и 3 раздела, то оно вернет
|Document_id|ImageId|SectionId|
|12345 |6 |6 |
при условии, что group by DocumentId.
5 сен 13, 15:02    [14800254]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
zxc1257
Member

Откуда:
Сообщений: 71
LexusR
а простой left join разве не тоже самое делает ?
select d.documentid,count( distinct  di.ImageId) as img_cnt,count( distinct  ds.SectionId) as sec_cnt
from document d
left join document_image di on di.documentid = d.documentid
left join document_section ds on ds.documentid = ds.documentid
group by d.documentid  


К сообщению приложен файл. Размер - 39Kb
5 сен 13, 15:06    [14800269]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
disharmonized
Member

Откуда:
Сообщений: 7
В этом, собственно, и состоит проблема. Если бы запрос возвращал результат в денормализованном виде, то я бы не заморачивался.
5 сен 13, 15:08    [14800282]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
disharmonized
Member

Откуда:
Сообщений: 7
zxc1257
LexusR
а простой left join разве не тоже самое делает ?
select d.documentid,count( distinct  di.ImageId) as img_cnt,count( distinct  ds.SectionId) as sec_cnt
from document d
left join document_image di on di.documentid = d.documentid
left join document_section ds on ds.documentid = ds.documentid
group by d.documentid  


Черт, а что можно так делать, я не знал) Еще раз благодарствую. Интересно, при каком размере таблиц приведенный пример будет работать медленнее, чем вариант с индексами?
5 сен 13, 15:12    [14800296]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
disharmonized
Member

Откуда:
Сообщений: 7
вернее, ощутимо медленнее.

ps возможность на этом форуме редактировать сообщения появляется при наборе определенного количества сообщений или вообще отсутствует?
5 сен 13, 15:16    [14800329]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
iap
Member

Откуда: Москва
Сообщений: 47144
disharmonized
вернее, ощутимо медленнее.

ps возможность на этом форуме редактировать сообщения появляется при наборе определенного количества сообщений или вообще отсутствует?
Вообще
5 сен 13, 15:17    [14800333]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
Гость333
Member

Откуда:
Сообщений: 3683
disharmonized
ps возможность на этом форуме редактировать сообщения появляется при наборе определенного количества сообщений или вообще отсутствует?

Появляется только при получении модераторской лычки.
5 сен 13, 15:18    [14800339]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
вообще то я просто опечатался а вы начали distinct - лепить
select d.documentid,count(di.ImageId) as img_cnt,count(ds.SectionId) as sec_cnt
from document d
left join document_image di on di.documentid = d.documentid
left join document_section ds on ds.documentid = d.documentid---было ) ds.documentid
group by d.documentid  
5 сен 13, 17:32    [14801153]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
zxc1257
Member

Откуда:
Сообщений: 71
LexusR
вообще то я просто опечатался а вы начали distinct - лепить
select d.documentid,count(di.ImageId) as img_cnt,count(ds.SectionId) as sec_cnt
from document d
left join document_image di on di.documentid = d.documentid
left join document_section ds on ds.documentid = d.documentid---было ) ds.documentid
group by d.documentid  


я на условие джоинов не смотрел, не зачем было, просто нашел ошибку раньше

;with
document as
(
	select 1 as documentid union all
	select 2 union all
	select 3
),
document_section as
(
	select 1 as documentid, 1 as sectionid union all
	select 1, 2 union all
	select 2, 2 union all
	select 2, 3
),
document_image as
(
	select 1 as documentid, 1 as imageid union all
	select 1, 7 union all
	select 2, 8 union all
	select 2, 9
)
select d.documentid,count(di.ImageId) as img_cnt,count(ds.SectionId) as sec_cnt
from document d
left join document_image di on di.documentid = d.documentid
left join document_section ds on ds.documentid = d.documentid
group by d.documentid  


documentidimg_cntsec_cnt
144
244
300


;with
document as
(
	select 1 as documentid union all
	select 2 union all
	select 3
),
document_section as
(
	select 1 as documentid, 1 as sectionid union all
	select 1, 2 union all
	select 2, 2 union all
	select 2, 3
),
document_image as
(
	select 1 as documentid, 1 as imageid union all
	select 1, 7 union all
	select 2, 8 union all
	select 2, 9
)
select d.documentid,count(distinct di.ImageId) as img_cnt,count(distinct ds.SectionId) as sec_cnt
from document d
left join document_image di on di.documentid = d.documentid
left join document_section ds on ds.documentid = d.documentid
group by d.documentid 


documentidimg_cntsec_cnt
122
222
300
5 сен 13, 20:52    [14801879]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
zxc1257
Member

Откуда:
Сообщений: 71
ну и план не торт. %) сортировки полезут, спулы и т. п..
5 сен 13, 20:58    [14801892]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
zxc1257
Member

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


К сообщению приложен файл. Размер - 64Kb
5 сен 13, 21:12    [14801941]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
zxc1257
Member

Откуда:
Сообщений: 71
disharmonized
вернее, ощутимо медленнее.


+ данные

with
l0(n) as (select 1 union all select 1),
l1(n) as (select 1 from l0 t1, l0 t2),
l2(n) as (select 1 from l1 t1, l1 t2),
l3(n) as (select 1 from l2 t1, l2 t2),
l4(n) as (select 1 from l3 t1, l3 t2),
l5(n) as (select 1 from l4 t1, l4 t2),
rt(n) as (select row_number() over(order by (select 0)) from l5)
insert into document(documentid)
select top(1000000) n as docid
from rt;

with
l0(n) as (select 1 union all select 1),
l1(n) as (select 1 from l0 t1, l0 t2),
l2(n) as (select 1 from l1 t1, l1 t2),
l3(n) as (select 1 from l2 t1, l2 t2),
l4(n) as (select 1 from l3 t1, l3 t2),
l5(n) as (select 1 from l4 t1, l4 t2),
rt(n) as (select row_number() over(order by (select 0)) from l5)
insert into document_image(documentid, imageid)
select t1.documentid, t2.imageid
from
(
select top(1000000) n as documentid
from rt
) as t1
cross apply
(
select top(cast(rand(checksum(newid())) * 5 as int)) n as imageid
from rt
) as t2

with
l0(n) as (select 1 union all select 1),
l1(n) as (select 1 from l0 t1, l0 t2),
l2(n) as (select 1 from l1 t1, l1 t2),
l3(n) as (select 1 from l2 t1, l2 t2),
l4(n) as (select 1 from l3 t1, l3 t2),
l5(n) as (select 1 from l4 t1, l4 t2),
rt(n) as (select row_number() over(order by (select 0)) from l5)
insert into document_section(documentid, sectionid)
select t1.documentid, t2.sectionid
from
(
select top(1000000) n as documentid
from rt
) as t1
cross apply
(
select top(cast(rand(checksum(newid())) * 5 as int)) n as sectionid
from rt
) as t2


+ union all / 12 сек

set statistics io on;
set statistics time on;

select t.documentid, count(is_section) as sec_cnt, count(is_image) as img_cnt
from
(
	select d.documentid, null as is_section, null as is_image
	from document d -- упорядоченное чтение idx_doc_docid

	union all -- даст merge

	select di.documentid, null as is_sрection, 1 as is_image
	from document_image di -- упорядоченное чтение idx_doc_img_docid

	union all -- даст merge

	select ds.documentid, 1 as is_section, null as is_image
	from document_section ds -- упорядоченное чтение idx_doc_sect_docid
) t
group by documentid

set statistics io off;
set statistics time off;


(строк обработано: 1000000)
Таблица "document_section". Число просмотров 1, логических чтений 6955, физических чтений 0, упреждающих чтений 1, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "document_image". Число просмотров 1, логических чтений 1740, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "document". Число просмотров 1, логических чтений 1243, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

(строк обработано: 1)

 Время работы SQL Server:
   Время ЦП = 4735 мс, затраченное время = 11972 мс.

 Время работы SQL Server:
   Время ЦП = 0 мс, затраченное время = 0 мс.




+ left join / 1 мин 17 сек

set statistics io on;
set statistics time on;

select d.documentid,count(distinct di.ImageId) as img_cnt,count(distinct ds.SectionId) as sec_cnt
from document d
left join document_image di on di.documentid = d.documentid
left join document_section ds on ds.documentid = d.documentid
group by d.documentid 

set statistics io off;
set statistics time off;


(строк обработано: 1000000)
Таблица "document". Число просмотров 3, логических чтений 1257, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "document_image". Число просмотров 3, логических чтений 1766, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "document_section". Число просмотров 3, логических чтений 7053, физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.
Таблица "Worktable". Число просмотров 2, логических чтений 11967749, физических чтений 1771, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

(строк обработано: 1)

 Время работы SQL Server:
   Время ЦП = 41252 мс, затраченное время = 76990 мс.

 Время работы SQL Server:
   Время ЦП = 0 мс, затраченное время = 0 мс.




К сообщению приложен файл. Размер - 104Kb
5 сен 13, 21:44    [14802048]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
LexusR
Member

Откуда: Novosibirsk
Сообщений: 1887
согласен с join-ми погорячился но наверно с агрегирующим подзапросом в select
будет быстрее чем подзапрос во FROM с union-ми
select d.documentid
,(select count(*) from document_section ds  where ds.documentid = d.documentid) as sec_cnt
,(select count(*) from document_image di  where di.documentid = d.documentid) as img_cnt
from document d
6 сен 13, 08:53    [14802617]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
zxc1257
Member

Откуда:
Сообщений: 71
LexusR
согласен с join-ми погорячился но наверно с агрегирующим подзапросом в select
будет быстрее чем подзапрос во FROM с union-ми
select d.documentid
,(select count(*) from document_section ds  where ds.documentid = d.documentid) as sec_cnt
,(select count(*) from document_image di  where di.documentid = d.documentid) as img_cnt
from document d


union all:
Время работы SQL Server:
Время ЦП = 3313 мс, затраченное время = 8034 мс.

correlated query:
Время работы SQL Server:
Время ЦП = 2516 мс, затраченное время = 8063 мс.


на самом деле если делать много запусков, они переменно на копейки опережают друг-друга.

не плохое время дает вариант 10-12 сек, что приводил выше с outer apply. он распараллеливается хорошо. и если у автора будут длинные документы, хорошо покажет себя. но на моем ноуте за 9990 рублей Cel 1.8/2Gb RAM/1 винт 5400rpm это преимущество не видно.
6 сен 13, 09:28    [14802703]     Ответить | Цитировать Сообщить модератору
 Re: Подзапрос в FROM, стоит или не стоит?  [new]
zxc1257
Member

Откуда:
Сообщений: 71
correlated query plan

К сообщению приложен файл. Размер - 37Kb
6 сен 13, 09:30    [14802710]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить