This is a showcase review for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers.
Introduction
Optimizing SQL statements during development can save much in time and money by ensuring your application will run at peak performance without over-burdening the database. This article will describe how Visual Studio developers can tackle SQL Server performance issues without requiring the skill of a DBA.
Visual Studio Developer vs. SQL Server DBA
A simple fact that too often gets lost during development is that SQL statements can be optimized. Concepts like execution plans, logical reads, CPU time etc, can seem confusing and are left to the SQL Server DBA. So an application doesn't get SQL performance tuned until it is moved from development to production. DBAs are keenly aware of the cost of performance tuning applications SQL when it is in production. However, they've also recognized that in the past developers didn't have the deep knowledge required to performance tune SQL.
SQL Server Queries
You know how to write SELECT, INSERT, UPDATE, and DELETE statements. But did you know there could be literally thousands of ways to write a SQL statement and produce the exact same results? When you issue a query to SQL Server, it will process the statement before executing it. It will look for primary keys, indexes, sorts, and a variety of other factors. Taking these factors into account along with other statistics, SQL Server will produce an execution plan. The plan determines how the server will go about gathering the records your SQL is asking for. Variations in how a SQL statement is written will cause SQL Server to create a different execution plan. A different execution plan causes SQL Server to change how it gathers up your records. Each of these variations performs at different levels, some are slow and some are fast. Finding the fastest way to write a SQL statement is the key to peak performance in a database application. A tool like SQL Optimizer for Visual Studio from Quest Software understands every detail of an execution plan and related statistics and uses this information to optimize your SQL statements.
How it works
The original query is examined to see which transformation can be applied to it. A simple transformation that you may have considered in the past is whether to use IN, EXISTS, or chained ORs in a where clause. The SQL Optimizer engine knows dozens of such transforms, all varying in complexity. After applying the transforms it looks at the resultant statements, again examining them to see which transforms can be applied.
Figure 1.Transforming SQL to produces many rewrites.
After only a few iterations of this process, it yields many different ways of writing the same SQL statement. Using a mathematical model based on set theory, each rewrite of the original statement is guaranteed to be semantically equivalent. In other words, they all produce the same records when executed. Finally, using an artificial intelligence "Feedback-Searching" engine, SQL Optimizer for Visual Studio arrives at the best way to write your SQL statement. The result is optimized SQL.
Optimizing SQL right inside of Visual Studio
SQL Optimizer for Visual Studio extends Visual Studio to allow any developer to take advantage of this process, to painlessly optimize their SQL statements.
Once SQL Optimizer is installed, right click on any Solution or Project node to find all the SQL in your application. All of the source code associated with your application will be scanned for SQL statements.
Figure 2. Finding all the SQL in a Solution/Project.
Each SQL statement will be added to the SQL Explorer. You can see what statements were optimized and the date they were last optimized. The SQL Explorer also classifies your SQL statements so you can tell at a glance which statements are simple, complex, or even potentially problematic.
Figure 3. SQL Explorer in Visual Studio manages SQL in your project
A right click form the SQL Explorer will start the automatic optimization of any or all of the statements in the explorer. Upon completion you can review a report comparing the before and after versions of the statement showing just how much of a performance gain you made.
Figure 4. Review performance improvements after SQL is optimized.
Conclusion
Whether your SQL is embedded in VB.Net or in stored procedures, SQL Optimizer for Visual Studio can automate the optimization process. SQL Optimizer for Visual Studio supports SQL Server 2000 and 2005, Visual Studio 2003 & 2005.
