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

Откуда: Москва
Сообщений: 598
 		Есть табличка остатки, там пишется движение по каждому штрихкоду, как он двигался с одного склада на другой или был отгружен клиенту.
CREATE TABLE [dbo].[ostatki](
	[code] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[identifier] [nvarchar](20) NOT NULL,
	[docID] [int] NULL,
	[motion] [smallint] NULL,
	[lineID] [smallint] NULL,
	[skladID] [smallint] NULL,
	[masterID] [int] NULL,
	[stateID] [smallint] NULL,
	[dateOut] [smalldatetime] NULL,
	[deleted] [smallint] NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
 CONSTRAINT [PK_ostatki] PRIMARY KEY CLUSTERED 
(
	[code] 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


В Этой таблице 109 млн записей и два индекса:
CREATE NONCLUSTERED INDEX [IX_deleted] ON [dbo].[ostatki]
(
	[deleted] ASC
)
INCLUDE ( 	[identifier],
	[docID],
	[motion],
	[masterID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]



CREATE NONCLUSTERED INDEX [IX_identifier] ON [dbo].[ostatki]
(
	[identifier] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


Есть табличка справочник складов компании она просто таблица без индексов там 120 записей
CREATE TABLE [dbo].[M_SKLAD](
	[OrgID] [int] NULL,
	[sklad] [nvarchar](20) NULL,
	[masterID] [int] NULL
) ON [PRIMARY]


Есть табличка фирмы, там более укрупненное название складов. В этой таблице 10 тыщ записей
CREATE TABLE [dbo].[firmName](
	[code] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
	[FirmName] [nvarchar](50) NOT NULL,
	[inn] [nvarchar](15) NULL,
	[FirmID] [int] NOT NULL,
	[globalID] [int] NOT NULL,
 CONSTRAINT [PK_firmName] PRIMARY KEY NONCLUSTERED 
(
	[code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [IX_firmName_1] UNIQUE NONCLUSTERED 
(
	[FirmName] DESC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Там есть 1 индекс 
CREATE UNIQUE CLUSTERED INDEX [IX_firmName] ON [dbo].[firmName]
(
	[FirmID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


Есть таблица с датами по документу, в этой таблице 2 млн записей
CREATE TABLE [dbo].[docsDate](
	[docCreateDate] [smalldatetime] NULL,
	[docExecuteDate] [smalldatetime] NULL,
	[docPrintDate] [smalldatetime] NULL,
	[docID] [int] NULL,
	[code] [int] IDENTITY(1,1) NOT NULL,
 CONSTRAINT [PK_docsDate] PRIMARY KEY NONCLUSTERED 
(
	[code] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


Там есть 3 индекса
CREATE UNIQUE CLUSTERED INDEX [IX_docsDate] ON [dbo].[docsDate]
(
	[docID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

CREATE NONCLUSTERED INDEX [NCI_docprintdate] ON [dbo].[docsDate]
(
	[docPrintDate] ASC
)
INCLUDE ( 	[docID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


CREATE NONCLUSTERED INDEX [IX_docexecutedate] ON [dbo].[docsDate]
(
	[docExecuteDate] ASC
)
INCLUDE ( 	[docID]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO


Я просчитываю остаток, какие штрихкоды находились на складах компании на заданную дату.
DECLARE @d smalldatetime
set dateformat dmy
set @d='07-03-2018'


CREATE TABLE #tbl (masterID int PRIMARY KEY,globalID int)
INSERT INTO #tbl(masterID,globalID)
select masterID,f.globalID 
from Accounting.dbo.M_SKLAD as s
LEFT join Attrib.dbo.firmname as f with(nolock) on s.masterid = f.firmid



SELECT  o.identifier,MAX(1000000000*convert(bigint,o.docID)+(case when o.motion=1 then o.masterID else 0 end))-1000000000*convert(bigint,MAX(o.docID)) as masterid
FROM #tbl as t with(nolock)
LEFT join main.dbo.ostatki as o with(nolock) on t.masterID=o.masterID
LEFT JOIN mainex.dbo.docsDate with(nolock) on docsDate.docid=o.docid
where o.deleted=0 and docsDate.docExecuteDate<@d  
group by o.identifier,t.globalID
having sum(o.motion)=1 
and MAX(1000000000*convert(bigint,o.docID)+(case when o.motion=1 then o.masterID else 0 end))-1000000000*convert(bigint,MAX(o.docID))<>0

option(recompile)


drop table #tbl


По коду поясняю, это длинная конструкция 
MAX(1000000000*convert(bigint,o.docID)+(case when o.motion=1 then o.masterID else 0 end))-1000000000*convert(bigint,MAX(o.docID))

нужна для того чтобы убрать пропуски, нарушена целостность базы, некоторые движений внутри складов нету в таблице, сейчас приведу небольшой пример как обстоят таблицы
SET NOCOUNT ON
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO


IF OBJECT_ID('[TestDoc].[Ostatki]') IS NOT NULL
  DROP TABLE [TestDoc].[Ostatki]

IF OBJECT_ID('[TestDoc].[M_SKLAD]') IS NOT NULL
  DROP TABLE [TestDoc].[M_SKLAD]

IF OBJECT_ID('[TestDoc].[firmname]') IS NOT NULL
  DROP TABLE [TestDoc].[firmname]

IF OBJECT_ID('[TestDoc].[docsdate]') IS NOT NULL
  DROP TABLE [TestDoc].[docsdate]

IF SCHEMA_ID('TestDoc') IS NULL
  EXEC('
    CREATE SCHEMA [TestDoc]
  ')
GO

IF OBJECT_ID('[TestDoc].[Ostatki]') IS NULL
  
CREATE TABLE [TestDoc].[Ostatki]
  (
    [code]        Int           NOT NULL  IDENTITY(1,1),
	[identifier] nvarchar(50) NOT NULL,
	[docid] [int] NULL,
    [motion] [smallint] NULL,
	[masterID] [int] NULL,
	[dateOut] [smalldatetime] NULL,
	[deleted] [smallint] NULL,
   PRIMARY KEY CLUSTERED([code])
  )
GO


IF OBJECT_ID('[TestDoc].[M_SKLAD]') IS NULL
  
CREATE TABLE [TestDoc].[M_SKLAD]
  (
    [OrgID] [int] NULL,
	[sklad] [nvarchar](20) NULL,
	[masterID] [int] NULL
  )
GO


IF OBJECT_ID('[TestDoc].[firmname]') IS NULL
  
CREATE TABLE [TestDoc].[firmname]
  (
    [code] [int] NOT NULL  IDENTITY(1,1),
	[FirmName] [nvarchar](50) NOT NULL,
	[inn] [nvarchar](15) NULL,
	[FirmID] [int] NOT NULL,
	[globalID] [int] NOT NULL,
	 PRIMARY KEY CLUSTERED([code])
  )
GO



IF OBJECT_ID('[TestDoc].[docsdate]') IS NULL
  
CREATE TABLE [TestDoc].[docsdate]
  (
    [docCreateDate] [smalldatetime] NULL,
	[docExecuteDate] [smalldatetime] NULL,
	[docPrintDate] [smalldatetime] NULL,
	[docID] [int] NULL,
	[code] [int] IDENTITY(1,1) NOT NULL,
 PRIMARY KEY CLUSTERED([code])

  )
GO

INSERT INTO TestDoc.docsdate
select '2017-03-01 10:00:00','2017-03-01 15:00:00','2017-03-01 00:00:00',1
UNION
select '2017-03-02 10:00:00','2017-03-02 15:00:00','2017-03-02 00:00:00',2
UNION
select '2017-03-05 10:00:00','2017-03-05 17:00:00','2017-03-05 00:00:00',3
UNION
select '2017-03-08 10:00:00','2017-03-08 15:00:00','2017-03-08 00:00:00',4
UNION
select '2017-03-02 10:00:00','2017-03-02 15:00:00','2017-03-02 00:00:00',5
UNION
select '2017-03-02 11:00:00','2017-03-02 15:00:00','2017-03-02 00:00:00',6
UNION
select '2017-03-08 10:00:00','2017-03-04 15:00:00','2017-03-04 00:00:00',8
UNION
select '2017-03-05 10:00:00','2017-03-05 15:00:00','2017-03-05 00:00:00',7
UNION
select '2017-03-02 10:00:00','2017-03-02 15:00:00','2017-03-02 00:00:00',9
UNION
select '2017-03-04 10:00:00','2017-03-04 15:00:00','2017-03-04 00:00:00',10
UNION
select '2017-03-08 10:00:00','2017-03-08 15:00:00','2017-03-08 00:00:00',11
UNION
select '2017-03-21 10:00:00','2017-03-22 15:00:00','2017-03-22 00:00:00',12


INSERT INTO [TestDoc].[firmname]
SELECT 'ТД','0101',150,150
UNION
SELECT 'ЮК','0102',110,150
UNION
SELECT 'ПК','0103',130,130


INSERT INTO [TestDoc].[M_SKLAD]
SELECT 1,'СКЛАД10',150
UNION
SELECT 1,'СКЛАД4',110
UNION
SELECT 2,'СКЛАД5',130


INSERT INTO [TestDoc].[Ostatki]

SELECT 'А700',1,-1,100,'2017-03-01 10:00:00',0
UNION
SELECT 'А700',1,1,150,'2017-03-01 10:00:00',0
UNION
SELECT 'А700',2,-1,150,'2017-03-02 11:00:00',0
UNION
SELECT 'А700',2,1,110,'2017-03-02 11:00:00',0
UNION
SELECT 'А700',3,-1,110,'2017-03-05 16:00:00',0
UNION
SELECT 'А700',3,1,130,'2017-03-05 16:00:00',0
UNION
SELECT 'А700',4,-1,130,'2017-03-08 10:00:00',0
UNION
SELECT 'А700',4,1,180,'2017-03-08 10:00:00',0
UNION
SELECT 'А600',5,-1,145,'2017-03-02 10:00:00',0
UNION
SELECT 'А600',5,1,200,'2017-03-02 10:00:00',0
UNION
SELECT 'А600',6,-1,200,'2017-03-02 11:00:00',0
UNION
SELECT 'А600',6,1,150,'2017-03-02 11:00:00',0
UNION
SELECT 'А600',7,-1,130,'2017-03-05 16:00:00',0
UNION
SELECT 'А600',7,1,160,'2017-03-05 16:00:00',0
UNION
SELECT 'А600',8,-1,150,'2017-03-08 10:00:00',0
UNION
SELECT 'А600',8,1,130,'2017-03-08 10:00:00',0
UNION
SELECT 'А800',9,-1,126,'2017-03-02 10:00:00',0
UNION
SELECT 'А800',9,1,110,'2017-03-02 10:00:00',0
UNION
SELECT 'А800',10,-1,150,'2017-03-04 11:00:00',0
UNION
SELECT 'А800',10,1,140,'2017-03-04 11:00:00',0
UNION
SELECT 'А800',11,-1,140,'2017-03-08 16:00:00',0
UNION
SELECT 'А800',11,1,110,'2017-03-08 16:00:00',0
UNION
SELECT 'А800',12,-1,130,'2017-03-22 10:00:00',0
UNION
SELECT 'А800',12,1,190,'2017-03-22 10:00:00',0


Уже очень долгое время бьюсь над оптимизацией данного выше запроса
План запроса прилагаю, закинул в рар, т.к. сам план весит 174 кб, превышает 150

К сообщению приложен файл (1.rar - 8Kb) cкачать
22 мар 18, 18:15    [21278669]     Ответить | Цитировать Сообщить модератору
 Re: И снова здравствуйте, Оптимизация.  [new]
Владислав Колосов
Member

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

ничего не сделаете с запросами: или грубой силой - увеличением вычислительной мощности или считайте агрегацию заранее.
Можно секционировать данные в остатках по masterID, если мало складов приходит во временную таблицу и данные об остатках более-менее распределены по ним. Будете просматривать меньший объём в запросе.
22 мар 18, 19:12    [21278754]     Ответить | Цитировать Сообщить модератору
 Re: И снова здравствуйте, Оптимизация.  [new]
minya13_85
Member

Откуда: Москва
Сообщений: 598
 		Владислав Колосов,
да вот же заранее не просчитать, это остаток на заданную дату, динамический запрос. Может еще есть варианты? как избавиться от параллелизма этого?
23 мар 18, 08:49    [21279425]     Ответить | Цитировать Сообщить модератору
 Re: И снова здравствуйте, Оптимизация.  [new]
nvv
Member

Откуда:
Сообщений: 54
 		minya13_85,
Почему нет?
Учётные системы именно так и делают.
Например хранят рассчитанные остатки на начало каждого месяца. Тогда движений может быть хоть миллиард, а остатков на каждый месяц по разному: где густо, а где и пусто. Таким образом усложняются запросы, но скорость на высоте.
23 мар 18, 20:24    [21282003]     Ответить | Цитировать Сообщить модератору
 Re: И снова здравствуйте, Оптимизация.  [new]
LSV
Member [заблокирован]

Откуда: Киев
Сообщений: 30817
 		Я сделал так:

Есть журнал движения (как в сабже) и есть журнал незакрытых партий, т.е. остаток партии без привязок "приход-расход".
Незакрытые партии и есть "остаток на сейчас".
А остаток на любое время это "сумма незакрытых партий" - "обороты от нужной даты до сейчас по журналу движения".

Производительность плавно убывает с удаленностью даты "от сейчас", но старые даты редко нужны.

Такая схема редко требует урезки.

зы: если пришло 10 и ушло 10, то партия исчерпана и ее не нужно считать в остатках.
23 мар 18, 22:04    [21282141]     Ответить | Цитировать Сообщить модератору
 Re: И снова здравствуйте, Оптимизация.  [new]
bilov
Member

Откуда: Санкт-Петербург
Сообщений: 498
 		Здравствуйте. Можно задать маленький вопрос чтоб не создавать тему. В MSSQL 2012 есть в таблице некластеризованный индекс из 3х полей, одно из его полей C_Tov не имеет ограничения (NOT NULL). Фактически значений NULL в этом поле нет и не будет. Таблица довольно большая и поле C_Tov участвует во множестве выборок и джоинов. Будет ли лучше для производительности этих запросов если я поставлю на него ограничение NOT NULL?
23 мар 18, 23:26    [21282299]     Ответить | Цитировать Сообщить модератору
 Re: И снова здравствуйте, Оптимизация.  [new]
uaggster
Member

Откуда:
Сообщений: 815
 		Будет.
25 мар 18, 21:20    [21284606]     Ответить | Цитировать Сообщить модератору
 Re: И снова здравствуйте, Оптимизация.  [new]
bilov
Member

Откуда: Санкт-Петербург
Сообщений: 498
 		uaggster,

Спасибо!!!
25 мар 18, 22:40    [21284720]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить