SQL with No Row Processed
 Concept

The 'rows processed' in SQL stats refers to the total number of rows returned by a SQL statement within a time period. Some SQL statements have their rows processed directly linked to their executions, while others show no pattern because different input parameters produce different result sets.

We need to pay attention to those SQL statements with no row processed and understand them in the context of application logic. It could be that those SQL statements are expected by design. Or, it could be that the business logic changed over time and those SQL statement could not produce result as expected before. If that is the case, those SQL statements need to be stopped. All running SQL statements consume system resources and we will benefit from reducing unnecessary workloads.

 Case Study Summary

The case scenarios include:

 Case 1: Identify the SQL statement with no row processed

 Case 2: Identify the SQL statement which did not run correctly

SQL statements with no row processed do not necessarily show rows processed as 0. For those SQL statements whose executions should have a ratio with rows processed, it is easier to detect whether some rows don’t get processed. As shown below, a SQL statement was a ‘insert’ statement whose executions should have one to one relationship with rows processed. However, it did not get processed correctly every time and ended up with much less rows processed than executions. Further investigation turns out that a unique constraint on the target table prevents inserting duplicate values but application code was careless about what values to be inserted.

Copyright © 2011 Actrace. All Rights Reserved.
Home | Product | Case Study | Support | Download | Contact Us