Learn More





DNN Community Blog

The Community Blog is a personal opinion of community members and by no means the official standpoint of DNN Corp or DNN Platform. This is a place to express personal thoughts about DNNPlatform, the community and its ecosystem. Do you have useful information that you would like to share with the DNN Community in a featured article or blog? If so, please contact .

The use of the Community Blog is covered by our Community Blog Guidelines - please read before commenting or posting.

Module Performance Enhancements

I often hear comments or questions regarding the performance of DotNetNuke. And although we constantly focus on optimizing the performance of the core framework, it is often the Modules which have the greatest impact on performance. Since many of the DNN modules were inherited from the IBuySpy Portal and have not been significantly enhanced, they contain some serious performance issues. In the upcoming release, we have spent some time on improving the performance of the Modules and I would like to share some of the information with the community ( for educational purposes ):

Note: I am going to focus on changes made to the Links module... but similar changes have been made to Html/Text, Announcements, Survey, Documents, etc...

Let start with the Database:

From 03.01.00.SqlDataProvider

Legacy Table Definition:

  CREATE TABLE {databaseOwner}[{objectQualifier}Links]
   [ItemID] [int] NOT NULL IDENTITY(0, 1),
   [ModuleID] [int] NOT NULL,
   [CreatedByUser] [nvarchar] (100) NULL,
   [CreatedDate] [datetime] NULL,
   [Title] [nvarchar] (100) NULL,
   [Url] [nvarchar] (250) NULL,
   [ViewOrder] [int] NULL,
   [Description] [nvarchar] (2000) NULL

  ALTER TABLE {databaseOwner}[{objectQualifier}Links] ADD PRIMARY KEY NONCLUSTERED  ([ItemID])
  CREATE NONCLUSTERED INDEX [IX_{objectQualifier}Links] ON {databaseOwner}[{objectQualifier}Links] ([ModuleID])

  ALTER TABLE {databaseOwner}[{objectQualifier}Links] WITH NOCHECK ADD FOREIGN KEY ([ModuleID]) REFERENCES {databaseOwner}[{objectQualifier}Modules] ([ModuleID]) ON DELETE CASCADE NOT FOR REPLICATION


- CreatedByUser - is defined as nvarchar(100) even though it actually contains a UserID. This is a legacy issue from back in the IBuySpy Portal where modules used to store Usernames in the CreatedByUser field ( changed to UserID in DNN 1.0 ). This column poses a significant performance hit when we do a join with the Users table - since the database needs to deal with associating an nvarchar(100) with an int ( the fact that behaviorally this field actually contains the "Last Modified By UserID" value rather than the "Created By userID" is irrelevant to this discussion ).

Legacy Stored Procedure:

CREATE procedure {databaseOwner}{objectQualifier}GetLink

@ItemId   int,
@ModuleId int


    'URL' = case when {objectQualifier}Files.FileName is null then {objectQualifier}Links.URL else {objectQualifier}Files.Folder + {objectQualifier}Files.FileName end,
    'CreatedByUser' = {objectQualifier}Users.FirstName + ' ' + {objectQualifier}Users.LastName,
from {objectQualifier}Links
left outer join {objectQualifier}Users on {objectQualifier}Links.CreatedByUser = {objectQualifier}Users.UserId
left outer join {objectQualifier}UrlTracking on {objectQualifier}Links.URL = {objectQualifier}UrlTracking.Url and {objectQualifier}UrlTracking.ModuleId = @ModuleID
left outer join {objectQualifier}Files on {objectQualifier}Links.URL = 'fileid=' +
where  {objectQualifier}Links.ItemId = @ItemId
and    {objectQualifier}Links.ModuleId = @ModuleId


- URL - since the URL field can contain a number of "values" (, TabID, "FileID=##" ) it does a join with the Files table using expensive string functions to try and get the FolderName/FileName.
- CreatedByUser - using a join with the Users table it pulls back the FirstName + ' ' + LastName of the User into the CreatedByUser field ( an expensive join for the reasons noted above ). This value is used for audit purposes. However, using a hardcoded format for the Users name is not good practice for some Eastern cultures ( ie. Localization ).


- LEFT OUTER JOIN - these joins are more expensive than INNER JOINS; however, they are required because we do not enforce referential integrity between the Module tables and the Core tables ( loose coupling ). An example of why this is required is if a User is deleted but their UserID is stored in the Modules CreatedByUser field - we still want the Link records to be selected, and an INNER JOIN on Users would exclude the records from the query.
- Lazy Loading - we typically use the same business object definition for loading a single object as we do for loading a collection of objects. However this does not mean that all of the business object properties need to be fully initialized with values. In the case of many Modules, the GetRecords stored procedure is designed to get a collection of records from the database - but there is no need for the module to load all the field values as many are not used in the default view.


