What is the Cost of an Exception

This Post Should Have been made in March. It’s old…

In 2 weeks we have Dynatrace on-site to do some product training. I’ve been fortunate enough to have been introduced over the past year to the training approaches that the Dynatrace consulting team takes with their customers. So I kind of know what’s going to happen when Asad comes back. He’s definitely going to grasp at low-hanging fruit. The biggest one that I know he’s going to push hard are unnecessary Java Exceptions.

Below is the PurePath details of a teacher Grade Center request. The path is “/webapps/gradebook/do/instructor/getJSONData” which was called 2x by the same thread for the same request. I have a suspicion that it has to do with the Page Processing framework. Either way if you dig into the request, you see littered throughout the calls are a series of Exceptions.

 

The first chart shows the details of (3) Constraint Violations. Looking into the exception, you see the chart below which is calling out that certain columns from the COURSE_USERS (intro, note and pinfo) are NULL. That could be a result of our data model. It could also be something all customers receive because these are table values that might seem irrelevant. I know for the purpose of this query, this data is completely unnecessary. What’s even more frustrating is that we are calling a NULL value exception, but the table does not restrict NULLs on those 3 columns.

So it’s a little more complicated than quantifying the cost. It comes back to poor programming decisions or poor DDL design. I would argue it’s the first. We really only want the PK1 and possibly a couple other foreign keys from COURSE_USERS for what we are doing. We certainly do not need to unmarshall a NCLOB, let alone 3 of them for no purpose. Imagine if we actually were populating those fields and their impact on memory.

SELECT pk1, dtmodified, users_pk1, crsmain_pk1, data_src_pk1, role, link_name_1, link_url_1, link_desc_1, link_name_2, link_url_2, link_desc_2, link_name_3, link_url_3, link_desc_3, photo_link, intro, note, pinfo, cartridge_ind, available_ind, receive_email_ind, roster_ind, row_status, enrollment_date, last_access_date, child_crsmain_pk1 FROM course_users WHERE  ( crsmain_pk1 = ? )  AND  ( users_pk1 = ? )  AND  ( row_status = ? ) 

\\ Below is the Table

SQL> desc course_users;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PK1                                       NOT NULL NUMBER(38)
 LINK_NAME_3                                        NVARCHAR2(100)
 CRSMAIN_PK1                               NOT NULL NUMBER(38)
 DATA_SRC_PK1                                       NUMBER(38)
 ROLE                                               NVARCHAR2(50)
 LINK_NAME_1                                        NVARCHAR2(100)
 LINK_URL_1                                         VARCHAR2(100)
 LINK_DESC_1                                        NVARCHAR2(255)
 LINK_NAME_2                                        NVARCHAR2(100)
 LINK_URL_2                                         VARCHAR2(100)
 LINK_DESC_2                                        NVARCHAR2(255)
 USERS_PK1                                 NOT NULL NUMBER(38)
 LINK_URL_3                                         VARCHAR2(100)
 LINK_DESC_3                                        NVARCHAR2(255)
 PHOTO_LINK                                         NVARCHAR2(500)
 INTRO                                              NCLOB
 NOTE                                               NCLOB
 PINFO                                              NCLOB
 CARTRIDGE_IND                             NOT NULL CHAR(1)
 AVAILABLE_IND                             NOT NULL CHAR(1)
 RECEIVE_EMAIL_IND                         NOT NULL CHAR(1)
 ROSTER_IND                                NOT NULL CHAR(1)
 SOS_ID_PK2                                NOT NULL NUMBER(38)
 ROW_STATUS                                         NUMBER(1)
 ENROLLMENT_DATE                                    DATE
 LAST_ACCESS_DATE                                   DATE
 CRSMAIN_SOS_ID_PK2                                 NUMBER(38)
 USERS_SOS_ID_PK2                                   NUMBER(38)
 DTMODIFIED                                         DATE
 CHILD_CRSMAIN_PK1                                  NUMBER(38)

SQL>

 

What’s My Point About Dynatrace

So you are probably asking what’s my point about Dynatrace…Well I know for certain they are going to focus on exceptions. They are right to do so. Dirty code is bad code, nobody can argue that. Yes, we should really fix these problems. This could have been a filtered load. Second, we could not have issued an exception for a NULL value on columns that do not restrict NULLs. Third, we could make the columns NOT NULL, but I wouldn’t do it.

The key point is that at somepoint, we are going to need to measure the true cost…

Advertisements

2 thoughts on “What is the Cost of an Exception

  1. Pingback: Digging Into Dynatrace Exceptions and Logging « Seven Seconds

  2. Pingback: Broken Window Theory…Exceptions and Log Messages « Seven Seconds

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s