Programmer Land

What is 3-tier application?

noreply@blogger.com (Kevin Wong) — Tue, 27 May 2008 17:09:00 +0000

A 3-tier application is an application program that is organized into three major parts, each of which is distributed to a different place or places in a network. The three parts are:

The workstation or presentation interface
The business logic
The database and programming related to managing it

In a typical 3-tier application, the application user's workstation contains the programming that provides the graphical user interface (GUI) and application-specific entry forms or interactive windows. (Some data that is local or unique for the workstation user is also kept on the local hard disk.)

Business logic is located on a local area network (LAN) server or other shared computer. The business logic acts as the server for client requests from workstations. In turn, it determines what data is needed (and where it is located) and acts as a client in relation to a third tier of programming that might be located on a mainframe computer.

The third tier includes the database and a program to manage read and write access to it. While the organization of an application can be more complicated than this, the 3-tier view is a convenient way to think about the parts in a large-scale program.

A 3-tier application uses the client/server computing model. With three tiers or parts, each part can be developed concurrently by different team of programmers coding in different languages from the other tier developers. Because the programming for a tier can be changed or relocated without affecting the other tiers, the 3-tier model makes it easier for an enterprise or software packager to continually evolve an application as new needs and opportunities arise. Existing applications or critical parts can be permanently or temporarily retained and encapsulated within the new tier of which it becomes a component.

Stored Procedures Optimization Tips

noreply@blogger.com (Kevin Wong) — Tue, 27 May 2008 17:04:00 +0000

Use stored procedures instead of heavy-duty queries.
This can reduce network traffic, because your client will send to server only stored procedure name (perhaps with some parameters) instead of large heavy-duty queries text. Stored procedures can be used to enhance security and conceal underlying data objects also. For example, you can give the users permission to execute the stored procedure to work with the restricted set of the columns and data.

*****

Include the SET NOCOUNT ON statement into your stored procedures to stop the message indicating the number of rows affected by a Transact-SQL statement.
This can reduce network traffic, because your client will not receive the message indicating the number of rows affected by a Transact-SQL statement.

*****

Call stored procedure using its fully qualified name.
The complete name of an object consists of four identifiers: the server name, database name, owner name, and object name. An object name that specifies all four parts is known as a fully qualified name. Using fully qualified names eliminates any confusion about which stored procedure you want to run and can boost performance because SQL Server has a better chance to reuse the stored procedures execution plans if they were executed using fully qualified names.

*****

Consider returning the integer value as an RETURN statement instead of an integer value as part of a recordset.
The RETURN statement exits unconditionally from a stored procedure, so the statements following RETURN are not executed. Though the RETURN statement is generally used for error checking, you can use this statement to return an integer value for any other reason. Using RETURN statement can boost performance because SQL Server will not create a recordset.

*****

Don't use the prefix "sp_" in the stored procedure name if you need to create a stored procedure to run in a database other than the master database.
The prefix "sp_" is used in the system stored procedures names. Microsoft does not recommend to use the prefix "sp_" in the user-created stored procedure name, because SQL Server always looks for a stored procedure beginning with "sp_" in the following order: the master database, the stored procedure based on the fully qualified name provided, the stored procedure using dbo as the owner, if one is not specified. So, when you have the stored procedure with the prefix "sp_" in the database other than master, the master database is always checked first, and if the user-created stored procedure has the same name as a system stored procedure, the user-created stored procedure will never be executed.

*****

Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve readability of your code when there are many parameters are used. When you use the sp_executesql stored procedure to executes a Transact-SQL statements that will be reused many times, the SQL Server query optimizer will reuse the execution plan it generates for the first execution when the change in parameter values to the statement is the only variation.

*****

Use sp_executesql stored procedure instead of temporary stored procedures.
Microsoft recommends to use the temporary stored procedures when connecting to earlier versions of SQL Server that do not support the reuse of execution plans. Applications connecting to SQL Server 7.0 or SQL Server 2000 should use the sp_executesql system stored procedure instead of temporary stored procedures to have a better chance to reuse the execution plans.

*****

If you have a very large stored procedure, try to break down this stored procedure into several sub-procedures, and call them from a controlling stored procedure.
The stored procedure will be recompiled when any structural changes were made to a table or view referenced by the stored procedure (for example, ALTER TABLE statement), or when a large number of INSERTS, UPDATES or DELETES are made to a table referenced by a stored procedure. So, if you break down a very large stored procedure into several sub-procedures, you get chance that only a single sub-procedure will be recompiled, but other sub-procedures will not.

*****

Try to avoid using temporary tables inside your stored procedure.
Using temporary tables inside stored procedure reduces the chance to reuse the execution plan.

*****

Try to avoid using DDL (Data Definition Language) statements inside your stored procedure.
Using DDL statements inside stored procedure reduces the chance to reuse the execution plan.

*****

Add the WITH RECOMPILE option to the CREATE PROCEDURE statement if you know that your query will vary each time it is run from the stored procedure.
The WITH RECOMPILE option prevents reusing the stored procedure execution plan, so SQL Server does not cache a plan for this procedure and the procedure is recompiled at run time. Using the WITH RECOMPILE option can boost performance if your query will vary each time it is run from the stored procedure because in this case the wrong execution plan will not be used.

*****

Use SQL Server Profiler to determine which stored procedures has been recompiled too often.
To check the stored procedure has been recompiled, run SQL Server Profiler and choose to trace the event in the "Stored Procedures" category called "SP:Recompile". You can also trace the event "SP:StmtStarting" to see at what point in the procedure it is being recompiled. When you identify these stored procedures, you can take some correction actions to reduce or eliminate the excessive recompilations.

*****

VB .NET New Logical Operators AndAlso and OrElse

noreply@blogger.com (Kevin Wong) — Sun, 25 May 2008 15:11:00 +0000

VB .NET features two new logical operators that help make your programming ... well ... more logical. The new operators are AndAlso and OrElse and they generally replace the VB 6 operators And and Or (but they do much more!).

The old VB 6 And and Or are still present in VB .NET. If you decide that they're what you need your program, go ahead and use them. They work the same way they used to work.

But AndAlso and OrElse have some properties that enhance your code in ways that VB 6 can't match. They offer advantages in two general categories:

You can avoid executing part of a logical expression to avoid problems.
You can optimize code by not executing any more of a compound expression than required.

AndAlso and OrElse are pretty much like And and Or except that they will "short circuit" an expression once the outcome is guaranteed. Suppose you're coding a test of a calculation result like this:

If a <>

If the variables in the block have these values:

Value1 = 1
Value2 = 1
Value3 = 0
a = 1
b = 1

The If expression generates a "divide by zero" error in VB 6 because Value3 is zero. It could be that the cases that result in Value3 being zero are very rare and only occur when you're enjoying a vacation a thousand miles away so you can be called back to fix the program in an emergency mode. (Hey! It happens!)

Let's recode the program as a .NET program using AndAlso and see what happens.

Dim Value1 As Integer
Dim Value2 As Integer
Dim Value3 As Integer
Dim a As Integer
Dim b As Integer
Value1 = 1
Value2 = 1
Value3 = 0
a = 1
b = 1
If a > Value1 AndAlso b > (Value2 / Value3) Then
  ' {body of If block}
End If
MsgBox("Hey! It works!")

First, of course, we have to declare our variables with VB .NET. Then after changing And to AndAlso, the program works! One reason is that the last part of the compound If condition - (value 2 / value3) - is never actually executed. When you use AndAlso, VB .NET knows that the expression can't succeed once it is determined that the first part of the condition - a is not greater than Value1 - is false. So VB .NET stops evaluating the expression right there.

Knowledgeable .NET'ers will point out that VB .NET supports a value for infinity - which is what the result of division by zero actually is in mathematical terms - so the expression would work anyway. Very true! But consider this example where our program tests a value obtained directly from the user:

Dim Value1 As Integer
Dim a As Integer
Dim b As String
Value1 = 1
a = 1
b = 1
If a > Value1 AndAlso b = Console.ReadLine() Then
  ' {body of If block}
End If
MsgBox("Hey! It works!")

In VB 6 (or if you use And in VB .NET), the program will ask for input every time. If you want the second test to be executed every time, use And. If that's not what you want, use AndAlso.

This analysis suggests how you can add some efficiency to your code by arranging a compound logical expression correctly. If you place the expression that is most likely to be false in the leftmost position when using AndAlso, you can prevent execution cycles from being used to evaluate the rightmost expression. In a single test, it wouldn't make enough difference to be worth even thinking about. But if your test is inside a loop of some kind and is executed zillions of times, it could make a big difference.

The OrElse operator has similar differences compared with the VB 6 Or operator. Knowing about these two new VB .NET logical operators can help you avoid very subtle errors or achieve subtle efficiencies.