Design Pattern - Adapter

Definition

- Convert the interface of a class into another interface clients expect. Adapter lets classes work together that couldn't otherwise because of incompatible interfaces.

UML Class Diagram



Adapter: when and where use it

- .NET developers write classes that expose methods that are called by clients. Most of the time they will be able to control the interfaces, but there are situations, for example, when using 3rd party libraries, where they may not be able to do so.

- The 3rd party library performs the desired services but the interface methods and property names are different from what the client expects. This is a scenario where you would use the
Adapter pattern. The Adapter provides an interface the client expects using the services of a class with a different interface. Adapters are commonly used in programming environments where new components or new applications need to be integrated and work together with existing programming components.

- Adapters are also useful in refactoring scenarios. Say, you have two classes that perform similar functions but have different interfaces. The client uses both classes, but the code would be far cleaner and simpler to understand if they would share the same interface.

- You cannot alter the interface, but you can shield the differences by using an Adapter which allows the client to communicate via a common interface. The Adapter handles the mapping between the shared interface and the original interfaces.

Sample
- Target class (the expected class client want to get)

- Adapter class (return class from services or server)

- Adaptee class (converter class - convert from a class to an expected class)

- Client class


(Ref: Gang of Four tutorial)

Table Value Constructors in SQL Server 2008

Table Value Constructors (TVCs) are a useful feature of 2008, allowing you to specify tables of values and expressions. This has all sorts of uses. Users who are stuck with previous versions of SQL Server can play along, since Rob demonstrates that there have, for a long time, been ways of doing this in SQL Server, though less elegantly.

Beginning with SQL Server 2008, you can define table value constructors (TVCs) within your data manipulation language (DML) statements so you can work with subsets of data in a tabular format. A TVC is an expression made up of a VALUES clause and one or more row value expressions, each of which returns a scalar value. Taken together, these values provide data to the DML statement in much the same way a subquery might return a table.

A TVC is made up of one or more columns and one or more rows of data. You can use a TVC as part of the VALUES clause in an INSERT statement, the USING clause in a MERGE statement, or the FROM clause in any DML statement. The following syntax shows the elements that make up a TVC:

VALUES( {DEFAULT|NULL|} [,...n] ) [,...n]

Samples
- SELECT statement


- INSERT statement

- MERGER statement

NOTES: TVC is limited to 1000 rows

(Ref: Simple-talk)

Design Pattern - Singleton

Definition
- Ensure a class has only one instance and provide a global point of access to it.

UML Class Diagram

Two type of singleton
- Lazily initialized (object will be initialized when the first time of accessing)

- Eagerly initialized (object will be initialized when the variable declared)


Singleton: when and where use it
- Most objects in an application are responsible for their own work and operate on self-contained data and references that are within their given area of concern. However, there are objects that have additional responsibilities and are more global in scope, such as, managing limited resources or monitoring the overall state of the system.

- The responsibilities of these objects often require that there be just one instance of the class. Examples include cached database records, or a scheduling service which regularly emails work-flow items that require attention. Having more than one database or scheduling service would risk duplication and may result in all kinds of problems.

- Other areas in the application rely on these special objects and they need a way to find them. This is where the Singleton design pattern comes in. The intent of the Singleton pattern is to ensure that a class has only one instance and to provide a global point of access to this instance. Using the Singleton pattern you centralize authority over a particular resource in a single object.

- Other reasons quoted for using Singletons are to improve performance. A common scenario is when you have a stateless object that is created over and over again. A
Singleton removes the need to constantly create and destroy objects. Be careful though as the Singleton may not be the best solution in this scenario; an alternative would be to make your methods static and this would have the same effect. Singletons have the unfortunate reputation for being overused by „pattern happy‟ developers.

- Global variables are frowned upon as a bad coding practice, but most practitioners acknowledge the need for a few globals. Using Singleton you can hold one or more
global variables and this can be really handy. In fact, this is how Singletons are frequently used – they are an ideal place to keep and maintain globally accessible
variables.
(Ref: Gang of Four tutorial)

Collect database performance info

The queries below will help us collect information related to database performance

SELECT CASE WHEN database_id = 32767 THEN 'Resource'
ELSE DB_NAME(database_id)
END AS DBName ,
OBJECT_SCHEMA_NAME(object_id, database_id) AS [SCHEMA_NAME] ,
OBJECT_NAME(object_id, database_id) AS [OBJECT_NAME] ,
*
FROM sys.dm_exec_procedure_stats

SELECT CASE WHEN database_id = 32767 THEN 'Resource'
ELSE DB_NAME(database_id)
END AS DBName ,
OBJECT_SCHEMA_NAME(object_id, database_id) AS [SCHEMA_NAME] ,
OBJECT_NAME(object_id, database_id) AS [OBJECT_NAME] ,
cached_time ,
last_execution_time ,
execution_count ,
total_worker_time / execution_count AS AVG_CPU ,
total_elapsed_time / execution_count AS AVG_ELAPSED ,
total_logical_reads / execution_count AS AVG_LOGICAL_READS ,
total_logical_writes / execution_count AS AVG_LOGICAL_WRITES ,
total_physical_reads / execution_count AS AVG_PHYSICAL_READS
FROM sys.dm_exec_procedure_stats
ORDER BY AVG_LOGICAL_READS DESC

SELECT CASE WHEN dbid = 32767 THEN 'Resource'
ELSE DB_NAME(dbid)
END AS DBName ,
OBJECT_SCHEMA_NAME(objectid, dbid) AS [SCHEMA_NAME] ,
OBJECT_NAME(objectid, dbid) AS [OBJECT_NAME] ,
MAX(qs.creation_time) AS 'cache_time' ,
MAX(last_execution_time) AS 'last_execution_time' ,
MAX(usecounts) AS [execution_count] ,
SUM(total_worker_time) / SUM(usecounts) AS AVG_CPU ,
SUM(total_elapsed_time) / SUM(usecounts) AS AVG_ELAPSED ,
SUM(total_logical_reads) / SUM(usecounts) AS AVG_LOGICAL_READS ,
SUM(total_logical_writes) / SUM(usecounts) AS AVG_LOGICAL_WRITES ,
SUM(total_physical_reads) / SUM(usecounts) AS AVG_PHYSICAL_READS
FROM sys.dm_exec_query_stats qs
JOIN sys.dm_exec_cached_plans cp ON qs.plan_handle = cp.plan_handle
CROSS APPLY sys.dm_exec_sql_text(cp.plan_handle)
WHERE objtype = 'Proc'
AND text NOT LIKE '%CREATE FUNC%'
GROUP BY cp.plan_handle ,
DBID ,
objectid

Performance tuning tips for database developers

1. Create a primary key on each table you create and unless you are really knowledgeable enough to figure out a better plan, make it the clustered index (note that if you set the primary key in Enterprise Manager it will cluster it by default).
2. Create an index on any column that is a foreign key. If you know it will be unique, set the flag to force the index to be unique.
3. Don’t index anything else (yet).
4. Unless you need a different behaviour, always owner qualify your objects when you reference them in TSQL. Use dbo.sysdatabases instead of just sysdatabases.
5. Use set nocount on at the top of each stored procedure (and set nocount off) at the bottom.
6. Think hard about locking. If you’re not writing banking software, would it matter that you take a chance on a dirty read? You can use the NOLOCK hint, but it’s often easier to use SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED at the top of the procedure, then reset to READ COMMITTED at the bottom.
7. I know you’ve heard it a million times, but only return the columns and the rows you need.
8. Use transactions when appropriate, but allow zero user interaction while the transaction is in progress. I try to do all my transactions inside a stored procedure.
9. Avoid temp tables as much as you can, but if you need a temp table, create it explicitly using Create Table #temp.
10. Avoid NOT IN, instead use a left outer join - even though it’s often easier to visualize the NOT IN.
11. If you insist on using dynamic sql (executing a concatenated string), use named parameters and sp_executesql (rather than EXEC) so you have a chance of reusing the query plan. While it’s simplistic to say that stored procedures are always the right answer, it’s also close enough that you won’t go wrong using them.
12. Get in the habit of profiling your code before and after each change. While you should keep in mind the depth of the change, if you see more than a 10-15% increase in CPU, Reads, or Writes it probably needs to be reviewed.
13. Look for every possible way to reduce the number of round trips to the server. Returning multiple resultsets is one way to do this.
14. Avoid index and join hints.
15. When you’re done coding, set Profiler to monitor statements from your machine only, then run through the application from start to finish once. Take a look at the number of reads and writes, and the number of calls to the server. See anything that looks unusual? It’s not uncommon to see calls to procedures that are no longer used, or to see duplicate calls. Impress your DBA by asking him to review those results with you.
16. Do not use user defined table types in reporting queries. There generally is only a need for use of these types when moving large amounts of data from .net into sql – for inner sql communication / data transference we should be use temp tables.
17. Do not use table variables – in almost every case where you are using a table variable, speed will be increased by using an appropriate temp table.
18. Do not have extremely large multi-table joins. SQL Server is most efficient when joining 4 tables at a time as that is the maximum number that the query optimizer can consider during the optimization cycle of query compilation. If you are using 5-6-7 tables in a join this is probably not the end of the world but if you find yourself using more (or joining against views) it is probably a good idea to create a temp table and start filling it with data through multiple updates/inserts.
19. Create appropriate indexes (covering, if needed) over tables or clustered indexes on views (only if absolutely necessary).
20. In most case we avoid create temp table, but with complex business a temp with index will improve performance. In some case may be more than 10 times.
21. With a large amount of record, please avoid self JOIN statement.