Prevent Stored Procedure Timeout
Write efficient Stored Procedures
Prevent More Disk Reads
Prevent Dead Locks
System.Data.SqlClient.SqlException (0x80131904): Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
The following are the collection of my learning as a SQL developer.
Because of large data on the tables, the join operations and other operations will take up lot of time hence causing the application to time out, also reducing the performance.
Parameter Sniffing
In short, parameter sniffing is one exec plan for all parameter combinations.
- This option causes the compilation process to ignore the value for the specified variable and use the specified value instead.
- Unlike the other workaround (Option(Recompile)), this option avoid the overhead of recompiling the query every time.
This is the most common problem, The workaround is to declare the input params again with different variable name and use the
new variable through out the SP.
In the Example @local_SetupId
CREATE PROCEDURE [dbo].[pr_get_user_ids]
(
@in_SetupId
int
)
As
begin
Declare
@local_SetupId
int
=@in_SetupId
Select UserId
from UserDetail
where SetupId=@local_SetupId
end
Use Local Temp Tables
Store
the data from large tables such as Product / Orders/ Hierarchy
into Local temp Tables and Use them when joining – This is
will Prevent the joining of heavy tables.
This
is Subject to temp Db Size. Consider the temp Db size.
Select only the needed columns for your Transaction operation and put it into a
local temp table. Hence on performing joins the tables will
be lighter and preventing the use of excess Resource.
In the Example We have filtered out few
columns from productHierarchy
table using setupid in Where clause
and used it to join with Product table.
SELECT * into #TempProductHierarchy
FROM
(SELECT ProductHierarchy,ProductHierarchyId,ProductId
from
ProductHierarchyDetails where SetupId=@local_SetupId
)
AS
TPH
SELECT ProductCodeInterface
from
masters.Product
P
inner join #tempProductHierarchy
on
P.ProductId=#TempProductHierarchy.ProductId
Instead of
SELECT ProductCodeInterface from
masters.Product
P
inner join masters.ProductHierarchyDetail
PHD on
P.ProductId=PHD.ProductId
where P.SetupId=@local_SetupId
Use NON Clustered Index
Use
Non Clustered Index on temp tables for quicker Execution
Once you have created a temp table, set
index on the primary key of the table – on which you will be performing the
join operation.
In the example we have read the Product
Hierarchy table
into a temp table and we have created a non-clustered index onto the primary key i.e ProductId
SELECT
*
into
#TempProductHierarchy
FROM
(SELECT ProductHierarchy,ProductHierarchyId,ProductId
from
ProductHierarchyDetails
where SetupId=@local_SetupId
)
AS
TPH
Create NonClustered
Index
IDX_TP4 On
#TempProductHierarchy(ProductId)
Use With(NOLOCK)
NOLOCK typically (depending on your DB
engine) means give me your data, and I don't care what state it is in, and
don't bother holding it still while you read from it. It is all at once faster,
less resource-intensive
Use NOLOCK on master tables and not
everywhere.
Using NoLock
may become dangerous sometimes,
so check the query exec plan when in doubt.
Select * from
ProductHierarchy WITH(NOLOCK)
Join On the InputParameter
This will reduce the operation size and
the disk read
Join on input parameter instead of
filtering in where condition.
As you can see here we have joined on setupid i.e the
input parameter instead of joining
on the setupid of
the other table and
then filtering in where condition.
SELECT * from trans.Promotion
P
inner join masters.Product
PP with(NOLOCK) on PP.SetupId=@local_SetupId
inner join masters.Customer
CC with(NOLOCK)
on
CC.SetupId=@local_SetupId
Instead Of
SELECT * from trans.Promotion
P
inner join masters.Product
PP with(NOLOCK) on PP.SetupId=P.SetupId
inner join masters.Customer
CC with(NOLOCK)
on
CC.SetupId=P.SetupId
Where PP.SetupId=@local_SetupId
Prevent the use of “Select *”
Causes
Indexing issues and Binding issues
Dont use "SELECT
*" in
a SQL
query
Insead use Select ProductId, ProductName from Products
Use EXISTS
To
check if any data exists in a particular table, use EXISTS instead of relying on Count its
more effective.
SELECT OrderId,AmendVersion
FROM
trans.OrdersWHERE
EXISTS
(SELECT
top 1 OrderId FROM
trans.OrderExtract WHERE
SetupId=1099)
Use Local Temp Tables (#TempTableName)
Prevent
using Global hash tables (##)
SELECT * into #TempProduct
FROM(SELECT
ProductId,LevelId,ProductCodeInterface,Name
from
Product where
SetupId=@local_SetupId
and
IsActive=1)
AS
TP
Use Local Variables to Store the FunctionCall return
Prevent
calling the string functions or date functions Over and over again, instead
store ‘em in local variables if you are going to reuse the
value.
Declare
@sampleString
varchar(max)
=
'nevermind the
promotions'
Declare @sizeOfString
int
set @sizeOfString=
len(@sampleString)
SELECT @sizeOfString
Use Try - Catch
BEGIN TRY
-- Logic / Query here
END TRY
BEGIN CATCH
-----------------------------------------------------------------------
DECLARE
@ErrMsg VARCHAR(255)
--
Error Message
,@ErrNo INT
--
Error Number
,@ErrSeverity
INT -- Error Severity
,@ErrProc VARCHAR(255)
--
Error Procedure
,@ErrLine INT
--
Error Line
SELECT
@ErrMsg = ERROR_MESSAGE()
,@ErrNo = ERROR_NUMBER()
,@ErrSeverity
=
17
,@ErrProc = ERROR_PROCEDURE()
,@ErrLine = ERROR_LINE()
RAISERROR (
@ErrMsg
,@ErrSeverity
,1
,@ErrNo
,@ErrLine
,@ErrProc
)
-----------------------------------------------------------------------
END CATCH
END
Use
SET
NOCOUNT
ON
Whenever we write any procedure and
execute it a message appears in message window that shows number of rows
affected with the statement written in the procedure.
When
SET NOCOUNT is ON, the count is not returned.
SET
NOCOUNT
ON
Select PromotionId from Promotion
Prevent Usage of DDL Statements
Do
not try to use DDL statements inside a stored procedure that will reduces the
chance to reuse the execution plan.
DDL statements like CREATE,ALTER,DROP,TRUNCATE etc.
DDL statements like CREATE,ALTER,DROP,TRUNCATE etc.
Use Alias
If an alias is not present, the engine
must resolve which tables own the specified columns. A short alias is parsed
more quickly than a long table name or alias. If possible, reduce the alias to
a single letter
--Wrong Statement
SELECT PromotionId
,
P.VersionedPromotionId,
Name,
PIE.InvestmentTypeId
from
Promotion P
Inner join PromotionInvestment
PIE on
PIE.VersionedPromotionId=P.VersionedPromotionId
where P.Name='Blah'
--Correct Statement
SELECT P.PromotionId
,
P.VersionedPromotionId,
P.Name,
PIE.InvestmentTypeId
from
Promotion P
Inner join PromotionInvestment
PIE on
PIE.VersionedPromotionId=P.VersionedPromotionId
where P.Name='Blah'
Don't use UPDATE instead of CASE
Take this scenario, for instance: You're
inserting data into a temp table and need it to display a certain value if
another value exists. Maybe you're pulling from the Customer table and you want
anyone with more than $100,000 in orders to be labeled as "Preferred."
Thus, you insert the data into the table and run an UPDATE statement to set the
CustomerRank
column to "Preferred" for anyone who has more than $100,000 in
orders. The problem is that the UPDATE statement is logged, which means it has
to write twice for every single write to the table. The way around this, of
course, is to use an inline CASE statement in
the SQL query itself. This tests every row for the order amount condition and
sets the "Preferred" label before it's written to the table.
Avoid Functions on RHS
Dont use this
select *
from Promotion
where YEAR(StartDate)
=
2015
and
MONTH(StartDate)
=
6
Use this
Select *
From Promotion
Where StartDate
between
'6/1/2015'
and
'6/30/2015'
Specify optimizer hints in SELECT
most cases the query optimizer will pick
the appropriate index for a particular table based on statistics, sometimes it
is better to specify the index name in your SELECT query.
Do
not use this unless you know what you are doing.
SELECT *
FROM Promotion
WITH ( Index(IdxPromotionId))
WHERE Name =
'blah'
and Setupid=1099
Hope these tips will help you prevent and solve the timeout exception you face. If you want to add any please mention in the comments.
