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

Откуда:
Сообщений: 193
Привет!
Задача оптимизировать запрос:

SELECT
CVT.shift_number ,
CVT.number ,
CVT.dt ,
CD.logNumKkm ,
CD.uid_shop ,
SUM(CV.goods_count) ,
SUM(CV.price * CV.goods_count) ,
SUM(CV.overal_sum) ,
SUM(CV.discount_sum)
FROM
(
cash_voucher_title AS CVT
INNER JOIN cash_voucher AS CV
ON CVT.uid = CV.uid_cash_voucher_title
)
LEFT JOIN cash_desk AS CD
ON CVT.uid_cash_desk = CD.uid
WHERE
CVT.dt BETWEEN '04/04/2013 00:00:00' AND '04/11/2013 23:59:59'
GROUP BY
CVT.shift_number,
CVT.number,
CVT.dt,
CD.logNumKkm,
CD.uid_shop

ORDER BY
CVT.dt DESC

Кол-во данных:
CD ~200000
CVT ~8000000
CV ~20000000

Созданные индексы:

ALTER TABLE [dbo].[cash_voucher_title] ADD CONSTRAINT [PK_CASH_VOUCHER_TITLE] PRIMARY KEY CLUSTERED
(
[uid] ASC
)

ALTER TABLE [dbo].[cash_desk] ADD CONSTRAINT [PK_CASH_DESK] PRIMARY KEY CLUSTERED
(
[uid] ASC
)

ALTER TABLE [dbo].[cash_voucher] ADD CONSTRAINT [PK_CASH_VOUCHER] PRIMARY KEY CLUSTERED
(
[uid] ASC
)

CREATE NONCLUSTERED INDEX [ix_uid_cash_voucher] ON [dbo].[cash_voucher]
(
[uid_cash_voucher_title] ASC
)

CREATE NONCLUSTERED INDEX [ix_uid_cash_desk_shift_number] ON [dbo].[cash_voucher_title]
(
[dt] ASC
)
INCLUDE ( [uid],
[uid_cash_desk],
[shift_number],
[number])

План выполнения в приложениях.

Есть проблема в сортировке - аж 40%, но не возьму в толк как ее решить.

К сообщению приложен файл. Размер - 62Kb
17 апр 13, 15:12    [14193197]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
а если поменять
ORDER BY
CVT.dt DESC

на
ORDER BY
CVT.dt 


план меняеться ?
17 апр 13, 15:19    [14193230]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
cdsmika
Member

Откуда:
Сообщений: 193
Если условие поменять так, чтобы возвращалась куча данных, план меняется до неузнаваемости на хэши и добавляется еще пара сортировок

К сообщению приложен файл. Размер - 57Kb
17 апр 13, 15:22    [14193250]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
cdsmika
Member

Откуда:
Сообщений: 193
Maxx
а если поменять
ORDER BY
CVT.dt DESC

на
ORDER BY
CVT.dt 


план меняеться ?


неа
17 апр 13, 15:22    [14193256]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
cdsmika
Member

Откуда:
Сообщений: 193
+ план №1

К сообщению приложен файл (plan.sqlplan - 43Kb) cкачать
17 апр 13, 15:24    [14193268]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
cdsmika
Member

Откуда:
Сообщений: 193
+ план №2

К сообщению приложен файл (plan2.sqlplan - 50Kb) cкачать
17 апр 13, 15:25    [14193276]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
cdsmika
Member

Откуда:
Сообщений: 193
Maxx
а если поменять
ORDER BY
CVT.dt DESC

на
ORDER BY
CVT.dt 


план меняеться ?


Тут проблема не в ORDER BY, его можно совсем убрать. А вот группировка на многое влияет. Можно ли индекс на несколько таблиц составлять?
17 апр 13, 15:27    [14193296]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
cdsmika
Можно ли индекс на несколько таблиц составлять

низя,недочитал попутал у вас групировку с сортировкой :)
17 апр 13, 15:30    [14193320]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
cdsmika
Member

Откуда:
Сообщений: 193
Вот структура, если поможет:

CREATE TABLE [dbo].[cash_desk](
[uid] [uniqueidentifier] NOT NULL,
[cash_desk_number] [int] NULL,
[logNumKkm] [int] NULL,
[isOptimized] [bit] NULL,
[uid_shop] [uniqueidentifier] NULL,
CONSTRAINT [PK_CASH_DESK] PRIMARY KEY CLUSTERED
(
[uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

CREATE TABLE [dbo].[cash_voucher_title](
[uid] [uniqueidentifier] NOT NULL,
[uid_cash_desk] [uniqueidentifier] NULL,
[shift_number] [int] NULL,
[number] [int] NULL,
[dt] [datetime] NULL,
[bank_card_number] [char](4) NULL,
[uid_card] [uniqueidentifier] NULL,
[sum] [float] NULL,
[discount_sum] [float] NULL,
[overal_sum] [float] NULL,
[uid_staff] [uniqueidentifier] NULL,
[idCashHeadAuto] [bigint] NULL,
[idShiftAuto] [bigint] NULL,
[id_status] [int] NULL,
CONSTRAINT [PK_CASH_VOUCHER_TITLE] PRIMARY KEY CLUSTERED
(
[uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[cash_voucher_title] WITH CHECK ADD CONSTRAINT [FK_CASH_VOU_REFERENCE_CASH_DES] FOREIGN KEY([uid_cash_desk])
REFERENCES [dbo].[cash_desk] ([uid])
GO

ALTER TABLE [dbo].[cash_voucher_title] CHECK CONSTRAINT [FK_CASH_VOU_REFERENCE_CASH_DES]
GO

CREATE TABLE [dbo].[cash_voucher](
[uid] [uniqueidentifier] NOT NULL,
[uid_cash_voucher_title] [uniqueidentifier] NOT NULL,
[uid_goods] [uniqueidentifier] NULL,
[goods_count] [int] NULL,
[price] [float] NULL,
[discount_sum] [float] NULL,
[overal_sum] [float] NULL,
CONSTRAINT [PK_CASH_VOUCHER] PRIMARY KEY CLUSTERED
(
[uid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[cash_voucher] WITH CHECK ADD CONSTRAINT [FK_CASH_VOUCHER_REFERENCE_CASH_VOUCHER_TITLE] FOREIGN KEY([uid_cash_voucher_title])
REFERENCES [dbo].[cash_voucher_title] ([uid])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[cash_voucher] CHECK CONSTRAINT [FK_CASH_VOUCHER_REFERENCE_CASH_VOUCHER_TITLE]
GO
17 апр 13, 15:31    [14193330]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
поситайте все для 2х таблиц но с учетом полей (uid_cash_desk) и патом уже джойните
как вариант
17 апр 13, 15:35    [14193382]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
aleks2
Guest
( cash_voucher_title AS CVT
INNER JOIN cash_voucher AS CV
ON CVT.uid = CV.uid_cash_voucher_title
)

Ну и нахера, страдалец, ты суешь ненужный JOIN под группировку?
Вот сдались те эти voucher_title.

Да и второй JOIN выглядит очень сомнительно.
17 апр 13, 15:40    [14193417]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
Мистер Хенки
Member

Откуда: канализация
Сообщений: 6615
cdsmika
Maxx
а если поменять
ORDER BY
CVT.dt DESC

на
ORDER BY
CVT.dt 


план меняеться ?


Тут проблема не в ORDER BY, его можно совсем убрать. А вот группировка на многое влияет. Можно ли индекс на несколько таблиц составлять?

нет. Полагаю, здесь нельзя избавиться от сортировки из-за группировки.
17 апр 13, 15:42    [14193436]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
select
 ............. 
from (
    select
          CVT.shift_number ,
        CVT.number ,
        CVT.dt ,
       CVT.uid_cash_desk ,
        SUM(CV.goods_count) ,
        SUM(CV.price * CV.goods_count) ,
        SUM(CV.overal_sum) ,
        SUM(CV.discount_sum) 
   from  cash_voucher_title AS CVT
    INNER JOIN cash_voucher AS CV ON CVT.uid = CV.uid_cash_voucher_title
   WHERE CVT.dt BETWEEN '04/04/2013 00:00:00' AND '04/11/2013 23:59:59' 
  GROUP BY
        CVT.shift_number,
        CVT.number,
        CVT.dt,
       CVT.uid_cash_desk
   )
   LEFT JOIN cash_desk AS CD
ON CVT.uid_cash_desk = CD.uid 
Не ?
17 апр 13, 15:47    [14193478]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
Мистер Хенки
Member

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


Тут проблема не в ORDER BY, его можно совсем убрать. А вот группировка на многое влияет. Можно ли индекс на несколько таблиц составлять?

нет. Полагаю, здесь нельзя избавиться от сортировки из-за группировки.

Хотя вру
если
cash_voucher_title AS CVT
INNER JOIN cash_voucher AS CV
ON CVT.uid = CV.uid_cash_voucher_title

вот так можно заменить, то можно избавиться от группировки в конце

	cash_voucher_title AS CVT
	cross apply (select SUM(CV.goods_count) ,
						SUM(CV.price * CV.goods_count) ,
						SUM(CV.overal_sum) ,
						SUM(CV.discount_sum) 
				from cash_voucher AS CV
				where CVT.uid = CV.uid_cash_voucher_title
				group by CV.uid_cash_voucher_title		) cvt (val1,val2,val3,val4)
17 апр 13, 15:50    [14193499]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
aleks2
Guest
Мистер Хенки
вот так можно заменить, то можно избавиться от группировки в конце


Ты чо, прикалываешься над тредстартером?
CROSS APPLY ишо никогда не был быстрым.
17 апр 13, 16:04    [14193609]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
cdsmika
Member

Откуда:
Сообщений: 193
Maxx, да согласен ступил
17 апр 13, 16:13    [14193693]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
cdsmika
Member

Откуда:
Сообщений: 193
Maxx
select
 ............. 
from (
    select
          CVT.shift_number ,
        CVT.number ,
        CVT.dt ,
       CVT.uid_cash_desk ,
        SUM(CV.goods_count) ,
        SUM(CV.price * CV.goods_count) ,
        SUM(CV.overal_sum) ,
        SUM(CV.discount_sum) 
   from  cash_voucher_title AS CVT
    INNER JOIN cash_voucher AS CV ON CVT.uid = CV.uid_cash_voucher_title
   WHERE CVT.dt BETWEEN '04/04/2013 00:00:00' AND '04/11/2013 23:59:59' 
  GROUP BY
        CVT.shift_number,
        CVT.number,
        CVT.dt,
       CVT.uid_cash_desk
   )
   LEFT JOIN cash_desk AS CD
ON CVT.uid_cash_desk = CD.uid 
Не ?


Да, не заметил, спасибо! Еще что-нть?
17 апр 13, 16:15    [14193702]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
cdsmika
Member

Откуда:
Сообщений: 193
Переписал на:

SELECT
CVT.shift_number AS НомерСмены,
CVT.number AS НомерЧека,
CVT.dt AS ДатаЧека,
CD.logNumKkm AS НомерКассы,
CD.uid_shop AS Подразделение_uid,
_V.goods_count AS Количество,
_V.[sum] AS СуммаБезСкидки,
_V.overal_sum AS Сумма,
_V.discount_sum AS СуммаСкидки
FROM
(
SELECT
CV.uid_cash_voucher_title,
SUM(CV.goods_count) AS goods_count,
SUM(CV.price * CV.goods_count) AS [sum],
SUM(CV.overal_sum) AS overal_sum,
SUM(CV.discount_sum) AS discount_sum
FROM
cash_voucher AS CV
GROUP BY
CV.uid_cash_voucher_title
) _V

INNER JOIN cash_voucher_title AS CVT
ON CVT.uid = _V.uid_cash_voucher_title
LEFT JOIN cash_desk AS CD
ON CVT.uid_cash_desk = CD.uid
WHERE
CVT.dt BETWEEN '04/04/2012 00:00:00' AND '04/11/2013 23:59:59'

ORDER BY
CVT.dt DESC

Добавил индекс:

CREATE NONCLUSTERED INDEX ix_goods_count_price_discount_overal_sum
ON [dbo].[cash_voucher] ([uid_cash_voucher_title])
INCLUDE ([goods_count],[price],[discount_sum],[overal_sum])

Время осталось прежним
17 апр 13, 16:51    [14193989]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
cdsmika,
план покажите ,да и запрос ваш отличаеться от тго,что вам таки предлагали
17 апр 13, 16:54    [14194009]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
cdsmika
Member

Откуда:
Сообщений: 193
Maxx
cdsmika,
план покажите ,да и запрос ваш отличаеться от тго,что вам таки предлагали


К сообщению приложен файл (plan3.sqlplan - 55Kb) cкачать
17 апр 13, 17:10    [14194156]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
Maxx
Member [скрыт]

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

а моего запроса ?
Просто у вас сначала считаеться вся таблица cash_voucher ,а только патом "фильтруеться"
17 апр 13, 17:16    [14194198]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
cdsmika
Member

Откуда:
Сообщений: 193
Maxx
cdsmika,

а моего запроса ?
Просто у вас сначала считаеться вся таблица cash_voucher ,а только патом "фильтруеться"


SELECT
_V.shift_number AS НомерСмены,
_V.number AS НомерЧека,
_V.dt AS ДатаЧека,
CD.logNumKkm AS НомерКассы,
CD.uid_shop AS Подразделение_uid,
_V.goods_count AS Количество,
_V.[sum] AS СуммаБезСкидки,
_V.overal_sum AS Сумма,
_V.discount_sum AS СуммаСкидки
FROM
(
SELECT
CVT.shift_number,
CVT.number,
CVT.dt,
CVT.uid_cash_desk,
SUM(CV.goods_count) AS goods_count,
SUM(CV.price * CV.goods_count) AS [sum],
SUM(CV.overal_sum) AS overal_sum,
SUM(CV.discount_sum) AS discount_sum
FROM
cash_voucher AS CV
INNER JOIN cash_voucher_title AS CVT
ON CVT.uid = CV.uid_cash_voucher_title
WHERE CVT.dt BETWEEN '04/04/2012 00:00:00' AND '04/11/2013 23:59:59'
GROUP BY
CVT.shift_number,
CVT.number,
CVT.dt,
CVT.uid_cash_desk
) _V

LEFT JOIN cash_desk AS CD
ON _V.uid_cash_desk = CD.uid
ORDER BY
_V.dt DESC

К сообщению приложен файл (plan4.sqlplan - 55Kb) cкачать
17 апр 13, 18:17    [14194619]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
cdsmika
Member

Откуда:
Сообщений: 193
Maxx
cdsmika,

а моего запроса ?
Просто у вас сначала считаеться вся таблица cash_voucher ,а только патом "фильтруеться"


Сортировки прибавилось
17 апр 13, 18:18    [14194626]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
cdsmika
Member

Откуда:
Сообщений: 193
Maxx
cdsmika,

а моего запроса ?
Просто у вас сначала считаеться вся таблица cash_voucher ,а только патом "фильтруеться"


Просто самые большая здесь cash_voucher, я подумал сначала ограничить кол-во записей группировкой, а потом объединять с cash_voucher_title

SELECT
_V.shift_number AS НомерСмены,
_V.number AS НомерЧека,
_V.dt AS ДатаЧека,
CD.logNumKkm AS НомерКассы,
CD.uid_shop AS Подразделение_uid,
_V.goods_count AS Количество,
_V.[sum] AS СуммаБезСкидки,
_V.overal_sum AS Сумма,
_V.discount_sum AS СуммаСкидки
FROM
(
SELECT
CVT.shift_number,
CVT.number,
CVT.dt,
CVT.uid_cash_desk,
SUM(CV.goods_count) AS goods_count,
SUM(CV.price * CV.goods_count) AS [sum],
SUM(CV.overal_sum) AS overal_sum,
SUM(CV.discount_sum) AS discount_sum
from cash_voucher AS CV
INNER JOIN cash_voucher_title AS CVT
ON CVT.uid = CV.uid_cash_voucher_title
WHERE CVT.dt BETWEEN '04/04/2012 00:00:00' AND '04/11/2013 23:59:59'
GROUP BY
CVT.shift_number,
CVT.number,
CVT.dt,
CVT.uid_cash_desk
) _V

LEFT JOIN cash_desk AS CD
ON _V.uid_cash_desk = CD.uid

ORDER BY
_V.dt DESC

Разница почти не заметна
17 апр 13, 18:24    [14194661]     Ответить | Цитировать Сообщить модератору
 Re: Помогите оптимизировать запрос!  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
угу как не крути 200к на 90к .... в средгнем 2 позиции в чеке
а реально ли ,что
select COUNT(1)
from cash_voucher AS CV
WHERE CVT.dt BETWEEN '04/04/2012 00:00:00' AND '04/11/2013 23:59:59'


вернет ~ 90 к записей ?
17 апр 13, 18:33    [14194713]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить