Hi,
I have about 10 tables in my Database. One of the table has about 70 fields in it and rest has about 10 to 30 fields.
Now, I am writing a query to get all the fields, also with so many different calculations depending upon different fields, so basically while returning from that Query, I have about 160 fields. I need to return everything from my Stored Procedure itself
as I have lots of calculations as per my system's functionality and I want to have all these calculations returned from my stored procedure itself so that I can use the same stored procedure all over my application and that's what is working perfect except
just one problem.
Now, the problem is that with about 2000 rows in my Table1, this stored procedure is taking about 2-3 seconds to execute. So I am looking for some efficient way to execute my stored procedure with this query.
In my stored procedure, first I am executing a big query with about 150 fields and inserting into a temptable. Then again running a query with calculation fields from that temptable and putting it into another temptable. And finally executing query from
that second temptable. The reason I am doing this way is because of my calculations.
So what I am looking for is, I will need to return all the columns with calculations as it is but I am looking for the best efficient way to write query inside this stored procedure. At present, as you see, first I am calcuating something and putting everything
in a temp table. Than again depending upon the values of these temp table, I am calculating and putting into another temptable and finally I am quering that second temptable with filter criteria.
At present, with about 2000 rows, this query is taking about 2-3 seconds to execute which will be problem once my records reach around 10000 rows so I want to optimize this stored procedure.
Will appreciate if someone can help me to find out the best approach to optimize this stored procedure.
Herewith I am providing my Stored procedure Stored Procedure Script : ( modified to fit here, added Table1.*, instead of all the fields one by one just to paste here, actually I have all the fields there ):
DECLARE @FilterExpression VARCHAR(MAX)
BEGIN
IF OBJECT_ID('tempdb..#tempTable1') IS NOT NULL
BEGIN
DROP TABLE #tempTable1;
END
IF OBJECT_ID('tempdb..#tempTableForCalculation') IS NOT NULL
BEGIN
DROP TABLE #tempTableForCalculation;
END
SELECT
CASE
WHEN ( ISNULL (Table3.Under5000Email, '' ) <> '' )
AND
( ISNULL ( Table2.Under5000ApprovedORRejected, 0 ) <> @ApprovedStatusID )
THEN Table3.Under5000Email
WHEN ( ISNULL (Table3.Under5000Email, '' ) <> '' )
AND
( ISNULL ( Table2.Under5000ApprovedORRejected, 0 ) <> @ApprovedStatusID )
AND
( ISNULL ( Table2.Under5000AssignedForApproval , 0 ) = 1 )
THEN Table3.Under5000Email
ELSE ''
END AS NextApprovalRequiredFromEmailAddress ,
Table1.* ,
Table2.Under5000ApprovedORRejected,
Table2.Over5000ApprovedORRejected,
Table2.FinanceApprovedORRejected ,
Table2.Over25000CFOApprovedORRejected,
Table2.Over25000COOApprovedORRejected,
Table2.Over25000CEOApprovedORRejected,
CASE
WHEN ( ISNULL ( Table2.Over25000CEOApprovedORRejected , 0 ) = @ApprovedStatusID )
THEN 'Approved'
WHEN ( ISNULL ( Table2.Over25000CEOApprovedORRejected , 0 ) = @RejectedStatusID )
THEN 'Rejected'
ELSE
''
END AS Over25000CEOApprovedORRejected_Text,
Under5000User.UserDisplayName AS Under5000_ApprovedORRejectedBy_UserDisplayName ,
Over5000User.UserDisplayName AS Over5000_ApprovedORRejectedBy_UserDisplayName,
FinanceUser.UserDisplayName AS Finance_ApprovedORRejectedBy_UserDisplayName,
Over25000CFOUser.UserDisplayName AS Over25000CFO_ApprovedORRejectedBy_UserDisplayName,
Over25000COOUser.UserDisplayName AS Over25000COO_ApprovedORRejectedBy_UserDisplayName,
Over25000CEOUser.UserDisplayName AS Over25000CEO_ApprovedORRejectedBy_UserDisplayName ,
CASE
WHEN (
(
ISNULL ( Table1.Completed , 0 ) = 1
OR
ISNULL ( Table1.Cancelled , 0 ) = 1
)
AND
ISNULL ( Table2.Over25000CFOApprovedORRejected , 0 ) > 0
AND
Over25000CFOUser.EmailAddress <> Table3_History_For_RequestInfo.Over25000CFOEmail
AND
ISNULL ( Over25000CFOUser.EmailAddress, '' ) <> ''
AND
ISNULL ( Table3_History_For_RequestInfo.Over25000CFOEmail, '' ) = ''
)
THEN
Over25000CFOUser.UserDisplayName
ELSE
''
END AS Over25000CFO_ApprovedORRejectedBy_UserDisplayName_Calculated ,
CASE
WHEN (
ISNULL ( Table1.Completed , 0 ) = 0
AND
ISNULL ( Table1.Cancelled , 0 ) = 0
AND
ISNULL ( Table2.Over25000CEOApprovedORRejected , 0 ) > 0
AND
Over25000CEOUser.EmailAddress <> Table3.Over25000CEOEmail
AND
ISNULL ( Over25000CEOUser.EmailAddress, '' ) <> ''
AND
ISNULL ( Table3.Over25000CEOEmail, '' ) = ''
)
THEN
Over25000CEOUser.UserDisplayName
ELSE
''
END AS Over25000CEO_ApprovedORRejectedBy_UserDisplayName_Calculated ,
ISNULL ( Table1.IsSubmitted , 0 ) AS isSubmitted ,
CASE
WHEN ISNULL ( Table1.IsSubmitted , 0 ) = 1
THEN 'Submitted'
ELSE
'Not Submitted'
END AS isSubmitted_Status_Display_Text,
Table1.SubmittedDate
INTO
#tempTable1
FROM
Table1
INNER JOIN Table2 ON
Table2.RequestInfoID = Table1.RequestInfoID
LEFT OUTER JOIN Table3 ON
Table3.UserID = Table1.UserID
LEFT OUTER JOIN Table3_History_For_RequestInfo ON
Table3_History_For_RequestInfo.UserID = Table1.UserID
AND
Table3_History_For_RequestInfo.RequestInfoID = Table1.RequestInfoID
LEFT OUTER JOIN tblUserInfo ON
tblUserInfo.UserID = Table1.UserID
LEFT OUTER JOIN tblUserInfo Under5000User ON
Under5000User.UserID = Table2.Under5000ApprovedORRejectedByUserID
LEFT OUTER JOIN tblUserInfo Over5000User ON
Over5000User.UserID = Table2.Over5000ApprovedORRejectedByUserID
LEFT OUTER JOIN tblUserInfo FinanceUser ON
FinanceUser.UserID = Table2.FinanceApprovedORRejectedByUserID
LEFT OUTER JOIN tblUserInfo Over25000CFOUser ON
Over25000CFOUser.UserID = Table2.Over25000CFOApprovedORRejectedByUserID
LEFT OUTER JOIN tblUserInfo Over25000COOUser ON
Over25000COOUser.UserID = Table2.Over25000COOApprovedORRejectedByUserID
LEFT OUTER JOIN tblUserInfo Over25000CEOUser ON
Over25000CEOUser.UserID = Table2.Over25000CEOApprovedORRejectedByUserID
LEFT OUTER JOIN tblPurchaseCategory ON
tblPurchaseCategory.PurchaseCategoryID = Table1.PurchaseCategoryID
LEFT OUTER JOIN tblCurrency ON
tblCurrency.CurrencyID = Table1.CurrencyID
LEFT OUTER JOIN tblCompany ON
tblCompany.CompanyID = Table1.CompanyID
LEFT OUTER JOIN tblDepartment ON
tblDepartment.DepartmentID = Table1.DepartmentID
LEFT OUTER JOIN tblRegion ON
tblRegion.RegionID = Table1.RegionID
LEFT OUTER JOIN tblUserInfo Table_VerifiedBy_FinanceUser ON
Table_VerifiedBy_FinanceUser.UserID = Table1.ReviewedByFinanceUserID
SELECT
#tempTable1.* ,
CASE
WHEN (
ISNULL ( TotalAmount , 0 ) < 5000
)
THEN
''
ELSE
Under5000ApprovedORRejected_Text +
' by ' +
Under5000_ApprovedORRejectedBy_UserDisplayName_Calculated +
' on ' +
CONVERT(varchar(12) , Under5000DateApprovedORRejected , 101 )
END AS Display_Under5000ApprovedORRejected_Full_Text ,
INTO #tempTableForCalculation
FROM
#tempTable1
DECLARE @strSQL VARCHAR(MAX);
SET @strSQL =
'
SELECT * FROM #tempTableForCalculation
' ;
IF ( ISNULL ( @FilterExpression , '' ) <> '' )
BEGIN
SET @strSQL = @strSQL + ' WHERE ' + @FilterExpression ;
END
EXEC ( @strSQL ) ;
IF OBJECT_ID('tempdb..#tempTable1') IS NOT NULL
BEGIN
DROP TABLE #tempTable1;
END
IF OBJECT_ID('tempdb..#tempTableForCalculation') IS NOT NULL
BEGIN
DROP TABLE #tempTableForCalculation;
END
END
Thanks in advance,
