Improving query performance SQL server

Lead Paragraph
This article provides best practices and guidelines for TSQL developers for improvement of the performance of SQL server database.
Introduction
Writing TSQL queries has become extremely easy for developers and that can be easily learned by anyone. But TSQL simplicity has also makes it easy to write poorly performing unstructured queries. In this article I have mentioned few important tips and guidelines for TSQL developers for improving the query performance for SQL server database.
Parameterized Queries
When you are executing series of TSQL queries from frontend application, use parameterized queries for better performance. Parameterized queries gives better performance by compiling the query once and executing the same compiled query multiple times. But you should also use the same command object to cache the query plan. Following is an example of parameterized query to return student information based on student roll number.
SELECT Student_Name, Class, Subjects FROM Students WHERE Roll_No =?
Creating Necessary Indexes
Create useful indexes to improve TSQL query performance. Before creating indexes, you must know how the data is used, the types of queries you would like to run on top of SQL server data so that the query processor can use indexes to find your data quickly. Index creation rules are simple and straight forward, if your queries are mostly SELECT statements; more indexes can be helpful for faster execution. But DML (INSERT, UPDATE, and DELETE) operations will get slow down if you have more indexes. Also try to avoid creating indexes on very small tables with just a few data pages. This saves the cost of loading and processing index pages.

Create indexes on primary keys and frequently used foreign keys because primary keys and foreign keys are mostly used in join conditions. If your query joins tables by using other columns, create indexes on those columns for the same reason.

To improve performance you can create Indexes on columns used in the WHERE clause of your TSQL statement. Performance of query depends on the selectivity of index. Selectivity is the ratio of qualifying rows to total rows. If the ratio is low, the index is highly selective. An index that is not selective is not as useful. All unique indexes are selective. You can evaluate the selectivity of an index by running the sp_show_statistics stored procedures on SQL Server.

Multiple-column indexes are useful for faster executing of filter expressions that match a prefix set of key columns. For example, the index CREATE INDEX Idx_Student_Name ON Student ("Last Name" ASC, "First Name" ASC) helps faster execution of the following queries:

... WHERE "Last Name" = 'Pal'

... WHERE "Last Name" = 'Pal' AND "First Name" = 'Tapas'

... WHERE "First Name" = 'Tani' AND "Last Name" = 'Paul'

Replace Subqueries With Joins
Sometimes JOIN gives better performance compared to subquery. Using JOIN you can manually control the order that data is selected. You can also decrease query execution time by forcing SQL server to pre-filter data in the table. The following query displays student details with mark obtained in different subjects.

SELECT stu.Student_Name, stu.Class, stu.Subject, stu.Marks,
(SELECT Markset.PassMark
FROM dbo.[Marks Details] AS Markset
WHERE stu.Subject= Markset.Subject) AS PassMark
FROM Students AS stu

The above nested query can be replaced by following INNER JOIN query for better performance.

SELECT stu.Student_Name, stu.Class, stu.Subject, stu.Marks,
Markset.PassMark
FROM Students AS stu
INNER JOIN dbo.[Marks Details] as Markset
On stu.Subject= Markset.Subject

Using Temporary Tables
SQL Server DBAs and TSQL developer’s loves to write SELECT INTO statement for data transfer from system tables, like this:

SELECT *
INTO #MyTempTable
FROM sysobjects

But the above query will create table locks when dealing with large number of records. As a result, other queries and procedures those need to use sysobjects object will have to wait until long-running query execution is complete. You can avoid system table locking by manually creating the temporary table with the CREATE TABLE command and load the data afterwards with another SQL query.

For example…

CREATE TABLE #MyTempTable
(All the Necessary Columns)

INSERT INTO # MyTempTable
SELECT *
FROM sys.objects

The above query will have same number of locks as the first one; but the time of the table lock period will be shorter for the second one compared to the previous query. This will allow other processes and objects to use system table.

Typically, when developing SQL code the development server has only a single user or few users. When working on SQL code, it's important to know when the code will impact sessions other than the current session. And unexpected interaction can cause major performance issues.

Stored Procedure Performance Improvement

Most of the time database developers follow standard naming convention while creating database objects. Like, while creating a stored procedure we start with prefix sp_. But this naming convention is not correct. All the system stored procedures begins with sp_. So when you write a custom stored procedure with prefix sp_ SQL server execution engine first searches definition of the stored procedure in master database before looking into the database it is called in. This may result slow performance.

Use the SET NOCOUNT ON statement on top of stored procedures to prevent SQL server from sending the message to the caller for each statement in a stored procedure. Each message contains the number of affected rows for the respective statement.

Conclusion
If you think a query is taking too much time to execute and not giving you expected performance use SQL server profiler for creating trace and viewing performance of your query in live environment. SQL developers usually can obtain best performance of TSQL queries by following correct development techniques.

Speech to Text conversion

I am now working on Speech to Text conversion. .NET 4.0 has System.Speech Library.
Refer following articles if you are interested.....

http://www.microsoft.com/windowsxp/using/setup/expert/moskowitz_02september23.mspx -- Microsoft Article
http://www.c-sharpcorner.com/UploadFile/ssrinivas/SpeeechRecognitionusingCSharp11222005054918AM/SpeeechRecognitionusingCSharp.aspx - Sample example
http://www.dotnetforce.com/Content.aspx?t=a&n=216 - Sample app
http://www.15seconds.com/issue/021126.htm - Article
http://www.codeguru.com/vb/gen/vb_misc/plugins/article.php/c11019__2/VB-and-Voice-Recognition.htm - Article
http://www.codeguru.com/vb/gen/vb_misc/plugins/article.php/c11019__2/VB-and-Voice-Recognition.htm - Article
http://stackoverflow.com/questions/3890102/c-and-microsoft-speech-recognition-and-speech-synthesis - Blog
http://www.c-sharpcorner.com/UploadFile/nipuntomar/3316/ - Test to Speech
http://www.c-sharpcorner.com/UploadFile/nipuntomar/3385/ - Speech to Text
http://www.dreamincode.net/forums/topic/84789-convertion-of-speech-to-text-using-visual-studio-8/ - Code
http://www.c-sharpcorner.com/uploadfile/mahesh/222/default.aspx - Code
http://stackoverflow.com/questions/3228554/i-want-to-convert-speech-to-text-in-wpf-how-can-i-do-this - Code
http://franksworld.com/blog/archive/2009/06/30/11617.aspx - code
http://www.dreamincode.net/forums/topic/134436-speech-to-text-conversion/ - code
http://www.dreamincode.net/forums/topic/206629-speech-to-text-conversion-in-c-sharp/ - code

Apply for Microsoft MVP

Microsoft MVP
-----------------
The Microsoft MVP Award Program recognizes outstanding members of technical communities for their pubic participation and willingness to help others in Technical area.

Nomination Form
---------------
http://download.microsoft.com/download/8/e/7/8e725d96-7ec3-498b-9fa7-86779aed101f/MVP%20Nomination%20Form.doc

https://profile.microsoft.com/RegSysProfileCenter/wizard.aspx?wizid=b8b0dc5c-37e3-48e3-af75-e509830c8163&lcid=1033

More Information
----------------
http://mvp.support.microsoft.com/
http://www.microsoft.com/india/mvp/indiamvp.aspx
http://www.microsoft.com/india/mvp/nominations.aspx

Top 10 SQL Server 2008 Security Features

Protecting database information from threats and vulnerabilities is vital for any organization. This article discusses 10 new security features introduced in SQL server 2008 that facilitate effective management of the database by using server surface area configuration (policy-based management configuration), full database encryption (TDE), enhanced auditing features and more.

Read full article..
http://www.databasejournal.com/features/mssql/article.phpr/3899866/article.htm

How to Use the Visual Studio Code Analysis Tool FxCop

In today's world, software security is the first and foremost requirement of any customer when you are developing a new application. But you can develop a secure application by mitigating all the risks in the development cycle itself.

The best way to mitigate software risks is by doing code reviews and code analysis throughout the development cycle.
Another positive aspect of code analysis is you can easily identify bugs early in the development cycle before those are identified by testers or even users of the application. Identifying and correcting critical bugs early in the software development cycle mitigates risks like reducing developer productivity, creating unnecessary bottlenecks in the software development lifecycle, increasing software development costs, etc. In this article I will discuss the integrated static code analysis tool (FxCop), introduced with Visual Studio 2010. Visual Studio code analysis rules are applicable for five different programming languages.

Read full article - http://www.devx.com/dotnet/Article/45499

Top 10 SQL Server 2008 Development Features

Many new developer features were introduced in SQL Server 2008 database to facilitate robust database development. SQL server 2008 improves developer productivity by providing seamless integration between frameworks, data connectivity technologies, programming languages, Web services, development tools and data. This article discusses the new top 10 developer features introduced in SQL server 2008. Read my full article.

http://www.databasejournal.com/features/mssql/article.php/3897251/article.htm

Developing Reports for ASP.NET Web applications Using SQL Server 2008 Reporting Services

Crystal Report was the most recommended reporting tool used with ASP.NET web application before SQL server reporting service (SSRS) was first introduced in 2004 as an add-on to SQL server 2000 by Microsoft. An enhanced version of this SQL server reporting service was released with SQL server 2005 and the latest version of SSRS is launched with SQL Server 2008. A SSRS report is an XML file with a .RDL extension (Report Definition Language). After installation of the business intelligent development studio (BIDS) tool , developers can create a RDL report file using Microsoft Visual Studio editor. SSRS also provides a web service (server) interface for custom reporting applications. Read my full article in codeguru
http://www.codeguru.com/csharp/.net/article.php/c17661