Additional indexes help optimize DNN performance

When dealing with the slow performance of a DNN site I quickly analyzed it in the test environment with the ANTS Performance Profiler and understood that most of the time the system spent inside the DB querying some data.

The analysis moved then to SQL Performance Tuning tools. The Activity Monitor in the Server Management Studio comes in very handy. With enough server roles you can get to this screen that shows you the "Recent Expensive Queries" window where you can see the top queries and their execution plans.

I added just 2 indexes:

• An index on ModuleId, IsPublished and LastModifiedOnDate for the HtmlText table
• An index on ModuleId and PortalId on the Modules table

As a result the average execution time on the 2 top queries with hundreds of executions went down from 3ms to 0ms with logical reads greatly reduces.

I wonder why these indexes do not come right out of the box.

ORA-06502 with RAW OUT parameter called from ODP.NET

An interesting problem popped up today while calling a PL/SQL stored procedure from ODP.NET. It turns out that when you have an output RAW parameter you MUST provide some buffer space for it while adding this parameter, even though the parameter is OUTPUT only.

Here's a part of the definition of the stored procedure in Oracle:

PROCEDURE bla-bla(o_user_guid OUT RAW(16)) ...

Here's how it's called from C#:

using (OracleCommand cmd = con.CreateCommand())
{
     ...
     cmd.AddParameter(new OracleParameter("o_user_guid", 
                                                                   OracleDbType.Raw,  
                                                                   ParameterDirection.Output));
}

Strangely enough this code returns the following error:

ORA-06502: PL/SQL: numeric or value error: raw variable length too long

Adding the size of 16 to the creation of the parameter does not help.


What helps though is adding a dummy buffer while creation of the parameter as follows:

using (OracleCommand cmd = con.CreateCommand())
{
     ...
     byte[] userGuidPlaceholder = new byte[16];

     cmd.AddParameter(new OracleParameter("o_user_guid", 
                                                                  OracleDbType.Raw,  
                                                                  16, 
                                                                  userGuidPlaceholder, 
                                                                  ParameterDirection.Output);
}

Weird.