We are using Nhibernate as our data access layer. We have a table of 1.7 million records which we need to index one by one through Lucene for our search. As we run the console app we wrote to build our index, it starts off fast, but as it goes through the items, it progressively gets slower and slower.
Our First iteration was to just index them all. The second iteration was to index them by category. Now, we are selecting subsets by category, and then breaking them into "pages" of 100. We still have a degredation in performance.
I turned on sql profiler and as it iterates the items, it is calling the sql server for each item, one by one, for the images, even though lazy loading is set to not for the image.
This is a commerce site and we are indexing catalog items (products). Off each catalog item are 0 to many images (stored in a seperate table.
Here is our Mapping:
public class ItemMap : ClassMap<Item>
{
public ItemMap()
{
Table("Products");
Id(x => x.Id, "ProductId").GeneratedBy.GuidComb();
Map(x => x.Model);
Map(x => x.Description);
Map(x => x.Created);
Map(x => x.Modified);
Map(x => x.IsActive);
Map(x => x.PurchaseUrl).CustomType<UriType>();
Component(x => x.Identifier, m =>
{
m.Map(x => x.Upc);
m.Map(x => x.Asin);
m.Map(x => x.Isbn);
m.Map(x => x.Tid);
});
Component(x => x.Price, m =>
{
m.Map(x => x.Currency);
m.Map(x => x.Amount, "Price");
m.Map(x => x.Shipping);
});
References(x => x.Brand, "BrandId");
References(x => x.Category, "CategoryId");
References(x => x.Supplier, "SupplierId");
References(x => x.Provider, "ProviderId");
HasMany(x => x.Images)
.Table("ProductImages")
.KeyColumn("ProductId")
.Not.LazyLoad();
// TODO: Add variants
}
}
And here is the root logic of the indexing app.
public void IndexProducts()
{
Console.WriteLine("--- Begin Indexing Products ---");
Console.WriteLine();
var categories = categoryRepository.GetAll().ToList();
Console.WriteLine(String.Format("--- {0} Categories found ---", categories.Count));
categories.Add(null);
foreach (var category in categories)
{
string categoryName = "\"None\"";
if (category != null)
categoryName = category.Name;
Console.WriteLine(String.Format("--- Begin Indexing Category ({0}) ---", categoryName));
var categoryItems = from p in catalogRepository.GetList(new ActiveProductsByCategoryQuery(category))
select p;
int count = categoryItems.Count();
int pageSize = 100;
int currentPage = 0;
int offest = currentPage * pageSize;
int current = 1;
Console.WriteLine(String.Format("Indexing {0} Products...", count));
while (offest < count)
{
var products = (from p in categoryItems
select p).Skip(offest).Take(pageSize);
foreach (var item in products)
{
indexer.UpdateContent(item);
UpdateCounter(current, count);
current++;
}
currentPage++;
offest = currentPage * pageSize;
}
Console.WriteLine();
Console.WriteLine(String.Format("--- End Indexing Category ({0}) ---", categoryName));
Console.WriteLine();
}
Console.WriteLine("--- End Indexing Products ---");
Console.WriteLine();
}
FYI, the count is 26552 for the category in question. The first query it runs is this:
exec sp_executesql N'SELECT TOP 100 ProductId100_1_, Upc100_1_, Asin100_1_, Isbn100_1_, Tid100_1_, Currency100_1_, Price100_1_, Shipping100_1_, Model100_1_, Descrip10_100_1_, Created100_1_, Modified100_1_, IsActive100_1_, Purchas14_100_1_, BrandId100_1_, CategoryId100_1_, SupplierId100_1_, ProviderId100_1_, CategoryId103_0_, Name103_0_, ShortName103_0_, Created103_0_, Modified103_0_, ShortId103_0_, DisplayO7_103_0_, IsActive103_0_, ParentCa9_103_0_ FROM (SELECT this_.ProductId as ProductId100_1_, this_.Upc as Upc100_1_, this_.Asin as Asin100_1_, this_.Isbn as Isbn100_1_, this_.Tid as Tid100_1_, this_.Currency as Currency100_1_, this_.Price as Price100_1_, this_.Shipping as Shipping100_1_, this_.Model as Model100_1_, this_.Description as Descrip10_100_1_, this_.Created as Created100_1_, this_.Modified as Modified100_1_, this_.IsActive as IsActive100_1_, this_.PurchaseUrl as Purchas14_100_1_, this_.BrandId as BrandId100_1_, this_.CategoryId as CategoryId100_1_, this_.SupplierId as SupplierId100_1_, this_.ProviderId as ProviderId100_1_, category1_.CategoryId as CategoryId103_0_, category1_.Name as Name103_0_, category1_.ShortName as ShortName103_0_, category1_.Created as Created103_0_, category1_.Modified as Modified103_0_, category1_.ShortId as ShortId103_0_, category1_.DisplayOrder as DisplayO7_103_0_, category1_.IsActive as IsActive103_0_, category1_.ParentCategoryId as ParentCa9_103_0_, ROW_NUMBER() OVER(ORDER BY CURRENT_TIMESTAMP) as __hibernate_sort_row FROM Products this_ left outer join Categories category1_ on this_.CategoryId=category1_.CategoryId WHERE (this_.IsActive = @p0 and (1=0 or (this_.CategoryId is not null and category1_.CategoryId = @p1)))) as query WHERE query.__hibernate_sort_row > 500 ORDER BY query.__hibernate_sort_row',N'@p0 bit,@p1 uniqueidentifier',@p0=1,@p1='A988FD8C-DD93-4119-8F84-0AF3656DAEDD'
Then for each product, it executes
exec sp_executesql N'SELECT images0_.ProductId as ProductId1_, images0_.ImageId as ImageId1_, images0_.ImageId as ImageId98_0_, images0_.Description as Descript2_98_0_, images0_.Url as Url98_0_, images0_.Created as Created98_0_, images0_.Modified as Modified98_0_, images0_.ProductId as ProductId98_0_ FROM ProductImages images0_ WHERE images0_.ProductId=@p0',N'@p0 uniqueidentifier',@p0='487EA053-4DD5-4EBA-AA36-95B30C42F0CD'
Which is fine. The problem is the first 2000 or so are really fast, but the longer it runs through the category, the slower it gets and the more memory it consumes - even though it's indexing the same number of products. GC is working because the memory usage drops, but overall it climbs as the processor works.
Is there anything we can do to speed up the indexer? Why is it steadily decreasing in performance? I don't think it is nhibernate or the queries because it starts out so fast. We're really at a loss here.
Thanks