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

Откуда:
Сообщений: 79
Есть пять табличек
CREATE TABLE [dbo].[DrugForm] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [nvarchar] (50) COLLATE Cyrillic_General_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Manufacture] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [nvarchar] (120) COLLATE Cyrillic_General_CI_AS NOT NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[ManufactureState] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[TYPE] [int] NOT NULL ,
	[ToManufacture] [int] NULL ,
	[ToState] [int] NULL 
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[TradeName] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[Name] [nvarchar] (200) COLLATE Cyrillic_General_CI_AS NOT NULL ,
	[Latin] [nvarchar] (200) COLLATE Cyrillic_General_CI_AS NULL ,
	[ShortName] [nvarchar] (200) COLLATE Cyrillic_General_CI_AS NULL ,
	[ToInternationalName] [int] NULL 
) ON [PRIMARY]

CREATE TABLE [dbo].[TradeUnit] (
	[ID] [int] IDENTITY (1, 1) NOT NULL ,
	[ToTradeName] [int] NULL ,
	[ToDrugForm] [int] NULL ,
	[ToDose] [int] NULL ,
	[ToManufactureState] [int] NULL ,

) ON [PRIMARY]


SELECT 
	M.ID MANUFACTURE_ID,
	N.ID TRADENAME_ID,
	D.ID DRUGFORM_ID,
	O.ID DOSE_ID,
	M.NAME MANUFACTURE_NAME,
	N.NAME TRADE_NAME,
	D.NAME DRUGFORM_NAME,
	O.NAME DOSE_NAME

FROM MANUFACTURE M  JOIN
	MANUFACTURESTATE S ON M.ID = S.ToManufacture 	LEFT JOIN
	TRADEUNIT T	 	ON S.ID = T.ToManufactureState LEFT JOIN
	TRADENAME N		ON N.ID = T.ToTradeName 	LEFT JOIN
	DRUGFORM D 		ON D.ID = T.ToDrugForm 	LEFT JOIN
	DOSE O		ON O.ID = T.ToDose

--ORDER BY 5 ,6,7
Все поля проиндексированы. Выводит 66000 записей, без order за 10
c ORDER - за 40. Можно как-то енто опимизнуть?

Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
22 дек 04, 00:08    [1198374]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
tpg
Member

Откуда: Novosibirsk
Сообщений: 23902
А планы сравнить обоих запросов?
22 дек 04, 06:36    [1198499]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
vavania
Member

Откуда:
Сообщений: 79
Ничего неожиданного: на "выходе" sort by по именам, занимающий 50%.
22 дек 04, 08:40    [1198604]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
это для вас ничего неожиданного... а для сервера...

строк сколько возвращается и по скольким строковым колонкам сортируете?


для спящего время бодрствования равносильно сну
22 дек 04, 09:32    [1198689]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
AlexJuice
Member

Откуда: Не от мира сего
Сообщений: 1413
Алексей2003
строк сколько возвращается и по скольким строковым колонкам сортируете?

Ну написал же чел :-)

--ORDER BY 5 ,6,7
22 дек 04, 09:35    [1198701]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
не праснулся еще...

Все поля проиндексированы
это в плане строковые поля проиндексированы?

план запроса приведите пожалуйста


для спящего время бодрствования равносильно сну
22 дек 04, 09:50    [1198782]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
vavania
Member

Откуда:
Сообщений: 79
Алексей2003
не праснулся еще...

Все поля проиндексированы
это в плане строковые поля проиндексированы?



Ну да текстовые, жалко что-ли ?

  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([T].[ToDose]) WITH PREFETCH)
|--Sort(ORDER BY:([M].[Name] ASC, [N].[Name] ASC, [D].[Name] ASC))
| |--Hash Match(Right Outer Join, HASH:([D].[ID])=([T].[ToDrugForm]), RESIDUAL:([D].[ID]=[T].[ToDrugForm]))
| |--Index Scan(OBJECT:([InfoPharm].[dbo].[DrugForm].[IX_DrugForm] AS [D]))
| |--Hash Match(Left Outer Join, HASH:([S].[ID])=([T].[ToManufactureState]), RESIDUAL:([S].[ID]=[T].[ToManufactureState]))
| |--Merge Join(Inner Join, MERGE:([M].[ID])=([S].[ToManufacture]), RESIDUAL:([S].[ToManufacture]=[M].[ID]))
| | |--Clustered Index Scan(OBJECT:([InfoPharm].[dbo].[Manufacture].[PK_Manufacture] AS [M]), ORDERED FORWARD)
| | |--Index Scan(OBJECT:([InfoPharm].[dbo].[ManufactureState].[IX_ManufactureState] AS [S]), ORDERED FORWARD)
| |--Merge Join(Right Outer Join, MERGE:([N].[ID])=([T].[ToTradeName]), RESIDUAL:([N].[ID]=[T].[ToTradeName]))
| |--Index Scan(OBJECT:([InfoPharm].[dbo].[TradeName].[PK_TradeName] AS [N]), ORDERED FORWARD)
| |--Clustered Index Scan(OBJECT:([InfoPharm].[dbo].[TradeUnit].[IX_TradeUnit_Name] AS [T]), ORDERED FORWARD)
|--Clustered Index Seek(OBJECT:([InfoPharm].[dbo].[Dose].[PK_Dose] AS [O]), SEEK:([O].[ID]=[T].[ToDose]) ORDERED FORWARD)
22 дек 04, 13:35    [1200115]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
Алексей2003
Member

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

для спящего время бодрствования равносильно сну
22 дек 04, 13:40    [1200144]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
vavania
Member

Откуда:
Сообщений: 79
Насколько я понимаю, при ORDER:

Sort(ORDER BY:([M].[Name] ASC, [N].[Name] ASC, [D].[Name] ASC))

он вообще индексы не использует - остюда и тормоз.
22 дек 04, 16:24    [1201150]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
Glory
Member

Откуда:
Сообщений: 104760
vavania
Насколько я понимаю, при ORDER:

Sort(ORDER BY:([M].[Name] ASC, [N].[Name] ASC, [D].[Name] ASC))

он вообще индексы не использует - остюда и тормоз.

Ну так у вас есть разве индекс по :([M].[Name] ASC, [N].[Name] ASC, [D].[Name] ASC ?
22 дек 04, 16:28    [1201174]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
ChA
Member

Откуда: Москва
Сообщений: 11136
Вообще-то, операция сортировки обычно работает очень быстро, а вот план запроса ужасен. Вероятно, без указания сортировки у Вас используется другой план, более "правильный"...
22 дек 04, 16:49    [1201293]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
vavania
Member

Откуда:
Сообщений: 79
Откровенно, говоря я впервые слышу о составных индексах по разным таблицам. А как их создать? По каждому из этих полей индексы есть.
22 дек 04, 16:49    [1201294]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
Glory
Member

Откуда:
Сообщений: 104760
Извиняюсь - недосмотрел. Индексы по столбцам из разных таблиц разумеется не построить.
22 дек 04, 16:52    [1201311]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
запустите estimated execution plan
а лучше запустите запрос и дождитесь выполнения
сколько в процентах, занимает sort команда?

для спящего время бодрствования равносильно сну
22 дек 04, 17:01    [1201375]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
vavania
Member

Откуда:
Сообщений: 79
ChA
Вообще-то, операция сортировки обычно работает очень быстро, а вот план запроса ужасен. Вероятно, без указания сортировки у Вас используется другой план, более "правильный"...


Да что и меня удивляет - элементарный запрос... Вот план без сортировки

  |--Hash Match(Right Outer Join, HASH:([O].[ID])=([T].[ToDose]), RESIDUAL:([O].[ID]=[T].[ToDose]))
|--Index Scan(OBJECT:([InfoPharm].[dbo].[Dose].[IX_Dose] AS [O]))
|--Hash Match(Right Outer Join, HASH:([D].[ID])=([T].[ToDrugForm]), RESIDUAL:([D].[ID]=[T].[ToDrugForm]))
|--Index Scan(OBJECT:([InfoPharm].[dbo].[DrugForm].[IX_DrugForm] AS [D]))
|--Hash Match(Left Outer Join, HASH:([S].[ID])=([T].[ToManufactureState]), RESIDUAL:([S].[ID]=[T].[ToManufactureState]))
|--Merge Join(Inner Join, MERGE:([M].[ID])=([S].[ToManufacture]), RESIDUAL:([S].[ToManufacture]=[M].[ID]))
| |--Clustered Index Scan(OBJECT:([InfoPharm].[dbo].[Manufacture].[PK_Manufacture] AS [M]), ORDERED FORWARD)
| |--Index Scan(OBJECT:([InfoPharm].[dbo].[ManufactureState].[IX_ManufactureState] AS [S]), ORDERED FORWARD)
|--Merge Join(Right Outer Join, MERGE:([N].[ID])=([T].[ToTradeName]), RESIDUAL:([N].[ID]=[T].[ToTradeName]))
|--Index Scan(OBJECT:([InfoPharm].[dbo].[TradeName].[PK_TradeName] AS [N]), ORDERED FORWARD)
|--Clustered Index Scan(OBJECT:([InfoPharm].[dbo].[TradeUnit].[IX_TradeUnit_Name] AS [T]), ORDERED FORWARD)
22 дек 04, 17:03    [1201390]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
а еще лучше
сделать
select 1
go
select 2
Ctrl+L
и отношение процентов каждого запроса.


для спящего время бодрствования равносильно сну
22 дек 04, 17:06    [1201412]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
ChA
Member

Откуда: Москва
Сообщений: 11136
Просмотрел сразу, у Вас в плане с сортировкой в самом конце выполняется NESTED LOOP JOINS. Для 66000 этот способ утомителен, надо бы избежать, не говоря уж о том, что возвращать 66000 записей клиенту редкая роскошь...
22 дек 04, 17:22    [1201521]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
vavania
Member

Откуда:
Сообщений: 79
Запросы относятся как 89.91 и 11.09
22 дек 04, 17:23    [1201526]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
Алексей2003
Member

Откуда: Москва
Сообщений: 5645
у sort команды сколько стоит процент?

как его оптимизнуть... еще раз...
создайте по 2 индекса с разной последовательностью колонок ИД и строки всего 6 штук получится, посмотрите что будет использовать оптимизатор...

для спящего время бодрствования равносильно сну
22 дек 04, 17:25    [1201540]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31622
Интересно, а если так написать:
SELECT 
  MANUFACTURE_ID,
  TRADENAME_ID,
  DRUGFORM_ID,
  DOSE_ID,
  MANUFACTURE_NAME,
  TRADE_NAME,
  DRUGFORM_NAME,
  DOSE_NAME
FROM (SELECT 
      M.ID MANUFACTURE_ID,
      N.ID TRADENAME_ID,
      D.ID DRUGFORM_ID,
      O.ID DOSE_ID,
      M.NAME MANUFACTURE_NAME,
      N.NAME TRADE_NAME,
      D.NAME DRUGFORM_NAME,
      O.NAME DOSE_NAME
    FROM MANUFACTURE M  JOIN
      MANUFACTURESTATE S ON M.ID = S.ToManufacture LEFT JOIN
      TRADEUNIT T ON S.ID = T.ToManufactureState LEFT JOIN
      TRADENAME NON N.ID = T.ToTradeName LEFT JOIN
      DRUGFORM D ON D.ID = T.ToDrugForm LEFT JOIN
      DOSE OON O.ID = T.ToDose
    ) as t
ORDER BY 5 , 6, 7
22 дек 04, 17:27    [1201552]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
AlexandrVM
Member

Откуда: Санкт-Петербург
Сообщений: 22
а если если писать не LEFT JOIN, а JOIN не станет лучше?
22 дек 04, 17:27    [1201557]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
vavania
Member

Откуда:
Сообщений: 79
Алексей2003
запустите estimated execution plan
а лучше запустите запрос и дождитесь выполнения
сколько в процентах, занимает sort команда?


79%

ChA

Просмотрел сразу, у Вас в плане с сортировкой в самом конце выполняется NESTED LOOP JOINS. Для 66000 этот способ утомителен, надо бы избежать, не говоря уж о том, что возвращать 66000 записей клиенту редкая роскошь...


Они у меня, конечно, не олигархи, но роскошь любят. Ну в-общем нада так.

alexeyvg

Интересно, а если так написать:


А серваку хоть в лоб, хоть по лбу - на 4 сек дольше, чем "традиционно"

AlexandrVM

а если если писать не LEFT JOIN, а JOIN не станет лучше?


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

Алексей2003

как его оптимизнуть... еще раз...
создайте по 2 индекса с разной последовательностью колонок ИД и строки всего 6 штук получится, посмотрите что будет использовать оптимизатор...


По-моему нифига не изменилось:

  |--Nested Loops(Left Outer Join, OUTER REFERENCES:([T].[ToDose]) WITH PREFETCH)
|--Sort(ORDER BY:([M].[Name] ASC, [N].[Name] ASC, [D].[Name] ASC))
| |--Hash Match(Right Outer Join, HASH:([D].[ID])=([T].[ToDrugForm]), RESIDUAL:([D].[ID]=[T].[ToDrugForm]))
| |--Index Scan(OBJECT:([InfoPharm].[dbo].[DrugForm].[IX_DrugForm_2] AS [D]))
| |--Hash Match(Left Outer Join, HASH:([S].[ID])=([T].[ToManufactureState]), RESIDUAL:([S].[ID]=[T].[ToManufactureState]))
| |--Merge Join(Inner Join, MERGE:([M].[ID])=([S].[ToManufacture]), RESIDUAL:([S].[ToManufacture]=[M].[ID]))
| | |--Index Scan(OBJECT:([InfoPharm].[dbo].[Manufacture].[IX_Manufacture_1] AS [M]), ORDERED FORWARD)
| | |--Index Scan(OBJECT:([InfoPharm].[dbo].[ManufactureState].[IX_ManufactureState] AS [S]), ORDERED FORWARD)
| |--Merge Join(Right Outer Join, MERGE:([N].[ID])=([T].[ToTradeName]), RESIDUAL:([N].[ID]=[T].[ToTradeName]))
| |--Index Scan(OBJECT:([InfoPharm].[dbo].[TradeName].[PK_TradeName] AS [N]), ORDERED FORWARD)
| |--Clustered Index Scan(OBJECT:([InfoPharm].[dbo].[TradeUnit].[IX_TradeUnit_Name] AS [T]), ORDERED FORWARD)
|--Index Seek(OBJECT:([InfoPharm].[dbo].[Dose].[IX_Dose_1] AS [O]), SEEK:([O].[ID]=[T].[ToDose]) ORDERED FORWARD)
22 дек 04, 18:02    [1201779]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
Гавриленко Сергей Алексеевич
Member

Откуда: Moscow
Сообщений: 37101
AlexandrVM
а если если писать не LEFT JOIN, а JOIN не станет лучше?

точно станет лучше, если не писать join'ов вообще. И селектов.
22 дек 04, 18:02    [1201781]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
AlexandrVM
Member

Откуда: Санкт-Петербург
Сообщений: 22
Гавриленко Сергей Алексеевич
AlexandrVM
а если если писать не LEFT JOIN, а JOIN не станет лучше?

точно станет лучше, если не писать join'ов вообще. И селектов.


м-м-м-м-м-м, в данном случае, left, ИМХО, действительно можно не писать, и, если вы внимательно посмотрите на запрос, подумаете, может и сами это поймете. впрочем, конечно это не значит, что он будет работать быстрее.

а, если вы так шутите, то, наверное, для этого, на даном форуме, есть другой топик.
22 дек 04, 18:07    [1201808]     Ответить | Цитировать Сообщить модератору
 Re: Элементарный ORDER BY  [new]
ChA
Member

Откуда: Москва
Сообщений: 11136
Вот этот план и зафиксируйте с помощью хинтов
 |--Hash Match(Right Outer Join, HASH:([O].[ID])=([T].[ToDose]), RESIDUAL:([O].[ID]=[T].[ToDose]))

       |--Index Scan(OBJECT:([InfoPharm].[dbo].[Dose].[IX_Dose] AS [O]))

       |--Hash Match(Right Outer Join, HASH:([D].[ID])=([T].[ToDrugForm]), RESIDUAL:([D].[ID]=[T].[ToDrugForm]))

            |--Index Scan(OBJECT:([InfoPharm].[dbo].[DrugForm].[IX_DrugForm] AS [D]))

            |--Hash Match(Left Outer Join, HASH:([S].[ID])=([T].[ToManufactureState]), RESIDUAL:([S].[ID]=[T].[ToManufactureState]))

                 |--Merge Join(Inner Join, MERGE:([M].[ID])=([S].[ToManufacture]), RESIDUAL:([S].[ToManufacture]=[M].[ID]))

                 |    |--Clustered Index Scan(OBJECT:([InfoPharm].[dbo].[Manufacture].[PK_Manufacture] AS [M]), ORDERED FORWARD)

                 |    |--Index Scan(OBJECT:([InfoPharm].[dbo].[ManufactureState].[IX_ManufactureState] AS [S]), ORDERED FORWARD)

                 |--Merge Join(Right Outer Join, MERGE:([N].[ID])=([T].[ToTradeName]), RESIDUAL:([N].[ID]=[T].[ToTradeName]))

                      |--Index Scan(OBJECT:([InfoPharm].[dbo].[TradeName].[PK_TradeName] AS [N]), ORDERED FORWARD)

                      |--Clustered Index Scan(OBJECT:([InfoPharm].[dbo].[TradeUnit].[IX_TradeUnit_Name] AS [T]), ORDERED FORWARD)
22 дек 04, 19:00    [1202022]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить