We're running DMX 5.2.9 on DNN 4.9.4.  Last week, we added a very large number of folder-level attributes (hundreds of them).  Ever since doing this, DMX has been much slower to respond.  Simply double-clicking a folder to open it now takes much longer (in any interface... AJAX, TemplateView, or WebDAV).  

We ran SQL profiler to see what might be going on, and while we're not confident of what we found, at least have a hypothesis.  When double-clicking on a folder that has NO attributes (inherited or explicit), we found a SQL query that contains parts that look like this:

exec sp_executesql N'SELECT e.*, CAST (CASE WHEN UserSubs.EntryId > -1 THEN 1 ELSE 0 END AS BIT) AS Subscribed, CAST (CASE WHEN UserSubs.LastAccess < e.LastModified THEN 1 ELSE 0 END AS BIT) AS Changed, CAST (CASE WHEN e.LockedUntil > GETDATE() THEN 1 ELSE 0 END AS BIT) AS IsLocked, ext.ControlToLoad, ext.Custom, ext.DownloadUrl, ext.EntryTypes, ext.Icon16, ext.Icon32, ext.IsPrivate, ext.MimeType, ext.ResourceFile, ext.SettingsControl, ext.ViewByDefault, CAST(CASE WHEN (PATINDEX(''Collection%'',e.EntryType)>0) THEN 1 ELSE 0 END AS BIT) AS IsCollection, ISNULL((SELECT [Text] FROM dbo.DMX_LongTexts WHERE TypeKey=''ENT'' AND SubTypeKey=''REMARKS'' AND ObjectId=e.EntryId AND Locale=@Locale),  (SELECT [Text] FROM dbo.DMX_LongTexts WHERE TypeKey=''ENT'' AND SubTypeKey=''REMARKS'' AND ObjectId=e.EntryId AND Locale='''')) Remarks, ISNULL((SELECT [Text] FROM dbo.DMX_ShortTexts WHERE TypeKey=''ENT'' AND SubTypeKey=''TITLE'' AND ObjectId=e.EntryId AND Locale=@Locale),  (SELECT [Text] FROM dbo.DMX_ShortTexts WHERE TypeKey=''ENT'' AND SubTypeKey=''TITLE'' AND ObjectId=e.EntryId AND Locale='''')) Title, ea23.Value [att23], ea24.Value [att24], ea25.Value [att25], ea26.Value [att26], ea27.Value [att27], ea28.Value [att28], ea29.Value [att29], ea30.Value [att30], ea31.Value [att31],

The last part of this query is what we find interesting.  The pattern will continue on up through the hundreds of attributes that are on our system (for instance, incrementally counting up to [att761]).   It seems like the query might be looping through every single attribute on the system, even though the folder has no attributes associated with it.  Is this really the case?

Would it be possible to optimize this a bit?  For instance, grab the attributes associated with the folder, then query only for those attributes instead of using the broadsword approach.  

Our user base has definitely noticed the slowdown, so we're at a bit of a loss on how to explain it.  Your thoughts?  Thanks!

Jason

The second half of the monster query above has entries such as this:

LEFT OUTER JOIN dbo.DMX_EntryAttributes ea23 ON ea23.EntryId=e.EntryId AND ea23.AttributeId=23 LEFT OUTER JOIN dbo.DMX_EntryAttributes ea24 ON ea24.EntryId=e.EntryId AND ea24.AttributeId=24 LEFT OUTER JOIN dbo.DMX_EntryAttributes ea25 ON ea25.EntryId=e.EntryId AND ea25.AttributeId=25 LEFT OUTER JOIN dbo.DMX_EntryAttributes ea26 ON ea26.EntryId=e.EntryId AND ea26.AttributeId=26 LEFT OUTER JOIN dbo.DMX_EntryAttributes ea27 ON ea27.EntryId=e.EntryId AND ea27.AttributeId=27 LEFT OUTER JOIN dbo.DMX_EntryAttributes ea28 ON ea28.EntryId=e.EntryId AND ea28.AttributeId=28 LEFT OUTER JOIN dbo.DMX_EntryAttributes ea29 ON ea29.EntryId=e.EntryId AND ea29.AttributeId=29 LEFT OUTER JOIN dbo.DMX_EntryAttributes ea30 ON ea30.EntryId=e.EntryId AND ea30.AttributeId=30

And this pattern also continues through every single attribute on the system.  Hope this might help!

Jason