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

Откуда: SPb
Сообщений: 65
Здравствуйте уважаемые коллеги.

Подскажите как построить запрос, чтобы побыстрее работал.
Есть собственно 2 множества, в 1ом различные сочетания (из двух других множеств) в другом допустимые сочетания.
Задача: Найти элементы множества 1, не удовлетворяющие допустимым значениям из множества 2, то есть пары с idMedicalService присутствующие в MedicalDiagnosisService и при этом не имеющие сочетания (услуга диагноз) в MedicalDiagnosisService.

Ну допустим такая структурка
--Таблица с данными
CREATE TABLE [dbo].[service](
	[idMedicalService] [nvarchar](10) NULL, -- услуга
	[mainDiagnosis] [nvarchar](10) NULL -- диагноз
) ON [PRIMARY]

--

--Сочетание ошибочно
INSERT INTO [dbo].[service]
           ([idMedicalService]
           ,[mainDiagnosis]
          )
     VALUES
           (
           '009301'
           ,'K04.2'
           )
--Корректное сочетание          
INSERT INTO [dbo].[service]
			([idMedicalService]
			,[mainDiagnosis]
			)
	 VALUES
			(
			'009301'
			,'K04.0'
			)
--Не должна участвовать в проверке (для idMedicalService допустим любой mainDiagnosis)
INSERT INTO [dbo].[service]
			([idMedicalService]
			,[mainDiagnosis]
			)
	 VALUES
			(
			'009511'
			,'K04.0'
			)
--Таблица допустимые сочетания
CREATE TABLE [dbo].[MedicalDiagnosisService](
	[medicalDiagnosisId] [nvarchar](10) NULL,
	[medicalServiceId] [nvarchar](10) NULL
) ON [PRIMARY]

--Допустимое
INSERT INTO [dbo].[MedicalDiagnosisService]
           ([medicalDiagnosisId]
           ,[medicalServiceId]
     VALUES
           ('K04.0'
           ,'009301'
           )

Я сделал что-то вроде этого...
SELECT 		
		   idMedicalService
		  ,mainDiagnosis
	FROM 
		  dbo.service,
		  MedicalDiagnosisService mds
	WHERE
			NOT EXISTS (SELECT *FROM MedicalDiagnosisService mds2
			                        WHERE mds2.medicalServiceId = dbo.service.idMedicalService
			                        AND mds2.medicalDiagnosisId = dbo.service.mainDiagnosis
			)
			AND idMedicalService = mds.medicalServiceId
в принципе работает, но если количество MedicalDiagnosisService > 2000 уже не комфортно - около 4 сек. У меня же около 40 000 допустимых сочетаний.

Спасибо.

P.S. В листинге мог ошибиться, не судите строго. ))
1 дек 09, 16:03    [8003465]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks2
Guest
SELECT   S.idMedicalService, S.mainDiagnosis
FROM 
  dbo.service S
  left OUTER JOIN
  MedicalDiagnosisService MD
  ON 
  MD.medicalServiceId = S.idMedicalService
  AND 
  MD.medicalDiagnosisId = S.mainDiagnosis
WHERE
MD.medicalDiagnosisId is null
1 дек 09, 16:22    [8003627]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
iljy
Member

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

SELECT   S.idMedicalService, S.mainDiagnosis
FROM 
  dbo.service S
  left OUTER JOIN
  MedicalDiagnosisService MD
  ON 
  MD.medicalServiceId = S.idMedicalService
GROUP BY S.idMedicalService, S.mainDiagnosis
HAVING COUNT(case when s.MainDiagnosis = md.medicalDiagnosisId then 1 end) = 0 and
	   COUNT(MD.medicalServiceId) > 0
1 дек 09, 16:31    [8003718]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
iap
Member

Откуда: Москва
Сообщений: 46975
DZeus
в принципе работает, но если количество MedicalDiagnosisService > 2000 уже не комфортно - около 4 сек. У меня же около 40 000 допустимых сочетаний.
Ни одного индекса! Что же Вы хотите!?
1 дек 09, 16:31    [8003720]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
DZeus
Member

Откуда: SPb
Сообщений: 65
iap,

Есть конечно. Не счёл нужным указать эту деталь, так же как и инсёрты 40 000 записей.
1 дек 09, 16:43    [8003848]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
iljy
Member

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

я думаю тут проблема не в индексе, а во вложенном цикле. Если сделать через join - цикл уйдет даже без индекса, будет HASH JOIN, для 40000 записей - нормально.
1 дек 09, 16:50    [8003926]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
DZeus
Member

Откуда: SPb
Сообщений: 65
iljy,

Премного благодарен, ваш вариант работает.
(2 мин. 13 сек. на 40 000 (MedicalDiagnosisService) с индексами)

На первый взгляд, задача решена. Если что позже отпишу.
1 дек 09, 16:59    [8004008]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
DZeus
iljy,

Премного благодарен, ваш вариант работает.
(2 мин. 13 сек. на 40 000 (MedicalDiagnosisService) с индексами)

На первый взгляд, задача решена. Если что позже отпишу.

Вы бы индексы на таблицах все-таки показали. 2 минуты это долго как-то.
1 дек 09, 17:01    [8004042]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
DZeus
Member

Откуда: SPb
Сообщений: 65
iljy,

Посмотрел ваш вариант с DTA, он предложил мне (к тем индексам что у меня есть) создать 2 статистики и 1 индекс: 6% импрувемента.
1 дек 09, 17:09    [8004123]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
DZeus
Member

Откуда: SPb
Сообщений: 65
Мистер Хенки,

В service ~ 400 000 записей (но в мой запрос берётся порядка 10 000), в MedicalDiagnosisService ~ 40 000.

DTA предложил импрувемент в 6%, есть основания ему не верить?
1 дек 09, 17:15    [8004169]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
DZeus
Мистер Хенки,

В service ~ 400 000 записей (но в мой запрос берётся порядка 10 000), в MedicalDiagnosisService ~ 40 000.

DTA предложил импрувемент в 6%, есть основания ему не верить?

Вы бы индексы показали для полноты картины.
1 дек 09, 17:20    [8004224]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks2
Guest
iljy
DZeus,

SELECT   S.idMedicalService, S.mainDiagnosis
FROM 
  dbo.service S
  left OUTER JOIN
  MedicalDiagnosisService MD
  ON 
  MD.medicalServiceId = S.idMedicalService
GROUP BY S.idMedicalService, S.mainDiagnosis
HAVING COUNT(case when s.MainDiagnosis = md.medicalDiagnosisId then 1 end) = 0 and
	   COUNT(MD.medicalServiceId) > 0


Иногда даж удивительно: через какую только задницу можно сделать абсолютно тривиальную вещь...

declare @t table([medicalServiceId] [nvarchar](10) primary key clustered)

insert @t
select [medicalServiceId]
FROM [dbo].[MedicalDiagnosisService]
GROUP BY [medicalServiceId]

SELECT   S.idMedicalService, S.mainDiagnosis
FROM 
  (select * FROM dbo.service x inner join @t t ON t.[medicalServiceId]=x.idMedicalService) S
  left OUTER JOIN
  MedicalDiagnosisService MD
  ON 
  MD.medicalServiceId = S.idMedicalService
  AND 
  MD.medicalDiagnosisId = S.mainDiagnosis
WHERE
MD.medicalDiagnosisId is null

1 дек 09, 19:03    [8004805]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
iljy
Member

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

Иногда даж удивительно: через какую только задницу можно сделать абсолютно тривиальную вещь...

declare @t table([medicalServiceId] [nvarchar](10) primary key clustered)

insert @t
select [medicalServiceId]
FROM [dbo].[MedicalDiagnosisService]
GROUP BY [medicalServiceId]

SELECT   S.idMedicalService, S.mainDiagnosis
FROM 
  (select * FROM dbo.service x inner join @t t ON t.[medicalServiceId]=x.idMedicalService) S
  left OUTER JOIN
  MedicalDiagnosisService MD
  ON 
  MD.medicalServiceId = S.idMedicalService
  AND 
  MD.medicalDiagnosisId = S.mainDiagnosis
WHERE
MD.medicalDiagnosisId is null


Ага, а сделать через временную таблицу и с двумя проходами по таблице вместо одного - это типа через передницу жжешь чувак, пеши исчо
1 дек 09, 19:26    [8004868]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks2
Guest
iljy

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


Идиосинкразия на временные таблицы? Сочувствую...

Только этот вариант будет на порядок быстрее твоего.
1 дек 09, 19:47    [8004922]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
iljy
Member

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

Посмотрел ваш вариант с DTA, он предложил мне (к тем индексам что у меня есть) создать 2 статистики и 1 индекс: 6% импрувемента.


Можно попробовать так соптимизить в принципе.
SELECT   S.idMedicalService, S.mainDiagnosis
FROM 
  MedicalDiagnosisService MD
 ==>JOIN
  dbo.service S
  ON 
       MD.medicalServiceId = S.idMedicalService
GROUP BY S.idMedicalService, S.mainDiagnosis
HAVING COUNT(case when s.MainDiagnosis = md.medicalDiagnosisId then 1 end) = 0

Ну а дальше - индексы надо смотреть, и план. DTA конечно хорошая вещь, но метод пристального взгляда тоже никто не отменял.
1 дек 09, 19:49    [8004926]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
iljy
Member

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

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


Идиосинкразия на временные таблицы? Сочувствую...

Только этот вариант будет на порядок быстрее твоего.

Нет, просто не вижу причин делать через еще большую задницу. Информация о количестве записей появилась на несколько постов позже, про индексы инфы нет до сих пор, а без этого - спор беспредметный. И думаю мой исправленый вариант при наличии хотя бы индекса по [idMedicalService] и [medicalDiagnosisId] будет побыстрее. А твоему - индексы по обоим колонкам понадобяться, иначе смысла в нем никакого.
1 дек 09, 19:53    [8004941]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
iljy
Member

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

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

+ Скрипты создания

use tempdb
--if(OBJECT_ID('service') is not null) drop table service
--if(OBJECT_ID('[MedicalDiagnosisService]') is not null) drop table [MedicalDiagnosisService]
--Таблица с данными
CREATE TABLE [dbo].[service](
	[idMedicalService] [nvarchar](10) NULL, -- услуга
	[mainDiagnosis] [nvarchar](10) NULL -- диагноз
) ON [PRIMARY]

--
INSERT INTO [dbo].[service]
           ([mainDiagnosis]
           ,[idMedicalService])
select top 4000000 'K04.' + CAST(N%9 as NCHAR(1)),
           CAST(N / 5 as nvarchar(10))
from TestDB..Numbers

--Таблица допустимые сочетания
CREATE TABLE [dbo].[MedicalDiagnosisService](
	[medicalDiagnosisId] [nvarchar](10) NULL,
	[medicalServiceId] [nvarchar](10) NULL
) ON [PRIMARY]

--Допустимое
INSERT INTO [dbo].[MedicalDiagnosisService]
           ([medicalDiagnosisId]
           ,[medicalServiceId])
select top 400000 'K04.' + CAST(N%7 as NCHAR(1)),
           CAST(N / 5 * 2 as nvarchar(10))
from TestDB..Numbers

CREATE clustered index  IX_MEDICAL_DIAGS on dbo.MedicalDiagnosisService
([medicalServiceId]
)
CREATE clustered index  IX_SERVICE_DIAGS on dbo.service
( idMedicalService
)

+ мой модифицированный запрос

SELECT S.idMedicalService, S.mainDiagnosis
FROM 
  dbo.service S
	JOIN
  MedicalDiagnosisService MD
  ON 
  MD.medicalServiceId = S.idMedicalService
GROUP BY s.idMedicalService, S.mainDiagnosis
HAVING COUNT(case when s.MainDiagnosis = md.medicalDiagnosisId then 1 end) = 0


(177782 row(s) affected)
Таблица "MedicalDiagnosisService". Число просмотров 3, логических чтений 2233, физических
чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob
упреждающих чтений 0.
Таблица "service". Число просмотров 3, логических чтений 20946, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих
чтений 0.
Таблица "Worktable". Число просмотров 0, логических чтений 0, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих
чтений 0.

Время работы SQL Server:
Время ЦП = 8517 мс, затраченное время = 6083 мс.
+ твой супербыстрый запрос с временными таблицами, которыми ты кстати не умеешь пользоваться
declare @t table([medicalServiceId] [nvarchar](10) primary key clustered)

insert @t
select [medicalServiceId]
FROM [dbo].[MedicalDiagnosisService]
GROUP BY [medicalServiceId]

SELECT   S.idMedicalService, S.mainDiagnosis
FROM 
  (select * FROM dbo.service x inner join @t t ON t.[medicalServiceId]=x.idMedicalService) S
  left OUTER JOIN
  MedicalDiagnosisService MD
  ON 
  MD.medicalServiceId = S.idMedicalService
  AND 
  MD.medicalDiagnosisId = S.mainDiagnosis
WHERE
MD.medicalDiagnosisId is null


Таблица "MedicalDiagnosisService". Число просмотров 1, логических чтений 2034, физических
чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob
упреждающих чтений 0.

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

(80001 row(s) affected)

(177782 row(s) affected)
Таблица "MedicalDiagnosisService". Число просмотров 400004, логических чтений 1212302,
физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0,
lob упреждающих чтений 0.
Таблица "service". Число просмотров 80001, логических чтений 242425, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих
чтений 0.
Таблица "#56E8E7AB". Число просмотров 1, логических чтений 235, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих
чтений 0.

Время работы SQL Server:
Время ЦП = 5703 мс, затраченное время = 5992 мс.
Суммарное время - 6310мс
+ твой супербыстрый слегка модифицированный запрос

declare @t table([medicalServiceId] [nvarchar](10) primary key clustered)

insert @t
select [medicalServiceId]
FROM [dbo].[MedicalDiagnosisService]
GROUP BY [medicalServiceId]

SELECT   S.idMedicalService, S.mainDiagnosis
FROM 
  (select * FROM dbo.service x inner join @t t ON t.[medicalServiceId]=x.idMedicalService) S
WHERE
not exists
(
  select * from
  MedicalDiagnosisService MD
  where  
  MD.medicalServiceId = S.idMedicalService
  AND 
  MD.medicalDiagnosisId = S.mainDiagnosis
)

Таблица "MedicalDiagnosisService". Число просмотров 1, логических чтений 2034, физических
чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob
упреждающих чтений 0.

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

(80001 row(s) affected)

