Databases Reference
In-Depth Information
write. But more importantly, conditional logic in queries and stored procedures defeats the
query optimization logic the database engines use to improve performance. When a query
designed to handle all 31 combinations of 5 parameters is executed for the first time, the
probability of having the same combination of parameter values next can be as low as 3%,
so the execution plan cached for the first query is likely to be invalid next time.
FIGURE 5.1
Customer Search page.
A better alternative to placing conditional logic in a static query code is to create the
query dynamically, based on the specific combination of the parameter values. This
approach leads to smaller, more focused queries that can be processed by the query opti-
mizer effectively. Although dynamic queries can be created in the database and applica-
tion code, this approach could open the application to SQL injection attacks if the
developers are not careful to use parameters and not string concatenation to build the
queries. This and the long entrenched assumption that dynamic SQL queries are slower
than stored procedures are the reasons why database administrators often resist use of
dynamic queries in internal applications.
Implementing search functionality became a lot easier with the advent of LINQ
(Language Integrated Query) and LINQ-based data access frameworks, such as Entity
Framework and LINQ to SQL. LINQ enables using SQL-like queries directly in C# and
Visual Basic code:
using (NorthwindEntities context = new NorthwindEntities())
{
IQueryable<Customer> query = from c in context.Customers
where c.City == “London”
select c;
