I just released TurboDNN SQL Scripts 0.9.5 for DNN 7.1.2 to 7.3.3.

• Support for DNN 7.3.3 added
• Fixed a typo in procedure GetAvailableUsersForIndex
• Adjusted MoveTabToParent procedure

Download for free from https://dnnscript.codeplex.com/releas....

Please follow instructions in readme file.

---

Cheers from Germany,
Sebastian Leupold

   

Speed up your DNN Websites with TurboDNN

I submitted around 150 items with SQL improvements to Jira, around 50 got included, the more important ones got rejected for some reasons and the others are pending.
With this background, I got tired of adding and updating Jira items for it and prefer focusing on SQL improvements itself, which you may easily apply to your database.

---

Cheers from Germany,
Sebastian Leupold

   

Speed up your DNN Websites with TurboDNN

FYI:

1. I didn't modify any table with a single row - but even in this case, a table should have a primary key and clustered index (which defines the physical order of record, requirement for AzureSQL).


2. If a table is always queried by a specific filter or order, it makes sense to adjust the clustered index accordingly, despite the number of records - it might speed up joins with larger tables as well.


3. Some tables are in bad need of index optimization, e.g. Tabs table in DNN core version has an index with 23 columns included (I reduced this number in a previous step from 32), which is far from being optimal. The better solution is moving the clustered key - which requires to recreate primary key - which requires to remove all foreign keys from other tables. As we know that there are 3rd party modules, which placed a foreign key on tabs, in TurboDNN I created a solution to store all 3rd party relations, drop it, modify the table and recreate the keys.


4. There are known bugs in the data layer, which cannot be fixed just in API and therefore require some logic to be moved to the data layer, e.g. retrieving the master portal of a portal group when retrieving users filtered by roles, as roles are site specific, but profile and other data is taken from master portal. In TurboDNN, this has been solved easily without affecting single portals or measurable performance decrease (just a single record lookup per procedure call).


5. Schemabinding is used in TurboSchema for several views and functions, allowing to use indexed views, which perform like a single table and may speed up some queries dramatically. The downside of schemabinding: you cannot modify affected tables, as long as it is used in a schemabound view/function. That's the reason, why you have to run TurboUnschema, before performing DNN Upgrades - it replaces the schemabound views, functions and using procedures with unbound versions. I didn't submit any of the schemabinding to Jira, because I was aware that DNN developers are unable to handle it without changes in upgrade process. (this doesn't affect 3rd party module installs, because they should never modify core tables, but upgrades of DNN itself).


6. While I did not measure the performance gain for each of my changes individually - some are just best practice, others just obvious improvements, I did check in SQL profiler that the performance of each stored procedure has not been affected negatively.


7. As a summary, small sites will gain maybe around 20% improved page load, large sites with many users and folders will benefit dramatically (from "unusable" to "fair" or from "slow" to "good"). Optimization is an ongoing process, and I am already working on a complete repository to optimize all tables, indexes, constraints, vies, functions and procedures. This does affect compatibility with DNN core release, but it modifies the data layer only, like using a 3rd party data provider for a different database server. This provider should be faster, lass faulty and more consistent than the default one.

---

Cheers from Germany,
Sebastian Leupold

   

Speed up your DNN Websites with TurboDNN