(177782 row(s) affected)
Таблица "MedicalDiagnosisService". Число просмотров 400004, логических чтений 1208901,
физических чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0,
lob упреждающих чтений 0.
Таблица "service". Число просмотров 80001, логических чтений 242425, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих
чтений 0.
Таблица "#5AB9788F". Число просмотров 1, логических чтений 235, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих
чтений 0.

Время работы SQL Server:
Время ЦП = 5157 мс, затраченное время = 5366 мс.
суммарное время - 5686мс
+ почему ты НЕ УМЕЕШЬ пользоваться временными таблицами

if(OBJECT_ID('#t') is not null) drop table #t
create table #t([medicalServiceId] [nvarchar](10) primary key clustered)

insert #t
select [medicalServiceId]
FROM [dbo].[MedicalDiagnosisService]
GROUP BY [medicalServiceId]

SELECT   S.idMedicalService, S.mainDiagnosis
FROM 
  (select * FROM dbo.service x inner join #t t ON t.[medicalServiceId]=x.idMedicalService) S
WHERE
not exists
(
  select * from
  MedicalDiagnosisService MD
  where  
  MD.medicalServiceId = S.idMedicalService
  AND 
  MD.medicalDiagnosisId = S.mainDiagnosis
)


Таблица "MedicalDiagnosisService". Число просмотров 1, логических чтений 2034, физических
чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob
упреждающих чтений 0.

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

(80001 row(s) affected)

(177782 row(s) affected)
Таблица "Worktable". Число просмотров 0, логических чтений 0, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих
чтений 0.
Таблица "MedicalDiagnosisService". Число просмотров 1, логических чтений 2034, физических
чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob
упреждающих чтений 0.
Таблица "service". Число просмотров 1, логических чтений 20860, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих
чтений 0.
Таблица "#t__________________________________________________________________________________________________________________00000000002D".
Число просмотров 1, логических чтений 236, физических чтений 0, упреждающих чтений 0, lob
логических чтений 0, lob физических чтений 0, lob упреждающих чтений 0.

Время работы SQL Server:
Время ЦП = 3266 мс, затраченное время = 4719 мс.
Суммарное время 5031мс
+ как это надо делать
SELECT S.idMedicalService, S.mainDiagnosis
FROM 
  dbo.service S
	join
  (
		select distinct [medicalServiceId]
		FROM [dbo].[MedicalDiagnosisService]
  ) s1
	ON s1.medicalServiceId = S.idMedicalService
WHERE
not exists
(
  select * from
  MedicalDiagnosisService MD
  where  
  MD.medicalServiceId = S.idMedicalService
  AND 
  MD.medicalDiagnosisId = S.mainDiagnosis
)


(177782 row(s) affected)
Таблица "Worktable". Число просмотров 0, логических чтений 0, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих
чтений 0.
Таблица "service". Число просмотров 1, логических чтений 20860, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих
чтений 0.
Таблица "MedicalDiagnosisService". Число просмотров 2, логических чтений 4068, физических
чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob
упреждающих чтений 0.

Время работы SQL Server:
Время ЦП = 3500 мс, затраченное время = 3914 мс.
1 дек 09, 21:06    [8005096]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks2
Guest
У меня немного другие результаты... хе-хе...

--Таблица с данными
CREATE TABLE [dbo].[service](
	[idMedicalService] [nvarchar](10) NULL, -- услуга
	[mainDiagnosis] [nvarchar](10) NULL -- диагноз
) ON [PRIMARY]
CREATE UNIQUE clustered index  IX_SERVICE_DIAGS on dbo.service
( idMedicalService, [mainDiagnosis])

--Таблица допустимые сочетания
CREATE TABLE [dbo].[MedicalDiagnosisService](
	[medicalDiagnosisId] [nvarchar](10) NOT NULL,
	[medicalServiceId] [nvarchar](10) NOT NULL,
	CONSTRAINT [PK_MEDICAL_DIAGS] PRIMARY KEY clustered ([medicalServiceId], [medicalDiagnosisId])
) ON [PRIMARY]

CREATE  index  IX_MedicalDiagnosisService on dbo.MedicalDiagnosisService
( [medicalServiceId])


Moe
(80010 row(s) affected)

Table '#0D7A0286'. Scan count 0, logical reads 160856, physical reads 0, read-ahead reads 0.
Table 'MedicalDiagnosisService'. Scan count 1, logical reads 1692, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 1012 ms, elapsed time = 1046 ms.

(88911 row(s) affected)

Table 'MedicalDiagnosisService'. Scan count 200050, logical reads 600993, physical reads 0, read-ahead reads 0.
Table 'service'. Scan count 80010, logical reads 321639, physical reads 0, read-ahead reads 0.
Table '#0D7A0286'. Scan count 1, logical reads 280, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 3745 ms, elapsed time = 8317 ms.

Твое
(88911 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.
Table 'MedicalDiagnosisService'. Scan count 4000000, logical reads 12519261, physical reads 0, read-ahead reads 0.
Table 'service'. Scan count 1, logical reads 19454, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 46356 ms, elapsed time = 46930 ms.

И эта разница будет расти с ростом данных.

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

SELECT   S.idMedicalService, S.mainDiagnosis
FROM 
  dbo.service S
  left OUTER JOIN
  MedicalDiagnosisService MD
  ON 
  MD.medicalServiceId = S.idMedicalService
  AND 
  MD.medicalDiagnosisId = S.mainDiagnosis
WHERE
exists(select * FROM MedicalDiagnosisService MD1 WHERE MD1.medicalServiceId=S.idMedicalService)
AND
MD.medicalDiagnosisId is null

(88911 row(s) affected)

Table 'service'. Scan count 1, logical reads 19450, physical reads 0, read-ahead reads 0.
Table 'MedicalDiagnosisService'. Scan count 2, logical reads 3384, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 3976 ms, elapsed time = 9337 ms.
2 дек 09, 07:45    [8005769]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks2
Guest
Справедливости ради, заметим, шо последний вариант уже ничего, только шибко мудрено закручено

SELECT S.idMedicalService, S.mainDiagnosis
FROM 
  dbo.service S
	join
  (
		select distinct [medicalServiceId]
		FROM [dbo].[MedicalDiagnosisService]
  ) s1
	ON s1.medicalServiceId = S.idMedicalService
WHERE
not exists
(
  select * from
  MedicalDiagnosisService MD
  where  
  MD.medicalServiceId = S.idMedicalService
  AND 
  MD.medicalDiagnosisId = S.mainDiagnosis
)

(88911 row(s) affected)

Table 'service'. Scan count 1, logical reads 19450, physical reads 0, read-ahead reads 0.
Table 'MedicalDiagnosisService'. Scan count 2, logical reads 3384, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 4777 ms, elapsed time = 9324 ms.

хе-хе... но его оптимизатор преобразует к
SELECT   S.idMedicalService, S.mainDiagnosis
FROM 
  dbo.service S
  left OUTER JOIN
  MedicalDiagnosisService MD
  ON 
  MD.medicalServiceId = S.idMedicalService
  AND 
  MD.medicalDiagnosisId = S.mainDiagnosis
WHERE
exists(select * FROM MedicalDiagnosisService MD1 WHERE MD1.medicalServiceId=S.idMedicalService)
AND
MD.medicalDiagnosisId is null
2 дек 09, 07:54    [8005785]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
DZeus
Member

Откуда: SPb
Сообщений: 65
Напомните пожалуйста что сделать нужно при выполнении запроса чтобы показать вам подобное

iljy
(177782 row(s) affected)
Таблица "MedicalDiagnosisService". Число просмотров 3, логических чтений 2233, физических
чтений 0, упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob
упреждающих чтений 0.
Таблица "service". Число просмотров 3, логических чтений 20946, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих
чтений 0.
Таблица "Worktable". Число просмотров 0, логических чтений 0, физических чтений 0,
упреждающих чтений 0, lob логических чтений 0, lob физических чтений 0, lob упреждающих
чтений 0.

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


Запрос
SELECT 
			idMedicalService,mainDiagnosis
	FROM 
		  dbo.service 
		  LEFT OUTER JOIN 
		  MaxMedOrgPassport.dbo.MedicalDiagnosisService t
		  ON t.medicalServiceId = dbo.service.idMedicalService
WHERE
			dbo.service.idLpuFromFileName = '1107001'
			AND
			t.medOrgId IS NOT NULL 
			
GROUP BY 
	dbo.service.idMedicalService,dbo.service.mainDiagnosis
HAVING 
	COUNT(CASE WHEN mainDiagnosis = t.medicalDiagnosisId THEN 1 END) = 0
AND 
	COUNT(t.medicalServiceId) > 0 
надеюсь тот факт что я WHERE не упомянул при постановке вопроса не столь критично... )))

Actual Execution Plan использует эти индексы

/****** Object:  Index [serviceId]    Script Date: 12/02/2009 10:35:07 ******/
CREATE NONCLUSTERED INDEX [serviceId] ON [dbo].[MedicalDiagnosisService] 
(
	[medOrgId] ASC,
	[medicalDiagnosisId] ASC,
	[medicalServiceId] ASC,
	[isActual] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO

USE [maxExpert]
GO

/****** Object:  Index [indexForSM]    Script Date: 12/02/2009 10:43:04 ******/
CREATE NONCLUSTERED INDEX [indexForSM] ON [dbo].[service] 
(
	[idLpuFromFileName] ASC
)
INCLUDE ( [recid],
[dateOfService],
[idMedicalService],
[mainDiagnosis],
[idTreatResult],
[idMedicalProfile],
[snInsurance],
[dateOfServiceStart]) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
GO
Сам план - в приложении.
В Итоге - 5 сек. =)
2 дек 09, 11:00    [8006745]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
DZeus
Member

Откуда: SPb
Сообщений: 65
ПЛАН XML

К сообщению приложен файл (Execution plan.xml - 58Kb) cкачать
2 дек 09, 11:01    [8006751]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks2
Guest
1. Приведите НАСТОЯЩИЕ определения таблиц с данными
2. Приведите НАСТОЯЩИЙ запрос.

Опосля можно мусолить тему индексов.

PS. Глупо использовать в запросах группировку БЕЗ НЕОБХОДИМОСТИ. Ибо это означает сканирование таблиц/индексов, т.е. тормоза.
2 дек 09, 11:06    [8006792]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
iljy
Member

Откуда:
Сообщений: 8711
aleks2
У меня немного другие результаты... хе-хе...

CREATE UNIQUE clustered index  IX_SERVICE_DIAGS on dbo.service
( idMedicalService, [mainDiagnosis])

Ну вот ничегошеньки этот индекс на таблице этой не дает. Хотя бы потому, что ищутся строки, НЕ удовлетворяющие условию (idMedicalService, mainDiagnosis) = чему-то. Разьве что размер листьев может стать поменьше за счет исчезновения уникального суффикса, но и то - при этом узлов станет больше, так что преимущества очень спорны.
aleks2

Твое
(88911 row(s) affected)

Warning: Null value is eliminated by an aggregate or other SET operation.
Table 'MedicalDiagnosisService'. Scan count 4000000, logical reads 12519261, physical reads 0, read-ahead reads 0.
Table 'service'. Scan count 1, logical reads 19454, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 46356 ms, elapsed time = 46930 ms.

И эта разница будет расти с ростом данных.

ты какой запрос тестировал, милый?? Как ты Scan Count 4000000 получил??? Да, запрос с LEFT JOIN (изначальный) выполняется дольше, но не с такой статистикой и не столько времени! А заменив LEFT на INNER - получаем то, что я сказал.

aleks2

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

SELECT   S.idMedicalService, S.mainDiagnosis
FROM 
  dbo.service S
  left OUTER JOIN
  MedicalDiagnosisService MD
  ON 
  MD.medicalServiceId = S.idMedicalService
  AND 
  MD.medicalDiagnosisId = S.mainDiagnosis
WHERE
exists(select * FROM MedicalDiagnosisService MD1 WHERE MD1.medicalServiceId=S.idMedicalService)
AND
MD.medicalDiagnosisId is null

(88911 row(s) affected)

Table 'service'. Scan count 1, logical reads 19450, physical reads 0, read-ahead reads 0.
Table 'MedicalDiagnosisService'. Scan count 2, logical reads 3384, physical reads 0, read-ahead reads 0.

SQL Server Execution Times:
CPU time = 3976 ms, elapsed time = 9337 ms.

Да вот нифига так не быстрее по сравнению с моим последним вариантом. Разница в пределах погрешности, но и то - мой чуть быстрее получается (3900мс против 3970 ). А насчет одно к другому приводится - это ты как такой вывод сделал?
Твой запрос:
RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions
1777821SELECT S.idMedicalService, S.mainDiagnosis FROM dbo.service S left OUTER JOIN MedicalDiagnosisService MD ON MD.medicalServiceId = S.idMedicalService AND MD.medicalDiagnosisId = S.mainDiagnosis WHERE exists(select * FROM MedicalDiagnosisService MD1 WHERE MD1.medicalServiceId=S.idMedicalService) AND MD.medicalDiagnosisId is null110NULLNULLNULLNULL89836,77NULLNULLNULL45,91518NULLNULLSELECT0NULL
1777821 |--Filter(WHERE:([tempdb].[dbo].[MedicalDiagnosisService].[medicalDiagnosisId] as [MD].[medicalDiagnosisId] IS NULL))121FilterFilterWHERE:([tempdb].[dbo].[MedicalDiagnosisService].[medicalDiagnosisId] as [MD].[medicalDiagnosisId] IS NULL)NULL89836,7700,23512173445,91518[S].[idMedicalService], [S].[mainDiagnosis]NULLPLAN_ROW01
4000041 |--Hash Match(Right Outer Join, HASH:([MD].[medicalServiceId], [MD].[medicalDiagnosisId])=([S].[idMedicalService], [S].[mainDiagnosis]), RESIDUAL:([tempdb].[dbo].[MedicalDiagnosisService].[medicalServiceId] as [MD].[medicalServiceId]=[tempdb].[dbo].[service].[idMedicalService] as [S].[idMedicalService] AND [tempdb].[dbo].[MedicalDiagnosisService].[medicalDiagnosisId] as [MD].[medicalDiagnosisId]=[tempdb].[dbo].[service].[mainDiagnosis] as [S].[mainDiagnosis]))132Hash MatchRight Outer JoinHASH:([MD].[medicalServiceId], [MD].[medicalDiagnosisId])=([S].[idMedicalService], [S].[mainDiagnosis]), RESIDUAL:([tempdb].[dbo].[MedicalDiagnosisService].[medicalServiceId] as [MD].[medicalServiceId]=[tempdb].[dbo].[service].[idMedicalService] as [S].[idMedicalService] AND [tempdb].[dbo].[MedicalDiagnosisService].[medicalDiagnosisId] as [MD].[medicalDiagnosisId]=[tempdb].[dbo].[service].[mainDiagnosis] as [S].[mainDiagnosis])NULL489836,8015,057674645,68006[S].[idMedicalService], [S].[mainDiagnosis], [MD].[medicalDiagnosisId]NULLPLAN_ROW01
4000001 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[MedicalDiagnosisService].[IX_MEDICAL_DIAGS] AS [MD]))143Clustered Index ScanClustered Index ScanOBJECT:([tempdb].[dbo].[MedicalDiagnosisService].[IX_MEDICAL_DIAGS] AS [MD])[MD].[medicalDiagnosisId], [MD].[medicalServiceId]4000001,5009030,440157331,94106[MD].[medicalDiagnosisId], [MD].[medicalServiceId]NULLPLAN_ROW01
4000041 |--Merge Join(Inner Join, MERGE:([MD1].[medicalServiceId])=([S].[idMedicalService]), RESIDUAL:([tempdb].[dbo].[MedicalDiagnosisService].[medicalServiceId] as [MD1].[medicalServiceId]=[tempdb].[dbo].[service].[idMedicalService] as [S].[idMedicalService]))163Merge JoinInner JoinMERGE:([MD1].[medicalServiceId])=([S].[idMedicalService]), RESIDUAL:([tempdb].[dbo].[MedicalDiagnosisService].[medicalServiceId] as [MD1].[medicalServiceId]=[tempdb].[dbo].[service].[idMedicalService] as [S].[idMedicalService])NULL400467,508,6151433428,68132[S].[idMedicalService], [S].[mainDiagnosis]NULLPLAN_ROW01
800011 |--Stream Aggregate(GROUP BY:([MD1].[medicalServiceId]))176Stream AggregateAggregateGROUP BY:([MD1].[medicalServiceId])NULL80712,200,2403561212,181416[MD1].[medicalServiceId]NULLPLAN_ROW01
4000001 | |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[MedicalDiagnosisService].[IX_MEDICAL_DIAGS] AS [MD1]), ORDERED FORWARD)187Clustered Index ScanClustered Index ScanOBJECT:([tempdb].[dbo].[MedicalDiagnosisService].[IX_MEDICAL_DIAGS] AS [MD1]), ORDERED FORWARD[MD1].[medicalServiceId]4000001,5009030,440157211,94106[MD1].[medicalServiceId]NULLPLAN_ROW01
39999961 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[service].[IX_SERVICE_DIAGS] AS [S]), ORDERED FORWARD)1106Clustered Index ScanClustered Index ScanOBJECT:([tempdb].[dbo].[service].[IX_SERVICE_DIAGS] AS [S]), ORDERED FORWARD[S].[idMedicalService], [S].[mainDiagnosis]400000013,484614,4001573417,88476[S].[idMedicalService], [S].[mainDiagnosis]NULLPLAN_ROW01

Мой запрос:
RowsExecutesStmtTextStmtIdNodeIdParentPhysicalOpLogicalOpArgumentDefinedValuesEstimateRowsEstimateIOEstimateCPUAvgRowSizeTotalSubtreeCostOutputListWarningsTypeParallelEstimateExecutions
1777821SELECT S.idMedicalService, S.mainDiagnosis FROM dbo.service S join ( select distinct [medicalServiceId] FROM [dbo].[MedicalDiagnosisService] ) s1 ON s1.medicalServiceId = S.idMedicalService WHERE not exists ( select * from MedicalDiagnosisService MD where MD.medicalServiceId = S.idMedicalService AND MD.medicalDiagnosisId = S.mainDiagnosis )110NULLNULLNULLNULL394818,7NULLNULLNULL45,55238NULLNULLSELECT0NULL
1777821 |--Hash Match(Right Anti Semi Join, HASH:([MD].[medicalServiceId], [MD].[medicalDiagnosisId])=([S].[idMedicalService], [S].[mainDiagnosis]), RESIDUAL:([tempdb].[dbo].[MedicalDiagnosisService].[medicalServiceId] as [MD].[medicalServiceId]=[tempdb].[dbo].[service].[idMedicalService] as [S].[idMedicalService] AND [tempdb].[dbo].[MedicalDiagnosisService].[medicalDiagnosisId] as [MD].[medicalDiagnosisId]=[tempdb].[dbo].[service].[mainDiagnosis] as [S].[mainDiagnosis]))121Hash MatchRight Anti Semi JoinHASH:([MD].[medicalServiceId], [MD].[medicalDiagnosisId])=([S].[idMedicalService], [S].[mainDiagnosis]), RESIDUAL:([tempdb].[dbo].[MedicalDiagnosisService].[medicalServiceId] as [MD].[medicalServiceId]=[tempdb].[dbo].[service].[idMedicalService] as [S].[idMedicalService] AND [tempdb].[dbo].[MedicalDiagnosisService].[medicalDiagnosisId] as [MD].[medicalDiagnosisId]=[tempdb].[dbo].[service].[mainDiagnosis] as [S].[mainDiagnosis])NULL394818,7014,929993445,55238[S].[idMedicalService], [S].[mainDiagnosis]NULLPLAN_ROW01
4000001 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[MedicalDiagnosisService].[IX_MEDICAL_DIAGS] AS [MD]))132Clustered Index ScanClustered Index ScanOBJECT:([tempdb].[dbo].[MedicalDiagnosisService].[IX_MEDICAL_DIAGS] AS [MD])[MD].[medicalDiagnosisId], [MD].[medicalServiceId]4000001,5009030,440157331,94106[MD].[medicalDiagnosisId], [MD].[medicalServiceId]NULLPLAN_ROW01
4000041 |--Merge Join(Inner Join, MERGE:([tempdb].[dbo].[MedicalDiagnosisService].[medicalServiceId])=([S].[idMedicalService]), RESIDUAL:([tempdb].[dbo].[service].[idMedicalService] as [S].[idMedicalService]=[tempdb].[dbo].[MedicalDiagnosisService].[medicalServiceId]))142Merge JoinInner JoinMERGE:([tempdb].[dbo].[MedicalDiagnosisService].[medicalServiceId])=([S].[idMedicalService]), RESIDUAL:([tempdb].[dbo].[service].[idMedicalService] as [S].[idMedicalService]=[tempdb].[dbo].[MedicalDiagnosisService].[medicalServiceId])NULL400467,508,6151433428,68132[S].[idMedicalService], [S].[mainDiagnosis]NULLPLAN_ROW01
800011 |--Stream Aggregate(GROUP BY:([tempdb].[dbo].[MedicalDiagnosisService].[medicalServiceId]))154Stream AggregateAggregateGROUP BY:([tempdb].[dbo].[MedicalDiagnosisService].[medicalServiceId])NULL80712,200,2403561212,181416[tempdb].[dbo].[MedicalDiagnosisService].[medicalServiceId]NULLPLAN_ROW01
4000001 | |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[MedicalDiagnosisService].[IX_MEDICAL_DIAGS]), ORDERED FORWARD)165Clustered Index ScanClustered Index ScanOBJECT:([tempdb].[dbo].[MedicalDiagnosisService].[IX_MEDICAL_DIAGS]), ORDERED FORWARD[tempdb].[dbo].[MedicalDiagnosisService].[medicalServiceId]4000001,5009030,440157211,94106[tempdb].[dbo].[MedicalDiagnosisService].[medicalServiceId]NULLPLAN_ROW01
39999961 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[service].[IX_SERVICE_DIAGS] AS [S]), ORDERED FORWARD)184Clustered Index ScanClustered Index ScanOBJECT:([tempdb].[dbo].[service].[IX_SERVICE_DIAGS] AS [S]), ORDERED FORWARD[S].[idMedicalService], [S].[mainDiagnosis]400000013,484614,4001573417,88476[S].[idMedicalService], [S].[mainDiagnosis]NULLPLAN_ROW01

Найди 10 отличий Из них кстати очень хорошо видно, откуда берется разница в скорости вычислений.
Да, и еще - чтоб снять лишние вопросы:
Microsoft SQL Server 2008 (SP1) - 10.0.2746.0 (Intel X86)   Nov  9 2009 16:59:31
Copyright (c) 1988-2008 Microsoft Corporation Developer Edition on Windows NT 5.1 <X86>
(Build 2600: Service Pack 3)
2 дек 09, 11:08    [8006806]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
iljy
Member

Откуда:
Сообщений: 8711
aleks2
1. Приведите НАСТОЯЩИЕ определения таблиц с данными
2. Приведите НАСТОЯЩИЙ запрос.

Опосля можно мусолить тему индексов.

PS. Глупо использовать в запросах группировку БЕЗ НЕОБХОДИМОСТИ. Ибо это означает сканирование таблиц/индексов, т.е. тормоза.

Истинный борец с ветряными мельницами... Ты хоть обратил внимание, что во ВСЕХ (ах да, не во всех, в твоем СУПЕР_ПУПЕР_БЫСТРОМ_ЗАПРОСЕ с табличной переменной стоит Index Seek, а заодно и Estimated Row Number = 1 ) вариантах идет Clustered Index Scan, может заодно объяснишь почему?
2 дек 09, 11:11    [8006822]     Ответить | Цитировать Сообщить модератору
 Re: Помогите с запросом  [new]
aleks2
Guest
iljy,

Дарагуля, эта разница несущественна. Я бы тебе и вовсе не советовал глядеть дальше ПЕРВОЙ цифры и количества знаков. Ибо сгенерированные данные, скорее, всего не соответствуют статистике распределения данных тредстартера.
2 дек 09, 11:12    [8006839]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить