| Tertium |
| Отправлено: 03.08.2005, 20:16 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
Перевожу прогу с mySQL4.1 на MSSQL2ksp3. В ней использован один приёмчик: для сверхбыстрой вставки большого числа записей данные выгружаются из некоторого защищенного и пакованного файла сначала в текстовый файл tmpfile.$$$ (поля разделены табами, а в конце слеш-эн), а затем подхватываются мускульной конструкцией
| SQL | | load data infile 'с:/tmpfile.$$$' ignore into table mytable(...) |
Снижение времени запроса от её использования — в десятки раз. И — что важно — она не материт, если в таблицу вставляются записи, конфликтующие с индексом UNIQUE. Существует ли аналог такой конструкции в MSSQL? Или хотя бы что-то подобное, чему на вход можно сунуть текстовый файл, который реально изготовить вручную?
Отредактировано Tertium — 03/08/2005, 20:19
|
|
| Tertium |
| Отправлено: 04.08.2005, 00:16 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
нашёл Bulk Insert:
use gambling
BULK INSERT STATS_TRASH
from 'c:\stats.backup'
WITH
(
CODEPAGE='RAW',
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)
В файле поля разделены табом, а строки — \r\n.
Однако вот пока не пойму, как заставить его игнорировать записи, конфликтующие с UNIQUE CONSTRAINT. Но если это невозможно, тогда нафиг вообще нужен такой запрос? Например, я выливаю в таблицу данные, причём некоторые строки в ней уже есть (периодический бэкап).
Отредактировано Tertium — 04/08/2005, 01:00
|
|
| AVC |
| Отправлено: 04.08.2005, 08:14 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
| QUOTE |
Однако вот пока не пойму, как заставить его игнорировать записи, конфликтующие с UNIQUE CONSTRAINT. Но если это невозможно, тогда нафиг вообще нужен такой запрос?
|
Сервер гарантирует вам целостность данных в рамках тех правил, которые вы для него определили. А вы сами пытаетесь их нарушать.
Вариант разрешения конфликта — грузить в промежуточную таблицу, не имеюшей такого ограничения. Убирать лишние записи. Результат грузить в основную таблицу.
Или отменять constraint, разбираться с записями, включать constraint.
Но восстанавливать целостность придется в любом случае. |
|
| Tertium |
| Отправлено: 04.08.2005, 13:01 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
всё это жутко тормозно. В mysql же есть ignore в запросе. и ненужные записи просто игнорируюся. Или есть какой быстрый способ в таблице без ограничений на unique провести чистку в соответствии с ключом вроде:
CONSTRAINT my_unique_stats UNIQUE NONCLUSTERED
(
[place_no] ASC,
[club_id] ASC,
[last_query] ASC
)
?
Пока мне это представляется оч тормозным 
Загрузка во временную таблицу, затем копирование, которые уникальны в скопе из двух таблиц, затем убийство временной таблы. Да еще этот поиск уникальных....
|
|
| olegenty |
| Отправлено: 04.08.2005, 16:12 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
2 Tertium — не подходи к MSSQL со стереотипом MySQL. я тоже делал эту ошибку, когда был вынужден перейти на MSSQL с Interbase. Много плевался, но усилиями pkarklin (есть такой участник на SQL.RU) пришёл к выводу, что это у меня руки кривые, а не MSSQL плохой. в результате даже руки стали попрямее, и запросы побыстрее. и даже стереотипы пополнились (в Interbase я деревья обходил рекурсивно, а в MSSQL есть ограничение на уровень вложенности вызова = 32, поэтому как угодно, но без рекурсии...)
|
|
| Tertium |
| Отправлено: 04.08.2005, 19:08 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
да я понимаю, что мелкомягкий движок гораздо глубже, просто в данный момент стоит задача переноса приложения с мускула. И мне MS гораздо больше нравится чем мускул, к слову. Здесь прям раздолье для программера:) Только вот всего сразу, как в мускуле, не ухватишь:) Отсюда и непонятки. Там всё просто было: вложенных запросов нет — и хоть ты что, а тут стока вариантов для решения каждой задачи.
А подход с загрузкой из файла вместо кучи инсертов в данном случае так же оправдывает себя как в мускуле — всётки инсерт — везде инсерт. Только вот я как представлю чем чревато наличие уже имеющихся записей... жаль, что нельзя в sql что-то типа try...catch...finally использовать:) А ограничить записи так, чтоб они в bulk insert подавались уже только уникальные относительно my_unique_stats нельзя. У меня тут что-то вроде извращённой репликации — прога может выгружать в специальный файл некоторую заданную выборку данных из своих таблиц и загружать из этого файла. Причём файл зашифрованный оригинальным алгоритмом и врагам нельзя его прочесть. Ну вобщем несско выбиваемся из стандартной репликации, поэтому так всё извратно. И выгрузку в файл не сделаешь залпом (там нескуэльная специфика) и загрузку через DTS тоже низя. Все программно. И естесственно ситуации дублирования данных сплошь и рядом. Я не говорю что пишу единственным возможным образом, просто таков мой стиль. И в данном случае надо всего-то подешевле вставить залпом данные. Вот и придётся щас экспериментировать с запросами. Просто у меня в башке не укладывается, насколько долго это всё будет. Это ж почти как туча инсертов последовательно. И вот там уж я вполне могу на программном уровне делать try...catch  Правда, конечно, задержка на адошные вызовы — та еще хрень... Ладно, поэкспериментирую и выложу тада результат. Мож поругает кто Это бывает полезно
|
|
| olegenty |
| Отправлено: 05.08.2005, 06:36 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
тебе предложили вариант с загрузкой во временную таблицу. так вот, прежде чем его отвергнуть — попробуй.
|
|
| Tertium |
| Отправлено: 05.08.2005, 12:04 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
вариант с загрузкой во временную таблицу и так существовал с самого начала, он сам собой разумеется при отсутствии в природе опции IGNORE Даже на sql.ru ничего дельного не предложили...
|
|
| Tertium |
| Отправлено: 05.08.2005, 20:50 |
|
Машинист паровоза
Группа: Почетный участник
Сообщений: 192
|
Эх, народ, народ... Таблицы, панимаш, временные... Констрэйнт, понимаш, отменять...
Ну да, всё гениальное просто. Сначала я написал ХП, которая загружала во врем. таблицу и потом вставляла в осоновную записи в соответствии с униключом.
| SQL | CREATE PROC dbo.add_stats_from_file
@filename varchar(255)
--WITH ENCRYPTION
AS CREATE TABLE [#temp_stats] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[place_no] [tinyint] NOT NULL DEFAULT (0),
[club_id] [int] NOT NULL DEFAULT (0),
[currentIn] [bigint] NOT NULL DEFAULT (0),
[currentOut] [bigint] NOT NULL DEFAULT (0),
[last_query] [datetime] NOT NULL DEFAULT GETDATE(),
PRIMARY KEY CLUSTERED
(
[id] ASC )
)
DECLARE @query nvarchar(2000)
set @query='BULK INSERT #temp_stats
from style='color:red'>'''+@filename+'''
WITH
(
CODEPAGE=''RAW'',
FIELDTERMINATOR = ''\t'',
ROWTERMINATOR = ''\n''
)'
exec sp_executesql @query
DECLARE @place_no tinyint
DECLARE @club_id int
DECLARE @currentIn bigint
DECLARE @currentOut bigint
DECLARE @last_query datetime
DECLARE @dbl_cnt int
DECLARE @added int
set @added=0
DECLARE temp_stats_cursor CURSOR FOR
select place_no,club_id,currentIn,currentOut,last_query from #temp_stats OPEN temp_stats_cursor
FETCH NEXT FROM temp_stats_cursor INTO @place_no,@club_id,@currentIn,@currentOut,@last_query while @@FETCH_STATUS = 0
begin select @dbl_cnt=count(*) from stats where @place_no=place_no and @club_id=@club_id and @last_query=last_query
if @dbl_cnt=0
begin insert into stats(place_no,club_id,currentIn,currentOut,last_query) values(@place_no,@club_id,@currentIn,@currentOut,@last_query)
set @added=@added+1
end
FETCH NEXT FROM temp_stats_cursor INTO @place_no,@club_id,@currentIn,@currentOut,@last_query end
CLOSE temp_stats_cursor
DEALLOCATE temp_stats_cursor
Drop table #temp_stats
select @added
|
Она работает, возвращает ровсет из одного инта — кол-ва реально вставленных записей.
Потом уже собрался взяться за вариант с временным убийством униключа. И вдруг внимательно вчитался в доки.
И выяснилось что в MS тот самый аналог IGNORE вынесен из параметров запроса вставки в параметры самого униключа. Что вообще-то логично в каком-то смысле. И это как раз есть тот, фигурально выражаясь, catch, который мне был так нужен
Так что всего-то навсего:
создание таблы:
| SQL | CREATE TABLE [Stats] (
[id] [int] IDENTITY (1, 1) NOT NULL ,
[place_no] [tinyint] NOT NULL DEFAULT (0),
[club_id] [int] NOT NULL DEFAULT (0),
[currentIn] [bigint] NOT NULL DEFAULT (0),
[currentOut] [bigint] NOT NULL DEFAULT (0),
[last_query] [datetime] NOT NULL DEFAULT GETDATE(),
PRIMARY KEY CLUSTERED
(
[id] ASC )
)
create unique index my_unique on stats
(
[place_no] ASC,
[club_id] ASC,
[last_query] ASC )
with IGNORE_DUP_KEY |
собственно вставка:
| SQL | BULK INSERT stats
from style='color:red'>'c:\tmp_file1.txt'
WITH
(
CODEPAGE='RAW',
FIELDTERMINATOR = '\t',
ROWTERMINATOR = '\n'
)
|
Ведь вы же прекрасно знали, так ведь?
|
|
|
