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

К сообщению приложен файл. Размер - 54Kb
30 июл 15, 16:49    [17956344]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
Minamoto
Member

Откуда: Москва
Сообщений: 1162
Winnipuh
ты то ладно, а SQL Server не удивился файлу master.dat?

Тут не очень давно всплывала тема про базы 6.5, и для меня тоже открытием было, что, ЕМНИП, до версии 6.5 дефалтовое расширение для БД было именно ".dat".
30 июл 15, 16:53    [17956369]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
komrad
Member

Откуда:
Сообщений: 5758
Winnipuh

ты то ладно, а SQL Server не удивился файлу master.dat?


так удивился, что от смеха не мог подняться ;)
30 июл 15, 17:04    [17956423]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
o-o
Guest
komrad
;) нда, красивая идея была

нет, это -- массовый склероз.
мы же еще в той теме проверяли, будет ли "слегка увеличенный" мастер ресториться.
и нет, не будет:
Посоветуйте как обойти ограничение по размеру базы.
и это мастер-мастер, а не бывший мастер.
так что можно было и не проверять.
думаю, при любом ресторе/аттаче последовательность такая:
1. проверка на размер того, что будет. превышение? -- до свидания
2. рестор/аттач и апгрэйд, если надо.
3. проверка на фичи из энтерпрайза, на наличие необходимых таблиц, если это мастер и т.д.

так что лучше все же на подмене файлов играть.
раз приаттачено, то явно в порядке, проверять не будем.

и бэкапить все это дело остается остановкой сервера(случай мастера)/оффлайном базы с копированим файлов баз
30 июл 15, 17:47    [17956675]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
voter
Member

Откуда:
Сообщений: 21
... всем привет!

Рад бурному обсуждению... :-)

Если вернуться к своим скромным баранам:
- как писал, вынес 2 справочника, сделал внешние ключи;
- создал базу с нуля и перенес в нее скриптом структуру табличек;
- переписал код, который теперь после импорта данных во вспомогательную таблицу сначала обновляет справочники новыми данными, а затем переносит данные в основную таблицу заменяя часть данных кодами из справочников;
- прогнал тест на данных 5 месяцев (как и вчера) - 113 млн записей... файл базы весит 5,3 Гб... ;
- на все это ушли почти сутки, даже новости не смотрел... %-( это реально жесть, голова опухла...
- считаю, что пока обойдусь этой версией продукта - просто нет времени и сил все кругом менять...
- импорт и обработка заняли почти час, это прилично дольше чем вчера, поэтому буду пытаться улучшить быстродействие...
- если кому-то будет интересно - могу тут выложить что получилось с целью получить советы по оптимизации логики, приемов работы и т.д..

Признателен всем за участие... %-)
30 июл 15, 22:28    [17957582]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Выложите по возможности... Интересные задачки в пятницу бодрости добавляют :)
31 июл 15, 09:48    [17958530]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
voter
Member

Откуда:
Сообщений: 21
Привет AlanDenton, привет ALL!

Буду признателен за комментарии, обсуждение, подсказки и критику...


SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

USE [test3]

CREATE TABLE #Files (FullFilePath	VARCHAR (1000) )    -- список обрабатываемых файлов
CREATE TABLE #MisFut (BA_ID			tinyint,
                      FU_Seccode	nvarchar(4))		-- список для обновления справочника Futures

DELETE FROM BulkForts;  

--получение списка файлов из указанного каталога и его подкаталагов
INSERT INTO #Files exec master..xp_cmdshell 'DIR "D:\MOEX\2015\1501*ft.csv" /S /B /A-D'

--убираем строку NULL из полученного списка
DELETE FROM #Files WHERE FullFilePath is null 

DECLARE @FILE_NAME VARCHAR(1000)
DECLARE @SQL_CSV VARCHAR(1000)

-- Цикл по таблице #Files, содержащей имена загружаемых файлов
WHILE 1 = 1
BEGIN
SELECT top(1) @FILE_NAME = FullFilePath FROM #Files
IF @@ROWCOUNT = 0  BREAK

-- Командная строка для загрузки текстового файла
set @SQL_CSV ='BULK INSERT BulkForts FROM '''+@FILE_NAME+'''
WITH(CODEPAGE = ''RAW'',
FIRSTROW = 2,
FIELDTERMINATOR = '';'',
ROWTERMINATOR = ''\n'')'

-- Загрузка текстового файла в таблицу
exec (@SQL_CSV)
PRINT 'Импортирован файл - ' + @FILE_NAME + ' - ' + (CONVERT( VARCHAR(24), GETDATE(), 20))


-- Удаляем записи, в которых код контракта не соответствует Bassets
DELETE FROM BulkForts 
WHERE Left(BulkForts.IP_Seccode, 2) not in (SELECT Distinct Left(BA_Basset, 2) FROM Bassets )

-- ниже за 3 итерации решаю задачу одновления справочника Futures - лаконичнее пока не умею
--Дополняем список для обновления справочника Futures новыми контрактами
INSERT #MisFut (FU_Seccode)       
SELECT distinct [IP_Seccode]
FROM BulkForts
WHERE not exists (SELECT top 1 1 FROM Futures WHERE Futures.FU_Seccode = BulkForts.IP_Seccode)

--Проставляем код базового актива из Bassets (этот справочник изначально наполнен и пополняется только вручную)
update #MisFut 
SET BA_ID = Bassets.BA_ID 
FROM #MisFut join Bassets on Left(#MisFut.FU_Seccode, 2) = Left(Bassets.BA_Basset, 2)

-- Переносим список для обновления справочника фьючерсов в сам справочник
INSERT INTO Futures 
           ([BA_ID]
           ,[FU_Seccode])
SELECT BA_ID, FU_Seccode from #MisFut ;

SELECT * from #MisFut -- смотреть в процессе отладки
DELETE FROM #MisFut


--Пополняем справочник дней - тут проще - столбец 1 - справился 1-м запросом
INSERT into Days (DA_Date )       
SELECT distinct CAST(Left([IP_Date], 10) As date)  
FROM BulkForts
WHERE not exists (SELECT top 1 1 FROM Days WHERE Days.DA_Date = BulkForts.IP_Date)


--Обновляем BulkForts - названия контрактов на их коды из справочника
update BulkForts 
SET IP_Seccode = Futures.FU_ID    
FROM BulkForts join Futures on BulkForts.IP_Seccode = Futures.FU_Seccode


--Обновляем BulkForts - ДНИ сделок на их коды из справочника
update BulkForts 
SET IP_Nosystem = Days.DA_ID          -- не хватило поля, пишем в неиспользуемое IP_Nosystem --> 
-- --> пытался приделать в таблицу BulkForts 2 дополнительных поля, где хотел хранить получаемые --> 
-- --> из справочников ИД записей  - но при импорте текстового файла возникала ошибка при наличии лишних полей
FROM BulkForts join Days on BulkForts.IP_Date = Days.DA_Date


--Копируем данные в таблицу Forts 
INSERT INTO Forts ([IP_Number]
      ,[FU_ID]
      ,[DA_ID]
      ,[IP_Time]    
      ,[IP_Price]
      ,[IP_Amount]) 
SELECT CAST([IP_Number] AS integer), 
       IP_Seccode,    
       IP_Nosystem,                  -- берем дату из IP_Nosystem
       CAST(SUBSTRING([IP_Date], 12, 8) As time),	   
       CAST([IP_Price] AS money),
       CAST([IP_Amount] AS integer)
FROM BulkForts

PRINT 'Данные перенесены - ' + @FILE_NAME + ' - ' + (CONVERT( VARCHAR(24), GETDATE(), 20)) 


--Очищаем BulkForts;
DELETE FROM BulkForts;

-- Удаление строки, содержащей имя загруженного файла из таблицы
DELETE FROM #Files WHERE FullFilePath = @FILE_NAME



END  


Схему прилагаю...

К сообщению приложен файл. Размер - 123Kb
31 июл 15, 10:26    [17958752]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
DELETE FROM #MisFut
DELETE FROM BulkForts


могу посоветовать использовать TRUNCATE TABLE которая явно быстрее отработает в данной ситуации.
Да и лог Вам спасибо скажет :)

и еще SELECT TOP(1) в EXISTS необязателен. Оптимизатор запроса прекрасно осведомлен, что нужно вернуть хотя бы одну строку.

Остальное чуть позже постараюсь ответить - надо педалить :)
31 июл 15, 10:37    [17958839]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
voter,

ктсати, проанализируйте свои данные на предмет Sparse Column использовать
31 июл 15, 10:40    [17958855]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
voter
Member

Откуда:
Сообщений: 21
AlanDenton, спасибо!

Буду рад любым подсказкам... для меня это первый опыт с MS SQL, очень понравилось... %-) (в разы быстрее привычного MS Access)
Проблема в том, что нет знакомых, которые шарят в этом и могли бы быстро показать основные приемы и ответить на первые вопросы применительно к реальной задаче... книжки читаю уже неделю, но сразу оно не придет... все написанное - компиляция материалов данного форума применительно к моей задаче...
31 июл 15, 10:51    [17958921]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
voter
Member

Откуда:
Сообщений: 21
Winnipuh
voter,

ктсати, проанализируйте свои данные на предмет Sparse Column использовать


... сразу не могу вам ответить по сути т.к. первый раз слышу (:-)), полез читать... спасибо!
31 июл 15, 10:55    [17958947]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
voter
Member

Откуда:
Сообщений: 21
Winnipuh
voter,

кстати, проанализируйте свои данные на предмет Sparse Column использовать


... почитал... насколько я понял, объявление поля sparse будет полезно если данные в нем содержат много значений NULL... имхо у меня такого нет, основная таблица - биржевые данные, если события нет - нет и записи, если есть - у него есть все свойства - дата, время, товар, цена, количество...
31 июл 15, 11:16    [17959071]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
Vadim999
Member [скрыт]

Откуда:
Сообщений: 107
Странно, если автор хочет использовать именно Express, т.е. принципиально бесплатную версию БД и при этом сталкивается с ограничениями объема, то логично было бы отказаться от Express :)
31 июл 15, 11:26    [17959136]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
o-o
Guest
Vadim999
Странно, если автор хочет использовать именно Express, т.е. принципиально бесплатную версию БД и при этом сталкивается с ограничениями объема, то логично было бы отказаться от Express :)

если у кого-то нет денег, то вполне логично пользоваться тем, что доступно.
тем более, что и баз можно наплодить 32,767, и в мастер налить безлимитно
и заранее создать терабайтную базу, огребая при этом лишь проблемы бэкапа/рестора.
выбор просто офигителен
31 июл 15, 11:32    [17959170]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
voter
Member

Откуда:
Сообщений: 21
Vadim999, приветствую...

Спасибо за интерес:

- это был мой первый опыт взаимодействия с MS SQL
- при выборе руководствовался тем, что ограничение EXPRESS на размер базы 10 Гб... так как в MS Access уперся в ограничение 2 Гб (при 40 млн записей основной таблице) - это показалось много,... подумалось, что этого должно хватить для моих ближайших планов...

- в значительной мере выбор был сделан благодаря стараниям маркетологов MS - express рекламируется как бесплатное приложение и широко доступен в сети... это как печенька на уровне глаз - протяни руку и возьми... расчет в общем-то верный - возможности понравились (скорость поражает), а жадность человеческая конечно теперь будет требовать наращивания версии программы и т.д...

- как писал выше, снизил объем базы почти вдвое и теперь на первом месте не проблема нехватки объема файла базы (на месяц-два ее теперь точно хватит), а оптимизация написанного кода - для меня это первый опыт - буду признателен за критику...

- про MS SQL сомневался когда ставил - смогу ли, насколько удобно и т.д. - теперь сомнений нет - это ВЕШЬ, нужно уметь ее использовать и учиться это делать правильно... поставить другую версию не проблема, как только будет реальная причина это сделать - поставлю....
31 июл 15, 11:52    [17959277]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
voter
Member

Откуда:
Сообщений: 21
o-o
... выбор просто офигителен


Добрый день!

Учитывая, что за мной не стоит корпорация, у меня нет доступа к халявным серверам/лицензиям и это просто мой личный проект/задачка/идея/тест - это отличная возможность попробовать приложить себя к продукту... весь ресурс - домашний ноутбук, и все летает... это уже мощнее и быстрее MS Access и вдобавок бесплатно.... так что согласен с вами... :-)
... и, как уже писал, маркетологи - их не видно, но дело свое делают - насаживают людей на что-то попроще и бесплатно, чтобы взять денег потом.... и оно в целом работает...
31 июл 15, 12:08    [17959359]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
NCHAR -> NVARCHAR - поскольку даже если Вы в комментарии ввели вместо 50 символов ввели 10, для остальных он добавит пробелы. Также в зависимости от коллейшена Вашего можно не юникод юзать, а обычный VARCHAR. MONEY -> SMALLMONEY. Далее хранить дату в INT не сильно кошерно. Лучше DATE - 3 байта. INT - 4.

ИМХО - это еще одна возможность снизить объем, а вместе с ним и количество логических чтений.

Не забываем, что данные читаются с диска и помещаются в буффер пул, а из него потом и читаются. У Вас на экспрессе ограничение на 1Гб используемой сервером ОЗУ. Чем компактнее будут данные - тем меньше будут занимать места и быстрее все должно шевелиться (+/-).

Будет время почитайте на досуге.
31 июл 15, 12:13    [17959391]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
voter
o-o
... выбор просто офигителен


Добрый день!

Учитывая, что за мной не стоит корпорация, у меня нет доступа к халявным серверам/лицензиям и это просто мой личный проект/задачка/идея/тест - это отличная возможность попробовать приложить себя к продукту... весь ресурс - домашний ноутбук, и все летает... это уже мощнее и быстрее MS Access и вдобавок бесплатно.... так что согласен с вами... :-)
... и, как уже писал, маркетологи - их не видно, но дело свое делают - насаживают людей на что-то попроще и бесплатно, чтобы взять денег потом.... и оно в целом работает...


Не в курсе, какие приложения вы используете, но можно использовать PostgreSQL, ограничений нет, денег не просит.
31 июл 15, 12:24    [17959445]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
voter
Member

Откуда:
Сообщений: 21
Winnipuh, добрый день!

Давний приверженец MS Access, ms-продукты мне ближе и понятнее... поэтому ничего больше не рассматривал. Спасибо.
31 июл 15, 12:33    [17959493]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
Winnipuh
Member [заблокирован]

Откуда: Київ
Сообщений: 10428
voter
Winnipuh, добрый день!

Давний приверженец MS Access, ms-продукты мне ближе и понятнее... поэтому ничего больше не рассматривал. Спасибо.


Опять же, я не нашел описания какие у вас клиентские приложения, как они с базой работают, но судя по простой схеме вы спокойно можете поэкспериментировать с PostgreSQL.
31 июл 15, 12:43    [17959540]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
voter
Member

Откуда:
Сообщений: 21
AlanDenton
NCHAR ->
Будет время почитайте на досуге.

... отличная статья - доступно и полезно, спасибо...

AlanDenton
NCHAR -> ... Также в зависимости от коллейшена Вашего можно не юникод юзать, а обычный VARCHAR. MONEY -> SMALLMONEY. Далее хранить дату в INT не сильно кошерно. Лучше DATE - 3 байта. INT - 4.


... под коллейшен понимается файловая система? У меня NTFS, Win8.1... тогда можно VARCHAR, правильно?

Насчет даты в integer - когда делал справочник, пытался сделать smallint - мне казалось, что если я хочу пронумеровать дни и обращаться к ним по номеру, извлекая при необходимости саму дату - будет достаточно smallint, но система видимо думает по другому... кстати так и не понял этого момента - просто подчинился... получается, что пронумеровать дату (дни) с использованием меньшего числа нельзя?
Насчет MONEY -> SMALLMONEY - сейчас буду читать подробнее, т.к. величины не гигантсткие (а бытовые) - должно хватить...

Спасибо!
31 июл 15, 13:16    [17959699]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
https://msdn.microsoft.com/ru-ru/library/ms184391(v=sql.120).aspx

это по поводу COLLATE. чтобы узнать какой у Вас выполните на целевой базе:

SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation')


но подразумеваю что Cyrillic... в общем я это к чему... подумайте - а реально нужен ли Вам юникод.

...хранить дату как INT идея не сильно, как по мне. Правда такой подход часто использует RedGate в своих продуктах, но у них это оправдано. Их базы должны разворачиватся на всех актуальных версиях SQL Server. А в Вашем случае DATE лучше подойдет (он кстати появился в 2008 редакции).

и по поводу NCHAR... не забывайте про пробелы... к примеру:

DECLARE
	  @a CHAR(50) = '123'
	, @b VARCHAR(50) = '123'

SELECT LEN(@a), LEN(@b), '[' + @a + ']', '[' + @b + ']'


думаю что хранить лишние пробелы Вам смысла не имеет.
31 июл 15, 13:31    [17959778]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
voter
Member

Откуда:
Сообщений: 21
AlanDenton
...
SELECT DATABASEPROPERTYEX(DB_NAME(), 'Collation')

...
нужен ли Вам юникод.



...Cyrillic_General_CI_AS... понял, юникод не нужен, в базе будут только цифры и буквы английские...

AlanDenton
...хранить дату как INT идея не сильно, как по мне. ...А в Вашем случае DATE лучше подойдет (он кстати появился в 2008 редакции).


... я сделал справочник дней сначала следуя той логике, что номер дня из моего справочника будет весить легче чем сам день (в основном только для этого), если это не так (день =3 байта, а "номер" в int = 4 байта и меньше ничего нет) - тогда проще отказаться от справочника... если же оставить его из других соображений (комментарии к некоторым дням и т.д. и т.п.) - тогда действительно выгодно поменять INT на Date, согласен... спасибо что разъяснили...

AlanDenton
и по поводу NCHAR... не забывайте про пробелы... к примеру:
...
думаю что хранить лишние пробелы Вам смысла не имеет.


... по своей книжке вижу, что текстовых типов данных всего 4... два, связанных с юникоде отпадают, остается 2... для полей типа комментариев лучше varchar т.к. длина их будет переменна... правильно? Если есть поле кода инструмента фиксированной длины - 4 символа - имеет смысл применять что то кроме CHAR(4)? Вроде оптимально....

P.S.
Посмотрел сейчас снова на свою базу - много переделывать... %-) Вот к чему азарт приводит... %-)

... но с другой стороны - кто отец опыта?... ошибки [трудные]...

Спасибо!
31 июл 15, 14:16    [17960113]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
AlanDenton
Member [скрыт]

Откуда:
Сообщений: 1004
Ответ на все Ваши вопросы - да.

Если честно, прекрасному предела нет. Не раз в этом убеждаюсь. Даже с учетом Ваших будующих изменений всегда есть возможность сделать еще круче. Если будет желание - выложите после изменения.

На самом деле текстовых данных немного больше. Например, TEXT, NTEXT, но их лучше не использовать - они устаревшие. Есть еще такой одиозный тип данных - XML. Когда на прошлом месте работал - смотрели как он храниться (на 2008). Оказалось как NVARCHAR.
31 июл 15, 14:22    [17960146]     Ответить | Цитировать Сообщить модератору
 Re: Экономия размера базы  [new]
o-o
Guest
AlanDenton
На самом деле текстовых данных немного больше. Например, TEXT, NTEXT, но их лучше не использовать - они устаревшие. Есть еще такой одиозный тип данных - XML. Когда на прошлом месте работал - смотрели как он храниться (на 2008). Оказалось как NVARCHAR.

ну, про XML известно, что он LOB.
а вот первые 2 вообще лучше не упоминать, особенно в свете 2014-ого.
я слегка влезу с оффтопом, вроде ТС не ругается, что мы помойку разводим в теме...
кто-нибудь может предложить, как побороть мерзкое хранение этих типов?
("одиозный" это кстати от "odioso"? )
у нас есть г. таблица, всего 6 Гиг, строк 2 млн, но зато какие.
меется ntext. и вот ето г. требуется регулярно копировать в другую базу.
уму непостижимо. 45 минут вычитывает и ровно столько же пишет.
если класть в nvarchar(max), на 18 минут меньше, но картины не меняет.
если НЕ копировать блоб-поле, вообще моментально, но нет, им жутко нужен этот ntext.
и там достаточно строк длины 50.000 символов.
куда и как такое запаковать?
31 июл 15, 15:17    [17960576]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: Ctrl  назад   1 [2] 3   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить