Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
Author |
Topic |
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-05 : 21:35:37
|
Hi.I am developing a data migration tool from a complicated database. using SQL Server 2008 R2 + all updates.A user account can have multiple customers.Each user account can have other specific items associated with it.So I am going through each item and generating an insert statement but making sure that the record does not already exist and if not, then insert.There are literally thousands.Now, this is my .NET code (I dont expect most to understand but you should get the drift:quote: string insertAdminAccountMenuWebAccessLevel = " IF NOT EXISTS (SELECT TOP 1 ID FROM MUserWebAccessLevel WHERE UserID = '{0}' AND CustomerID = '{1}' AND MenuID = {2}) BEGIN INSERT INTO [MUserWebAccessLevel] (UserID, CustomerID, MenuID) VALUES ('{0}', '{1}', {2}) END "; StringBuilder allInsertAdminAccountMenuWebAL = new StringBuilder(Int16.MaxValue); using (SqlCommand command = new SqlCommand(insertAdminAccountMenuWebAccessLevel, new SqlConnection(connStr))) { command.Connection.Open(); foreach (UserAndMenus currentUser in userAndMenus.Where(u => u.User.IsAdmin).OrderBy(u => u.User.UserID)) { Console.Write("User {0}...", currentUser.User.UserID + ".. is an admin..."); if (currentUser.CustHelper.Count == 0) { Console.WriteLine("...No customers found for this user"); } foreach (UserAndMenus.CustomerHelper currentCustomer in currentUser.CustHelper.OrderBy(c => c.Customer.CustID)) { Console.WriteLine("CustomerID {0}...", currentCustomer.Customer.CustID); foreach (int currentAdminMenuItem in adminMenus) { command.CommandText = string.Format(insertAdminAccountMenuWebAccessLevel, currentUser.User.UserID, currentCustomer.Customer.CustID, currentAdminMenuItem); allInsertAdminAccountMenuWebAL.AppendLine(command.CommandText); } if (allInsertAdminAccountMenuWebAL.Length > 0) { command.CommandText = allInsertAdminAccountMenuWebAL.ToString(); command.CommandTimeout = 900; command.ExecuteNonQuery(); } allInsertAdminAccountMenuWebAL.Clear(); allInsertAdminAccountMenuWebAL = new StringBuilder(); } // exec if (allInsertAdminAccountMenuWebAL.Length > 0) { command.CommandText = allInsertAdminAccountMenuWebAL.ToString(); command.CommandTimeout = 900; command.ExecuteNonQuery(); } allInsertAdminAccountMenuWebAL.Clear(); allInsertAdminAccountMenuWebAL = new StringBuilder(); } command.Connection.Close(); }
as you can maybe see, going through a collection of items for each item etc... and generating the insert statement.I get the error: There is insufficient system memory in resource pool default to run this queryAny ideas how I can better generate/execute the insert statement?this isnt meant to be a polished tool as it is a one off (or every now and again) tool. |
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-02-05 : 21:52:46
|
quote: I am developing a data migration tool from a complicated database
What about using Integration Services? It's specifically designed for moving data between systems with high performance and flexibility. It has built-in lookup features to detect missing and matched rows and handle them accordingly.Are you migrating data between different databases, possibly on different SQL Servers? |
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-05 : 22:19:45
|
same database and same SQL Server. The thing is, its going to take me so much time to iron out the bad data etc... and I have written a tool which pretty much nails it. plus my tool does some other funky stuff it needs to once it establishes the relationships between different tables and the data itself to get it out of the mess it is in.The problem I have is the performance issue with the IF NOT EXISTS INSERT INTO clause.... for multiple records (thousands). I batch them up as you an see but takes a around 3 seconds to execute. a batch can contain from 1 to 12 records. times that by thousands....took around 3 hours to complete. |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2013-02-06 : 07:51:49
|
Are there indexes on the columns being compared? I'd recommend generating the SQL for a few rows and then executing it in Management Studio with Show Execution Plan on. Look for scans (table or clustered index), if you see those then try adding indexes on the columns in question.When you say "establish relationships between tables" do you mean they have FOREIGN KEY constraints? If not, they should.A cheap way to work around duplicate testing is to create your primary key or unique constraints with IGNORE_DUP_KEY = ON. When a duplicate key is found it won't be inserted. This doesn't help with the related tables however, unless you have a FOREIGN KEY constraint. Unfortunately having one would generate an error if you try to insert related rows that were skipped in the referenced table. |
|
|
tech_1
Posting Yak Master
129 Posts |
Posted - 2013-02-06 : 10:20:30
|
Hi.I added an index to that table and it sped things up ALOT.unfortunately no referential integrity was used at all in this database - and this has nothing to do with me but how the design was done a few years ago (again, not by me). So this is why the extra work is needing to be done which is why I am doing this :)But yes, adding the index to this new table in question with the 3 columns really worked. |
|
|
|
|
|
|
|