Catalog Pages take 30 Seconds to start loading. Major TTFB issue.

Issue website: http://new.fanmats.com/arizona-cardinals
Nop Commerce Version: 4.10
SQL Server 2008
Backward Compatibility: Enabled
MiniProfiler: Enabled

Catalog page size 30
Total Num Products: 25K
Product Images approx 65K
Categories approx 100
Website uses Tier Prices.
Image Qaulity is set to 90%

Essential Tables are indexed.

All catalog pages on my website are experiencing super high TTFB ( Time To First Byte ) delays on Catalog Controller.

Catalog/CategoryMILWEBSVR01 (27256.1 ms) Thu, 25 Apr 2019 11:09:58 GMT
                                           duration (ms)  sql (ms)
http://new.fanmats.com/arizona-cardinals      33.8  
Controller: Catalog.Category                   27110.2  225.9 (41)
ViewComponent: CategoryNavigation               2.4  
ViewComponent: RecentlyViewedProductsBlock       4.0  2.1 (1)
ViewComponent: HeaderLinks                      26.2  2.0 (1)
ViewComponent: FlyoutShoppingCart              11.9  
ViewComponent: MegaMenu                               3.6  
ViewComponent: MegaMenuCategory                       6.2  
ViewComponent: TopMenu                              38.0  
                                                          sql:  0.8 %

client event    duration (ms)
Request                   27720
Response                    36
DOM Loading                242

This issue started only when i switched hosting and db server.
The only files i had changed were:
App Data folder connection string mentioning new db.
Web Config file connection string mentioning new db.
Cleared Thumbs folder and cleared cached.
(Oh i had uninstalled few plugins before i did this.)
Any help would be greatly appreciated.

Full MiniProfiler Log:
  duration (ms)  sql (ms)
http://new.fanmats.com/arizona-cardinals  33.8  
Controller: Catalog.Category  27110.2  225.9 (41)
ViewComponent: Widget  0.2  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: CategoryNavigation  2.4  
ViewComponent: Widget  0.0  
ViewComponent: ManufacturerNavigation  0.0  
ViewComponent: VendorNavigation  0.0  
ViewComponent: RecentlyViewedProductsBlock  4.0  2.1 (1)
ViewComponent: PopularProductTags  0.0  
ViewComponent: PollBlock  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: AdminHeaderLinks  0.9  
ViewComponent: Widget  0.0  
ViewComponent: HeaderLinks  26.2  2.0 (1)
ViewComponent: FlyoutShoppingCart  11.9  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: TaxTypeSelector  0.0  
ViewComponent: CurrencySelector  0.0  
ViewComponent: LanguageSelector  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Logo  0.9  
ViewComponent: Widget  0.4  
ViewComponent: MotionTheme  0.8  
ViewComponent: SearchBox  0.7  
ViewComponent: Widget  0.3  
ViewComponent: InstantSearch  1.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.4  
ViewComponent: MegaMenu  3.6  
ViewComponent: MegaMenuCategory  1.3  
ViewComponent: Widget  0.0  
ViewComponent: MegaMenuCategory  0.4  
ViewComponent: MegaMenuListSubCategories  0.7  
ViewComponent: MegaMenuCategory  1.2  
ViewComponent: Widget  0.0  
ViewComponent: MegaMenuCategory  0.4  
ViewComponent: MegaMenuListSubCategories  0.6  
ViewComponent: MegaMenuCategory  6.2  
ViewComponent: Widget  0.0  
ViewComponent: MegaMenuCategory  0.4  
ViewComponent: MegaMenuListSubCategories  0.7  
ViewComponent: MegaMenuCategory  1.2  
ViewComponent: Widget  0.0  
ViewComponent: MegaMenuCategory  0.4  
ViewComponent: MegaMenuListSubCategories  0.6  
ViewComponent: MegaMenuCategory  0.7  
ViewComponent: Widget  0.0  
ViewComponent: MegaMenuCategory  0.1  
ViewComponent: MegaMenuListSubCategories  0.3  
ViewComponent: MegaMenuCategory  0.3  
ViewComponent: Widget  0.0  
ViewComponent: MegaMenuCategory  0.2  
ViewComponent: MegaMenuListSubCategories  0.1  
ViewComponent: MegaMenuCategory  0.2  
ViewComponent: Widget  0.0  
ViewComponent: MegaMenuCategory  0.1  
ViewComponent: MegaMenuListSubCategories  0.1  
ViewComponent: MegaMenuCategory  0.2  
ViewComponent: Widget  0.0  
ViewComponent: MegaMenuCategory  0.1  
ViewComponent: MegaMenuListSubCategories  0.1  
ViewComponent: MegaMenuCategory  0.2  
ViewComponent: Widget  0.0  
ViewComponent: MegaMenuCategory  0.1  
ViewComponent: MegaMenuListSubCategories  0.1  
ViewComponent: MegaMenuMenuItem  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: TopMenu  38.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Footer  1.1  
ViewComponent: TopicBlock  0.2  
ViewComponent: NewsletterBox  0.4  
ViewComponent: SocialButtons  0.2  
ViewComponent: StoreThemeSelector  0.0  
ViewComponent: Widget  0.0  
ViewComponent: EuCookieLaw  0.0  
ViewComponent: Widget  0.0  
ViewComponent: Widget  0.2  
ViewComponent: ProductTabs  0.7  
ViewComponent: Widget  0.2  
ViewComponent: MotionTheme  0.2  
ViewComponent: NewsRssHeaderLink  0.0  
ViewComponent: BlogRssHeaderLink  0.0  
ViewComponent: Favicon  0.2  
sql:  0.8 %


~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Call Type
Step
Duration (from start)
Call Stack
Command
15.50 ms  
http://new.fanmats.com/arizona-cardinals — 14.70 ms
sql - Open
Controller: Catalog.Category
0.7 ms (T+15.5 ms)
SaveChanges > SaveChanges > Execute > Execute > Execute > BeginTransaction > Open > OpenDbConnection > ConnectionOpening > Write
Connection Open()
4.30 ms  
Controller: Catalog.Category — 4.30 ms
sql - ExecuteReader
Controller: Catalog.Category
1.8 ms (T+20.5 ms)
First Result: 1.8 ms
SaveChanges > SaveChanges > SaveChanges > Execute > Execute > Execute > Execute > ExecuteReader > Execute > CommandExecuting > Write
SET NOCOUNT ON;
UPDATE [Customer] SET [Active] = 1, [AdminComment] = '', [AffiliateId] = 0, [BillingAddress_Id] = 11, [CannotLoginUntilDateUtc] = null, [CreatedOnUtc] = 2018-09-28T11:41:30, [CustomerGuid] = e64fc17f-eb18-40a5-920c-3d0512829d1e, [Deleted] = 0, [Email] = '[email protected]', [EmailToRevalidate] = '', [FailedLoginAttempts] = 0, [HasShoppingCartItems] = 1, [IsSystemAccount] = 0, [IsTaxExempt] = 0, [LastActivityDateUtc] = 2019-04-25T11:09:58, [LastIpAddress] = '10.10.40.111', [LastLoginDateUtc] = 2019-04-25T06:22:46, [RegisteredInStoreId] = 1, [RequireReLogin] = 0, [ShippingAddress_Id] = 12, [SystemName] = '', [Username] = '', [VendorId] = 0
WHERE [Id] = 966;
SELECT @@ROWCOUNT;

sql - Close
Controller: Catalog.Category
0.6 ms (T+25.1 ms)
SaveChanges > SaveChanges > SaveChanges > Execute > Execute > Execute > Commit > ClearTransaction > Close > ConnectionClosing > Write
Connection Close()
sql - Open
Controller: Catalog.Category
0.7 ms (T+27.1 ms)
GetEnumerator > TryGetFirst > MoveNext > Execute > BufferlessMoveNext > Open > OpenDbConnection > ConnectionOpening > Write
Connection Open()
sql - ExecuteReader
Controller: Catalog.Category
2.4 ms (T+28.0 ms)
First Result: 2.4 ms
GetEnumerator > TryGetFirst > MoveNext > Execute > BufferlessMoveNext > ExecuteReader > Execute > CommandExecuting > Write
SELECT TOP(1) [e].[Id], [e].[AllowCustomersToSelectPageSize], [e].[CategoryTemplateId], [e].[CreatedOnUtc], [e].[Deleted], [e].[Description], [e].[DisplayOrder], [e].[IncludeInTopMenu], [e].[LimitedToStores], [e].[MetaDescription], [e].[MetaKeywords], [e].[MetaTitle], [e].[Name], [e].[PageSize], [e].[PageSizeOptions], [e].[ParentCategoryId], [e].[PictureId], [e].[PriceRanges], [e].[Published], [e].[ShowOnHomePage], [e].[SubjectToAcl], [e].[UpdatedOnUtc]
FROM [Category] AS [e]
WHERE [e].[Id] = 74
sql - Close
Controller: Catalog.Category
0.7 ms (T+30.5 ms)
<CompileQueryCore>b__0 > First > TryGetFirst > MoveNext > MoveNext > GetEnumerator > TryGetFirst > Dispose > Close > ConnectionClosing > Write
Connection Close()
sql - Open
Controller: Catalog.Category
0.5 ms (T+32.4 ms)
SaveChanges > SaveChanges > Execute > Execute > Execute > BeginTransaction > Open > OpenDbConnection > ConnectionOpening > Write
Connection Open()
sql - ExecuteReader
Controller: Catalog.Category
1.3 ms (T+33.9 ms)
First Result: 1.3 ms
SaveChanges > SaveChanges > SaveChanges > Execute > Execute > Execute > Execute > ExecuteReader > Execute > CommandExecuting > Write
SET NOCOUNT ON;
UPDATE [GenericAttribute] SET [EntityId] = 966, [Key] = 'LastContinueShoppingPage', [KeyGroup] = 'Customer', [StoreId] = 1, [Value] = 'http://new.fanmats.com/arizona-cardinals'
WHERE [Id] = 23;
SELECT @@ROWCOUNT;

sql - Close
Controller: Catalog.Category
0.7 ms (T+35.5 ms)
SaveChanges > SaveChanges > SaveChanges > Execute > Execute > Execute > Commit > ClearTransaction > Close > ConnectionClosing > Write
Connection Close()
9.70 ms  
Controller: Catalog.Category — 9.70 ms
sql - Open
Controller: Catalog.Category
0.6 ms (T+45.9 ms)
AddEnumerable > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > Open > OpenDbConnection > ConnectionOpening > Write
Connection Open()
sql - ExecuteReader
Controller: Catalog.Category
79.7 ms (T+48.8 ms)
First Result: 79.7 ms
AddEnumerable > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > ExecuteReader > Execute > CommandExecuting > Write
ProductLoadAllPaged '74', 0, 0, 0, 0, null, 1, 0, 0, 0, null, null, '', 0, 1, 1, 0, 1, 5, '', 0, 5, '', 0, 30, 0, null, 1, '' output, null output
sql - Close
Controller: Catalog.Category
0.7 ms (T+128.6 ms)
PrepareCategoryModel > SearchProducts > ToList > AddEnumerable > MoveNext > MoveNext > Dispose > Close > ConnectionClosing > Write
Connection Close()
19.10 ms  
Controller: Catalog.Category — 19.10 ms
sql - Open
Controller: Catalog.Category
0.7 ms (T+148.4 ms)
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > Open > OpenDbConnection > ConnectionOpening > Write
Connection Open()
sql - ExecuteReader
Controller: Catalog.Category
2.7 ms (T+149.3 ms)
First Result: 2.7 ms
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > ExecuteReader > Execute > CommandExecuting > Write
SELECT [e].[Id], [e].[CustomerRoleId], [e].[EndDateTimeUtc], [e].[Price], [e].[ProductId], [e].[Quantity], [e].[StartDateTimeUtc], [e].[StoreId]
FROM [TierPrice] AS [e]
WHERE [e].[ProductId] = 1568
sql - Close
Controller: Catalog.Category
0.7 ms (T+152.1 ms)
get_TierPrices > Proceed > Intercept > Load > Load > Load > MoveNext > MoveNext > Dispose > Close > ConnectionClosing > Write
Connection Close()
16.00 ms  
Controller: Catalog.Category — 16.00 ms
sql - Open
Controller: Catalog.Category
0.6 ms (T+168.8 ms)
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > Open > OpenDbConnection > ConnectionOpening > Write
Connection Open()
sql - ExecuteReader
Controller: Catalog.Category
1.4 ms (T+169.5 ms)
First Result: 1.4 ms
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > ExecuteReader > Execute > CommandExecuting > Write
SELECT [e].[Id], [e].[Active], [e].[DefaultTaxDisplayTypeId], [e].[EnablePasswordLifetime], [e].[FreeShipping], [e].[IsSystemRole], [e].[Name], [e].[OverrideTaxDisplayType], [e].[PurchasedWithProductId], [e].[SystemName], [e].[TaxExempt]
FROM [CustomerRole] AS [e]
WHERE [e].[Id] = 11
sql - Close
Controller: Catalog.Category
0.6 ms (T+171.0 ms)
get_CustomerRole > Proceed > Intercept > Load > Load > Load > MoveNext > MoveNext > Dispose > Close > ConnectionClosing > Write
Connection Close()
26.90 ms  
Controller: Catalog.Category — 26.90 ms
sql - Open
Controller: Catalog.Category
0.6 ms (T+198.5 ms)
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > Open > OpenDbConnection > ConnectionOpening > Write
Connection Open()
sql - ExecuteReader
Controller: Catalog.Category
1.4 ms (T+199.3 ms)
First Result: 1.4 ms
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > ExecuteReader > Execute > CommandExecuting > Write
SELECT [e].[Id], [e].[Active], [e].[DefaultTaxDisplayTypeId], [e].[EnablePasswordLifetime], [e].[FreeShipping], [e].[IsSystemRole], [e].[Name], [e].[OverrideTaxDisplayType], [e].[PurchasedWithProductId], [e].[SystemName], [e].[TaxExempt]
FROM [CustomerRole] AS [e]
WHERE [e].[Id] = 10
sql - Close
Controller: Catalog.Category
0.8 ms (T+200.7 ms)
get_CustomerRole > Proceed > Intercept > Load > Load > Load > MoveNext > MoveNext > Dispose > Close > ConnectionClosing > Write
Connection Close()
29.30 ms  
Controller: Catalog.Category — 29.30 ms
sql - Open
Controller: Catalog.Category
0.7 ms (T+230.8 ms)
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > Open > OpenDbConnection > ConnectionOpening > Write
Connection Open()
sql - ExecuteReader
Controller: Catalog.Category
2.3 ms (T+231.6 ms)
First Result: 2.3 ms
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > ExecuteReader > Execute > CommandExecuting > Write
SELECT [e].[Id], [e].[Active], [e].[DefaultTaxDisplayTypeId], [e].[EnablePasswordLifetime], [e].[FreeShipping], [e].[IsSystemRole], [e].[Name], [e].[OverrideTaxDisplayType], [e].[PurchasedWithProductId], [e].[SystemName], [e].[TaxExempt]
FROM [CustomerRole] AS [e]
WHERE [e].[Id] = 9
sql - Close
Controller: Catalog.Category
0.6 ms (T+233.9 ms)
get_CustomerRole > Proceed > Intercept > Load > Load > Load > MoveNext > MoveNext > Dispose > Close > ConnectionClosing > Write
Connection Close()
27.50 ms  
Controller: Catalog.Category — 27.50 ms
sql - Open
Controller: Catalog.Category
0.6 ms (T+262.0 ms)
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > Open > OpenDbConnection > ConnectionOpening > Write
Connection Open()
sql - ExecuteReader
Controller: Catalog.Category
5.3 ms (T+262.8 ms)
First Result: 5.3 ms
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > ExecuteReader > Execute > CommandExecuting > Write
SELECT [e].[Id], [e].[Active], [e].[DefaultTaxDisplayTypeId], [e].[EnablePasswordLifetime], [e].[FreeShipping], [e].[IsSystemRole], [e].[Name], [e].[OverrideTaxDisplayType], [e].[PurchasedWithProductId], [e].[SystemName], [e].[TaxExempt]
FROM [CustomerRole] AS [e]
WHERE [e].[Id] = 8
sql - Close
Controller: Catalog.Category
0.8 ms (T+268.2 ms)
get_CustomerRole > Proceed > Intercept > Load > Load > Load > MoveNext > MoveNext > Dispose > Close > ConnectionClosing > Write
Connection Close()
28.30 ms  
Controller: Catalog.Category — 28.30 ms
sql - Open
Controller: Catalog.Category
0.8 ms (T+297.3 ms)
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > Open > OpenDbConnection > ConnectionOpening > Write
Connection Open()
sql - ExecuteReader
Controller: Catalog.Category
1.5 ms (T+298.2 ms)
First Result: 1.5 ms
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > ExecuteReader > Execute > CommandExecuting > Write
SELECT [e].[Id], [e].[Active], [e].[DefaultTaxDisplayTypeId], [e].[EnablePasswordLifetime], [e].[FreeShipping], [e].[IsSystemRole], [e].[Name], [e].[OverrideTaxDisplayType], [e].[PurchasedWithProductId], [e].[SystemName], [e].[TaxExempt]
FROM [CustomerRole] AS [e]
WHERE [e].[Id] = 7
sql - Close
Controller: Catalog.Category
0.7 ms (T+299.8 ms)
get_CustomerRole > Proceed > Intercept > Load > Load > Load > MoveNext > MoveNext > Dispose > Close > ConnectionClosing > Write
Connection Close()
437.60 ms  
Controller: Catalog.Category — 437.60 ms
sql - Open
Controller: Catalog.Category
0.6 ms (T+738.1 ms)
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > Open > OpenDbConnection > ConnectionOpening > Write
Connection Open()
sql - ExecuteReader
Controller: Catalog.Category
1.6 ms (T+738.8 ms)
First Result: 1.6 ms
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > ExecuteReader > Execute > CommandExecuting > Write
SELECT [e].[Id], [e].[Address1], [e].[Address2], [e].[City], [e].[Company], [e].[CountryId], [e].[County], [e].[CreatedOnUtc], [e].[CustomAttributes], [e].[Email], [e].[FaxNumber], [e].[FirstName], [e].[LastName], [e].[PhoneNumber], [e].[StateProvinceId], [e].[ZipPostalCode]
FROM [Address] AS [e]
WHERE [e].[Id] = 11
sql - Close
Controller: Catalog.Category
0.7 ms (T+740.5 ms)
get_BillingAddress > Proceed > Intercept > Load > Load > Load > MoveNext > MoveNext > Dispose > Close > ConnectionClosing > Write
Connection Close()
39.40 ms  
Controller: Catalog.Category — 39.40 ms
sql - Open
Controller: Catalog.Category
0.9 ms (T+780.6 ms)
AddEnumerable > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > Open > OpenDbConnection > ConnectionOpening > Write
Connection Open()
sql - ExecuteReader
Controller: Catalog.Category
11.2 ms (T+781.8 ms)
First Result: 11.2 ms
AddEnumerable > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > ExecuteReader > Execute > CommandExecuting > Write
SELECT [ga].[Id], [ga].[EntityId], [ga].[Key], [ga].[KeyGroup], [ga].[StoreId], [ga].[Value]
FROM [GenericAttribute] AS [ga]
WHERE ([ga].[EntityId] = 966) AND ([ga].[KeyGroup] = 'Customer')
sql - Close
Controller: Catalog.Category
0.6 ms (T+793.1 ms)
GetAttributesForEntity > Get > ToList > AddEnumerable > MoveNext > MoveNext > Dispose > Close > ConnectionClosing > Write
Connection Close()
157.40 ms  
Controller: Catalog.Category — 157.40 ms
sql - Open
Controller: Catalog.Category
0.6 ms (T+951.1 ms)
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > Open > OpenDbConnection > ConnectionOpening > Write
Connection Open()
sql - ExecuteReader
Controller: Catalog.Category
1.8 ms (T+951.9 ms)
First Result: 1.8 ms
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > ExecuteReader > Execute > CommandExecuting > Write
SELECT [e].[Id], [e].[CustomerRoleId], [e].[EndDateTimeUtc], [e].[Price], [e].[ProductId], [e].[Quantity], [e].[StartDateTimeUtc], [e].[StoreId]
FROM [TierPrice] AS [e]
WHERE [e].[ProductId] = 10645
sql - Close
Controller: Catalog.Category
0.6 ms (T+953.8 ms)
get_TierPrices > Proceed > Intercept > Load > Load > Load > MoveNext > MoveNext > Dispose > Close > ConnectionClosing > Write
Connection Close()
722.90 ms  
Controller: Catalog.Category — 722.90 ms
sql - Open
Controller: Catalog.Category
0.7 ms (T+1677.3 ms)
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > Open > OpenDbConnection > ConnectionOpening > Write
Connection Open()
sql - ExecuteReader
Controller: Catalog.Category
1.6 ms (T+1678.1 ms)
First Result: 1.6 ms
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > ExecuteReader > Execute > CommandExecuting > Write
SELECT [e].[Id], [e].[CustomerRoleId], [e].[EndDateTimeUtc], [e].[Price], [e].[ProductId], [e].[Quantity], [e].[StartDateTimeUtc], [e].[StoreId]
FROM [TierPrice] AS [e]
WHERE [e].[ProductId] = 3415
sql - Close
Controller: Catalog.Category
0.7 ms (T+1679.8 ms)
get_TierPrices > Proceed > Intercept > Load > Load > Load > MoveNext > MoveNext > Dispose > Close > ConnectionClosing > Write
Connection Close()
834.00 ms  
Controller: Catalog.Category — 834.00 ms
sql - Open
Controller: Catalog.Category
0.8 ms (T+2514.5 ms)
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > Open > OpenDbConnection > ConnectionOpening > Write
Connection Open()
sql - ExecuteReader
Controller: Catalog.Category
2.4 ms (T+2515.5 ms)
First Result: 2.4 ms
Load > MoveNext > MoveNext > MoveNext > Execute > BufferlessMoveNext > ExecuteReader > Execute > CommandExecuting > Write
SELECT [e].[Id], [e].[CustomerRoleId], [e].[EndDateTimeUtc], [e].[Price], [e].[ProductId], [e].[Quantity], [e].[StartDateTimeUtc], [e].[StoreId]
FROM [TierPrice] AS [e]
WHERE [e].[ProductId] = 3736

Does is happen all the time, or only the first time you load the page after the server has not been accessed in a while? ASP.NET will unload idle apps (20 minutes by default).  Use a Keep Alive.

New York wrote:

Hi Thank you so much for quick reply. I greatly appreciate any help.
I made sure keep alive is 20 minutes.

Yes this happens every time. It feels like the catalog pages are not getting cached or something.
Even if i do normal refresh on the page it takes 25-60 seconds always.

New York wrote:

Update:
i discovered that if i reduce number of items on category page from 30 to 6, then the page becomes really fast.

But as the number of products increases the delay directly increases.

Update:
I have done all sorts of things to try and get this working.

But still no success.

Site has 12K products to be exact and 500 categories.
Keep Alive is 20 minutes and working fine.


I have diagnosed that it is "only" the product table that takes forever to load any query even from database.
Full Description column in particular is taking forever.

NopCommerce somehow seems to be calling the whole data of product table including full descriptions while loading catalog pages. i think as store procedure.

I have installed a new site and new db on same servers and put the data of only product table and ran query, same delay is there. But strangely this mega delay is only for this column.
It works fine in Server 2008R2, but in the live server which is 2008 it takes forever.

Will keep posted if i find any more clues.

Any tips and help is appreciated.

I don't think the issue is the database.  Your timing above shows that SQL is fast:

                                                         duration (ms)  sql (ms)
Controller: Catalog.Category                   27110.2  225.9 (41)

Run the Search Stored procedure manually in a tool like SSMS, and I think that you will see it runs quickly.

Update:

My team has got a temporary fix for the issue.
This is actually a NopCommerce 4.10 Bug.
Website does not get cached on server nor client.

What my team told me: after caching done via IIS and a dll file from NopCommerce 4.20 used, the issue has been fixed.

This is supposedly a known issue of NopCommerce 4.10 which they have supposedly fixed in 4.20
I wonder when 4.20 will be officially released.

Thanks,
Varun

varunnaresh wrote:
In about 2-3 weeks

There is still one issue pending where in the release server 2008 a direct db query of Full Description column in product table takes much longer to load in comparison to my test server 2008R2.
I only have 12K products but each product has a big description. Is there any way to make query of full description faster ? (PS: I have indexed all tables.)

Thanks,
Varun

Any way to enable NopCommerce to Cache on browser and server automatically ?

And also is it even possible to cache common sql queries ?

Thanks,
Varun