Please help me improve ths speed of my code that automatically insert specifications for a product

Here is my existing code which automatically inserts product specifications from a field in the excel import file. Read my comments to follow how it works.  

What really irks me is that webdev.webserver40.exe only uses 12% of the CPU while SQL server barely uses any CPU.  Why doesn't this entire process use all of my cpu? I tried setting the priority to high with no difference.

The following code gets extremely slow the more products I add.

//grab all specification attributes if any from nop
specificationAttributes = _specificationAttributeService.GetSpecificationAttributes();

//productSpecifications is an array that holds values like "Shoes:Red"[0]  "Laces:White"[1] from our excel import file

//loop through each specification we are to add
foreach (string productSpecification in productSpecifications)
   {
    //after split shoes:red will be productSpec[0] = shoes , productSpec[1] = red
   string[] productSpec = productSpecification.Split(':');

   //search specifications from nop to see if the specification already exists. c.Name is existing specifications in the database already and we compare it against our specifications indicated in our excel import file.

   //here we search to see if the specification attribute already exists.
   var specification = specificationAttributes.Where(c => c.Name.ToLower() == productSpec[0].ToLower()); //shoes

    //did we find the specification
    if (specification.Count() != 0)
      {
        //we did find the specification now lets loop through the specifications attribute options
         foreach (var sao in _specificationAttributeService.GetSpecificationAttributeOptionsBySpecificationAttribute(specification.FirstOrDefault().Id))
           {
           //does the attribute option for this specification from import match the option for this specification in nop
                if (productSpec[1].ToLower() == sao.Name.ToLower())
                {
                     //let's create a new association between this specification option and the product
                     var psa = new ProductSpecificationAttribute()
                             {
                             ProductId = product.Id,
                             SpecificationAttributeOptionId = sao.Id,
                             AllowFiltering = true,
                             ShowOnProductPage = true,
                             DisplayOrder = 0
                             };
                    //associate this specification attribute and option to the newly inserted product
                    _specificationAttributeService.InsertProductSpecificationAttribute(psa);
                    break;
                 }
             }
         }

Run SQL Profiler.  SQL queries may not be CPU-intensive, but there could be many of them (with their accompanying network IO / transport, etc.)  How's the pipe to your SQL server?

New York wrote:

It seems I don't have the admin privileges to run the sql profiler with my arvixe account.

However, I'm thinking of a different approach.  Searching for a specification and specification option by name to get the id is very inefficient.

Perhaps I should enter all the specifications before hand and then rebuild the excel import file with the specification, specification option ids.

This would eliminate having to search for an id by name in that for loop.

What do you think?

In the import method, you could pre-process the entire Excel file collecting all the unique values (e.g. in a dictionary), and then loop through dictionary to look them up with nop service  (and set the value in dictionary).  Then during second pass, look up in the dictionary.

P.S. ...

RE: " It seems I don't have the admin privileges to run the sql profiler with my arvixe account."

How did you get above "that webdev.webserver40.exe only uses 12% of the CPU while SQL server barely uses any CPU" ?
(Don't you do dev locally first?)

The above "premature optimization" really should only be considered if you know the root of your performance problem.  nopC does data caching.

I must of been tired. I fired up the sql profiler on local dev machine.  I'm inserting twelve product specification options for each product, this process is taking up to 4 seconds unfortunately.  There are about 900 products inserted now which took 1 hour to complete.  If I can import 1000 products an hour and have it stay that way I'll be happy. I'm just worried that the inserting keeps getting slower & slower.

If you want I can show you my new optimized code with the dictionary idea you mentioned.

Screen shot of profiler, this shows all the steps involved when inserting twelve product specification options for a single product.  
http://oi41.tinypic.com/nxp5iq.jpg

Here is the actual sql statements.

INCOMING BLOCK OF INSERTS
exec sp_executesql N'insert [dbo].[Product_SpecificationAttribute_Mapping]([ProductId], [SpecificationAttributeOptionId], [CustomValue], [AllowFiltering], [ShowOnProductPage], [DisplayOrder])
values (@0, @1, null, @2, @3, @4)
select [Id]
from [dbo].[Product_SpecificationAttribute_Mapping]
where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 int,@1 int,@2 bit,@3 bit,@4 int',@0=5394,@1=714,@2=1,@3=1,@4=0
go
exec sp_reset_connection 
go
exec sp_executesql N'insert [dbo].[Product_SpecificationAttribute_Mapping]([ProductId], [SpecificationAttributeOptionId], [CustomValue], [AllowFiltering], [ShowOnProductPage], [DisplayOrder])
values (@0, @1, null, @2, @3, @4)
select [Id]
from [dbo].[Product_SpecificationAttribute_Mapping]
where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 int,@1 int,@2 bit,@3 bit,@4 int',@0=5394,@1=858,@2=1,@3=1,@4=0
go
exec sp_reset_connection 
go
exec sp_executesql N'insert [dbo].[Product_SpecificationAttribute_Mapping]([ProductId], [SpecificationAttributeOptionId], [CustomValue], [AllowFiltering], [ShowOnProductPage], [DisplayOrder])
values (@0, @1, null, @2, @3, @4)
select [Id]
from [dbo].[Product_SpecificationAttribute_Mapping]
where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 int,@1 int,@2 bit,@3 bit,@4 int',@0=5394,@1=891,@2=1,@3=1,@4=0
go
exec sp_reset_connection 
go
exec sp_executesql N'insert [dbo].[Product_SpecificationAttribute_Mapping]([ProductId], [SpecificationAttributeOptionId], [CustomValue], [AllowFiltering], [ShowOnProductPage], [DisplayOrder])
values (@0, @1, null, @2, @3, @4)
select [Id]
from [dbo].[Product_SpecificationAttribute_Mapping]
where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 int,@1 int,@2 bit,@3 bit,@4 int',@0=5394,@1=728,@2=1,@3=1,@4=0
go
exec sp_reset_connection 
go
exec sp_executesql N'insert [dbo].[Product_SpecificationAttribute_Mapping]([ProductId], [SpecificationAttributeOptionId], [CustomValue], [AllowFiltering], [ShowOnProductPage], [DisplayOrder])
values (@0, @1, null, @2, @3, @4)
select [Id]
from [dbo].[Product_SpecificationAttribute_Mapping]
where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 int,@1 int,@2 bit,@3 bit,@4 int',@0=5394,@1=746,@2=1,@3=1,@4=0
go
exec sp_reset_connection 
go
exec sp_executesql N'insert [dbo].[Product_SpecificationAttribute_Mapping]([ProductId], [SpecificationAttributeOptionId], [CustomValue], [AllowFiltering], [ShowOnProductPage], [DisplayOrder])
values (@0, @1, null, @2, @3, @4)
select [Id]
from [dbo].[Product_SpecificationAttribute_Mapping]
where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 int,@1 int,@2 bit,@3 bit,@4 int',@0=5394,@1=888,@2=1,@3=1,@4=0
go
exec sp_reset_connection 
go
exec sp_executesql N'insert [dbo].[Product_SpecificationAttribute_Mapping]([ProductId], [SpecificationAttributeOptionId], [CustomValue], [AllowFiltering], [ShowOnProductPage], [DisplayOrder])
values (@0, @1, null, @2, @3, @4)
select [Id]
from [dbo].[Product_SpecificationAttribute_Mapping]
where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 int,@1 int,@2 bit,@3 bit,@4 int',@0=5394,@1=904,@2=1,@3=1,@4=0
go
exec sp_reset_connection 
go
exec sp_executesql N'insert [dbo].[Product_SpecificationAttribute_Mapping]([ProductId], [SpecificationAttributeOptionId], [CustomValue], [AllowFiltering], [ShowOnProductPage], [DisplayOrder])
values (@0, @1, null, @2, @3, @4)
select [Id]
from [dbo].[Product_SpecificationAttribute_Mapping]
where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 int,@1 int,@2 bit,@3 bit,@4 int',@0=5394,@1=892,@2=1,@3=1,@4=0
go
exec sp_reset_connection 
go
exec sp_executesql N'insert [dbo].[Product_SpecificationAttribute_Mapping]([ProductId], [SpecificationAttributeOptionId], [CustomValue], [AllowFiltering], [ShowOnProductPage], [DisplayOrder])
values (@0, @1, null, @2, @3, @4)
select [Id]
from [dbo].[Product_SpecificationAttribute_Mapping]
where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 int,@1 int,@2 bit,@3 bit,@4 int',@0=5394,@1=893,@2=1,@3=1,@4=0
go
exec sp_reset_connection 
go
exec sp_executesql N'insert [dbo].[Product_SpecificationAttribute_Mapping]([ProductId], [SpecificationAttributeOptionId], [CustomValue], [AllowFiltering], [ShowOnProductPage], [DisplayOrder])
values (@0, @1, null, @2, @3, @4)
select [Id]
from [dbo].[Product_SpecificationAttribute_Mapping]
where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 int,@1 int,@2 bit,@3 bit,@4 int',@0=5394,@1=782,@2=1,@3=1,@4=0
go
exec sp_reset_connection 
go
exec sp_executesql N'insert [dbo].[Product_SpecificationAttribute_Mapping]([ProductId], [SpecificationAttributeOptionId], [CustomValue], [AllowFiltering], [ShowOnProductPage], [DisplayOrder])
values (@0, @1, null, @2, @3, @4)
select [Id]
from [dbo].[Product_SpecificationAttribute_Mapping]
where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 int,@1 int,@2 bit,@3 bit,@4 int',@0=5394,@1=746,@2=1,@3=1,@4=0
go
exec sp_reset_connection 
go
exec sp_executesql N'insert [dbo].[Product_SpecificationAttribute_Mapping]([ProductId], [SpecificationAttributeOptionId], [CustomValue], [AllowFiltering], [ShowOnProductPage], [DisplayOrder])
values (@0, @1, null, @2, @3, @4)
select [Id]
from [dbo].[Product_SpecificationAttribute_Mapping]
where @@ROWCOUNT > 0 and [Id] = scope_identity()',N'@0 int,@1 int,@2 bit,@3 bit,@4 int',@0=5394,@1=725,@2=1,@3=1,@4=0
go

As expected - that explains low CPU - lot's of "traffic" with little real work :)
How did the dictionary work out?

New York wrote:

The dictionary solution is in process right now.  900 products over 1 hour.  If the next 1000 products takes one hour then I know things aren't getting slower which is a good sign.

You say lots of traffic with little real work, hmm any other solutions?  Something is severely wrong.  I imagined inserting 150,000 products with tons of sql inserts would only take a few minutes on an i7 processor 16gb ram with a fast hard drive.

Maybe going through the browser is a bad idea and a client side application needs to be written?

1. dictionary solution did help but only slightly.

2. The more inserts I do the slower it gets, it's inserting 6-7 products a minute now.

3. I think instead of inserting 12 times for each specification option maybe I should only access the database once for a single insert of all 12 specification options and product associations.

Right now every product specification mapping insert takes 600 milliseconds which is bad!

This is an important article:

http://stackoverflow.com/questions/1793169/which-is-faster-multiple-single-inserts-or-one-multiple-row-insert

If you are inserting many rows from the same client at the same time, use INSERT statements with multiple VALUES lists to insert several rows at a time. This is considerably faster (many times faster in some cases) than using separate single-row INSERT statements.

Like you said too many inserts of small amounts of data instead of passing more data with less inserts correcT?

You may want to look into SqlBulkCopy