How to View SQL Queries from Your Application Code with Prefix
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.
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
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, 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 Premium also built in suggestions that specifically look for this behavior and will flag it for your attention.
Viewing 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.