We will go over how to optimize Stored Procedure with making simple changes in the code. Please note there are many more other tips, which we will cover in future articles.
- Include SET NOCOUNT ON statement: With every SELECT and DML statement, the SQL server returns a message that indicates the number of affected rows by that statement. This information is mostly helpful in debugging the code, but it is useless after that. By setting SET NOCOUNT ON, we can disable the feature of returning this extra information. For stored procedures that contain several statements or contain Transact-SQL loops, setting SET NOCOUNT to ON can provide a significant performance boost because network traffic is greatly reduced.
CREATE PROC dbo.ProcName AS SET NOCOUNT ON; --Procedure code here SELECT column1 FROM dbo.TblTable1 -- Reset SET NOCOUNT to OFF SET NOCOUNT OFF; GO
- Use schema name with object name: The object name is qualified if used with schema name. Schema name should be used with the stored procedure name and with all objects referenced inside the stored procedure. This help in directly finding the complied plan instead of searching the objects in other possible schema before finally deciding to use a cached plan, if available. This process of searching and deciding a schema for an object leads to COMPILE lock on stored procedure and decreases the stored procedure’s performance. Therefore, always refer the objects with qualified name in the stored procedure like
SELECT * FROM dbo.MyTable -- Preferred method -- Instead of SELECT * FROM MyTable -- Avoid this method --And finally call the stored procedure with qualified name like: EXEC dbo.MyProc -- Preferred method --Instead of EXEC MyProc -- Avoid this method
- Do not use the prefix “sp_” in the stored procedure name: If a stored procedure name begins with “SP_,” then SQL server first searches in the master database and then in the current session database. Searching in the master database causes extra overhead and even a wrong result if another stored procedure with the same name is found in master database.
- Use IF EXISTS (SELECT 1) instead of (SELECT *): To check the existence of a record in another table, we uses the IF EXISTS clause. The IF EXISTS clause returns True if any value is returned from an internal statement, either a single value “1” or all columns of a record or complete recordset. The output of the internal statement is not used. Hence, to minimize the data for processing and network transferring, we should use “1” in the SELECT clause of an internal statement, as shown below:
IF EXISTS (SELECT 1 FROM sysobjects WHERE name = 'MyTable' AND type = 'U')
- Use the sp_executesql stored procedure instead of the EXECUTE statement.
The sp_executesql stored procedure supports parameters. So, using the sp_executesql stored procedure instead of the EXECUTE statement improve the re-usability of your code. The execution plan of a dynamic statement can be reused only if each and every character, including case, space, comments and parameter, is same for two statements. For example, if we execute the below batch:
DECLARE @Query VARCHAR(100) DECLARE @Age INT SET @Age = 25 SET @Query = 'SELECT * FROM dbo.tblPerson WHERE Age = ' + CONVERT(VARCHAR(3),@Age) EXEC (@Query)
If we again execute the above batch using different @Age value, then the execution plan for SELECT statement created for @Age =25 would not be reused. However, if we write the above batch as given below,
DECLARE @Query NVARCHAR(100) SET @Query = N'SELECT * FROM dbo.tblPerson WHERE Age = @Age' EXECUTE sp_executesql @Query, N'@Age int', @Age = 25
the compiled plan of this SELECT statement will be reused for different value of @Age parameter. The reuse of the existing complied plan will result in improved performance.
- Try to avoid using SQL Server cursors whenever possible: Cursor uses a lot of resources for overhead processing to maintain current record position in a recordset and this decreases the performance. If we need to process records one-by-one in a loop, then we should use the WHILE clause. Wherever possible, we should replace the cursor-based approach with SET-based approach. Because the SQL Server engine is designed and optimized to perform SET-based operation very fast. Again, please note cursor is also a kind of WHILE Loop.
- Keep the Transaction as short as possible: The length of transaction affects blocking and deadlocking. Exclusive lock is not released until the end of transaction. In higher isolation level, the shared locks are also aged with transaction. Therefore, lengthy transaction means locks for longer time and locks for longer time turns into blocking. In some cases, blocking also converts into deadlocks. So, for faster execution and less blocking, the transaction should be kept as short as possible.
- Use TRY-Catch for error handling: Prior to SQL server 2005 version code for error handling, there was a big portion of actual code because an error check statement was written after every t-sql statement. More code always consumes more resources and time. In SQL Server 2005, a new simple way is introduced for the same purpose. The syntax is as follows:
BEGIN TRY --Your t-sql code goes here END TRY BEGIN CATCH --Your error handling code goes here END CATCH
Reference: Pinal Dave (https://blog.sqlauthority.com)
181 Comments. Leave new
Hi Yashwant,
I think T4S Stored Procedure Generator is good but will it improve performance of my SPs or not?
Why do u make things complex by adding odd things like “SET NOCOUNT ON;”
try keep things simple.
“CEATE PROCEDURE proc_name
as
your query”
thats it
Contrary to your comment, SET NOCOUNT ON is quite important. What it ensures is that the number of rows affected is not returned to the caller.
You may be surprised to know, but it is true that not having SET NOCOUNT ON may result in the application not behaving as it should.
In my opinion, removing “SET NOCOUNT ON” is not making things simple – it’s making things suicidal!
Hi Pinal,
Nice set of tips
Does adding Comments in stored procedure or function causes ANY KIND of burden on SQL Server performance ?
Hi Pinal,
These tips are very nice and helping me allot
Thanks
Wonderful article Pinal.
Thank you!
Would you suggest using the schema name for all object references in a sproc including joins and conditional clauses?
For example:
SELECT b.BatchID, b.BatchName, bt.BatchTransactionID, bt.BatchTransactionName
FROM dbo.Batch
INNER JOIN dbo.BatchTrx WITH(NOLOCK) ON dbo.BatchTrx.BatchID = dbo.Batch.BatchID
WHERE dbo.Batch.StatusID = 1
AND dbo.BatchTrx.StatusID = 1
Hello Pinal Dave, thanks you for your blog, is a very good source for me.
My cuestion is when I execute the select directly in the SP does it reuse the
execution plan ?, like this
CREATE PROC dbo.ProcName @p1 int
AS
SET NOCOUNT ON;
–Procedure code here
SELECT column1 FROM dbo.TblTable1 where ProcID = @p1
— Reset SET NOCOUNT to OFF
SET NOCOUNT OFF;
GO
Thanks for the excellent Tips..
Very good tips !
These Tips are very much useful in optimizing the stored procedures.
I would like to add one more.
Minimize the usage of temporary tables,instead use variables to improve the performance
Dear Pinal,
I have written a stored procedure but, It takes on an average 1 minute to execute. I tried all tips you suggested, but still that haven’t optimized the query.
I am a newbie, please guide me to optimize below Stored Procedure
CREATE PROCEDURE [dbo].[usp_GetMessagesCountBySearchFilter]
(
@SearchText NVARCHAR(1000),
@FromDate DATETIME,
@ToDate DATETIME,
@IsPending BIT,
@Sender NVARCHAR(1000),
@Receiver NVARCHAR(1000)
)
AS
BEGIN
–SET NOCOUNT ON;
DECLARE
@local_SearchText NVARCHAR(1000),
@local_FromDate DATETIME,
@local_ToDate DATETIME,
@local_IsPending BIT,
@local_Sender NVARCHAR(1000),
@local_Receiver NVARCHAR(1000)
SET @SearchText= @local_SearchText
SET @FromDate =@local_FromDate
SET @ToDate =@local_ToDate
SET @IsPending=@local_IsPending
SET @Sender =@local_Sender
SET @Receiver =@local_Receiver
DECLARE @MessageCount INT
SELECT @MessageCount=COUNT(MessageID) FROM
(
SELECT
DISTINCT T1.MessageID,
MessageSubject,
MessageBody,
PostedDateTime,
(
SELECT
CAST(T4.UserID AS NVARCHAR(120))
+’,’
FOR XML PATH(”)
) AS PostedForIds,
(
SELECT
CASE T4.IsOrganization
WHEN 1 THEN ISNULL(T4.CompanyName,ISNULL(T4.FirstName+’ ‘+T4.LastName,T4.UserName))
ELSE ISNULL(T4.FirstName+’ ‘+T4.LastName,T4.UserName)
END
+’,’
FOR XML PATH(”)
) AS PostedForNames,
T2.FromUserId AS PostedById,
CASE T3.IsOrganization
WHEN 1 THEN ISNULL(T3.CompanyName,ISNULL(T3.FirstName+’ ‘+T3.LastName,T3.UserName))
ELSE ISNULL(T3.FirstName+’ ‘+T3.LastName,T3.UserName)
END AS PostedByName,
(SELECT COUNT(UserId) FROM dbo.tblMessageInbox T4 WHERE T4.MessageId=T2.MessageId) AS SentToCount,
(
SELECT CASE
WHEN COUNT(UserId)>0 THEN 1
ELSE 0
END
FROM dbo.tblMessageInbox T5
WHERE T5.MessageId=T2.MessageId
AND
T5.IsPending=1
) AS IsPending
FROM dbo.tblMessageDetails T1 LEFT OUTER JOIN dbo.tblMessageInbox T2
ON T1.MessageID=T2.MessageID
LEFT OUTER JOIN uvw_UserCompleteInformation T3
ON T2.FromUserID=T3.UserID
LEFT OUTER JOIN uvw_UserCompleteInformation T4
ON T2.UserID=T4.UserID
WHERE MessageType=’M’
UNION ALL
SELECT
DISTINCT T2.MessageID,
T1.MessageSubject,
T1.MessageBody,
T1.PostedDateTime,
T2.ToUserIDs AS PostedForIds,
T2.ToUserNames AS PostedForNames,
T2.UserID AS PostedById,
CASE T3.IsOrganization
WHEN 1 THEN ISNULL(T3.CompanyName,ISNULL(T3.FirstName + ‘ ‘ + T3.LastName,T3.UserName))
ELSE ISNULL(T3.FirstName + ‘ ‘ + T3.LastName,T3.UserName)
END AS PostedByName,
(SELECT COUNT(*) FROM dbo.fnSplit(T2.ToUserIDs,’,’)) AS SentToCount,
0 AS IsPending
FROM dbo.tblMessageDetails T1 LEFT OUTER JOIN dbo.tblMessageSentbox T2
ON T1.MessageID = T2.MessageID
LEFT OUTER JOIN uvw_UserCompleteInformation T3
ON T2.UserID = T3.UserID
WHERE
T2.MessageID NOT IN
(
SELECT DISTINCT(MessageID) FROM dbo.tblMessageInbox
)
AND T1.MessageType=’M’
) AS T
WHERE
T.PostedForIds IS NOT NULL
AND
T.PostedDateTime BETWEEN @local_FromDate AND @local_ToDate
AND
(
T.MessageSubject LIKE ‘%’+
CASE
WHEN @local_SearchText =”
THEN T.MessageSubject
ELSE @local_SearchText
END + ‘%’
OR
T.MessageBody LIKE ‘%’+
CASE
WHEN @local_SearchText =”
THEN T.MessageBody
ELSE @local_SearchText
END + ‘%’
)
AND
T.PostedByName LIKE ‘%’+
CASE
WHEN @local_Sender =”
THEN T.PostedByName
ELSE @local_Sender
END + ‘%’
AND
T.PostedForNames LIKE ‘%’+
CASE
WHEN @local_Receiver =”
THEN T.PostedForNames
ELSE @local_Receiver
END + ‘%’
AND
IsPending=@local_IsPending
SELECT @MessageCount
END
—
Thank You,
Amol
very usefull information. it really help me .
nice article….suggest me how to become expertise on sql server stored procedures
Hi Pinal,
Could you please tell me the “script ” for long running Queries/Stored procedures from last 24 hrs .It will help us to optimize Stored procedures.
Nice Article….
Nice topic
I face same problem, Please check my Procedure
ALTER PROCEDURE [dbo].[SP_ExtendedPTU]
(
@SQLForPTU as varchar(MAX)
)
AS
BEGIN TRAN
–DECLARE
–@SQLForPTU as varchar(MAX)
–SET @SQLForPTU= ‘WHERE OrderID IN(4233)And OrderType =3’
BEGIN–1
DECLARE
@ProductionTracingUnitID as int,
@OrderID as int,
@OrderType as smallint,
@ProductID as int,
@LabLabDipID as int,
@ColorName as varchar(255),
@PantonNo as varchar(127),
@Shade as varchar(4),
@DyeingOrderQty as decimal(30, 17),
@JobOrderQty as decimal(30, 17),
@ProductionPipeLineQty as decimal(30, 17),
@ProductionFinishedQty as decimal(30, 17),
@DeliveryQty as decimal(30, 17),
@BuyerID as int,
@FactoryID as int,
@ProductionGraceQty as decimal(30, 17),
@WeightLossGainQty as decimal(30, 17),
@RateInLBS as decimal(30, 17),
@State as smallint,
@ProductionLossQty as decimal(30, 17),
@ReturnQty as decimal(30, 17),
@ActualDeliveryQty as decimal(30,17),
@ReadyStockInhand as decimal(30, 17),
@JobOrderQtyApproved as decimal(30, 17),
@OrderNumber as varchar(50),
@FactoryName as varchar(200),
@BuyerName as varchar(200),
@ProductName as varchar(200),
@YetToDelivery as decimal(30,17),
@StockInHand as decimal(30,17),
@YetToProduction as decimal(30,17),
@LCID as int,
@LCNo as varchar(300),
@PIQty as decimal(30,17),
@ChangingQty as decimal(30,17),
@SampleAdjQty as decimal(30,17),
@SampleAdjValue as decimal(30,17),
@MKTPersonID as int,
@MKTPersonName as varchar(500),
@MerchandiserID as int,
@MerchandiserName as varchar(500),
@AmendmentStatus as smallint,
@AcceptanceValue as decimal(30,17),
@MaturityValue as decimal(30,17),
@BillAcceptanceValue as decimal(30,17),
@BillMaturityValue as decimal(30,17),
@BillAcceptancePercentage as decimal(30,17),
@BillMaturityPercentage as decimal(30,17),
@ExportLCValue as decimal(30,17),
@Acceptance as varchar(100),
@Maturity as varchar(100),
@YarnCount as varchar(50),
@PTUDQty as decimal(30,17),
@ShadeFromOrder as smallint,
@EWYDLRelabNo as varchar(100),
@EWYDLColorNo as varchar(100),
@DeliveryTo as int,
@FactoryPersonnelID as int,
@BuyerPersonnelID as int,
@OrderRcvBy as int,
@OrderState as smallint
CREATE TABLE #TempTableOne(
ProductionTracingUnitID int,
OrderID int,
OrderType smallint,
ProductID int,
DyeingOrderQty decimal(30,17),
JobOrderQty decimal(30,17),
ProductionPipeLineQty decimal(30,17),
ProductionFinishedQty decimal(30,17),
DeliveryQty decimal(30,17),
BuyerID int,
FactoryID int,
ProductionGraceQty decimal(30,17),
WeightLossGainQty decimal(30,17),
RateInLBS decimal(30,17),
ProductionLossQty decimal(30,17),
ActualDeliveryQty decimal(30,17),
ReadyStockInhand decimal(30,17),
OrderNumber varchar(50),
FactoryName varchar(200),
BuyerName varchar(200),
ProductName varchar(200),
ColorName varchar(200),
LabLabDipID int,
ReturnQty decimal(30,17),
YetToDelivery decimal(30,17),
StockInHand decimal(30,17),
YetToProduction decimal(30,17),
LCID int,
LCNo varchar(300),
PIQty decimal(30,17),
ChangingQty decimal(30,17),
SampleAdjQty decimal(30,17),
SampleAdjValue decimal(30,17),
MKTPersonID int,
MKTPersonName varchar(500),
MerchandiserID int,
MerchandiserName varchar(500),
AmendmentStatus smallint,
Acceptance varchar(100),
Maturity varchar(100),
YarnCount varchar(50),
EWYDLRelabNo varchar(50),
EWYDLColorNo varchar(50),
ShadeFromOrder smallint
)
–ProductionTracingUnitID,OrderID,OrderType,ProductID,LabLabDipID,ColorName,PantonNo,EWYDLColorNo,Shade,EWYDLRelabNo,DyeingOrderQty,JobOrderQty,ProductionPipeLineQty,ProductionFinishedQty,DeliveryQty,BuyerID,FactoryID,ProductionGraceQty,WeightLossGainQty,RateInLBS,State,ProductionLossQty,ShadeFromOrder,ReturnQty,ActualDeliveryQty,ReadyStockInhand,JobOrderQtyApproved
DECLARE
@SQL as varchar(MAX)
SET @SQL=
‘
DECLARE Cur_AB1 CURSOR GLOBAL FORWARD_ONLY KEYSET FOR
SELECT ProductionTracingUnitID,OrderID,OrderType,ProductID,DyeingOrderQty,JobOrderQty,ProductionPipeLineQty,ProductionFinishedQty,BuyerID,FactoryID,ProductionGraceQty,WeightLossGainQty,RateInLBS,ProductionLossQty,ActualDeliveryQty,ReadyStockInhand,ColorName,LabLabDipID,ReturnQty,EWYDLRelabNo,EWYDLColorNo,ShadeFromOrder FROM ProductionTracingUnit ‘+@SQLForPTU+’
‘
EXEC (@SQL)
OPEN Cur_AB1
FETCH NEXT FROM Cur_AB1 INTO @ProductionTracingUnitID,@OrderID,@OrderType,@ProductID,@DyeingOrderQty,@JobOrderQty,@ProductionPipeLineQty,@ProductionFinishedQty,@BuyerID,@FactoryID,@ProductionGraceQty,@WeightLossGainQty,@RateInLBS,@ProductionLossQty,@ActualDeliveryQty,@ReadyStockInhand,@ColorName,@LabLabDipID,@ReturnQty,@EWYDLRelabNo,@EWYDLColorNo,@ShadeFromOrder
WHILE(@@Fetch_Status -1)
BEGIN–2
SET @LCID=0
SET @LCNo=”
SET @PIQty=0
SET @AcceptanceValue =0
SET @MaturityValue= 0
SET @Acceptance= ”
SET @Maturity =”
SET @DeliveryQty=@ActualDeliveryQty-@ReturnQty
SET @YetToDelivery=@JobOrderQty-@ActualDeliveryQty+@ReturnQty
set @PTUDQty=(select sum(Qty) from PTUDistribution where ProductionTracingUnitID=@ProductionTracingUnitID )
IF(@PTUDQty>@YetToDelivery)
BEGIN–sih
SET @StockInHand =@YetToDelivery
END –sih
ELSE
BEGIN–sih2
SET @StockInHand =@PTUDQty
END –sih2
SET @YetToProduction=@JobOrderQty-@ReadyStockInhand-@ActualDeliveryQty+@ReturnQty
IF (@YetToProduction0 and @ExportLCValue is not null)
BEGIN
SET @BillAcceptancePercentage =(@BillAcceptanceValue*100)/@ExportLCValue — bill Percentage
SET @BillMaturityPercentage =(@BillMaturityValue*100)/@ExportLCValue
SET @AcceptanceValue=(@ChangingQty*@RateInLBS)*(@BillAcceptancePercentage/100)–Percentage Wise PI Valu
SET @MaturityValue=(@ChangingQty*@RateInLBS)*(@BillMaturityPercentage/100)
IF((@ChangingQty*@RateInLBS)>0 and (@ChangingQty*@RateInLBS) is not null)
BEGIN
SET @AcceptanceValue=(@AcceptanceValue*100)/(@ChangingQty*@RateInLBS)– PI ValuePercentage
SET @MaturityValue=(@MaturityValue*100)/(@ChangingQty*@RateInLBS)
END
SET @Acceptance=Convert(varchar(20),(CONVERT(float,round((@AcceptanceValue+@MaturityValue),0)))) +’%’
SET @Maturity =Convert(varchar(20),(CONVERT(float,round(@MaturityValue,0)))) +’%’
END
SET @FactoryName=”
IF(@FactoryID>0)
BEGIN–jam3
SET @FactoryName=(SELECT [Name] FROM Contractor WHERE ContractorID=@FactoryID)
END–jam3
SET @BuyerName=”
IF(@BuyerID>0)
BEGIN–jam4
SET @BuyerName=(SELECT [Name] FROM Contractor WHERE ContractorID=@BuyerID)
END–jam4
END–jam1
ELSE
BEGIN –jam2 IF Sample
SET @LCID=0
SET @LCNo=”
SET @OrderNumber=”
SET @PIQty=0
SET @DeliveryTo=0
SET @FactoryPersonnelID=0
SET @BuyerPersonnelID=0
SET @OrderRcvBy=0
SET @ChangingQty=0
SET @MerchandiserName=”
SET @MKTPersonName=”
SET @OrderState=0
SET @AmendmentStatus=0
SET @PIQty= (SELECT ISNULL(SUM(Qty),0) FROM SampleOrderDetail WHERE PTUID=@ProductionTracingUnitID)
SET @ChangingQty=@PIQty
SELECT @OrderNumber=ISNULL(SampleOrderNo,”), @DeliveryTo=ISNULL(DeliveryTo,0),@FactoryPersonnelID=ISNULL(FactoryPersonnelID,0),@BuyerPersonnelID=ISNULL(BuyerPersonnelID,0),@OrderRcvBy=ISNULL(OrderRcvBy,0),@OrderState=ISNULL(OrderState,0) FROM SampleOrder WHERE SampleOrderID=@OrderID
SET @AmendmentStatus=@OrderState
IF(@DeliveryTo=3)
BEGIN
SET @MerchandiserName=(SELECT ISNULL([Name],”) FROM ContactPersonnel WHERE ContactPersonnelID=@FactoryPersonnelID)
END
IF(@DeliveryTo=2)
BEGIN
SET @MerchandiserName=(SELECT ISNULL([Name],”) FROM ContactPersonnel WHERE ContactPersonnelID=@BuyerPersonnelID)
END
SET @MKTPersonName =(SELECT [Name] FROM Employee WHERE EmployeeID=@OrderRcvBy)
IF(@DeliveryTo=3)
BEGIN
SET @FactoryName=(SELECT [Name] FROM Contractor WHERE ContractorID=@FactoryID)
END
IF(@DeliveryTo=2)
BEGIN
SET @FactoryName=(SELECT [Name] FROM Contractor WHERE ContractorID=@BuyerID)
END
SET @BuyerName=”
IF(@BuyerID>0)
BEGIN–jam4
SET @BuyerName=(SELECT [Name] FROM Contractor WHERE ContractorID=@BuyerID)
END–jam4
END–jam2
SET @ProductName=”
SET @YarnCount =”
SELECT @ProductName=(‘[‘+ Code+ ‘] ‘+ [Name]), @YarnCount =[Count] FROM Yarncategory WHERE YarncategoryID=@ProductID
INSERT INTO #TempTableOne Values(ISNULL(@ProductionTracingUnitID,0),ISNULL(@OrderID,0),ISNULL(@OrderType,0),ISNULL(@ProductID,0),ISNULL(@DyeingOrderQty,0),ISNULL(@JobOrderQty,0),ISNULL(@ProductionPipeLineQty,0),ISNULL(@ProductionFinishedQty,0),ISNULL(@DeliveryQty,0),ISNULL(@BuyerID,0),ISNULL(@FactoryID,0),ISNULL(@ProductionGraceQty,0),ISNULL(@WeightLossGainQty,0),ISNULL(CONVERT (decimal(18,2),@RateInLBS),0),ISNULL(@ProductionLossQty,0),ISNULL(@ActualDeliveryQty,0),ISNULL(@ReadyStockInhand,0),ISNULL(@OrderNumber,”),ISNULL(@FactoryName,”),ISNULL(@BuyerName,”),ISNULL(@ProductName,”),ISNULL(@ColorName,”),ISNULL(@LabLabDipID,0),ISNULL(@ReturnQty,0),ISNULL(@YetToDelivery,0),ISNULL(@StockInHand,0),ISNULL(@YetToProduction,0),ISNULL(@LCID,0),ISNULL(@LCNo,”),ISNULL(@PIQty,0),ISNULL(@ChangingQty,0),ISNULL(@SampleAdjQty,0),ISNULL(@SampleAdjValue,0),ISNULL(@MKTPersonID,0),ISNULL(@MKTPersonName,”),ISNULL(@MerchandiserID,0),ISNULL(@MerchandiserName,”),ISNULL(@AmendmentStatus,0),ISNULL(@Acceptance,”),ISNULL(@Maturity,”),ISNULL(@YarnCount,”),ISNULL(@EWYDLRelabNo,”),ISNULL(@EWYDLColorNo,”),ISNULL(@ShadeFromOrder,0))
FETCH NEXT FROM Cur_AB1 INTO @ProductionTracingUnitID,@OrderID,@OrderType,@ProductID,@DyeingOrderQty,@JobOrderQty,@ProductionPipeLineQty,@ProductionFinishedQty,@BuyerID,@FactoryID,@ProductionGraceQty,@WeightLossGainQty,@RateInLBS,@ProductionLossQty,@ActualDeliveryQty,@ReadyStockInhand,@ColorName,@LabLabDipID,@ReturnQty,@EWYDLRelabNo,@EWYDLColorNo,@ShadeFromOrder
END–2
CLOSE Cur_AB1
DEALLOCATE Cur_AB1
SELECT * FROM #TempTableOne Order By OrderID
–Group By Product
SELECT ProductID,ProductName, YarnCount, SUM(PIQty) as PIQty, SUM(ChangingQty) AS ChangingQty, SUM(SampleAdjQty) AS SampleAdjQty, SUM(SampleAdjValue) as SampleAdjValue, SUM(DyeingOrderQty) as DyeingOrderQty,SUM(JobOrderQty)AS JobOrderQty,SUM(ProductionPipeLineQty)as ProductionPipeLineQty,SUM(ProductionFinishedQty) as ProductionFinishedQty,SUM(DeliveryQty)as DeliveryQty,SUM(ProductionGraceQty)AS ProductionGraceQty,SUM(WeightLossGainQty) as WeightLossGainQty,SUM(ProductionLossQty)as ProductionLossQty,SUM(ActualDeliveryQty)as ActualDeliveryQty,SUM(ReadyStockInhand)as ReadyStockInhand, SUM(ReturnQty) AS ReturnQty,SUM(YetToDelivery)AS YetToDelivery,SUM(StockInHand)AS StockInHand,SUM(YetToProduction)AS YetToProduction FROM #TempTableOne GROUP BY ProductID,ProductName,YarnCount Order By ProductID
–Group By Factory
SELECT FactoryID,FactoryName,SUM(PIQty) as PIQty, SUM(ChangingQty) AS ChangingQty, SUM(SampleAdjQty) AS SampleAdjQty, SUM(SampleAdjValue) as SampleAdjValue, SUM(DyeingOrderQty) as DyeingOrderQty,SUM(JobOrderQty)AS JobOrderQty,SUM(ProductionPipeLineQty)as ProductionPipeLineQty,SUM(ProductionFinishedQty) as ProductionFinishedQty,SUM(DeliveryQty)as DeliveryQty,SUM(ProductionGraceQty)AS ProductionGraceQty,SUM(WeightLossGainQty) as WeightLossGainQty,SUM(ProductionLossQty)as ProductionLossQty,SUM(ActualDeliveryQty)as ActualDeliveryQty,SUM(ReadyStockInhand)as ReadyStockInhand, SUM(ReturnQty) AS ReturnQty,SUM(YetToDelivery)AS YetToDelivery,SUM(StockInHand)AS StockInHand,SUM(YetToProduction)AS YetToProduction FROM #TempTableOne GROUP BY FactoryID,FactoryName
–Group By Order
SELECT OrderID,OrderNumber,LCNo,FactoryID,FactoryName,BuyerID,BuyerName,SUM(PIQty) AS PIQty,SUM(ChangingQty) AS ChangingQty,SUM(SampleAdjQty) AS SampleAdjQty,SUM(SampleAdjValue) AS SampleAdjValue,MKTPersonID,MKTPersonName,MerchandiserID,MerchandiserName,AmendmentStatus,Acceptance,Maturity,SUM(DyeingOrderQty) as DyeingOrderQty,SUM(JobOrderQty)AS JobOrderQty,SUM(ProductionPipeLineQty)as ProductionPipeLineQty,SUM(ProductionFinishedQty) as ProductionFinishedQty,SUM(DeliveryQty)as DeliveryQty,SUM(ProductionGraceQty)AS ProductionGraceQty,SUM(WeightLossGainQty) as WeightLossGainQty,SUM(ProductionLossQty)as ProductionLossQty,SUM(ActualDeliveryQty)as ActualDeliveryQty,SUM(ReadyStockInhand)as ReadyStockInhand, SUM(ReturnQty) AS ReturnQty,SUM(YetToDelivery)AS YetToDelivery,SUM(StockInHand)AS StockInHand,SUM(YetToProduction)AS YetToProduction FROM #TempTableOne GROUP BY OrderID,OrderNumber,OrderNumber,LCNo,FactoryID,FactoryName,BuyerID,BuyerName,MKTPersonID,MKTPersonName,MerchandiserID,MerchandiserName,AmendmentStatus,Acceptance,Maturity
–Default View
SELECT OrderID,OrderNumber,LCNo,FactoryID,FactoryName,BuyerID,BuyerName,ProductID,ProductName,YarnCount,PIQty,ChangingQty,SampleAdjQty,SampleAdjValue,RateInLBS,MKTPersonID,MKTPersonName,MerchandiserID,MerchandiserName,AmendmentStatus,Acceptance,Maturity, SUM(DyeingOrderQty) as DyeingOrderQty,SUM(JobOrderQty)AS JobOrderQty,SUM(ProductionPipeLineQty)as ProductionPipeLineQty,SUM(ProductionFinishedQty) as ProductionFinishedQty,SUM(DeliveryQty)as DeliveryQty,SUM(ProductionGraceQty)AS ProductionGraceQty,SUM(WeightLossGainQty) as WeightLossGainQty,SUM(ProductionLossQty)as ProductionLossQty,SUM(ActualDeliveryQty)as ActualDeliveryQty,SUM(ReadyStockInhand)as ReadyStockInhand, SUM(ReturnQty) AS ReturnQty,SUM(YetToDelivery)AS YetToDelivery,SUM(StockInHand)AS StockInHand,SUM(YetToProduction)AS YetToProduction FROM #TempTableOne GROUP BY OrderID,OrderNumber,LCNo,FactoryID,FactoryName,BuyerID,BuyerName,ProductID,ProductName,YarnCount,PIQty,ChangingQty,SampleAdjQty,SampleAdjValue,RateInLBS,MKTPersonID,MKTPersonName,MerchandiserID,MerchandiserName,AmendmentStatus,Acceptance,Maturity
DROP TABLE #TempTableOne
END–1
COMMIT TRAN
Note : At a time i want to get more than 500000 record from this store procedure .
Hi Pinal,
I am required to write stored procedure in sql server to get:
-the number of sessions on daily and hourly basis
-number of unique users
-Number of first time users.
i have a table with 100’s of rows & columns :
RequestID,ApplicationID,SessionID,UserId,UserName,RequestURL,RequestTimeStamp,InsertTimestampGMT,UpdateTimestampGMT,Idx
Please help!
Nice and simple article. Really helpful for those who constantly look for improvement.
Hi,
is there any difference between
select top 1 * from table where condition
and
select 1 from table where condition
“select Top 1 * ” returns 1 row which satisfies the given condition.
where as “Select 1” returns Number 1 if there is a record in that table which satisfies the given condition
Thanks @vara