Performance issue

Imar Spaanjaars

Posted on 28/06/2018 15:40:28

I have a customer with a site that has some performance issues. They enabled some external tool (Solar winds) and found out that a particular SQL statement took up a large amount of time (see images attached)

SELECT DISTINCT EcomGroups.*, EcomShopGroupRelation.ShopGroupShopID AS ShopID FROM EcomGroups FULL JOIN EcomShopGroupRelation ON EcomShopGroupRelation.ShopGroupGroupID = EcomGroups.GroupID

They only place I can find this code is here:

        Private Shared ReadOnly Property CachedGroups As Dictionary(Of String, Dictionary(Of String, Dictionary(Of String, Group)))
            Get
                Dim storage As Content.Caching.Storage = Content.Caching.StorageManager.Current.GetStorage("eCommerce")
               Dim groupsDictionary As New Dictionary(Of String, Dictionary(Of String, Group))()
                Dim groupShopDictionary As New Dictionary(Of String, Dictionary(Of String, Group))()
                Dim groupsShopWithoutLanguageDictionary As New Dictionary(Of String, Dictionary(Of String, Group))()
                Dim result As New Dictionary(Of String, Dictionary(Of String, Dictionary(Of String, Group)))

               If storage.Contains(cacheKey) Then
                    result = storage.Get(Of Dictionary(Of String, Dictionary(Of String, Dictionary(Of String, Group))))(cacheKey)
                Else
                    'Load all groups
                    Using groupReader As IDataReader = Database.CreateDataReader("SELECT DISTINCT EcomGroups.*, EcomShopGroupRelation.ShopGroupShopID AS ShopID FROM EcomGroups FULL JOIN EcomShopGroupRelation ON EcomShopGroupRelation.ShopGroupGroupID = EcomGroups.GroupID", "Ecom.mdb")
                        While groupReader.Read()
                               … Build results
                       End While
                    End Using
                    result.Add("GroupsByLanguage", groupsDictionary)
                    result.Add("GroupsByLanguageAndShop", groupShopDictionary)
                    result.Add("GroupsByShop", groupsShopWithoutLanguageDictionary)
                   storage.Insert(cacheKey, result)
                End If
               Return result
            End Get
        End Property

Looks like this should be called only once and then stored in the cache.

Could there be any reason for this to be not in the cache constantly and lead to almost an hour of execution time?

Some specs:

DW 8.9.2.20
Number of products: 37K
Number of groups: 11114
Query executes in under a second when executed directly against SQL Server...

Thanks,

Imar

Replies

Nicolai Pedersen

Posted on 29/06/2018 11:43:33

Hmm...

I would go through the indexes as one thing.

Does this solution have some load balancing configured? Maybe by accident? Because that could trigger the cache to invalidate.

And since the StorageManager in DW8 uses System.Web.Cache (as far as I remember) the server could dump the cache if there is an issue with memory...?

Any interessting stuff in event viewer?

Imar Spaanjaars

Posted on 12/07/2018 11:46:33

Hi Nicolai,

I looked into this further but can't find much. We're not using load balancing to my knowledge. I do see records in the ClusterInstance table but they are all disabled. Also, most of the InstanceStartup fields are really old. But there's one that's from the last time the site started. How does a record get there? This is DW 8 so I should have Dynamicweb.LoadBalancing.dll (or something like that) in my bin, right? I checked, and we don't have any files with cluster or load in the bin.

The site is behind a reverse proxy though as we're merging content from an existing PHP site and Dynamicweb into one site.

Nothing interesting in the Event Viewer; it could be a memory issue but I am not seeing any app pool restarts that could indicate a loss of the cache.

Is there a way to track cache misses in Dynamicweb? I turned on performance counters for asp.net cache missing but I am not seeing any. Anything else I can do to track this down?

Imar

Imar Spaanjaars

Posted on 12/07/2018 12:19:26

BTW, is this true?

>> And since the StorageManager in DW8 uses System.Web.Cache (as far as I remember)

I traced the code to StorageManager and Storage and it seems to be using a static Dictionary. If that 's the right code, that an app recycle would be needed for that memory to be lost, right?

Nicolai Pedersen

Posted on 13/07/2018 12:36:38

I've tried a local test on this and do not have an issue...

Can we in any ways reproduce this? Or describe in what scenario this happens - or is it occasionally or when the site runs the entire time?

Anyways - if we need to look further into this we need a copy of the site and some steps to reproduce or a page that is slow so we have a chance of finding the cause instead of just guessing.

BR Nicolau

You must be logged in to post in the forum

Developer forum

Performance issue

Replies