[.NET C# & SQL2008] problemen met bulk Inserten

[Blitz]

Beste.

Ik ben momenteel bezig met het maken van een Configuratie scherm voor mijn programma. Het doel hiervan is om als een gebruiker nog nooit het programma heeft gebruikt er wordt gekeken of er een database is zoniet dan zal hij deze voor de gebruiker aanmaken dit wordt in 2 stappen gedaan:

1. Maak een leeg database aan met alle tabellen.
2. Vul de master tabellen met records die bij alle klanten hetzelfde zijn.

Het probleem is dat we 500,000 records hebben die moeten worden geinsert. Ik heb via SQL management studio een export script gemaakt die voor mij een 650 MB .sql bestand heeft aangemaakt die er als volgt uitziet:

code:

INSERT [dbo].[Master.RedenEindeZorg] ([RedenEindeZorgId], [Omschrijving]) VALUES (50, N'Eenmalig onderzoek')
INSERT [dbo].[Master.RedenEindeZorg] ([RedenEindeZorgId], [Omschrijving]) VALUES (51, N'Screening zonder behandelvervolg')
INSERT [dbo].[Master.RedenEindeZorg] ([RedenEindeZorgId], [Omschrijving]) VALUES (99, N'Behandeling wegens onbekende reden beëindigd')
/****** Object:  Table [dbo].[Master.Recept]    Script Date: 06/29/2010 11:52:04 ******/
/****** Object:  Table [dbo].[Master.Postcode.Woonplaats]    Script Date: 06/29/2010 11:52:04 ******/
INSERT [dbo].[Master.Postcode.Woonplaats] ([Postcode], [Woonplaats]) VALUES (1000, N'Amsterdam')
INSERT [dbo].[Master.Postcode.Woonplaats] ([Postcode], [Woonplaats]) VALUES (1001, N'Amsterdam')
INSERT [dbo].[Master.Postcode.Woonplaats] ([Postcode], [Woonplaats]) VALUES (1002, N'Amsterdam')
INSERT [dbo].[Master.Postcode.Woonplaats] ([Postcode], [Woonplaats]) VALUES (1003, N'Amsterdam')
INSERT [dbo].[Master.Postcode.Woonplaats] ([Postcode], [Woonplaats]) VALUES (1005, N'Amsterdam')
INSERT [dbo].[Master.Postcode.Woonplaats] ([Postcode], [Woonplaats]) VALUES (1006, N'Amsterdam')
INSERT [dbo].[Master.Postcode.Woonplaats] ([Postcode], [Woonplaats]) VALUES (1007, N'Amsterdam')

Dit is maar een kleinstukje hiervan. Het probleem is dat ik nu via de Microsoft SMO objects probeer het hele .SQL bestand in te lezen wat leidt tot een Out Of Memory exception de code die ik gebruik ziet er zo uit:

code:

            FileInfo file = new FileInfo("eindklasse.sql");
            
            string script = file.OpenText().ReadToEnd(); // Hier is de exception
            
            try
            {
                Server server = new Server(new ServerConnection(conn));
                server.ConnectionContext.ExecuteNonQuery(script);

                return true;
            }
            catch (SqlException ex)
            {
                meldingbox.Text = ex.Message;
                return false;
            }

Ik heb oop google gezocht en kwam uit op een SQLBulkCopy maar deze accepteert volgens mij geen Insert querties, heeft iemand hier ervaring mee met het overzetten van zoveel bestanden zonder een exception te krijgen? als ik de database opsplits in kleine tabellen dan lukt het allemaal wel maar sommige tabellen zijn 200 MB groot waardoor ik alsnog de exception krijg ) Alvast bedankt!

Wijnand

[DoDo]

Je moet het script gewoon niet in een keer inlezen, maar steeds regel voor regel

[Edit]
Of steeds een stuk inlezen en dan uitvoeren. Dan hoef je minder querys uit te voeren naar de server toe.

[ Voor 41% gewijzigd door DoDo op 29-06-2010 13:42 ]

[mulder]

De wondere wereld van:
http://msdn.microsoft.com/en-us/library/ms188365.aspx

[Blitz]

Beste Dodo,

Dat was mijn eerste insteek ook, maar helaas om de zoveel regels zit er een statement tussen waar SQL SErver niks mee kan volgens mij bijvoorbeeld:

GO
Print 'written 200,000 records'
/**Database name = BTWType*/

Beste Mulder,

Dat was in mijn eerste insteek ook het idee maar helaas liep ik tegen het probleem op dat je dan de .SQL op een UNC path moet zetten. Ik wil als het mogelijk is de DAtabase over het netwerk laten bouwen. Dus dat ik op een client een verbinding maakt met de SQL Server en dat hij dan de script uitvoert. Bij de BulkTransact moet ik een UNC path opgeven die ik dan misschien niet heb.

Ik ga in ieder geval hiermee nog wat verder zoeken bedankt voor alle tips tot dusver

[Blitz]

Ik heb een Oplossing gevonden:

code:

StringCollection list = new StringCollection();
            StreamReader rdr = file.OpenText();
            while (!rdr.EndOfStream)
            {
                list.Add(rdr.ReadLine());
            }
            try
            {
                Server server = new Server(new ServerConnection(conn));
                server.ConnectionContext.ExecuteNonQuery(list);             
                return true;
            }
            catch (SqlException ex)
            {
                meldingbox.Text = ex.Message;
                return false;
            }

Bedankt iedereen

[mulder]

Waar draait de client en waar draait de SQL Server? Draait de client zelf een SQL Server?

[RobIII]

Waarom niet gewoon een "kale" _{(waarbij ik met "kale" bedoel dat je "master records" er dus wel al in staan, maar klant-specifieke zaken dus nog niet)} mdf-file meeleveren en die attachen? Scheelt een shitload aan records inserten en een huge-ass .sql bestand meeleveren.

[ Voor 12% gewijzigd door RobIII op 29-06-2010 14:33 ]

[__fred__]

Blitz schreef op dinsdag 29 juni 2010 @ 14:19:
Ik heb een Oplossing gevonden:

code:

StringCollection list = new StringCollection();
            StreamReader rdr = file.OpenText();
            while (!rdr.EndOfStream)
            {
                list.Add(rdr.ReadLine());
            }
            try
            {
                Server server = new Server(new ServerConnection(conn));
                server.ConnectionContext.ExecuteNonQuery(list);             
                return true;
            }
            catch (SqlException ex)
            {
                meldingbox.Text = ex.Message;
                return false;
            }

Bedankt iedereen

Dit werkt waarschijnlijk omdat een string in .NET contiguous (als één stuk geheugen) wordt opgeslagen, en lists in blokken worden gealloceerd die niet aansluitend hoeven te zijn. Dit is geen oplossing. Bij het alloceren van grote hoeveelheden geheugen moet je altijd bedacht zijn op out of memory exceptions. Op een ander systeem of als jouw systeem het toevallig net ff iets drukker heeft of minder geheugen vrij, dan crasht het alsnog.

Voer het script gewoon in delen uit waarbij je steeds een klein deel van het script in geheugen houdt of nog beter: doe bulk copies, batch inserts of een restore van backup bij de initialisatie van de database. Dat zijn de gebruikelijke manieren om grote hoeveelheden data te inserten. Dit kost nog eens een hoop tijd ook.

[ Voor 7% gewijzigd door __fred__ op 30-06-2010 14:01 ]

[Razr]

Ik heb hieronder even een snippet geplaatst van een stukje code welke ik zelf gebruik om snel vele records in te lezen in een tabel. De rijen worden aangeleverd vanuit een DataTable waarbij de tabelnaam en de kolomnamen overeenkomen. Ik maak ook gebruik van terugkoppelingen (NotifyAfter etc.) maar daar moet je zelf maar even mee puzzelen (wat je wel en niet nodig hebt).

C#:

private void StartBulkCopy(DataTable data)
{
    using (var connection = new SqlConnection(Settings.Default.DbConnStr))
    {
        connection.Open();
        using (var transaction = connection.BeginTransaction())
        {
            try
            {
                using (var copy = new SqlBulkCopy(connection, SqlBulkCopyOptions.Default, transaction))
                {
                    copy.BulkCopyTimeout = 0;
                    copy.NotifyAfter = 5000;
                    copy.SqlRowsCopied += SqlRowsCopied;
                    copy.DestinationTableName = data.TableName;
                    copy.WriteToServer(data);

                    transaction.Commit();
                }
            }
            catch (SqlException)
            {
                transaction.Rollback();
                throw;
            }
            finally
            {
                connection.Close();
            }
        }
    }
}