From 03.03.00.SqlDataProvider

The following statements are used to convert the CreatedByUser column from an nvarchar(100) to an int. Note: I prefer NOT to use SQL Enterprise Manager to generate a script in this case as it always generates a script which drops the ENTIRE table and then recreates it. When doing work in the core, this technique can cause serious problems if there are third party modules which have added contraints to core tables ( as the core does not know about them ). As a result I prefer to write my own SQL script which simply adds a new column, transfers the data, drops the old column, creates a new column with the old name, transfers the data back, and drops the redundant column.

/** Change CreatedByUser column to an int for performance **/

ALTER TABLE {databaseOwner}{objectQualifier}Links ADD
 CreatedByUserID int NULL

update {databaseOwner}{objectQualifier}Links
set    CreatedByUserID = convert(int,CreatedByUser)

ALTER TABLE {databaseOwner}{objectQualifier}Links
 DROP COLUMN CreatedByUser

ALTER TABLE {databaseOwner}{objectQualifier}Links ADD
 CreatedByUser int NOT NULL CONSTRAINT DF_{objectQualifier}Links_CreatedByUser DEFAULT 0

update {databaseOwner}{objectQualifier}Links
set    CreatedByUser = CreatedByUserID

ALTER TABLE {databaseOwner}{objectQualifier}Links

ALTER TABLE {databaseOwner}{objectQualifier}Links
 DROP CONSTRAINT DF_{objectQualifier}Links_CreatedByUser

The following stored procedure is optimized for performance. Notice that joins are minimized and efficient and there are no expensive string functions. Also notice the use of "with (nolock)" which instructs SQL Server to ignore locks and read directly from the tables.

CREATE procedure {databaseOwner}{objectQualifier}GetLink

@ItemId   int,
@ModuleId int


select  {objectQualifier}Links.ItemId,
from    {objectQualifier}Links with (nolock)
left outer join {objectQualifier}UrlTracking on {objectQualifier}Links.URL = {objectQualifier}UrlTracking.Url and {objectQualifier}UrlTracking.ModuleId = @ModuleID
where  {objectQualifier}Links.ItemId = @ItemId
and    {objectQualifier}Links.ModuleId = @ModuleId

There may be a need to join to the Users table in some Module circumstances. Since the CreatedByUser is now defined as an Integer, this should be fine as the database can now join these tables efficiently.

Data Access Layer:

Change the CreatedByUser parameter to an Integer for type safety.

Legacy DAL Method:

       Public Overrides Function AddLink(ByVal ModuleId As Integer, ByVal UserName As String, ByVal Title As String, ByVal Url As String, ByVal ViewOrder As String, ByVal Description As String) As Integer
            Return CType(SqlHelper.ExecuteScalar(ConnectionString, DatabaseOwner & ObjectQualifier & "AddLink", ModuleId, UserName, Title, Url, GetNull(ViewOrder), Description), Integer)
        End Function

Enhanced DAL Method:

        Public Overrides Function AddLink(ByVal ModuleId As Integer, ByVal UserId As Integer, ByVal Title As String, ByVal Url As String, ByVal ViewOrder As String, ByVal Description As String) As Integer
            Return CType(SqlHelper.ExecuteScalar(ConnectionString, DatabaseOwner & ObjectQualifier & "AddLink", ModuleId, UserId, Title, Url, GetNull(ViewOrder), Description), Integer)
        End Function

When the Modules were converted to Private Assembly projects, they were given their own DataProviders. Unfortunately, the constructor for the DataProvider references the ConnectionString setting in the web.config directly. This is bad practice as the DataProvider should reference the Core GetConnectionString() method so that they are insulated from config changes.


 _connectionString = System.Configuration.ConfigurationSettings.AppSettings(objProvider.Attributes("connectionStringName"))


 _connectionString = Config.GetConnectionString()

( You will also need to add an Imports DotNetNuke.Common.Utilities )

Business Logic Layer:

Change LinkInfo so that the CreatedByUser property is an Integer ( Get/Set ):


 Private _CreatedByUser As String


Private _CreatedByUser As Integer

( This results in some minor changes in the LinkController.vb class as well to deal with the Type change )

User Interface:


The is no change required as it leverages all of the Core methods correctly already. In terms of supporting the new Secure Folder options, the following method provides this functionality with no code changes:

NavigateUrl='<%# FormatURL(DataBinder.Eval(Container.DataItem,"Url"),DataBinder.Eval(Container.DataItem,"TrackClicks")) %>'

        Public Function FormatURL(ByVal Link As String, ByVal TrackClicks As Boolean) As String
            Return Common.Globals.LinkClick(Link, TabId, ModuleId, TrackClicks)
       End Function

( the Link value passed for File links will "FileID=##" - since this is what is stored in the Links.Url field. The core LinkClick() method generates the proper link to use the File Server ).


The module no longer retrieves the users FirstName + ' ' + LastName for audit purposes - instead it retrieves a UserID value. However there is no change required to the module, as the AuditControl will take care of loading the User record and displaying the users DisplayName when required.

ctlAudit.CreatedByUser = objLink.CreatedByUser.ToString
ctlAudit.CreatedDate = objLink.CreatedDate.ToString

DotNetNuke has supported module caching for a long time - and there are some significant performance benefits of using it for semi-static information. This is the case in the Links module. However, the problem for administrators is that once module caching is enabled, they need to remember to manually Clear the Cache after doing content updates. This is not intuitive and leads to support issues ( ie. why did my update not take effect? ). As a result in DNN 3.x we added a core framework method in PortalModuleBase which can be used to programmatically refresh the module cache. Therefore, in any method where the underlying data is being changed ( add, edit, delete, and display settings ), the module should call the SynchronizeModule() method. This eliminates the need for an administrator to manually clear the cache.

Private Sub cmdDelete_Click(ByVal sender As Object, ByVal e As EventArgs) Handles cmdDelete.Click
        If itemId <> -1 Then
            Dim links As New LinkController
        End If

        ' redirect to home page
        Response.Redirect(NavigateURL(), True)
    Catch exc As Exception ' Module failed to load
        ProcessModuleLoadException(Me, exc)
    End Try
End Sub


In DotNetNuke 3.x we added a DefaultCacheTime to the ModuleDefinitions table. This field is used as a default value for CacheTime whenever a new instance of the module is added to a page ( generally this simplifies the application for the administrator as they do not understand what "module caching" means ). This field can be specified in the Module Manifest ( *.dnn ).


Note: Module Caching is not applicable to all modules. It works well for modules which are not interactive.



Comment Form

Only registered users may post comments.


2sic Daniel Mettler (124)
Aderson Oliveira (15)
Alec Whittington (11)
Alex Shirley (10)
Andrew Nurse (30)
Anthony Glenwright (5)
Antonio Chagoury (28)
Ash Prasad (21)
Ben Schmidt (1)
Benjamin Hermann (25)
Benoit Sarton (9)
Beth Firebaugh (12)
Bill Walker (36)
Bob Kruger (5)
Brian Dukes (2)
Brice Snow (1)
Bruce Chapman (20)
Bryan Andrews (1)
cathal connolly (55)
Charles Nurse (163)
Chris Hammond (203)
Chris Paterra (55)
Clinton Patterson (28)
Cuong Dang (21)
Daniel Bartholomew (2)
Dave Buckner (2)
David Poindexter (3)
David Rodriguez (2)
Doug Howell (11)
Erik van Ballegoij (30)
Ernst Peter Tamminga (74)
Geoff Barlow (6)
Gifford Watkins (3)
Gilles Le Pigocher (3)
Ian Robinson (7)
Israel Martinez (17)
Jan Blomquist (2)
Jan Jonas (3)
Jaspreet Bhatia (1)
Jenni Merrifield (6)
Joe Brinkman (270)
John Mitchell (1)
Jon Henning (14)
Jonathan Sheely (4)
Jordan Coopersmith (1)
Joseph Craig (2)
Kan Ma (1)
Keivan Beigi (3)
Ken Grierson (10)
Kevin Schreiner (6)
Leigh Pointer (31)
Lorraine Young (60)
Malik Khan (1)
Matthias Schlomann (15)
Mauricio Márquez (5)
Michael Doxsey (7)
Michael Tobisch (3)
Michael Washington (202)
Mike Horton (19)
Mitchel Sellers (28)
Nathan Rover (3)
Navin V Nagiah (14)
Néstor Sánchez (31)
Nik Kalyani (14)
Peter Donker (52)
Philip Beadle (135)
Philipp Becker (4)
Richard Dumas (22)
Robert J Collins (5)
Roger Selwyn (8)
Ruben Lopez (1)
Ryan Martinez (1)
Salar Golestanian (4)
Sanjay Mehrotra (9)
Scott McCulloch (1)
Scott S (11)
Scott Wilkinson (3)
Scott Willhite (97)
Sebastian Leupold (80)
Shaun Walker (237)
Shawn Mehaffie (17)
Stefan Cullmann (12)
Stefan Kamphuis (12)
Steve Fabian (31)
Timo Breumelhof (24)
Tony Henrich (3)
Torsten Weggen (2)
Vicenç Masanas (27)
Vincent Nguyen (3)
Vitaly Kozadayev (6)
Will Morgenweck (37)
Will Strohl (163)
William Severance (5)
Try Evoq
For Free
Start Free Trial
a Demo
See Evoq Live
Need More Information?