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

Откуда:
Сообщений: 2631
Microsoft SQL Server 2005 - 9.00.4309.00 (X64) Aug 9 2010 14:49:09 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 2)
USE TEMPDB
IF EXISTS(select * from sys.objects where name = 'detections' and type = 'U') DROP TABLE dbo.Orders

CREATE TABLE dbo.Orders(ID int NOT NULL,date datetime NOT NULL,CNT int NOT NULL)
INSERT INTO dbo.Orders
SELECT 1,'2011-01-01',51 UNION
SELECT 1,'2011-01-02',51 UNION
SELECT 1,'2011-01-03',51 UNION
SELECT 1,'2011-01-04',40 UNION
SELECT 1,'2011-01-05',39 UNION
SELECT 1,'2011-01-06',38 UNION
SELECT 1,'2011-01-07',11 UNION
SELECT 1,'2011-01-08',11 UNION
SELECT 1,'2011-01-09',11 UNION

SELECT 2,'2011-02-11',151 UNION
SELECT 2,'2011-02-12',151 UNION
SELECT 2,'2011-02-13',151 UNION
SELECT 2,'2011-02-14',140 UNION
SELECT 2,'2011-02-15',139 UNION
SELECT 2,'2011-02-16',138 UNION
SELECT 2,'2011-02-17',111 UNION
SELECT 2,'2011-02-18',111 UNION
SELECT 2,'2011-02-19',111 

CREATE CLUSTERED INDEX IXcls ON dbo.Orders(ID,CNT)
Теперь запросы:
SET SHOWPLAN_ALL ON
--1)
SELECT ID,MIN(date) as MN ,CNT 
  FROM dbo.Orders as D1
 WHERE CNT IN ( SELECT MAX(CNT)
                            FROM dbo.Orders as D2
                           WHERE D2.ID = D1.ID)
GROUP BY ID,CNT

--2)
SELECT ID,MAX(date) as MN ,CNT 
  FROM dbo.Orders as D1
 WHERE CNT IN ( SELECT MIN(CNT)
                            FROM dbo.Orders as D2
                           WHERE D2.ID = D1.ID)
GROUP BY ID,CNT

SET SHOWPLAN_ALL OFF

Как легко видеть, планы в 1) и 2) отличаются, хотя запросы по сути одинаковые.
Вопросы:
1) Почему так происходит?
2) Как заставить 1 работать по плану segment + top ?



На всякий случай планы:
1)
  |--Stream Aggregate(GROUP BY:([D1].[ID], [D1].[CNT]) DEFINE:([Expr1007]=MIN([tempdb].[dbo].[Orders].[date] as [D1].[date])))
       |--Nested Loops(Inner Join, OUTER REFERENCES:([D2].[ID], [Expr1006]))
            |--Stream Aggregate(GROUP BY:([D2].[ID]) DEFINE:([Expr1006]=MAX([tempdb].[dbo].[Orders].[CNT] as [D2].[CNT])))
            |    |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Orders].[IXcls] AS [D2]), ORDERED FORWARD)
            |--Clustered Index Seek(OBJECT:([tempdb].[dbo].[Orders].[IXcls] AS [D1]), SEEK:([D1].[ID]=[tempdb].[dbo].[Orders].[ID] as [D2].[ID] AND [D1].[CNT]=[Expr1006]) ORDERED FORWARD)

2)
  |--Stream Aggregate(GROUP BY:([D1].[ID], [D1].[CNT]) DEFINE:([Expr1007]=MAX([tempdb].[dbo].[Orders].[date] as [D1].[date])))
       |--Top(TOP EXPRESSION:((1)))
            |--Segment
                 |--Clustered Index Scan(OBJECT:([tempdb].[dbo].[Orders].[IXcls] AS [D1]), ORDERED FORWARD)

-----------------
open your mind

Сообщение было отредактировано: 7 июн 11, 10:04
7 июн 11, 08:09    [10774293]     Ответить | Цитировать Сообщить модератору
 Re: Почему на симметричные запросы получаются разные планы?  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
--__Александр__--
2) Как заставить 1 работать по плану segment + top ?

CREATE INDEX IXnoncls ON dbo.Orders(ID,CNT desc) include ( date )
?

Сообщение было отредактировано: 7 июн 11, 10:09
7 июн 11, 10:08    [10774711]     Ответить | Цитировать Сообщить модератору
 Re: Почему на симметричные запросы получаются разные планы?  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
Гавриленко Сергей Алексеевич,

не взлетело. Что логично.


-----------------
open your mind
7 июн 11, 10:14    [10774749]     Ответить | Цитировать Сообщить модератору
 Re: Почему на симметричные запросы получаются разные планы?  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
order by ID desc ,CNT desc
?
7 июн 11, 10:42    [10774910]     Ответить | Цитировать Сообщить модератору
 Re: Почему на симметричные запросы получаются разные планы?  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
Гадя Петрович,
не помогло, так как скулю пофиг, в какую сторону упорядочен индекс, при поиске MAX(),MIN().

Коллеги, по-моему данная задача подбором не решается )) Нужно понять, почему для симметричного запроса идет другой план.

-----------------
open your mind
7 июн 11, 11:12    [10775163]     Ответить | Цитировать Сообщить модератору
 Re: Почему на симметричные запросы получаются разные планы?  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
--__Александр__--
Гадя Петрович,
не помогло
да ну враки
кэш почистили?
DBCC FREESYSTEMCACHE('ALL')
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC DROPCLEANBUFFERS 
7 июн 11, 11:14    [10775187]     Ответить | Цитировать Сообщить модератору
 Re: Почему на симметричные запросы получаются разные планы?  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
для гарантии

+
USE TEMPDB
IF EXISTS(select * from sys.objects where name = 'detections' and type = 'U') DROP TABLE dbo.Orders

CREATE TABLE dbo.Orders(ID int NOT NULL,date datetime NOT NULL,CNT int NOT NULL)
INSERT INTO dbo.Orders
SELECT 1,'2011-01-01',51 UNION
SELECT 1,'2011-01-02',51 UNION
SELECT 1,'2011-01-03',51 UNION
SELECT 1,'2011-01-04',40 UNION
SELECT 1,'2011-01-05',39 UNION
SELECT 1,'2011-01-06',38 UNION
SELECT 1,'2011-01-07',11 UNION
SELECT 1,'2011-01-08',11 UNION
SELECT 1,'2011-01-09',11 UNION

SELECT 2,'2011-02-11',151 UNION
SELECT 2,'2011-02-12',151 UNION
SELECT 2,'2011-02-13',151 UNION
SELECT 2,'2011-02-14',140 UNION
SELECT 2,'2011-02-15',139 UNION
SELECT 2,'2011-02-16',138 UNION
SELECT 2,'2011-02-17',111 UNION
SELECT 2,'2011-02-18',111 UNION
SELECT 2,'2011-02-19',111 

CREATE CLUSTERED INDEX IXcls ON dbo.Orders(ID,CNT)

go
SET SHOWPLAN_ALL ON
go
--1)
SELECT ID,MIN(date) as MN ,CNT 
  FROM dbo.Orders as D1
 WHERE CNT = ( SELECT MAX(CNT)
                            FROM dbo.Orders as D2
                           WHERE D2.ID = D1.ID)
GROUP BY ID,CNT
order by ID desc ,CNT desc

--2)
SELECT ID,MAX(date) as MN ,CNT 
  FROM dbo.Orders as D1
 WHERE CNT = ( SELECT MIN(CNT)
                            FROM dbo.Orders as D2
                           WHERE D2.ID = D1.ID)
GROUP BY ID,CNT
go
SET SHOWPLAN_ALL OFF
go

drop table dbo.Orders

CHECKPOINT
DBCC FREESYSTEMCACHE('ALL')
DBCC FREEPROCCACHE
DBCC FREESESSIONCACHE
DBCC DROPCLEANBUFFERS 
CHECKPOINT
7 июн 11, 11:15    [10775196]     Ответить | Цитировать Сообщить модератору
 Re: Почему на симметричные запросы получаются разные планы?  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
ЗЫ добавьте
insert into dbo.Orders(id,date,cnt) values (1,'2011-02-12',33)
go 40000
дабы удивиться
7 июн 11, 11:23    [10775280]     Ответить | Цитировать Сообщить модератору
 Re: Почему на симметричные запросы получаются разные планы?  [new]
--__Александр__--
Member

Откуда:
Сообщений: 2631
Гадя Петрович,

А можно услышать какие-либо комментарии, дабы удивление было максимально полным?
7 июн 11, 11:45    [10775490]     Ответить | Цитировать Сообщить модератору
 Re: Почему на симметричные запросы получаются разные планы?  [new]
Гадя Петрович
Member

Откуда: планета Плюк, 215 в тентуре, галактика Кин-дза-дза в Спирали
Сообщений: 52912
--__Александр__--
Гадя Петрович,

А можно услышать какие-либо комментарии, дабы удивление было максимально полным?
на малом количестве данных стоимость плана ниже - всего и делов :)
или как вариант, оптимизатор может не искать оптимальный план при нахождении первого достаточно дешевого плана - запрос то легкий
7 июн 11, 11:50    [10775539]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить