Массовый импорт данных Excel с вложенными отношениями
Я получаю медленную проблему производительности при импорте данных Excel с вложенными отношениями.
У меня есть две основные таблицы для вставки и четыре другие таблицы, которые имеют отношения "один ко многим" и "многие ко многим" с основными таблицами.
Я проверяю импортированные данные для четырех существующих или новых таблиц перед тем, как вставить данные для двух основных таблиц, поскольку импортированный файл может содержать одинаковые данные для четырех таблиц и одни и те же данные не могут вставляться несколько раз.
Вот почему производительность медленная.
Как я мог решить эту проблему?
2 ответа
Мне приходилось сталкиваться с этой ситуацией с большими объемами данных, которые включают миллион записей. Из многих потраченных недель опыта:
1) Делайте все, что в ваших силах, чтобы не использовать Excel. Это медленно и съедает много памяти. Один лист с 500,00 записями может потребовать более 2 гигабайт памяти только для загрузки файла. Импорт занимает 30-40-50 минут или больше для одного листа. Рассмотрите возможность преобразования данных в CSV и их импорта с помощью SqlBulkCopy, Он может обрабатывать огромное количество записей от нескольких секунд до нескольких минут, а не часов.
2) Вы мало что можете сделать, когда дело доходит до улучшения производительности o Entity Framework в этой ситуации. Я обнаружил, что лучшим и быстрым подходом было загрузить каждый лист в свою временную таблицу в базе данных. Затем я построил SQL для массовых вставок в их конечные таблицы. Результаты промежуточных вставок могут быть записаны в выходные таблицы, чтобы вы могли получить доступ к ключам, необходимым для выполнения любых соединений из временной таблицы или вставок в связанные таблицы. Конечно, вы можете "украсть" часть автоматически сгенерированного EF SQL, но тогда вам нужно будет его настроить.
3) Несмотря на то, что SQL ненавидит циклы, я закодировал свои операторы sql для запуска в цикле и вставки, скажем, 100000 записей одновременно. Это заставило вставки работать намного быстрее.
Чтобы дать вам представление, после массового импорта каждого листа формы CSV:
Сначала определите ваши переменные и типы, хранящиеся в связанных таблицах по мере необходимости:
DECLARE @Max INT = @RecordsPerLoop
DECLARE @Min INT = 0
DECLARE @TotalRECORD INT = (
SELECT count(*)
FROM TempClassMemberRecords
)
DECLARE @Country VARCHAR(50)
SET @Country = 'USA'
-- Const variables for class member inserts
DECLARE @DefaultCommPreference VARCHAR(50) = (
SELECT TOP 1 CommPreference
FROM Actors
WHERE PKID = 0
)
,@PrimaryActorTypeId INT = (
SELECT TOP 1 PKId
FROM ActorTypes
WHERE ActorTypeName = 'PrimaryClaimant'
)
,@SecondaryActorTypeId INT = (
SELECT TOP 1 PKId
FROM ActorTypes
WHERE ActorTypeName = 'CoClaimant'
)
,@HomePhoneTypeId INT = (
SELECT TOP 1 PKId
FROM PhoneTypes
WHERE PhoneTypeName = 'Home'
)
,@WorkPhoneTypeId INT = (
SELECT TOP 1 PKId
FROM PhoneTypes
WHERE PhoneTypeName = 'Work'
)
,@PrimaryCountryId INT = IsNull((
SELECT TOP 1 PKId
FROM Countries
WHERE @Country IN (
CountryName
,CountryCode
)
), 0)
,@DefaultCountryId INT = IsNull((
SELECT TOP 1 PKId
FROM Countries
WHERE CountryCode = 'USA'
), 0)
,@SubmitTypeId INT = (
SELECT TOP 1 PKId
FROM ClaimSubmitTypes
WHERE SubmitTypeName = 'Bulk'
)
,@ClaimStatusId INT = (
SELECT TOP 1 PKId
FROM ClaimStatusTypes
WHERE StatusName = 'Active'
)
,@ModifiedBy VARCHAR(20) = @uploadUser
,@ModifiedDate DATETIME = GETDATE()
,@CaseCode VARCHAR(50) = (
SELECT TOP 1 CaseCode
FROM Cases
ORDER BY PKId DESC
) + ''
,@IndividualClaimantType INT = (
SELECT TOP 1 PKId
FROM claimanttypes
WHERE ClaimantTypeName = 'Individual'
)
,@CompanyClaimantType INT = (
SELECT TOP 1 PKId
FROM claimanttypes
WHERE ClaimantTypeName = 'Corporation'
)
,@Checked BIT = 0
,@startingPKId INT = (
SELECT max(PKId) + 1
FROM dbo.Entities WITH (NOLOCK)
);
--Record per group insert
IF (@TotalRECORD <= @RecordsPerLoop)
SET @max = @TotalRECORD
Запустите ваш цикл вставки:
-- our main loop
WHILE (@min <= @TotalRECORD)
BEGIN
IF OBJECT_ID('tempdb..#EntityIds') IS NOT NULL
DROP TABLE #EntityIds
IF OBJECT_ID('tempdb..#RefNumRepository') IS NOT NULL
DROP TABLE #RefNumRepository
IF OBJECT_ID('tempdb..#ActorIds') IS NOT NULL
DROP TABLE #ActorIds
IF OBJECT_ID('tempdb..#SecondaryActorIds') IS NOT NULL
DROP TABLE #SecondaryActorIds
CREATE TABLE #EntityIds (
pkid INT identity(1, 1) NOT NULL
,mid INT
,eid INT
)
CREATE TABLE #ActorIds (
pkid INT identity(1, 1) NOT NULL
,mid INT
,aid INT
)
CREATE TABLE #SecondaryActorIds (
pkid INT identity(1, 1) NOT NULL
,mid INT
,aid INT
)
CREATE TABLE #RefNumRepository (
pkid INT identity(1, 1) NOT NULL
,RefNum VARCHAR(50)
)
BEGIN TRANSACTION
BEGIN TRY
UPDATE TOP (@RecordsPerLoop + 1) RefNumRepository
SET IsUsed = 1
OUTPUT deleted.RefNum
INTO #RefNumRepository(RefNum)
WHERE IsUsed = 0;
PRINT 'Entities'
INSERT INTO Entities (
ModifiedBy
,ModifiedDate
,RecordOwnerName
,IsConflictOfInterest
,FKClaimantTypeId
,OtherClaimantType
,InstitutionAccountNumber
,RefNum
,FKSubmitTypeId
,FKClaimStatusTypeId
,RecordType
,ClaimNum
,FilingDate
,FirstName
,Lastname
,Email
,SSN
,Source
,ClaimDataCertifiedDate
)
OUTPUT Inserted.pkid
,Inserted.source
INTO #EntityIds(eid, mid)
SELECT @ModifiedBy
,@ModifiedDate
,NULL
,1
,CASE
WHEN IsNull(company, '') = ''
THEN @IndividualClaimantType
ELSE @CompanyClaimantType
END
,NULL
,NULL
,''
,@SubmitTypeId
,@ClaimStatusId
,'CM'
,NULL
,@ModifiedDate
,IsNull(fname, '')
,IsNull(lname, '')
,IsNull(Email, '')
,IsNull(ssn, '')
,rawID
,@ModifiedDate
FROM TempClassMemberRecords
WHERE rawID BETWEEN @min
AND @max
AND IsProcessed IS NULL
EXEC dbo.[USP_AssignClassMemberRefNums] @startingPKId
PRINT 'Actors'
-- bulk insert our range of class members into Actors while inserting the primary key into our temp table
INSERT INTO Actors (
FKActorTypeId
,ModifiedBy
,ModifiedDate
,LastName
,FirstName
,MiddleName
,CommPreference
,IsPayee
,IsUSCitizen
,ein
,ssn
,company
,attention
,NotificationsBlocked
,SearchName
,ClientAcctNumber
)
OUTPUT Inserted.pkid
,inserted.attention
INTO #ActorIds(aid, mid)
SELECT @PrimaryActorTypeId
,@ModifiedBy
,@ModifiedDate
,IsNull(lname, '')
,IsNull(fname, '')
,''
,IsNull(@DefaultCommPreference, 'Mail')
,1
,NULL
,IsNull(ein, '')
,IsNull(ssn, '')
,IsNull(company, '')
,rawid
,0
,CASE WHEN len(ISNULL(company, '')) > 0 THEN company
ELSE
CASE WHEN (len(ISNULL(lname, '')) > 0 OR len(ISNULL(fname, '')) > 0)
THEN lname + ', ' + fname
ELSE ''
END
END
,ACCTNUM
FROM TempClassMemberRecords
WHERE (
isnull(company, '') <> ''
OR isNull(fname, '') <> ''
OR isNull(lname, '') <> ''
)
AND rawid BETWEEN @Min
AND @Max
AND IsProcessed IS NULL
PRINT 'Entities2Actors'
-- bulk insert the relations of Entities to Actors in Entities2Actors
INSERT INTO Entities2Actors (
FKEntityId
,FKActorId
,IsActorBeneficiary
,ModifiedBy
,ModifiedDate
)
SELECT e.eid
,a.aid
,1
,@ModifiedBy
,@ModifiedDate
FROM #EntityIds e
INNER JOIN #ActorIds a ON e.mid = a.mid
-- etc...
PRINT 'Addressed'
--Bulk Insert into Address table for Primary Actor Address
INSERT INTO Addresses (
FKActorId
,ModifiedBy
,ModifiedDate
,Address1
,Address2
,City
,STATE
,Zip
,Zip4
,FKCountryId
)
SELECT a.aid
,@ModifiedBy
,@ModifiedDate
,IsNull(Address, '')
,IsNull(Address2, '')
,IsNull(City, '')
,IsNull([State], '')
,IsNull(Zip, '')
,IsNull(Zip4, '')
,ISNULL(@PrimaryCountryId, @DefaultCountryId)
FROM #ActorIds a
INNER JOIN TempClassMemberRecords c ON a.mid = c.rawId
-- etc...
UPDATE tempClassMemberRecords
SET IsProcessed = 1
WHERE rawid BETWEEN @Min
AND @Max
AND IsProcessed IS NULL
SET @Min = @max + 1
SET @max = @max + @RecordsPerloop
COMMIT TRANSACTION
WAITFOR DELAY '000:00:00.400'
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION
RAISERROR (N'Error in moving data from Temporary table to Main tables.', -- Message text.
1,
1);
PRINT 'Failed with error: ' + ERROR_MESSAGE()
END CATCH
Считайте весь файл в объект строкового массива, используя метод.Net ReadAllLines(), затем запустите цикл Parallel For, чтобы обработать все строки параллельно.
private bool ProcessFile(string FolderPath, string FileExtension)
{
try
{
//all files with requisite file extension
DirectoryInfo dinfo = new DirectoryInfo(FolderPath);
FileInfo[] Files = dinfo.GetFiles(FileExtension);
foreach (FileInfo file in Files)
{
List<String> AllLines = new List<String>();
using (StreamReader sr = File.OpenText(file.FullName))
{
int x = 0;
while (!sr.EndOfStream)
{
AllLines.Add(sr.ReadLine());
x += 1;
}
sr.Close();
}
Parallel.For(0, AllLines.Count, x =>
{
InsertDataCheck(AllLines[x]);
});
}
GC.Collect();
return true;
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
return false;
}
private void InsertDataCheck(string Line)
{
//check if you want to insert data on the basis of your condition
//and then insert your data
}