SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Performance query - large inserts
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

tech_1
Posting Yak Master

109 Posts

Posted - 02/05/2013 :  21:35:37  Show Profile  Reply with Quote
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 query



Any 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

USA
15668 Posts

Posted - 02/05/2013 :  21:52:46  Show Profile  Visit robvolk's Homepage  Reply with Quote
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?
Go to Top of Page

tech_1
Posting Yak Master

109 Posts

Posted - 02/05/2013 :  22:19:45  Show Profile  Reply with Quote
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.

Edited by - tech_1 on 02/05/2013 22:20:45
Go to Top of Page

robvolk
Most Valuable Yak

USA
15668 Posts

Posted - 02/06/2013 :  07:51:49  Show Profile  Visit robvolk's Homepage  Reply with Quote
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.
Go to Top of Page

tech_1
Posting Yak Master

109 Posts

Posted - 02/06/2013 :  10:20:30  Show Profile  Reply with Quote
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.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000