DotNetNuke is an open source content management system / application development framework that leverages ASP.NET / Microsoft.NET. The DNN Database schema contains the SiteLog table where all page hits are logged. Every visit to the DNN site results in a row being inserted into the SiteLog table. Depending on how busy the site is this table can get quite large.
The image above shows a sample SiteLog table with nearly 2 million rows and over 1 GB in size. In contrast the entire DNN database including the SiteLog table is about 1.4 GB.
Joining the data in the SiteLog table with the Tabs table or better yet the vw_Tabs view allows for the analysis of hit statistics per tab/page. Note that query performance could be greatly improved if appropriate indexes on the SiteLog table existed; particularly on the DateTime and TabId columns. However, since this is a write-heavy table, performance of the site could be affected negatively if additional indexes are added to the SiteLog table. (Remember that every insert to the table would need to update any indexes as well which could be a performance hit depending on how busy the site is.)
Since the SiteLog table is written to for every site hit directly querying the SiteLog table, especially during times where the site is busy, can degrade performance that will manifest in blocking of the query. Use caution when querying the SiteLog table on a production system. With that background and associated cautions out of the way – let us look at a tsql script that queries the DNN SiteLog using a CTE. (MSDN CTE Documentation)
Three parameters need to be set at the beginning of the script: @BeginDate, @EndDate, and @TabId. The script starts by creating a temporary table, #HitCount, containing each TabId, along with Hit Count, and Unique Hit Count from the SiteLog table where the DateTime column values are GTE @BeginDate and LT @EndDate. The CTE that follows can then join on the temporary table instead of the live SiteLog table improving performance. The CTE utilizes a third parameter, @TabId, to determine the starting point for recursion.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68
USE [DotNetNuke] GO DECLARE @BeginDate DATETIME ; DECLARE @EndDate DATETIME ; DECLARE @TopTabId INT ; SET @BeginDate = '01-Oct-2012' ; -- Change this to the desired Begin Date. SET @EndDate = '01-Nov-2012' ; -- Change this to the desired End Date. SET @TopTabId = 54 ; -- Change this to the desired starting / top Tab Id. SELECT [sitelog].[TabId] , COUNT([sitelog].[SiteLogId]) AS [HitCount] , COUNT(DISTINCT [sitelog].[UserHostAddress]) AS [UniqueHitCount] INTO [#HitCount] FROM [dbo].[sitelog] (NOLOCK) WHERE [sitelog].[DateTime] >= @BeginDate AND [sitelog].[DateTime] < @EndDate GROUP BY [sitelog].[TabId] ORDER BY [sitelog].[TabId] ASC ; WITH MenuTabs ( [TabId], [TabName], [ParentId], [MenuLevel], [Level], [Title], [TabPath], [HasChildren], [IsVisible], [HitCount], [UniqueHitCount] ) AS ( SELECT [VT1].[TabId] , [VT1].[TabName] , [VT1].[ParentId] , 0 AS [MenuLevel] , [VT1].[Level] , [VT1].[Title] , [VT1].[TabPath] , [VT1].[HasChildren] , [VT1].[IsVisible] , [H1].[HitCount] , [H1].[UniqueHitCount] FROM [dbo].[vw_Tabs] [VT1] INNER JOIN [#HitCount] [H1] ON [VT1].[TabId] = [H1].[TabId] WHERE [VT1].[TabId] = @TopTabId AND [VT1].[IsVisible] = 1 UNION ALL SELECT [VT2].[TabId] , [VT2].[TabName] , [VT2].[ParentId] , [M].[MenuLevel] + 1 , [VT2].[Level] , [VT2].[Title] , [VT2].[TabPath] , [VT2].[HasChildren] , [VT2].[IsVisible] , [H2].[HitCount] , [H2].[UniqueHitCount] FROM [dbo].[vw_Tabs] [VT2] INNER JOIN [#HitCount] [H2] ON [VT2].[TabId] = [H2].[TabId] INNER JOIN MenuTabs [M] ON [VT2].[ParentID] = [M].[TabId] WHERE [VT2].[IsVisible] = 1 ) SELECT [TabId] , [TabName] , [ParentId] , [Level] , [Title] , [TabPath] , [HasChildren] , [IsVisible] , [HitCount] , [UniqueHitCount] FROM [MenuTabs] ORDER BY [TABPATH] ; DROP TABLE [#HitCount] ;
If repeated queries of the data are desired; a better idea may be to offload / transform the data from the SiteLog table periodically into another table where additional indexes can be added. A follow-up post will explore options along these lines.
* Note that my experiences with DNN and the corresponding data structures are related to version 5.x. Version 6.x is in mainstream release and version 7.0 is in beta at the time of this writing.
“DNN is a leading Web Content Management Platform (or CMS) for Microsoft, powering over 700,000 production web sites worldwide. The flexible DNN open source CMS platform also functions as a web application development framework. Depending on your role within your organization, DNN provides powerful benefits to support your Web initiatives.” http://www.dotnetnuke.com/