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

Откуда: Екатеринбург
Сообщений: 310
Господа, добрый день!

Microsoft SQL Server 2012 - 11.0.5343.0 (X64)
May 4 2015 19:11:32
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.3 <X64> (Build 9600: )


Использую Linq2Sql. В коде идет обращение к представлению, в котором я пользуюсь window-function. При обращении к представлению передается параметр, который отфильтровывает небольшое количество строк. Все запросы к серверу выполняются через sp_executesql. Столкнулся ухудшением производительности.

Проблема в том, что при такой работе, сначала выбираются все данные, а и лишь потом применяется фильтр. При этом, если выполнять запрос без sp_executesql, или используя exec, или в запросе sp_executesql раскрыть код представления, то фильтр применяется до полной выборки

Как можно побороться с этой ситуацией?

Тестовые данные
+

    set ansi_nulls on
    set quoted_identifier on
    go


    if object_id('[dbo].[TestData]') is not null
    drop table [dbo].[TestData]
    go

    create table [dbo].[TestData] (
      [id] int not null primary key
    )

    insert into [dbo].[TestData]
    values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    go

    if object_id('[dbo].[TestDataList]') is not null
    drop table [dbo].[TestDataList]
    go

    create table [dbo].[TestDataList] (
        [id]        int     not null primary key identity(1,1)
     ,  [parentId]  int     not null
     ,  [value]     [money] not null
    )

    insert into [dbo].[TestDataList]
    values(1,4),(1,5),(1,6),(1,7)
         ,(2,5),(2,1),(2,4),(2,5)
         ,(3,6)
    go



    if object_id('dbo.TestView') is not null
        drop view dbo.TestView
    go

    create view dbo.TestView
      as
       select [d].[id] 
            , [dl].[value] / sum([dl].[value]) over(partition by [d].[id]) as [Value]

          from[dbo].[TestData] [d]
            inner join [dbo].[TestDataList] [dl]
              on [d].[id] = [dl].[parentId]
    go



Запросы
+

    -- Query
    set statistics io on
    go

    select * from dbo.TestView where [id] = 1
    go

    exec('select * from dbo.TestView where [id] = 1')

    exec sp_executesql N'select [d].[id] 
                              , [dl].[value] / sum([dl].[value]) over(partition by [d].[id]) as [Value]

                           from[dbo].[TestData] [d]
                             inner join [dbo].[TestDataList] [dl]
                               on [d].[id] = [dl].[parentId]
                           where [d].[id] = @p0',N'@p0 int',@p0=1


    exec sp_executesql N'select * from dbo.TestView where [id] = @p0',N'@p0 int',@p0=1




На что стоит обратить внимание в плане. Принтскрины.
+

Хороший план
Картинка с другого сайта.

Плохой план
Картинка с другого сайта.


PS. Если из представления убрать windows-function, то планы будут одинаковые.
5 авг 15, 09:40    [17976921]     Ответить | Цитировать Сообщить модератору
 Re: Плохая производительность при работе Window function в предствлениях  [new]
sti
Member

Откуда:
Сообщений: 769
.Anatoly.,

вы сравниваете разные вещи, вообще говоря. Информация для размышления:

declare @id int = 1
select * from dbo.TestView where [id] = @id


exec sp_executesql N'select * from dbo.TestView where [id] = 1'
5 авг 15, 10:24    [17977200]     Ответить | Цитировать Сообщить модератору
 Re: Плохая производительность при работе Window function в предствлениях  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
sti, абсолютно верное замечание.

Запросы стоит рассматривать вот такие
+

    declare @id int = 1;
    select * from dbo.TestView where [id] = @id

    exec('declare @id int = 1; select * from dbo.TestView where [id] = @id')

    exec sp_executesql N'select [d].[id] 
                              , [dl].[value] / sum([dl].[value]) over(partition by [d].[id]) as [Value]

                           from[dbo].[TestData] [d]
                             inner join [dbo].[TestDataList] [dl]
                               on [d].[id] = [dl].[parentId]
                           where [d].[id] = @p0',N'@p0 int',@p0=1


    exec sp_executesql N'select * from dbo.TestView where [id] = @p0',N'@p0 int',@p0=1


В данном случае, только третий запрос показывает хороший план. Все остальные фильтруют лишь после получения всех данных.
5 авг 15, 10:37    [17977292]     Ответить | Цитировать Сообщить модератору
 Re: Плохая производительность при работе Window function в предствлениях  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
@.Anatoly. почитайте про parameter sniffing и Вы сами ответите на свой вопрос. Если не хочется заморачиваться - смотрите в сторону OPTION(RECOMPILE)
5 авг 15, 10:42    [17977321]     Ответить | Цитировать Сообщить модератору
 Re: Плохая производительность при работе Window function в предствлениях  [new]
SomewhereSomehow
Member

Откуда: Moscow
Сообщений: 2480
Блог
.Anatoly.,

Добрый день. Известная ситуация, обсуждалось такое.
Вот почитайте 17524656
5 авг 15, 10:47    [17977353]     Ответить | Цитировать Сообщить модератору
 Re: Плохая производительность при работе Window function в предствлениях  [new]
.Anatoly.
Member

Откуда: Екатеринбург
Сообщений: 310
AlanDenton, спасибо за подсказку!
SomewhereSomehow, спасибо за очень развернутый ответ. Сорри, что не смог правильно воспользоваться поиском. У меня действительно именно такая ситуация.
5 авг 15, 19:39    [17980640]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить