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

Откуда:
Сообщений: 15
Помогите пжлста оптимизировать след. запрос на MS SQL Server 2005:

SELECT count(p.ID)
FROM dbo.Product p
inner join join dbo.Supplier s on p.SupplierID = s.ID
WHERE (p.Deleted=0) and s.IsSpecial = 1
	and pcdd.ProductID in (
		SELECT DISTINCT pc.ProductID
		FROM dbo.ProductCategory pc
		inner join dbo.Category c on pc.CategoryID = c.ID
		WHERE c.TLeft >= 1 and c.TRight <= 33378
	)

Не много о запросе - есть связь многие ко многим между Продуктами и Категориями (ProductCategory). Категории имеют иерархическую структуру в виде дерева (TLeft\TRight).
При наличии 15-ти тысяч продуктов запрос выполняеться около минуты что есть не оч хорошо.
Важно, используеться NHibernate!

Может какие индексы подобавлять, или еще что можно сделать на стороне сервера?

Буду рад любым предложениям,
Спасибо
27 июл 09, 22:05    [7466481]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Kostya Malyasov
Member

Откуда:
Сообщений: 15
Поправка: pcdd.ProductID == p.ID
27 июл 09, 22:08    [7466486]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Glory
Member

Откуда:
Сообщений: 104760
Kostya Malyasov
Помогите пжлста оптимизировать след. запрос на MS SQL Server 2005:

А он на ваш взгляд неопитмальный ? Почему ?
27 июл 09, 22:16    [7466499]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Kostya Malyasov
Member

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

Допустим используя вспомогательную таблицу можно уменьшить время выполнения до 1-й секунды (когда выше изложенный запрос выполняеться в течении минуты).
Вот пример:
declare @CatProducts table (
	ProductID int not null
)

insert into @CatProducts
	SELECT DISTINCT pc.ProductID
	FROM dbo.ProductCategory pc
	inner join dbo.Category c on pc.CategoryID = c.ID
	WHERE c.TLeft >= 1 and c.TRight <= 33378

SELECT count(p.ID)
FROM dbo.Product p
inner join join dbo.Supplier s on p.SupplierID = s.ID
inner join join @CatProducts cp on p.ID = cp.ProductID
27 июл 09, 22:37    [7466524]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Glory
Member

Откуда:
Сообщений: 104760
А что мешает сразу сделать SELECT count( DISTINCT p.ID) ?
27 июл 09, 22:50    [7466547]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Kostya Malyasov
Member

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

т.к. это только первый запрос, за ним следует практически идентичный запрос по выбору продуктов для определенной страницы грида (пейджинг).
27 июл 09, 23:01    [7466578]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Glory
Member

Откуда:
Сообщений: 104760
Kostya Malyasov
Glory,

т.к. это только первый запрос, за ним следует практически идентичный запрос по выбору продуктов для определенной страницы грида (пейджинг).

Вы уж определитесь, какой запрос вы хотите оптимизировать
27 июл 09, 23:07    [7466587]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Kostya Malyasov
Member

Откуда:
Сообщений: 15
Извените Glory,

Но мне интересны реальные предложения, а не флуд по сторонам. Вопрос поставлен коректно т.к. выполнения обоих запросов занимает практически одно время.
27 июл 09, 23:10    [7466593]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Kostya Malyasov
Member

Откуда:
Сообщений: 15
Glory
А что мешает сразу сделать SELECT count( DISTINCT p.ID) ?


Время выполнения ни сколько не уменьшилось. По моему проблема лежит в IN операторе и под-запросе (ИМХО)
27 июл 09, 23:15    [7466596]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Kostya Malyasov
Member

Откуда:
Сообщений: 15
Kostya Malyasov
Glory
А что мешает сразу сделать SELECT count( DISTINCT p.ID) ?


Время выполнения ни сколько не уменьшилось. По моему проблема лежит в IN операторе и под-запросе (ИМХО)


Еще один интересный факт - если убрать join к Supplier, запрос выполняеться довольно быстро. Даже не знаю чем это характеризуеться... в таблице Supplier всего 2-е строки
27 июл 09, 23:21    [7466604]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Kostya Malyasov,

Без планов запроса флуд получается с Вашей стороны. Не надо с больной головы на здоровую переваливать. А еще и DDL таблиц, учавствующих в запросе не мешало бы привести...
27 июл 09, 23:35    [7466618]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
iljy
Member

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

а вы план не пытались посмотреть? Обычно оптимизацию именно с этого начинают.
27 июл 09, 23:35    [7466619]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Glory
Member

Откуда:
Сообщений: 104760
Kostya Malyasov
Извените Glory,

Но мне интересны реальные предложения, а не флуд по сторонам. Вопрос поставлен коректно т.к. выполнения обоих запросов занимает практически одно время.


И вы можете коректно ответить, почему вы не используете count( DISTINCT p.ID) ?
Или будете флудить про про какие то группы запросов ?

Сообщение было отредактировано: 27 июл 09, 23:36
27 июл 09, 23:36    [7466620]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Glory
Member

Откуда:
Сообщений: 104760
Kostya Malyasov
Glory
А что мешает сразу сделать SELECT count( DISTINCT p.ID) ?


Время выполнения ни сколько не уменьшилось. По моему проблема лежит в IN операторе и под-запросе (ИМХО)

Я фигею, если count( DISTINCT p.ID), то какой IN то ? Не будет никакого IN-а
27 июл 09, 23:46    [7466632]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Kostya Malyasov
Member

Откуда:
Сообщений: 15
Glory
Kostya Malyasov
Glory
А что мешает сразу сделать SELECT count( DISTINCT p.ID) ?


Время выполнения ни сколько не уменьшилось. По моему проблема лежит в IN операторе и под-запросе (ИМХО)

Я фигею, если count( DISTINCT p.ID), то какой IN то ? Не будет никакого IN-а


IN используеться для отфильтровки не нужных продуктов по категориям и для устранения повторения, т.к. мы могли бы написать простой джоин.
27 июл 09, 23:56    [7466643]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Glory
Member

Откуда:
Сообщений: 104760
Мда уж. Для фильтрации
SELECT count(distinсt p.ID)
FROM dbo.Product p
inner join dbo.Supplier s on p.SupplierID = s.ID
inner join dbo.ProductCategory pc on pc.ProductID = p.ID
inner join dbo.Category c on pc.CategoryID = c.ID
WHERE c.TLeft >= 1 and c.TRight <= 33378
AND (p.Deleted=0) and s.IsSpecial = 1
28 июл 09, 00:01    [7466649]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Glory
Мда уж. Для фильтрации
SELECT count(distinсt p.ID)
FROM dbo.Product p
inner join dbo.Supplier s on p.SupplierID = s.ID
inner join dbo.ProductCategory pc on pc.ProductID = p.ID
inner join dbo.Category c on pc.CategoryID = c.ID
WHERE c.TLeft >= 1 and c.TRight <= 33378
AND (p.Deleted=0) and s.IsSpecial = 1
Это напрасно Вы так. Автор же сказал
автор
Не много о запросе - есть связь многие ко многим между Продуктами и Категориями (ProductCategory)


Автор, вы предоставьте план запроса и желательно DDL таблиц и информацию об индексах на таблицах.
28 июл 09, 00:11    [7466660]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Kostya Malyasov
Member

Откуда:
Сообщений: 15
Senya_L
Kostya Malyasov,

Без планов запроса флуд получается с Вашей стороны. Не надо с больной головы на здоровую переваливать. А еще и DDL таблиц, учавствующих в запросе не мешало бы привести...


DDL-ы:
CREATE TABLE [dbo].[Product](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[SupplierID] [int] NOT NULL,
	[Length] [int] NULL,
	[Width] [int] NULL,
	[Height] [int] NULL,
	[Weight] [int] NULL,
	[Volume] [int] NULL,
	[ImagePath] [varchar](200) COLLATE Cyrillic_General_CI_AS NULL,
	[IsAvalible] [bit] NOT NULL,
	[Deleted] [bit] NOT NULL CONSTRAINT [DF_Product_Deleted]  DEFAULT ((0)),
 CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Category](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ParentID] [int] NULL,
	[TLeft] [int] NOT NULL,
	[TRight] [int] NOT NULL,
	[SupplierID] [int] NOT NULL,
	[IsAvailible] [bit] NOT NULL,
	[Deleted] [bit] NOT NULL CONSTRAINT [DF_Category_Deleted]  DEFAULT ((0)),
 CONSTRAINT [PK_Category] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ProductCategory](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[ProductID] [int] NOT NULL,
	[CategoryID] [int] NOT NULL,
 CONSTRAINT [PK_ProductCategory] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[Supplier](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[code] [varchar](50) COLLATE Cyrillic_General_CI_AS NOT NULL,
	[Address] [nvarchar](255) COLLATE Cyrillic_General_CI_AS NULL,
	[City] [nvarchar](50) COLLATE Cyrillic_General_CI_AS NULL,
	[ZipCode] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL,
	[Phone] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL,
	[Fax] [varchar](50) COLLATE Cyrillic_General_CI_AS NULL,
	[Email] [varchar](255) COLLATE Cyrillic_General_CI_AS NULL,
	[Deleted] [bit] NOT NULL CONSTRAINT [DF_Supplier_Deleted]  DEFAULT ((0)),
 CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

В планах особо ничего не понимаю, но заскришотить могу (смотри в атачменте)

К сообщению приложен файл. Размер - 0Kb
28 июл 09, 00:19    [7466670]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Kostya Malyasov,

Ваш запрос
SELECT count(p.ID)
FROM dbo.Product p
inner join dbo.Supplier s on p.SupplierID = s.ID
WHERE (p.Deleted=0) and s.IsSpecial = 1
	and pcdd.ProductID in (
		SELECT DISTINCT pc.ProductID
		FROM dbo.ProductCategory pc
		inner join dbo.Category c on pc.CategoryID = c.ID
		WHERE c.TLeft >= 1 and c.TRight <= 33378
	)
дает ошибку. Помимо дублирования JOIN еще
Сообщение 207, уровень 16, состояние 1, строка 4
Недопустимое имя столбца "IsSpecial".
Сообщение 4104, уровень 16, состояние 1, строка 4
Не удалось привязать составной идентификатор "pcdd.ProductID".
28 июл 09, 00:27    [7466680]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Kostya Malyasov
Member

Откуда:
Сообщений: 15
Senya_L
Kostya Malyasov,

Ваш запрос
SELECT count(p.ID)
FROM dbo.Product p
inner join dbo.Supplier s on p.SupplierID = s.ID
WHERE (p.Deleted=0) and s.IsSpecial = 1
	and pcdd.ProductID in (
		SELECT DISTINCT pc.ProductID
		FROM dbo.ProductCategory pc
		inner join dbo.Category c on pc.CategoryID = c.ID
		WHERE c.TLeft >= 1 and c.TRight <= 33378
	)
дает ошибку. Помимо дублирования JOIN еще
Сообщение 207, уровень 16, состояние 1, строка 4
Недопустимое имя столбца "IsSpecial".
Сообщение 4104, уровень 16, состояние 1, строка 4
Не удалось привязать составной идентификатор "pcdd.ProductID".


Я уже писал о паправке :)
Поправка: pcdd.ProductID == p.ID
28 июл 09, 00:31    [7466686]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Но вообще странный план запроса. Я не понимаю как классификатор категорий столько "кушает" от всех затрат на запрос. Сколько записей в таблице [ProductCategory] и [Category]?
28 июл 09, 00:31    [7466687]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Kostya Malyasov
Member

Откуда:
Сообщений: 15
Senya_L
Но вообще странный план запроса. Я не понимаю как классификатор категорий столько "кушает" от всех затрат на запрос. Сколько записей в таблице [ProductCategory] и [Category]?


select count(*) from Product = 13651
select count(*) from Category = 16689
select count(*) from ProductCategory = 60500
28 июл 09, 00:34    [7466691]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Kostya Malyasov
Я уже писал о паправке :)
Дабы не смеялось :) Все равно ошибка
Сообщение 207, уровень 16, состояние 1, строка 4
Недопустимое имя столбца "IsSpecial".
Про поправку я потом уже увидел
28 июл 09, 00:35    [7466692]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Senya_L
Member

Откуда: Москва
Сообщений: 5381
Kostya Malyasov,

Дайте информацию о существующих индексах на таблице ProductCategory
28 июл 09, 00:37    [7466694]     Ответить | Цитировать Сообщить модератору
 Re: Оптимизация запроса  [new]
Kostya Malyasov
Member

Откуда:
Сообщений: 15
Senya_L
Kostya Malyasov
Я уже писал о паправке :)
Дабы не смеялось :) Все равно ошибка
Сообщение 207, уровень 16, состояние 1, строка 4
Недопустимое имя столбца "IsSpecial".
Про поправку я потом уже увидел


Упс, и правда ошибка, извените, был не внемателен когда удалял не нужные колонки.
Таблица [Supplier]
	[IsSpecial] [bit] NOT NULL,
З.Ы. я надеюсь что полный перечень колонок не обязателен т.к. их много и многие из них пока не используються
28 июл 09, 00:40    [7466696]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить