SQL tools – 8 ways you can’t live (or work) without them

Why are SQL tools important? Because they help you take all that SQL expertise knowledge that you've cultivated and easily apply it to your company’s competitive advantage.

 

SQL tools are at the heart of the database management tools we described recently. Database developers use them for the blocking-and-tackling basics:

• Adding and editing SQL, schemas and scripts
• Collaborating on code review
• Creating and running unit tests
• Tuning SQL
• Profiling code for bottlenecks
• Comparing and testing SQL alternatives
• Integrating with version control systems
• Automating all of the above, where possible

Sooner or later, almost every database administrator (DBA) or database developer like you sits down at the keyboard to deal with some common situations. In light of those situations, let’s look at 8 ways you can’t live (or work) without SQL tools.

 

1. “I need to wrap my head around the environment I’m in.”

 

Browsing schemas is a top-level function for a SQL tool. A schema browser allows you to view, add and modify objects in the database and inspect detailed information about an object you select. The detailed information for a table, for example, includes elements like its subpartitions, columns, indexes, data and grants.

When you face an existing database for the first time, you’ll probably want to see how its tables relate to one another. That’s what an entity relationship diagram (ERD) is for. The ERD makes it easy to see tables joined through foreign keys and to retrieve information about database objects including the makeup of the object, the data it contains, the foreign keys and any scripts. If need be, it allows you to add other tables connected through foreign keys, along with other database objects.

Because the ERD brings so much information about the database right up to the surface, some SQL tools enable the short hop from there to building SQL queries based on table columns. Or, to test for referential integrity (i.e., to ensure that foreign key values reference a valid primary key in the parent table), the tools generate data in all the tables. In both cases, the SQL tools take advantage of immediate data to save you the trouble of writing your own SQL statements.

Another reason to wrap your head around the database environment is to avoid making changes to the wrong objects or code, like stored procedures. To understand the potential impact those changes may have on other objects in the schema, you’ll want to examine code relationships in a code roadmap. The roadmap lets you see how, for example, a function you’ve invoked makes calls to other functions, which may in turn have references to still other tables or functions. When you can see all the interdependency in your code, you exercise more care in the changes you make and you produce fewer errors.

 

2. “I want to create test cases for my code.”

 

As soon as you’ve created a new function, SQL tools let you create test cases for it. Each test case requires an input value, like an expression, a literal value or an error message, and its corresponding expected outcome.

When you run a test case and something fails, you will receive notification with a pointer that indicates where in your code the failure arose. The failure could be due to incorrectly written code, or to something not properly defined within the test case. If you receive a message about a runtime error, then you'll need to debug the error (see #4 below).

 

3. “How do we integrate this with our version control system (VCS)?”

 

Since SQL tools are for working with code, and most code lives in a VCS, it makes sense that the tools should integrate with the VCS.

That integration includes:

• Offering a view of all the files in the VCS that contain database code
• Displaying version history of individual files
• Comparing file versions
• Automatically checking a file out to you when you edit it
• Checking it back in again when you’ve finished editing

The goals of VCS integration are easy access to code and greater assurance of code integrity.

 

4. “How long does each line of code take to execute?”

 

SQL tools combine debugging with profiling. You insert break points where you know that the code is causing a runtime error, then execute the SQL in debug mode. The SQL tool steps through your code line by line, showing how your variables have been resolved as they’ve gone through the database.

At the same time, you can enable profiling to record the execution time of each line of code, then store the data in a repository for analysis. Profiling lets you see, in both tabular and graphical formats, how long each line of code took to execute in a given program every time it ran. Offending lines represent potential bottlenecks to resolve in your code.

 

5. “I want to quickly optimize my SQL and see alternatives.”

 

You don’t always have the time for full SQL optimization: indexes, plan baselines, batch optimization, impact analysis and the like. When you’re editing, it’s convenient to let the SQL tool quickly auto-optimize by offering potentially more-efficient rewrites of your SQL statements.

Many statements use local variables likep_price or p_ID, which won’t be defined when the statements are out of context during optimization. The SQL tool temporarily replaces local variables with bind variables, then prompts you for a bind variable value when the SQL statement is executed during tuning. It also prompts for information about the application that is using the function. Is it a general OLTP application or a data warehouse application? Do you want to use any optimizer hints or do you simply want to have it ready for production? Are there so many SQL statements that you want to generate the rewrites without executing each one?

You can generate an execution plan, but it doesn’t usually tell you how to properly address the problem. The optimization engine in SQL tools automatically generates rewrites from your original SQL statement and continuously looks for better alternatives, each of which has a unique execution plan. It offers alternatives, gives each one a number and shows elapsed time, CPU usage, I/O or plan cost, whichever metric is most important for your optimization. You can compare the original and the alternative side by side to see how the new script differs from the original.

The alternative generates the same amount of data when it runs; however, it runs faster and with a different execution plan.

 

6. “I want to optimize multiple SQL statements in a batch.”

 

Now suppose you want to apply that auto-optimization across an entire application. SQL tools allow you to select a unit of application code files — SQL code, for example — and tune all of the SQL inside of that code unit. With batch mode you can run the optimizer overnight and tune all the code in the application.

SQL scanning is a way to specify any code, whether inside or outside the database, and scan it for embedded SQL statements. It shows you which SQL statements are in greatest need of tuning and lets you submit them for optimization in batch mode.

 

7. “We need to ensure that our code conforms with our internal standards.”

 

The code analysis feature in SQL tools enables developers to check their code against a set of standards defined as rules. Examples include incorrect JOIN syntax and numeric variables without a specified precision. Following the check, any violations are exposed as wavy lines or colored text in the code, with an explanation of the violation so you can correct it.

Team leads and development managers with responsibility for hundreds or thousands of programs can use a code analysis dashboard. Opening up code from either the database, the file systems or version control, they can load all the programs in and run a check against whichever rules or standards they want to apply. The dashboard reveals, program by program, where the coding violations are. From there, the managers can examine each program for violations, generate a report based on the results or automate the code analysis to run regularly.

 

8. “How do I know my code will scale well in production?”

 

One way or another, most code needs to meet a service-level agreement (SLA) or performance threshold. That means testing it to ensure it will scale up to production-type user loads. What if you could select a piece of code and immediately test it for performance?

SQL tools extend to benchmark testing based on variables like types of latencies, a given number of concurrent users and number of times to execute the SQL in the code. The SQL tool launches a program to ramp up a scalability job that simulates the volume in the context of industry-standard benchmark tests. It then generates a profile detailing the workload as the number of users increases.

When you see the transactions per second and transaction times under load, you can decide whether the code will meet expectations for the production environment. If not, then you can consider performing more SQL optimization in the application.

So that’s why SQL tools are important. They’re important for a lot more than just adding and editing SQL. And you can depend on them for a lot more than just auto-completing as you type in an editing window. As we’ve described, look for capabilities around automation, testing, collaboration, code review and SQL optimization/tuning.

If you’re a developer trying to compare features and learn more about the landscape of SQL tools, have a look at our in-depth tutorial.

 

Attend a Free webcast: Boost Database Performance with SQL Optimizer in Toad for Oracle Xpert Edition In this session of Ask Toad on September 3, Gary Jerep, software solutions consultant, will demonstrate how the SQL Optimizer in Toad for Oracle Xpert edition works to improve your code. You will learn: Why you should use SQL Optimizer Optimization steps and results How to scan existing code for performance problems Capturing running SQL and identifying issues Join us to learn how the SQL Optimizer will ensure poorly-written queries don’t affect the performance of your Oracle databases.