Hello, I am Sneha, and together with Peter Lim and Leilani Battle we worked on a survey to understand the tools and techniques developed for database query debugging
and recorded the experience of real users while they performed query debugging.
Before I go on to introduce our contributions of this research,
let me provide you with some motivation about why we should care about database query debugging.
It is estimated that every second a single person generates 1.7 megabytes of data in just one second. With the
current number of internet users, this results to about 2.5 quintillion bytes of data, BIG DATA each day.
To extract information from this enormous complex data it needs to be stored and processed. DBMSs like PostgreSQL
systematically allow to create, retrieve, update and manage such large data. DBMSs enable users to specify their desired data by issues queries on the datasets. However, in process to retrieve the appropriate desired data, users need to be able to write perfect errorless queries,
for which they need to debug their queries. Debugging queries is more than just fixing syntactical errors. Complicated errors like zero results or duplicate entries are thrown
by the DBMSs and in order to deal with such errors, the user’s need to have an intuitive understanding of both the structure of the queries they are writing and the DBMSs.
Now that you are aware of why we should care about database query debugging and the challenges it faces, let me inform you of ur contributions. We
wanted to study the works done in effort to alleviate database query debugging and know how real database users are benefitted from these techniques.
In our research, we first performed a literature review across multiple research areas of databases, visualization and HCI to find a shocking lack of work on debugging of complex database queries.
Second, to better understand how the techniques in literature are used by database users in the real world, we conducted a user study. We found that participants
rarely used the debugging techniques proposed in literature and had hardly heard of any database debugging tools.
Further, we reached out to 6 database tool designers to understand the industry perspective on query debugging and found that the industry is aware
of database query debugging to be an issue, but has other pressing needs like solving issues of query optimization. For the sake of time, I
will be talking about only our findings of the literature review and user study. For more surprising findings of the database tool designers please read our paper.
Let me walk you through the literature review
We selected our review papers from across multiple research areas, such as databases, visualization and HCI as work of query debugging spans over all
these areas. Our selection process was framed around the review topic of algorithms, interfaces and tools designed to support the debugging of database queries.
Other related works were found by targeted search on related topics like understanding query behaviour, understanding user debugging behaviour, visualizing queries, etc.
We reviewed a total of 112 papers of past 25 years to understand strategies that users employ to reason about and debug queries.
The first key takeaways of our literature review was that even though research across databases, visualization and HCI touches on how users interact with with analyze queries
it does not consider the challenge of debugging complex SQL queries. Moreover, we saw a dearth of information about user’s database debugging strategies and tools across the 3 research areas.
In another finding we see that, query debugging features seem to be more effective if adopted from multiple research areas, for example, visualizations of program behavior,
intuitive interaction widgets and efficient database debugging strategies.Further, we observed that even though proposed debugging techniques have been around for over a decade,
none of them exist in the current commercial debugging tools, suggesting that there is wide gap between the literature and existing commercial debugging tools.
Apart from works specific to debugging database queries, we found alot of other works not performing SQL debugging but being tangentially related to SQL debugging.
For example, there have been numerous interview studies to understand data analysis process of analysts, long-term projects logging queries issued by user’s,
written, spoken and visual alternatives to SQL altogether or data cleaning techniques. All of these may
make querying simpler for the user, but does not improvise user’s querying or help user’s debug their queries per se.
We categorize works specific to database query debugging in 4 categories: first,
performing logical analysis where only the structure of the query is analyzed without executing the query, second performing execution analysis of the query to know the intermediate results
of the parts of the query, third, performing both logical and execution analysis and lastly the commercial debugging tools we know off.
QueryViz is an example of the logical analysis category where nodes and link diagrams are used to demonstrate relationships between operators. Even
though such techniques can show the structure of the query to the user, they cannot be used if users want to gain deeper understandings of how a query
affects the underlying data. Executing the query allows to view the provenance of the query and inturn be able to see the intermediate results in the form of provenance graphs.
An observation though was that database provenance unlike analytic provenance focuses on history and source of program results and not on user interactions and annotations.
The third and most promising strategy of analyzing queries used both the logical and execution structure of erroneous queries.
These techniques aimed to answer both the “why” and “why not” questions about the query, that is why a certain record is seen
in the results of the query and why a certain record is not seen in the result. These techniques provide the user
with partial provenance information, but poses implementation challenges making it difficult for commercial adoption.
Lastly, we came across many commercial database debugging tools, though all of them focused on debugging the queries from the optimization
and performance perspective and hardly helped the users to debug their queries. Furthermore, none of these tools seemed to implement the state-of-the-art in query or program debugging research.
To better understand how techniques in literature translate into the real world, we conducted interviews with 20 database users,
ranging from students taking database courses to full-time industry analysts.
We asked these database users to first share the debugging strategies and tools they use on a daily basis. Second, we asked
them to inform us of database debuggers they knew, how they used them or any other techniques they used to verify results and be confident of their queries.
Then, we wanted to know about the strengths and weaknesses of the database debugging tools and techniques they have been using.
Lastly, we asked the participants to share examples of visual aids they used to perform debugging and how these visual aids benefitted them.
The first takeaway from the user study was that regardless of the ton of research across multiple areas and many
commercial database debugging tools developed, 95% participants performed debugging of queries manually by hand.
Moreover, 17 out of 20 participants were unaware of any kind of database debuggers or formal debugging strategies.
Of the 3 participants who were aware of database debuggers, only one participant actually used it. This participant, who was
an expert additionally expressed that the debugger was very daunting for her.
Further, we observed that all 20 participants used the nested debugging strategy.
In this technique, participants break a complex query into simpler SQL subqueries and debug the simplest components first.
Once the first component returns expected results, then the user debugs the next simpler component and so on.
As quoted by a participant, this technique is also used to perform unit testing in form of small, targeted inputs.
For example, consider this query where the user wants to view the names of all items whose quantity is greater then 100.
The participants would tend to simplify the query into sequence of steps as shown and perform debugging of each of these.
Let me walk you through the nested debugging strategy.
In the first step, the participants broke the main query into 2 simpler ones and performed the second inner query first to see its results.
They first viewed all the productId’s and quantities in the orderItem table to have an estimate of the number of
rows they should expect in the result and then filtered the table having quantities more than 100.
Once, the participants were sure about the results of the inner subquery, they focused on the
ID’s in this intermediate result and then performed the complete query to gauge that they saw similar IDs in the final result.
Finally, they got the needed ProductNames. This nested debugging strategy was seen in the provenance-based debugging strategies in literature,
pointing that there is alignment of techniques used by real users and the solutions proposed in literature.
Although, these techniques are not adopted in commercial tools and if they are, the users are not using them.
Another observation made was that 15 out of 20 participants used some form of visual aids while performing debugging
and shared that having visual aids would benefit them largely to fasten their process. Examples of the visual
aids that participants pointed to were flowcharts to simplify their nested debugging as seen here or having annotations
on the queries to view the database schema. These are very simple features that can be easily incorporated in current tools.
Also, a participant who used sophisticated DBMSs did mention that the system had some features of visualising
data such as bar charts but it only visualized upto 10,000 rows of data which was not very helpful.
On the basis of our findings of the literature review and the study with database users, we suggest
some design recommendations for improving debugging tools in the future.
First, we suggest new debugging tools to be built such that they can be integrated into existing tools like
command-line tools or DBMSs IDEs. Also, all of the commercial tools mentioned by our participants of the user study are
very similar and hence it will be easier to establish collaborations in the industry to develop integrated tools that support multiple DBMSs.
From our user study we also found that participants stressed on lack of training to use debugging tools.
Our second recommendation focuses on developing educational modules to fill this gap. An example feature that
can be incorporated is hover-triggered pop-ups to explain database specific terms when users interact with queries.
A third recommendation we emphasize is to have tools built with automatic features that support the nested debugging strategy
that were observed across all 20 of our participants. Many participants hand-drew simple visual aids as part of this nested
debugging approach and such visualizations can be prioritized as a starting point for improving query debugging features.
Further, annotation features like highlighting and writing text notes could help users track the query components
they have already tested. Other possible annotations could be the ability to view the schema of a table in
order to view metadata immediately and reduce the cognitive burden of remembering details.
In summary, DBMSs are important to manage and process large complex datasets to gain valuable information.
The primary way to converse with these DBMSs is to issue queries and we find that even the expert users struggle
to make sense of and debug SQL queries.
To understand how the issue of query debugging is approached in the research community we performed a literature review
of works across 3 academic communities: databases, visualization and HCI. Having found very little work specifically
on user query debugging, we conducted a user study with 20 database users to observe how users debug their queries
and saw very high dependence on informal self-learnt debugging strategies. Lastly, to better understand the
industry perspective, we reached out to 6 database tool designers.
Finally, our major takeaways from our research, we found that all participants, whether experts or
new to SQL, all seemed to favour the simple nested query debugging strategy. However, the existing literature
and commercial tools seem oblivious to nested debugging leading to users learning their own debugging techniques
informally. Moreover, as no debugging techniques are taught in schools or workplaces, we also see a lack of infrastructure
for learning how to debug queries effectively.
Thank you.