In one of our custom DNN modules we are grabbing the list of Countries and Regions for each Country for the DotNetNuke Host List. This is normally a good thing since we then have a standardized list of Countries/Regions, and it also allows the DNN Site Administrator to add new Countries and/or Regions to the list and have them show up automatically in our module.
The problem is that the DNN ListController API seems to be very slow when dealing with larger sets of Countries and Regions. A customer of ours had customized their list to include many more countries, and many more regions for each country. All of a sudden our module slowed to a crawl!
I ran the RedGate ANTS profiler on the code and found the “GetListEntryInfoCollection()” method on the “ListController” DNN class was taking almost 4 seconds to execute!!
I ended up writing my own helper method to retrieve the same list in under 0.5 seconds. This may not be a best practice, since my function is calling straight SQL through ADO.Net, but it’s definitely faster to execute!
The relevant code snippets are below.
——————————————————————-
internal static List GetCountryListAdoNet()
{
string sql = string.Format(@"
SELECT
country.Value as CountryCode
,country.[Text] as CountryName
,region.Value as RegionCode
,region.[Text] as RegionName
FROM {0}Lists country
LEFT JOIN {0}Lists region ON country.EntryID = region.ParentID
WHERE country.ListName = 'Country'
--ORDER BY country.[Text], region.[Text]
", GetDbObjectQualifier() ?? "");
Dictionary countries = new Dictionary();
using (IDataReader reader = DotNetNuke.Data.DataProvider.Instance().ExecuteSQL(sql))
{
while (reader.Read())
{
string countryCode = reader.GetString(0);
string countryName = reader.GetString(1);
CountryInfo country;
if(!countries.TryGetValue(countryCode, out country))
{
country = new CountryInfo() { CountryCode = countryCode, Name = countryName };
countries[countryCode] = country;
}
if (!reader.IsDBNull(2) && !reader.IsDBNull(3))
{
country.Regions.Add(reader.GetString(3), new RegionInfo() { RegionCode = reader.GetString(2), Name = reader.GetString(3) });
//country.Regions.Add(new RegionInfo() { RegionCode = reader.GetString(2), Name = reader.GetString(3) });
}
}
}
var list = countries.Values.ToList();
list.Sort((left, right) => left.Name.CompareTo(right.Name));
return list;
}
internal class CountryInfo
{
public string CountryCode { get; set; }
public string Name { get; set; }
public SortedList Regions { get; set; }
public CountryInfo()
{
Regions = new SortedList();
}
}
internal class RegionInfo
{
public string RegionCode { get; set; }
public string Name { get; set; }
}
Let’s see how this goes, excited to try the new taxonomy and user profile features
Why DNN object qualifiers are a pain in the butt!
After a lot of hard work, we (Web Ascender) have launched a brand new Product Division: www.DNNspot.com ! DNNspot provides pre-packaged modules for the popular DotNetNuke content management system. Some of our current modules that are available now:
We plan to add more modules in the future, but for now, congratulations to the team for all their hard work!
Web Ascender announces product division
Just installed Chrome Beta and a delicious extension, feels great to finally have extensions in chrome
I’m attending CodeMash 2010
Nifty tool for checking/validation your website for SEO and Performance optimizations