Добро пожаловать в форум, Guest >> Войти | Регистрация | Поиск | Правила | | В избранное | Подписаться | ||
Все форумы / Microsoft SQL Server |
![]() ![]() |
minya13_85 Member Откуда: Москва Сообщений: 603 |
Есть табличка остатки, там пишется движение по каждому штрихкоду, как он двигался с одного склада на другой или был отгружен клиенту.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] Ответить | Цитировать Сообщить модератору |
Владислав Колосов Member Откуда: Сообщений: 8353 |
minya13_85, ничего не сделаете с запросами: или грубой силой - увеличением вычислительной мощности или считайте агрегацию заранее. Можно секционировать данные в остатках по masterID, если мало складов приходит во временную таблицу и данные об остатках более-менее распределены по ним. Будете просматривать меньший объём в запросе. |
22 мар 18, 19:12 [21278754] Ответить | Цитировать Сообщить модератору |
minya13_85 Member Откуда: Москва Сообщений: 603 |
Владислав Колосов, да вот же заранее не просчитать, это остаток на заданную дату, динамический запрос. Может еще есть варианты? как избавиться от параллелизма этого? |
23 мар 18, 08:49 [21279425] Ответить | Цитировать Сообщить модератору |
nvv Member Откуда: Сообщений: 54 |
minya13_85, Почему нет? Учётные системы именно так и делают. Например хранят рассчитанные остатки на начало каждого месяца. Тогда движений может быть хоть миллиард, а остатков на каждый месяц по разному: где густо, а где и пусто. Таким образом усложняются запросы, но скорость на высоте. |
23 мар 18, 20:24 [21282003] Ответить | Цитировать Сообщить модератору |
LSV Member [заблокирован] Откуда: Киев Сообщений: 30817 |
Я сделал так: Есть журнал движения (как в сабже) и есть журнал незакрытых партий, т.е. остаток партии без привязок "приход-расход". Незакрытые партии и есть "остаток на сейчас". А остаток на любое время это "сумма незакрытых партий" - "обороты от нужной даты до сейчас по журналу движения". Производительность плавно убывает с удаленностью даты "от сейчас", но старые даты редко нужны. Такая схема редко требует урезки. зы: если пришло 10 и ушло 10, то партия исчерпана и ее не нужно считать в остатках. |
23 мар 18, 22:04 [21282141] Ответить | Цитировать Сообщить модератору |
bilov Member Откуда: Санкт-Петербург Сообщений: 498 |
Здравствуйте. Можно задать маленький вопрос чтоб не создавать тему. В MSSQL 2012 есть в таблице некластеризованный индекс из 3х полей, одно из его полей C_Tov не имеет ограничения (NOT NULL). Фактически значений NULL в этом поле нет и не будет. Таблица довольно большая и поле C_Tov участвует во множестве выборок и джоинов. Будет ли лучше для производительности этих запросов если я поставлю на него ограничение NOT NULL? |
23 мар 18, 23:26 [21282299] Ответить | Цитировать Сообщить модератору |
uaggster Member Откуда: Сообщений: 960 |
Будет. |
25 мар 18, 21:20 [21284606] Ответить | Цитировать Сообщить модератору |
bilov Member Откуда: Санкт-Петербург Сообщений: 498 |
uaggster, Спасибо!!! |
25 мар 18, 22:40 [21284720] Ответить | Цитировать Сообщить модератору |
Все форумы / Microsoft SQL Server | ![]() |