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.

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Performance query - large inserts

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 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

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?
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -