Recompile the store procedure to improve the performance

One of our report runs under 2min in TEST environment, but over 10 min when deployed in production.  After days of research, it turns out all we need to do is to recompile the stored procedure.

Below is from MSDN:

When a procedure is compiled for the first time or recompiled, the procedure’s query plan is optimized for the current state of the database and its objects. If a database undergoes significant changes to its data or structure, recompiling a procedure updates and optimizes the procedure’s query plan for those changes. This can improve the procedure’s processing performance.

example:

USE AdventureWorks2012;
GO
EXEC sp_recompile N'Sales.Customer';
GO

https://msdn.microsoft.com/en-us/library/ms190439.aspx

Increase performance by adding NONCLUSTERED INDEX

Got a call from a client reporting an issue that is randomly occurred. after looking into the log file, I found one method took over a min to complete. The code shows only a simple select statement with where clause on "sentby" column. This column is a "nvarchar" and the table itself has over 1 million records.

Here is what I learned when I trying to fix this issue:

1. use "varchar" instead of "nvarchar". only use "nvarchar" when you have different languages in the same column! if you look at the sql execution plan ( sql -> display estimated ececution plan), search "nvarchar" does an index scan and "varchar" does index seek. index seek is orders of magnitude faster than a scan!

2. no index is set on "sentby" column. It won't be an issue in the small table (sql does index scan on small table), but it will impact performance when data grows. I used the script below to add a NONCLUSTERED INDEX on this column.

CREATE NONCLUSTERED INDEX IDX_MyMessageTable ON dbo.MyMessageTable
(
SentBy
)

after the index is created, the 1 min search time is down to under a second.

Can't use SCOPE_IDENTITY() to return uniqueidentifier id

SCOPE_IDENTITY() returns the last generated *identity* value in the current scope (function, stored proc., trigger, etc.). Identity columns need to be of type bigint, int or smallint and are not compatible with type uniqueidentifier (i.e. you can't assign an integer to a variable defined as a uniqueidentifier).

So, if you're asking SQL Server to give you last identity inserted and then assigning that to a variable previously declared as a uniqueidentifier, you'll end up with the compatibility error that you're receiving.

SQL: Return limit Rows

In the asp.net application, i found the good way to return limit rows from data table is to use the ROWCOUNT

CREATE PROCEDURE GetContacts
(
 @ModuleID int,
 @maxrows int = 0
)
AS
 SET ROWCOUNT @maxrows

 SELECT
   ItemID,
   CreatedDate,
   CreatedByUser,
   Name,
   Role,
   Email,
   Contact1,
   Contact2
 FROM
   Contacts
 WHERE
   ModuleID = @ModuleID

For more info: http://authors.aspalliance.com/stevesmith/articles/sprowcount.asp

SQL 2005 installation

I always run into problem with installation of SQL 2005. Here is step by step guide:

http://sequelserver.blogspot.com/2007/06/20070703sql-server-2005-installation.html

Change the table schema

To change the schema of the tables, use this script command:

Alter schema "new_schema_name" transfer "old_schema_name.table_name"

Restore the SQL 2005 Database using script

The SQL 2005 management studio has wizard for restore the database. It works pretty good for most cases, but I found the scripting is much for flexible. Recently, I tried to restore a database to with a different name, but the wizard won't let me to do it. Here is the example of the alternative script way. It worked great for me.

Restore Database YourDBName
From Disk = 'TheBAKFileName.bak'
With Move 'MDFFileName' To 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\YourDBName.MDF',
Move 'LDFFileName_Log' To 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\YourDBName.ldf',
REPLACE

Dynamic SQL in stored procedure

By using sp_executesql (system stored procedure), you can write the dynamic SQL inside the stored procedure.

sp_ExecuteSql accepts a list of parameters, the first two of which MUST be either ntext, nchar or nvarchar. The first is your sql statement and the second is the parameter names. The third part is the list of parameter values.

Example:
DECLARE @SqlString nvarchar(4000)
SET @SqlString = N'SELECT * FROM ' + @TableName + ' WHERE ID = @ID';
EXECUTE sp_executesql
@SQLString
,N'@ID int'
,@ID

The @TableName and @ID are parameters passed into the stored procedure.

for more info, check:
http://doc.ddart.net/mssql/sql2000/html/acdata/ac_8_con_04_9uek.htm
http://msdn.microsoft.com/en-us/library/ms175170.aspx