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

Откуда:
Сообщений: 2028
Всем доброго времени суток. В БД существуют 3 таблицы: App_Folder, App_File - описание связей папок с файлами и
связующая таблица с корневой папкой App_ConcernRootFileFolder.

Если есть 5 записей в App_ConcernRootFileFolder, и около 100 в сумме в App_Folder, App_File, то при удалении данных
через запрос Delete from App_ConcernRootFileFolder удаление происходит порядка 3х минут + пухнет лог транзакций (модель восстановление базы - SIMPLE). Данных в таблице App_File на 2ГБ. (колонка varbinary(max))

Как нибудь можно повлиять на скорость удаления и(или) лог транзакций?
Возможно криво написан триггер на удаление...


Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1) (Hypervisor)


Создание таблиц в БД:
+


CREATE TABLE [dbo].[App_Folder](
[idFolder] [int] IDENTITY(1,1) NOT NULL,
[folderName] [varchar](200) NOT NULL,
[idParentFolder] [int] NULL,
[idCreator] [int] NOT NULL,
[idEditor] [int] NULL,
[dateCreated] [datetime] NULL,
[dateEdited] [datetime] NULL,
[isRemoved] [bit] NOT NULL,
[folderPath] [varchar](1000) NULL,
CONSTRAINT [PK_App_Folder] PRIMARY KEY CLUSTERED
(
[idFolder] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_App_Folder] UNIQUE NONCLUSTERED
(
[idFolder] ASC,
[idParentFolder] 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].[App_Folder] WITH CHECK ADD CONSTRAINT [FK_Folder_Parent_Child] FOREIGN KEY([idParentFolder])
REFERENCES [dbo].[App_Folder] ([idFolder])
GO

ALTER TABLE [dbo].[App_Folder] CHECK CONSTRAINT [FK_Folder_Parent_Child]
GO


CREATE TABLE [dbo].[App_File](
[idFile] [int] IDENTITY(1,1) NOT NULL,
[idFolder] [int] NOT NULL,
[fileName] [varchar](200) NOT NULL,
[fileExtention] [varchar](10) NOT NULL,
[idCreator] [int] NOT NULL,
[idEditor] [int] NULL,
[dateCreated] [datetime] NULL,
[dateEdited] [datetime] NULL,
[isRemoved] [bit] NOT NULL,
[filePath] [varchar](1000) NULL,
[fileCompressedData] [varbinary](max) NULL,
[isCompressed] [bit] NULL,
CONSTRAINT [PK_App_File] PRIMARY KEY CLUSTERED
(
[idFile] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_App_File] UNIQUE NONCLUSTERED
(
[idFolder] ASC,
[fileName] ASC,
[fileExtention] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO

ALTER TABLE [dbo].[App_File] WITH CHECK ADD CONSTRAINT [FK_File_Folder] FOREIGN KEY([idFolder])
REFERENCES [dbo].[App_Folder] ([idFolder])
ON UPDATE CASCADE
ON DELETE CASCADE
GO

ALTER TABLE [dbo].[App_File] CHECK CONSTRAINT [FK_File_Folder]
GO

CREATE TABLE [dbo].[App_ConcernRootFileFolder](
[idConcernRootFileFolder] [int] IDENTITY(1,1) NOT NULL,
[idFolder] [int] NOT NULL,
[idConcern] [int] NOT NULL,
[idCreator] [int] NOT NULL,
[idEditor] [int] NULL,
[dateCreated] [datetime] NULL,
[dateEdited] [datetime] NULL,
CONSTRAINT [PK_ConcernRootFileFolder] PRIMARY KEY CLUSTERED
(
[idConcernRootFileFolder] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_App_ConcernRootFileFolder_Concern] UNIQUE NONCLUSTERED
(
[idConcern] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [UK_App_ConcernRootFileFolder_Folder] UNIQUE NONCLUSTERED
(
[idFolder] 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].[App_ConcernRootFileFolder] WITH CHECK ADD CONSTRAINT [FK_ConcernRootFileFolder_Folder] FOREIGN KEY([idFolder])
REFERENCES [dbo].[App_Folder] ([idFolder])
GO

ALTER TABLE [dbo].[App_ConcernRootFileFolder] CHECK CONSTRAINT [FK_ConcernRootFileFolder_Folder]
GO



CREATE TRIGGER [dbo].[ConcernFolder_Delete_Trigger]
ON [dbo].[App_ConcernRootFileFolder]
for DELETE

AS
SET NOCOUNT ON;

DELETE FROM App_Folder
WHERE idFolder in ( SELECT idFolder FROM DELETED )

GO


CREATE TRIGGER [dbo].[Folder_Delete_Trigger]
ON [dbo].[App_Folder]
INSTEAD OF DELETE
AS

SET NOCOUNT ON;

WITH cte AS
(
SELECT idFolder, idParentFolder
FROM DELETED
UNION ALL
SELECT c.idFolder, c.idParentFolder
FROM App_Folder AS c
INNER JOIN cte AS p
ON c.idParentFolder = p.idFolder
)

SELECT *
into #tmp
FROM cte
OPTION (MAXRECURSION 32767)

DELETE FROM App_Folder
where idFolder IN (
SELECT idFolder FROM #TMP)


drop table #tmp


GO
28 июн 12, 15:32    [12789355]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с удалением дерева из БД (быстродействие)  [new]
Glory
Member

Откуда:
Сообщений: 104760
LameUser
Как нибудь можно повлиять на скорость удаления

Проанализировать план выполнения

LameUser
и(или) лог транзакций?

Уменьшить размер данных в таблицах
28 июн 12, 16:14    [12789676]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с удалением дерева из БД (быстродействие)  [new]
Программист-Любитель
Member

Откуда:
Сообщений: 16839
Записей совсем немного - 5 на 100. Проблема, наверное в том что поле варбинари и общий объем получается 2 ГБ. У нас была таблица, в которой огромные блобы тоже лежали прямо в самой таблице сущностей. Мы их вынесли в отдельную таблицу отдельной базы, где ничего кроме блобов не хранится. Работает все хорошо. Размер очень средний - число записей до ста тысяч не дотягивает, объем базы с блобами меньше 100ГБ. Правда крутится все на очень-очень слабом сервере.
28 июн 12, 16:35    [12789801]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с удалением дерева из БД (быстродействие)  [new]
LameUser
Member

Откуда:
Сообщений: 2028
Программист-Любитель
Записей совсем немного - 5 на 100. Проблема, наверное в том что поле варбинари и общий объем получается 2 ГБ. У нас была таблица, в которой огромные блобы тоже лежали прямо в самой таблице сущностей. Мы их вынесли в отдельную таблицу отдельной базы, где ничего кроме блобов не хранится. Работает все хорошо. Размер очень средний - число записей до ста тысяч не дотягивает, объем базы с блобами меньше 100ГБ. Правда крутится все на очень-очень слабом сервере.


А отдельная база на том же сервере? или на другой машине?
29 июн 12, 06:11    [12792043]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с удалением дерева из БД (быстродействие)  [new]
LameUser
Member

Откуда:
Сообщений: 2028
Glory
LameUser
Как нибудь можно повлиять на скорость удаления

Проанализировать план выполнения

LameUser
и(или) лог транзакций?

Уменьшить размер данных в таблицах


Размер данных уменьшить не могу, нет никаких возможностей в режиме recovery Simple повлиять на размер лога во время транзакций? На данный момент получается урезать лог только после бэкапа базы...


План выполнения чуть позже предоставлю, пытаюсь на большем объеме проверить ситуацию..
29 июн 12, 06:21    [12792048]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с удалением дерева из БД (быстродействие)  [new]
Программист-Любитель
Member

Откуда:
Сообщений: 16839
LameUser
А отдельная база на том же сервере? или на другой машине?
На том же сервере.
29 июн 12, 08:05    [12792101]     Ответить | Цитировать Сообщить модератору
 Re: Проблемы с удалением дерева из БД (быстродействие)  [new]
Glory
Member

Откуда:
Сообщений: 104760
LameUser
Размер данных уменьшить не могу, нет никаких возможностей в режиме recovery Simple повлиять на размер лога во время транзакций?

Все изменяемые в транзакции данные помещаются в журнал. Больше данных - больше места в журнале. И это не зависит от модели восстановления.

LameUser
На данный момент получается урезать лог только после бэкапа базы.

Пока транзакция не завершена, вы не можете удалить ее данные из журнала.
29 июн 12, 12:18    [12793605]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить