View SQL Queries From Your Code With Prefix

Have you heard the saying “you can’t manage what you can’t measure?” The phrase is often attributed to management thinker Peter Drucker, and less often to other authors as well. It can also slightly vary in its wording, depending on the source. At the end of the day, the exact wording of the sayer and who said it first doesn’t matter as much as whether it’s true or not. We’re here to tell you that yes, it’s true for a lot of things in life. Database performance is one of those things.

In today’s post we’re going to talk about database performance, but not from an abstract point of view. On the contrary: we’re going to tell you about Prefix and show you some of the things it can do for you when it comes to tracking SQL queries from your code.

Let’s get started.

Prefix: What It Is And What Can It Do For You

Prefix enables developers to easily see what their code is doing as they write and test their code. Including SQL queries, HTTP calls, errors, logs, and much more. This makes Prefix really handy for viewing SQL queries your code is using.

Prefix is free! Learn more about Prefix

After installing Prefix, it will automatically track all SQL calls. No code changes are needed, unlike some other solutions.

Prefix can show you several important details:

• Total amount of time a query takes
• How much time was just downloading the results (vs. server execution time)
• Number of records affected
• Server and database connected to
• View complete SQL queries and parameters

You can view these details for every single SQL query in your code. There is a little copy icon that you can use if you want to quickly copy the SQL and go work on tuning it in your SQL management tool.

In this simple example, the complete query takes 126ms, but you can see that 111ms of that was how long it took to download the 295 records.

Identify SQL parameter values

Even if you are using stored procedures, you need to see the input parameters to help debug your applications. Prefix can capture these and show them to you automatically.

Common ORMs Abstract SQL Away from Us & How to View LINQ Generated SQL Queries

Almost every application uses some form of ORM (object-relational mapping) tool these days. These are tools like Entity Framework, Hibernate, Dapper, etc. They make it really easy to query databases and do basic CRUD type functions without manually hand-typing all of the SQL code. These frameworks instead auto-generate the code, which can create some very bizarre SQL statements.

Since Prefix can track every SQL statement, including LINQ generated SQL, it can help you understand what your ORM is doing to help decide if perhaps you should make the SQL query a stored procedure instead.

Read More: Fun blog post about EF & ORM Includes

This is a fairly simple SQL statement but you can see how the ORM makes pretty weird SQL syntax by using a CASE statement and sub selects.

Solved: Identifying the Dreaded N+1 Problem

One common problem that developers run in to is the dreaded N+1 pattern. What seems like simple code all of a sudden is doing a database query for every item in a list. I have seen examples where this causes hundreds of database queries by accident every time the web page is loaded. #angryDBA

Prefix makes it easy to find these problems by simply looking for requests with a very high number of SQL queries. Prefix also built in suggestions that specifically look for this behavior and will flag it for your attention.

Can You View SQL Queries without Prefix?

Warning: Don’t try this at work. Just go get Prefix – it’s free!

If you don’t have a tool like Prefix, hunting down your SQL statements can be a tedious and almost impossible task. The most common solution would be writing them to a log file or debug console to see them. Although, having to modify your code in every place you do a SQL statement is no fun and could take forever. Also, you likely can’t even get the SQL statements being generated by your ORM.

With Prefix you don’t have to change your code or do anything. It just works.

SQL Queries: You Can’t Improve Them Without Measuring Them

Managing and improving the performance of an application is no easy task, especially when we’re talking about web apps.

If your web application is struggling with performance issues, there are lots of possible culprits: unoptimized images, too many HTTP requests, non-minified CSS and Javascript, and the list goes on. However, the frontend technologies shouldn’t be the only ones to blame. Often, the worst offender is no other than the good old database.

When it comes to improving database performance, one of the most true and tested things you can do is to improve your SQL queries. Two queries that produce the exact same results can vary dramatically in their performances.

What to make of this? Simple: query optimization is something you can’t afford to live without if you care about your application’s performance. But in order to improve your queries, you first have to know which queries need optimizing. You can’t improve if you don’t measure, so measure you must. That’s why you need Prefix.

Today’s post explained what Prefix is, and offered a brief but comprehensive overview of the insights it can give you about your code.
Prefix works with .NET, Java, PHP, Node.js, Ruby, and Python for Mac and Windows users. Download Prefix and give it a try today.

Author

mwatson

More articles by mwatson