<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Mining performance hotspots with JProfiler, jQAssistant, Neo4j and Pandas
TL;DR I show how I determine the parts of an application that trigger unnecessary SQL statements by using graph analysis of a call tree.
Introduction
General claim
We don't need more tools that show use more problems in our software. We need ways to determine the right problem to solve. But often we just fix the symptoms on the surface rather than the underlying problems. I find that approach non-professional and want to do my part to improve this situation by delivering root cause analysis of symptoms to get to the real problems in our software.
What you will see
In this notebook, I'll show you one of my approaches for mining performance problems based on application runtime performance analysis. In general, I use this approach to make the point that there are severe design errors that have a negative influence on the application's overall performance. In this example, I show you how I determine the reasons behind a massive amount of executed SQL statements in an application step by step.
The key idea is to use graph analysis to analyze call stacks that were created by a profiling tool. With this approach, I'm not only able to show the hotspots that are involved in the performance issue (because the hotspots show that some SQL statements take long to execute or are executed too often), but also the reasons behind these hotspots. I achieve this by extracting various additional information like the web requests, application's entry points and the triggers within the application causing the hotspots.
This is very helpful to determine the most critical parts in the application and gives you a hint where you could start improving immediately. I use this analysis at work to determine the biggest performance bottleneck in a medium sized application (~700 kLOCs). Based on the results, we work out possible improvements for that specific hotspot, create a prototypical fix for it, measure the fix's impact and, if the results are convincing, roll out the fix for that problem application wide (and work on the next performance bottleneck and so on).
I hope you'll see that this is a very reasonable approach albeit the simplified use case that I show in this blog post/notebook.
Used software
Before we start, I want to briefly introduce you to the setup that I use for this analysis
Step1:  For a full version of this file, see GitHub
Step3:  We execute a simple query for one XML element and it's relationships to its attributes.
For example, if we want to display the data of this <tt>&lt;hotspot</tt> element
xml
&lt;hotspot 
    leaf="false" 
    value="SELECT id, name FROM types ORDER BY name" 
    time="78386" 
    count="107"&gt;
as graph, we get that information from all the attributes of an element (don't worry about the syntax of the following two Cypher statements. They are just there to show you the underlying data as an example).
Step5:  As seen in the picture with the huge graph above, each <tt>&lt;hotspot&gt;</tt> node refers to the further <tt>&lt;node&gt;</tt>s, that call the hotspots. In our case, these nodes are the methods in our application that are responsible for the executions of the  SQL statements.
If we list all attributes of such a node, we've got plenty of information of the callees of the hotspots. For example these nodes contain information about the method name (<tt>method</tt>) or the number of executed SQL statements (<tt>count</tt>)
Step7:  Prepare performance analysis
Because it's a bit cumbersome to work at the abstraction level of the XML file, let's enrich this graph with a few better concepts for mining performance problems.
Clean up (optional)
Before executing the first statements, we clean up any preexisting data from previous queries. This is only necessary when you execute this notebook several times on the same data store (like me). It makes the results repeatable and thus more reproducible (a property we should generally strive for!).
Step9:  Consolidate the data
We create some new nodes that contain all the information from the XML part of the graph that we need. We simply copy the values of some attributes to new <tt>Call</tt> nodes.
In our Cypher query, we first retrieve all <tt>&lt;node&gt;</tt> elements (identified by their "name" property) and some attributes that we need for our analysis. For each relevant information item, we create a variable to retrieve the information later on
Step11:  We do the same for the <tt>&lt;hotspot&gt;</tt> elements. Here, the attributes are a little bit different, because we are gathering data from the hotspots that contain information about the executed SQL statements
Step13:  Now, we have many new nodes in our database that are aren't directly connected. E. g. a <tt>Call</tt> node looks like this
Step15:  And there we have it! Just click in the Neo4j browser on the relationship CALLS and the you'll see our call tree from JProfiler as call graph in Neo4j, ready for root cause analysis!
Root Cause Analysis
Conceptual model
All the work before was just there to get a nice graph model that feels more natural. Now comes the analysis part
Step17:  Identifying all entry points into the application
Next, we identify the entry points aka first nodes of our application. We can achieve this by first searching all the shortest paths between the already existing <tt>Request</tt> nodes and all the nodes that have our package name. From all these subgraphs, we take only the single subgraph that has only a single node with the package name of our application. This is the first node that occurs in the call graph when starting from the request (I somehow can feel that there is a more elegant way to do this. If so, please let me know!). We mark that nodes as <tt>Entry</tt> nodes.
Step19:  With the same approach, we can mark all the calls that trigger the execution of the SQL statements with the label <tt>Trigger</tt>.
Step21:  After marking all the relevant nodes, we connect them via a new relationshop <tt>LEADS_TO</tt> to enable more elegant queries later on.
Step23:  Getting results
All the previous steps where needed to enable this simple query, that gives us the spots in the application, that lead eventually to the hotspots!
Step24:  The returned data consists of
* <tt>request</tt>
Step25:  We see immediately that we have an issue with the loading of the pet's owners via the <tt>OwnerController</tt>. Let's look at the problem from another perspective
Step26:  And group the hotspots accordingly.
Step27:  Now we made the problem more obvious
Step28:  You could also have a look at the most problematic spot in the application by grouping the data by the class and the method that triggers the execution of the most SQL statements.
<ASSISTANT_TASK:>
Python Code:
with open (r'input/spring-petclinic/JDBC_Probe_Hot_Spots_jmeter_test.xml') as log:
    [print(line[:97] + "...") for line in log.readlines()[:10]]
Explanation: Mining performance hotspots with JProfiler, jQAssistant, Neo4j and Pandas
TL;DR I show how I determine the parts of an application that trigger unnecessary SQL statements by using graph analysis of a call tree.
Introduction
General claim
We don't need more tools that show use more problems in our software. We need ways to determine the right problem to solve. But often we just fix the symptoms on the surface rather than the underlying problems. I find that approach non-professional and want to do my part to improve this situation by delivering root cause analysis of symptoms to get to the real problems in our software.
What you will see
In this notebook, I'll show you one of my approaches for mining performance problems based on application runtime performance analysis. In general, I use this approach to make the point that there are severe design errors that have a negative influence on the application's overall performance. In this example, I show you how I determine the reasons behind a massive amount of executed SQL statements in an application step by step.
The key idea is to use graph analysis to analyze call stacks that were created by a profiling tool. With this approach, I'm not only able to show the hotspots that are involved in the performance issue (because the hotspots show that some SQL statements take long to execute or are executed too often), but also the reasons behind these hotspots. I achieve this by extracting various additional information like the web requests, application's entry points and the triggers within the application causing the hotspots.
This is very helpful to determine the most critical parts in the application and gives you a hint where you could start improving immediately. I use this analysis at work to determine the biggest performance bottleneck in a medium sized application (~700 kLOCs). Based on the results, we work out possible improvements for that specific hotspot, create a prototypical fix for it, measure the fix's impact and, if the results are convincing, roll out the fix for that problem application wide (and work on the next performance bottleneck and so on).
I hope you'll see that this is a very reasonable approach albeit the simplified use case that I show in this blog post/notebook.
Used software
Before we start, I want to briefly introduce you to the setup that I use for this analysis:
Fork of the Spring sample project PetClinic as application to torture
Tomcat 8 installation as servlet container for the application (standalone, for easier integration of the profiling tool)
JMeter load testing tool for executing some requests
JProfiler profiler for recording performance measures
jQAssistant static analysis tool for reading in call trees
Neo4j graph database and Cypher graph query language for executing the graph analysis
Pandas, py2neo and Bokeh on Jupyter* as documentation, execution and analysis environment
The first ones are dependent on the environment and programming language you use. jQAssistant, Neo4j and Pandas are my default environment for software analytics so far. I'll show your how all those tools fit together.
So let's get started!
*actually, what you see here, is the result of an executed Jupyter notebook, too. You can find that notebook on GitHub.
Performance Profiling
As a prerequisite for this analysis, we need performance profiling data gathered by a profiler. A profiler will be integrated into the runtime environment (e. g. Java Virtual Machine) of your application and measures diverse properties like method execution time, number of web service calls, executed SQL statements etc. Additionally, we need something that uses or clicks through our application to get some numbers. In my case, I run the Spring PetClinic performance test using JMeter.
As profiling tool, I use JProfiler to record some performance measures while the test was running.  
<p><tt>&lt;advertisment&gt;</tt><br />
At this point, I want to thank ej-technologies for providing me with a [free open-source license](https://www.ej-technologies.com/buy/jprofiler/openSource) for JProfiler that enables this blog post in exchange of mentioning their product:
<a href="http://www.ej-technologies.com/products/jprofiler/overview.html">
![](https://www.ej-technologies.com/images/product_banners/jprofiler_large.png)</a>
JProfiler is a great commercial tool for profiling Java application and costs around 400 €. It really worth the money because it gives you deep insights how your application performs under the hood.
<tt>&lt;/advertisment&gt;</tt>
</p>
Also outside the advertisement block, I personally like JProfiler a lot because it does what it does very very good. Back to the article.
The recording of the measures starts before the execution of the performance test and stops after the test has finished successfully. The result is stored in a file as so-called "snapshot". The use of a snapshot enables you to repeat your analysis over and over again with exactly the same performance measures.
What we usually need for performance analysis is a recorded runtime stack of all method calls as a call tree. A call tree shows you a tree of the called methods. Below, you can see the call tree for the called methods with their measured CPU wall clock time (aka the real time that is spent in that method) and the number of invocations for a complete test run:
With such a view, you see which parts of your application call which classes and methods by drilling down the hierarchy by hand:
But there is more: You can also "turn around" the call tree and list all the so-called "HotSpots". Technically, e. g. for CPU HotSpots, JProfiler sums up all the measurements for the method call leafs that take longer than 0.1% of all method calls. With this view, you see the application's hotspots immediately:
These views are also available for other measures like web service calls, file accesses or DB calls, that is shown below:
This is the data that we need for our SQL statement analysis. The big problem is, that we can't easily see where all those SQL statements come from because we just see the isolated SQL statements.
And this is where our journey begins...
Reading XML into Neo4j
The input data
For further processing, we export such a call tree into a XML file (via the JProfiler GUI or the command line tool jpexport). If we export the data of the SQL hotspots (incl. the complete call tree) with JProfiler, we'll get a XML file like the following:
End of explanation
import pandas as pd
from py2neo import Graph
graph = Graph()
Explanation: For a full version of this file, see GitHub: https://github.com/feststelltaste/software-analytics/blob/master/notebooks/input/spring-petclinic/JDBC_Probe_Hot_Spots_jmeter_test.xml
This file consists of all the information that we've seen in the JProfiler GUI, but as XML elements and attributes. And here comes the great part: The content itself is graph-like because the XML elements are nested! So the <tt>&lt;tree&gt;</tt> element contains the <tt>&lt;hotspot&gt;</tt> elements that contain the <tt>&lt;node&gt;</tt> elements and so on. A case for a graph database like Neo4j!
But how do we get that XML file into our Neo4j database? jQAssistant to the rescue!
Scanning with jQAssistant
jQAssistant is a great and versatile tool for scanning various graph-like structured data into Neo4j (see my experiences with jQAssistant so far for more information). I just downloaded the version 1.1.3, added the binary to my <tt>PATH</tt> system variable and executed the following command (works for jQAssistant versions prior to 1.2.0, I haven't figured it out how to do it with newer versions yet):
<pre>
jqassistant scan -f xml:document::JDBC_Probe_Hot_Spots_jmeter_test.xml
</pre>
This will import the XML structure as a graph into the Neo4j graph database that is used by jQAssistant under the hood.
Exploring the data
So, if we want to have a quick look at the stored data, we can start jQAssistant's Neo4j embedded instance via 
<pre>
jqassistant server
</pre>
open <tt>http://localhost:7474</tt>, click in the overview menu at the label <tt>File</tt>, click on some nodes and you will see something like this:
It shows the content of the XML file from above as a graph in Neo4j:
* The pink node is the entry point &ndash; the XML file. 
* To the right, there is the first XML element <tt>&lt;tree&gt;</tt> in that file, connected by the <tt>HAS_ROOT_ELEMENT</tt> relationship. 
* The <tt>&lt;tree&gt;</tt> element has some attributes, connected by <tt>HAS_ATTRIBUTE</tt>. 
* From the <tt>&lt;tree&gt;</tt> element, there are multiple outgoing relationships with various <tt>&lt;hotspot&gt;</tt> nodes, containing some information about the executed SQLs in the referenced attributes. 
* The attributes that are connected to these elements contain the values that we need for our purpose later on. 
So, for example the attribute with the name <tt>value</tt> contains the executed SQL statement:
The attribute with the name <tt>count</tt> contains the number of executions of a SQL statement:
Each element or attribute is also labeled correspondingly with <tt>Element</tt> or <tt>Attribute</tt>.
Looking at real data
I want to show you the data from the database in a more nicer way. So, we load our main libraries and initialize the connection to Neo4j database by creating a <tt>Graph</tt> object (for more details on this have a look at this blog post)
End of explanation
query=
MATCH (e:Element)-[:HAS_ATTRIBUTE]->(a:Attribute)
WHERE a.value = "SELECT id, name FROM types ORDER BY name"
WITH e as node
MATCH (node)-[:HAS_ATTRIBUTE]->(all:Attribute)
RETURN all.name, all.value
pd.DataFrame(graph.run(query).data())
Explanation: We execute a simple query for one XML element and it's relationships to its attributes.
For example, if we want to display the data of this <tt>&lt;hotspot</tt> element
xml
&lt;hotspot 
    leaf="false" 
    value="SELECT id, name FROM types ORDER BY name" 
    time="78386" 
    count="107"&gt;
as graph, we get that information from all the attributes of an element (don't worry about the syntax of the following two Cypher statements. They are just there to show you the underlying data as an example).
End of explanation
query=
MATCH (e:Element)-[:HAS_ATTRIBUTE]->(a:Attribute)
WHERE id(e) = 12 //just select an arbitrary node
RETURN a.name, a.value
pd.DataFrame(graph.run(query).data())
Explanation: As seen in the picture with the huge graph above, each <tt>&lt;hotspot&gt;</tt> node refers to the further <tt>&lt;node&gt;</tt>s, that call the hotspots. In our case, these nodes are the methods in our application that are responsible for the executions of the  SQL statements.
If we list all attributes of such a node, we've got plenty of information of the callees of the hotspots. For example these nodes contain information about the method name (<tt>method</tt>) or the number of executed SQL statements (<tt>count</tt>):
End of explanation
query=
MATCH (n:Node)-[r:CALLS|CREATED_FROM|LEADS_TO]->()
DELETE r, n
RETURN COUNT(r), COUNT(n)
graph.run(query).data()
Explanation: Prepare performance analysis
Because it's a bit cumbersome to work at the abstraction level of the XML file, let's enrich this graph with a few better concepts for mining performance problems.
Clean up (optional)
Before executing the first statements, we clean up any preexisting data from previous queries. This is only necessary when you execute this notebook several times on the same data store (like me). It makes the results repeatable and thus more reproducible (a property we should generally strive for!).
End of explanation
query = 
MATCH (n:Element {name: "node"}),
    (n)-[:HAS_ATTRIBUTE]->(classAttribut:Attribute {name : "class"}),
    (n)-[:HAS_ATTRIBUTE]->(methodAttribut:Attribute {name : "methodName"}),
    (n)-[:HAS_ATTRIBUTE]->(countAttribut:Attribute {name : "count"}),
    (n)-[:HAS_ATTRIBUTE]->(timeAttribut:Attribute {name : "time"})
CREATE 
   (x:Node:Call {
        fqn: classAttribut.value,
        class: SPLIT(classAttribut.value,".")[-1], 
        method: methodAttribut.value,
        count: toFloat(countAttribut.value), 
        time: toFloat(timeAttribut.value)
        })-[r:CREATED_FROM]->(n)
RETURN COUNT(x), COUNT(r)
graph.run(query).data()
Explanation: Consolidate the data
We create some new nodes that contain all the information from the XML part of the graph that we need. We simply copy the values of some attributes to new <tt>Call</tt> nodes.
In our Cypher query, we first retrieve all <tt>&lt;node&gt;</tt> elements (identified by their "name" property) and some attributes that we need for our analysis. For each relevant information item, we create a variable to retrieve the information later on:
cypher
MATCH (n:Element {name: "node"}),
    (n)-[:HAS_ATTRIBUTE]-&gt;(classAttribut:Attribute {name : "class"}),
    (n)-[:HAS_ATTRIBUTE]-&gt;(methodAttribut:Attribute {name : "methodName"}),
    (n)-[:HAS_ATTRIBUTE]-&gt;(countAttribut:Attribute {name : "count"}),
    (n)-[:HAS_ATTRIBUTE]-&gt;(timeAttribut:Attribute {name : "time"})
For each <tt>&lt;node&gt;</tt> element we've found, we tag the nodes with the label <tt>Node</tt> to have a general marker for the JProfiler measurements (which is "node" by coincidence) and mark all nodes that contain information about the calling classes and methods with the label <tt>Call</tt>:
cypher
CREATE 
   (x:Node:Call {
We also copy the relevant information from the <tt>&lt;node&gt;</tt> element's attributes into the new nodes. We put the value of the class attribute (that consists of the Java package name and the class name) into the <tt>fqn</tt> (full qualified name) property and add just the name of the class in the <tt>class</tt> property (just for displaying reasons in the end). The rest is copied as well, including some type conversions for <tt>count</tt> and <tt>time</tt>:
cypher
        fqn: classAttribut.value,
        class: SPLIT(classAttribut.value,".")[-1], 
        method: methodAttribut.value,
        count: toFloat(countAttribut.value), 
        time: toFloat(timeAttribut.value)
        })
Additionally, we track the origin of the information by a <tt>CREATED_FROM</tt> relationship to connect the new nodes later on:
cypher
     -[r:CREATED_FROM]-&gt;(n)
So, the complete query looks like the following and will be executed against the Neo4j data store:
End of explanation
query = 
MATCH (n:Element { name: "hotspot"}),
    (n)-[:HAS_ATTRIBUTE]->(valueAttribut:Attribute {name : "value"}),
    (n)-[:HAS_ATTRIBUTE]->(countAttribut:Attribute {name : "count"}),
    (n)-[:HAS_ATTRIBUTE]->(timeAttribut:Attribute {name : "time"})
    
WHERE n.name = "hotspot"
CREATE 
   (x:Node:HotSpot {
        value: valueAttribut.value,
        count: toFloat(countAttribut.value), 
        time: toFloat(timeAttribut.value)
        })-[r:CREATED_FROM]->(n)
RETURN COUNT(x), COUNT(r)
graph.run(query).data()
Explanation: We do the same for the <tt>&lt;hotspot&gt;</tt> elements. Here, the attributes are a little bit different, because we are gathering data from the hotspots that contain information about the executed SQL statements:
End of explanation
query=
MATCH (outerNode:Node)-[:CREATED_FROM]->
      (outerElement:Element)-[:HAS_ELEMENT]->
      (innerElement:Element)<-[:CREATED_FROM]-(innerNode:Node)
CREATE (outerNode)<-[r:CALLS]-(innerNode)
RETURN COUNT(r)
graph.run(query).data()
Explanation: Now, we have many new nodes in our database that are aren't directly connected. E. g. a <tt>Call</tt> node looks like this:
So, let's connect them. How? We've saved that information with our <tt>CREATED_FROM</tt> relationship:
This information can be used to connect the <tt>Call</tt> nodes as well as the <tt>HotSpot</tt> nodes.
End of explanation
query=
MATCH (x)
WHERE x.fqn = "_jprofiler_annotation_class" AND x.method STARTS WITH "HTTP"
SET x:Request
RETURN COUNT(x)
graph.run(query).data()
Explanation: And there we have it! Just click in the Neo4j browser on the relationship CALLS and the you'll see our call tree from JProfiler as call graph in Neo4j, ready for root cause analysis!
Root Cause Analysis
Conceptual model
All the work before was just there to get a nice graph model that feels more natural. Now comes the analysis part: As mentioned in the introduction, we don't only want the hotspots that signal that something awkward happened, but also 
* the trigger in our application of the hotspot combined with 
* the information about the entry point (e. g. where in our application does the problem happen) and
* (optionally) the request that causes the problem (to be able to localize the problem)
Speaking in graph terms, we need some specific nodes of our call tree graph with the following information:
* <tt>HotSpot</tt>: The executed SQL statement aka the <tt>HotSpot</tt> node
* <tt>Trigger</tt>: The executor of the SQL statement in our application aka the <tt>Call</tt> node with the last class/method call that starts with our application's package name
* <tt>Entry</tt>: The first call of our own application code aka the <tt>Call</tt> node that starts also with our application's package name
* <tt>Request</tt>: The <tt>Call</tt> node with the information about the HTTP request (optional, but because JProfiler delivers this information as well, we use it here in this example)
These points in the call tree should give us enough information that we can determine where to look for improvements in our application. 
Challenges
There is one thing that is a little bit tricky to implement: It's to model what we see as "last" and "first" in Neo4j / Cypher. Because we are using the package name of a class to identify our own application, there are many <tt>Call</tt> nodes in one call graph part that have that package name. Neo4j would (rightly) return too many results (for us) because it would deliver one result for each match. And a match is also given when a <tt>Call</tt> node within our application matches the package name. So, how do we mark the first and last nodes of our application code? 
Well, take one step at a time. Before we are doing anything awkward, we are trying to store all the information that we know into the graph before executing our analysis. I always favor this approach instead of trying to find a solution with complicated cypher queries, where you'll probably mix up things easily.
Preparing the query
First, we can identify the request, that triggers the SQL statement, because we configured JProfiler to include that information in our call tree. We simply label them with the label <tt>Request</tt>.
Identifying all request
End of explanation
query=
MATCH 
    request_to_entry=shortestPath((request:Request)-[:CALLS*]->(entry:Call))
WHERE
    entry.fqn STARTS WITH "org.springframework.samples.petclinic"
AND 
    SINGLE(n IN NODES(request_to_entry) 
        WHERE exists(n.fqn) AND n.fqn STARTS WITH "org.springframework.samples.petclinic")
SET 
    entry:Entry
RETURN COUNT(entry)
graph.run(query).data()
Explanation: Identifying all entry points into the application
Next, we identify the entry points aka first nodes of our application. We can achieve this by first searching all the shortest paths between the already existing <tt>Request</tt> nodes and all the nodes that have our package name. From all these subgraphs, we take only the single subgraph that has only a single node with the package name of our application. This is the first node that occurs in the call graph when starting from the request (I somehow can feel that there is a more elegant way to do this. If so, please let me know!). We mark that nodes as <tt>Entry</tt> nodes.
End of explanation
query=
MATCH
    trigger_to_hotspot=shortestPath((trigger:Call)-[:CALLS*]->(hotspot:HotSpot))
WHERE 
    trigger.fqn STARTS WITH "org.springframework.samples.petclinic"
AND 
    SINGLE(n IN NODES(trigger_to_hotspot) 
        WHERE exists(n.fqn) AND n.fqn STARTS WITH "org.springframework.samples.petclinic")
SET 
    trigger:Trigger
RETURN count(trigger)
graph.run(query).data()
Explanation: With the same approach, we can mark all the calls that trigger the execution of the SQL statements with the label <tt>Trigger</tt>.
End of explanation
query=
MATCH
   (request:Request)-[:CALLS*]->
       (entry:Entry)-[:CALLS*]->
           (trigger:Trigger)-[:CALLS*]->
               (hotspot:HotSpot)
CREATE UNIQUE
    (request)-[leads1:LEADS_TO]->
        (entry)-[leads2:LEADS_TO]->
            (trigger)-[leads3:LEADS_TO]->(hotspot)
RETURN count(leads1), count(leads2), count(leads3)
graph.run(query).data()
Explanation: After marking all the relevant nodes, we connect them via a new relationshop <tt>LEADS_TO</tt> to enable more elegant queries later on.
End of explanation
query=
MATCH 
   (request:Request)-[:LEADS_TO]->
       (entry:Entry)-[:LEADS_TO]->
           (trigger:Trigger)-[:LEADS_TO]->
               (hotspot:HotSpot)
RETURN  
    request.method as request, 
    request.count as sql_count, 
    entry.class as entry_class,
    entry.method as entry_method,    
    trigger.class as trigger_class,    
    trigger.method as trigger_method,
    hotspot.value as sql, 
    hotspot.count as sql_count_sum
hotspots = pd.DataFrame(graph.run(query).data())
hotspots.head()
Explanation: Getting results
All the previous steps where needed to enable this simple query, that gives us the spots in the application, that lead eventually to the hotspots!
End of explanation
sqls_per_method = hotspots.groupby([
    'request',
    'entry_class', 
    'entry_method', 
    'trigger_class', 
    'trigger_method']).agg(
        {'sql_count' : 'sum',
         'request' : 'count'})
sqls_per_method
Explanation: The returned data consists of
* <tt>request</tt>: the name of the HTTP request 
* <tt>sql_count</tt>: the number of SQL statements caused by this HTTP request 
* <tt>entry_class</tt>: the class name of the entry point into our application
* <tt>entry_method</tt>: the method name of the entry point into our application 
* <tt>trigger_class</tt>: the class name of the exit point out of our application
* <tt>trigger_method</tt>: the method name of the exit point out of our application
* <tt>sql</tt>: the executed SQL statement
* <tt>sql_count_sum</tt>: the amount of all executed SQL statements of the same kind
A look at a subgraph
If we take a look at the Neo4j browser and execute the statement from above but returning the nodes, aka
cypher
MATCH 
   (request:Request)-[:LEADS_TO]-&gt;
       (entry:Entry)-[:LEADS_TO]-&gt;
           (trigger:Trigger)-[:LEADS_TO]-&gt;
               (hotspot:HotSpot)
RETURN  
    request, entry, trigger, hotspot
we get a nice overview of all our performance hotspots, e. g.
With this graphical view, it's easy to see the connection between the requests, our application code and the hotspots.
Albeit this view is nice for exploration, it's not actionable. So let's use Pandas to shape the data to knowledge!
In-depth analysis
First, we have a look which parts in the application trigger all the SQLs. We simply group some columns to get a more dense overview:
End of explanation
hotspots['table'] = hotspots['sql'].\
    str.upper().str.extract(
        r".*(FROM|INTO|UPDATE) ([\w\.]*)",
        expand=True)[1]
hotspots['table'].value_counts()
Explanation: We see immediately that we have an issue with the loading of the pet's owners via the <tt>OwnerController</tt>. Let's look at the problem from another perspective: What kind of data is loaded by whom from the tables. We simply chop the SQL and extract just the name of the database table (in fact, the regex is so simple that some of the tables weren't identified. But because these are special cases, we can ignore them):
End of explanation
grouped_by_entry_class_and_table = hotspots.groupby(['entry_class', 'table'])[['sql_count']].sum()
grouped_by_entry_class_and_table
Explanation: And group the hotspots accordingly.
End of explanation
from bokeh.charts import Donut, show, output_notebook
plot_data = grouped_by_entry_class_and_table.reset_index()
d = Donut(plot_data, label=['entry_class', 'table'], 
          values='sql_count',
          text_font_size='8pt',
          hover_text='sql_count'
         )
output_notebook()
show(d)
Explanation: Now we made the problem more obvious: The class <tt>OwnerController</tt> works heavily with the <tt>PETS</tt> table and the pet's <tt>TYPES</tt> table. Surely an error in our program.
Let's visualize the problem with a nice donut chart in Bokeh:
End of explanation
hotspots.groupby(['trigger_class', 'trigger_method'])[['sql_count']].sum().sort_values(
    by='sql_count', ascending=False).head(5)
Explanation: You could also have a look at the most problematic spot in the application by grouping the data by the class and the method that triggers the execution of the most SQL statements.
End of explanation
<END_TASK>