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 NoLockmay 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.PromotionP
inner join masters.ProductPP with(NOLOCK) on PP.SetupId=@local_SetupId
inner join masters.CustomerCC with(NOLOCK)onCC.SetupId=@local_SetupId
Instead Of
SELECT * from trans.PromotionP
inner join masters.ProductPP with(NOLOCK) on PP.SetupId=P.SetupId
inner join masters.CustomerCC with(NOLOCK)onCC.SetupId=P.SetupId
Where PP.SetupId=@local_SetupId
Prevent the use of “Select *”
Causes Indexing issues and Binding issues
Dont use "SELECT *" ina SQLquery
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,AmendVersionFROMtrans.OrdersWHEREEXISTS(SELECTtop 1 OrderId FROMtrans.OrderExtract WHERESetupId=1099)
Use Local Temp Tables (#TempTableName)
Prevent using Global hash tables (##)
SELECT * into #TempProductFROM(SELECTProductId,LevelId,ProductCodeInterface,NamefromProduct whereSetupId=@local_SetupIdandIsActive=1)ASTP
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@sampleStringvarchar(max)='nevermind the promotions'
Declare @sizeOfStringint
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
,@ErrSeverityINT -- 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.
SETNOCOUNTON
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.
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.InvestmentTypeIdfromPromotion P
Inner join PromotionInvestmentPIE onPIE.VersionedPromotionId=P.VersionedPromotionId
where P.Name='Blah'
--Correct Statement
SELECT P.PromotionId,P.VersionedPromotionId,P.Name,PIE.InvestmentTypeIdfromPromotion P
Inner join PromotionInvestmentPIE onPIE.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 CustomerRankcolumn 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 StartDatebetween'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.
