In any database application, there is a fundamental decision as to whether to place the SQL statements in the Data Access Layer or in the Database Layer. As you might expect, there are advantages and disadvantages to both.
If SQL statements are placed in the Data Access Layer, they are embedded in the application’s source code itself. In this style, the burden of writing and maintaining the SQL typically falls to the application developers, because they are the ones writing the application source code and the best able to write and insert the SQL statements at the appropriate places in their code. Developers can tightly integrate the SQL and the query results with their source code. However, doing this requires application developers to be experts in two languages – their primary development language – C#, Visual Basic, or Java – as well as SQL. This is oftentimes difficult for most developers.
If SQL statements are placed in the Database Layer, they are stored in stored procedures resident in the database itself. This not only moves the SQL to a different layer in an N-tier architecture, but in most cases moves it to a physically separate server machine. In this style, the burden of writing and maintaining the SQL typically falls to the database administrator (DBA) or an individual developer whose skill and expertise is writing SQL. Developers must rely on the DBA to implement part of their application (the SQL) and must interface their code with the stored procedures created by the DBA.
However, centralizing the SQL in the Database Layer allows a certain amount of application logic to be maintained in a central location and shared across multiple application programs. Moreover, many developers and DBA’s consider implementing queries in stored procedures as a “best practice”. Consolidating most or all of an application’s SQL in stored procedures makes it easier to review, modify and tune individual queries without having to dig into application code.
Finally there is the issue of execution performance. Generally, more complex SQL statements can be placed in stored procedures and executed locally on the database server, reducing the number of round-trips between the application program and the database. While bandwidth and latency aren’t issues in many behind-the-firewall applications, this can present a problem in web services environments where the application programs aren’t physically proximate to the database executing the queries.
Iron Speed Designer automatically creates all the SQL statements required for each database-connected form, web page, table, and report in your application, as well as all the database access logic and storage management code. Because Iron Speed Designer creates SQL based on your database table and user interface design you do not need to know any SQL to build applications in Iron Speed Designer. A variety of data filters and navigation components provide your end-users with additional viewing and reporting flexibility.
Most of the SQL built with Iron Speed Designer is placed in stored procedures, enhancing run-time performance because the queries are stored and executed directly from the database. This also minimizes the number of round-trips between the application program and the database, further enhancing performance.
Specifically:
All table and record retrieval, record insertion, record update and record deletion SQL queries are placed in stored procedures.
Table join queries, however, are in placed in application code functions.
All dynamic queries used by QuickSelector, Charts and aggregate controls do not use stored procedures and use inline code instead.
The stored procedures for creating, updating, and deleting records accept field values as parameters, such as record ID's and filter strings, and then execute the appropriate INSERT, UPDATE, or DELETE statements in the stored procedures.
Transact-SQL Stored Procedures for Microsoft SQL Server