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