Wednesday, November 24, 2010

Static Vs Dynamic Vs Embedded Vs Entity SQL

Structured Query Language (SQL) is a way to communicate with a relational database that lets you define, query, modify, and control the data. Using SQL syntax, you can construct a statement that extracts records according to criteria you specify (I know, you know that). However, there are certain flavors of SQL, which you should be aware of like Static SQL, Dynamic SQL and Embedded SQL, to better understand and apply them, as and when required.

With .NET you would also end up using Entity SQL, which when used with Entities (as in the Entity Framework), could eventually generate dynamic SQL, executing and returning results as entities.

Static SQL: The most commonly used type of SQL, static SQL, as its name implies allows you to fulfill your data access requirements, which might be known to you at design time of your application. Having static SQL queries can lead to better performance, since the queries are not required to be parsed every time before being executed.

Dynamic SQL: There are instances of applications where the data to be processed cannot be determined at the design time of the application. A typical example would be processing of a spreadsheet, which in turn can contain variable number of columns, and the program is needed to process and store the data into the database. Typically, you would generate a string value with the columns and send across the database to process it.

The following points are to be considered:

  • Since the database engine doesn’t have an access plan, it would parse, compile and generate an access plan. Hence dynamic SQL is slower than static SQL.
  • EXECUTE IMMEDIATE statement can be used to execute the dynamic SQL statement, which in turn passes the SQL to database for compilation and execution.
  • The problem with EXECUTE IMMEDIATE is that the database would be executing each of the 5 steps of processing SQL statement and the overhead could be significant for multiple dynamic statements executed at once.
  • Dynamic SQL offers optimization via prepared execution whereby all the host variables are replaced by a question mark (?), known as parameter marker, which can be substituted later with the host value. The PREPARE statement is used by the program to pass the SQL statement to the DBMS for parsing, validation and optimizing the statement. The EXECUTE statement is used instead of the EXECUTE IMMEDIATE statement, and the parameter values are supplied via a special data structure called the SQLDA or SQL Data Area. By having the execute statement and supplying different parameter values, optimization is gained over the EXECUTE IMMEDIATE statement.
  • It is to be noted that PREPARED execution is still slower than static SQL since with static SQL the first 4 steps of processing takes place at compile time, whereas with PREPARED execution, they are still executed at runtime, however, only for the first time.

Embedded SQL: Because SQL does not use variables and control-of-flow statements, it is often used as a database sublanguage that can be added to a program written in a conventional programming language, such as C or COBOL. This is a central idea of embedded SQL: placing SQL statements in a program written in a host programming language.

The following points are to be considered:

  • Embedded SQL is processed by a special SQL precompiler.
  • Host language variables can be used in place of the constants in the SQL statements.
  • To optimize the SQL which returns a single row as a result, singleton SELECT statements are used.
  • Statements that return and require multiple rows are processed using CURSORS.
  • Errors while executing Embedded SQL are reported to the application programs via SQL Communications Area or SQLCA.

Disclaimer: The following does not directly relate to databases and are features specific to the Microsoft .NET Framework.

Typically you would be developing an application to access the data stored in the databases. Relational databases provide specific added advantages and are widely used in the general business applications domain. Microsoft .NET 3.5 SP1 introduced Entity Framework, which in turn allows you to model your database objects as entities, which may or may not be inter related, and provides an abstracted way to process them via Entity SQL or LINQ to Entities.

Entity SQL: Entity SQL is a SQL-like language that enables you to query conceptual models in the Entity Framework. Conceptual models represent data as entities and relationships, and Entity SQL allows you to query those entities and relationships in a format that is familiar to those who have used SQL.

The following points are to be considered:

  • Entity SQL supports conceptual model features like relationships and inheritance.
  • Collections are treated as first class citizens in Entity SQL, hence set operators like UNION, INTERSECT and EXCEPT, work on collections.
  • Everything in Entity SQL is an expression which in turn enables it to be more composable than Transact SQL (the SQL language of Microsoft SQL Server).
  • Entity SQL doesn’t support the * construct, and hence the count(*) statement is invalid, instead use count(0).
  • Entity SQL supports a subset of Transact-SQL's built in functions and operators and does not provide support for DDL in the current version.

LINQ to Entities provides Language-Integrated Query (LINQ) support that enables developers to write queries against the Entity Framework conceptual model using Visual Basic or Visual C#. Queries against the Entity Framework are represented by command tree queries, which execute against the object context. LINQ to Entities converts Language-Integrated Queries (LINQ) queries to command tree queries, executes the queries against the Entity Framework, and returns objects that can be used by both the Entity Framework and LINQ. MSDN

The above mentioned constitutes literally all the typical types of SQL related concepts that you might encounter in your general database (relational) programming tasks day-in-day-out or in any developer centric interview session.

As always, you are welcome to share any ideas or thoughts about any other flavor of SQL, which you might have encountered.

Prepare > Execute!

1 comment:

  1. Guess you have missed out the point that entity based queries work only on in-memory objects.
    Nice informative blog entry otherwise.