June 16, 2015 at 3:12 am
Hi Team,
Below query is taking over 2.5 minz due to which we are getting timeout.
SELECT PPS.No, PPS.PID, PPS.Date,
PPS.Amnt1 + PPS.AMNT2 + PPS.Amnt3 - ISNULL(PPSL.Amnt4,0) + PPS.Amnt5
AS Amnts
FROM TPSTab PPS
INNER JOIN TTCTAB C
ON PPS.Date > @dttDate AND PPS.DATE <= @dtMDate
JOIN TPSTab_1 PPSL ON PPSL.NO = PPS.NO AND
PPSL.PID = PPS.PID AND ISNULL(PPSL.Ex,'false') <> 'true'
AND C.Meth = N''DEF''
AND PPS.No = C.NO AND C.stat = ''Active'' AND PPS.Rstat = N'A' AND
C.RECORD_STATUS = N''RCACT''
AND PPS.AMNT1 + PPS.AMNT2 + PPS.AMNT3 - ISNULL(PPSL.amnt4,0) + PPS.amnt5 > 0 INNER JOIN TSD_TAB CSD ON
CSD.Col = 112345
AND C.NO = CSD.NO
JOIN TTC_TAb2 CLCL ON CLCL.NO = CSD._NO AND CLCL.SPD = 'XYZ'
WHERE
(
1 = CASE
WHEN PPS.DATE =
(
SELECT MIN(P.DATE)
FROM TPSTab P
WHERE P.No = C.No
AND (P.Amnt1 + P.AMNT2 + P.AMNT3) > 0
AND CLCL.FPM ='ABC'
)
THEN 2
ELSE 1
END
)
The where clause is taking lot of time on this, how can i improve this query, any pointers or suggestions would help.
Also there are enough indexes on the tables, so creating a index is not a solution for now.
And this query is executed from code and not via SP, will converting into a SP will fasten this? as we can make use of temp tables.
Regards.
GCIT
June 16, 2015 at 3:28 am
GonnaCatchIT (6/16/2015)
Hi Team,Below query is taking over 2.5 minz due to which we are getting timeout.
SELECT PPS.No, PPS.PID, PPS.Date,
PPS.Amnt1 + PPS.AMNT2 + PPS.Amnt3 - ISNULL(PPSL.Amnt4,0) + PPS.Amnt5
AS Amnts
FROM TPSTab PPS
INNER JOIN TTCTAB C
ON PPS.Date > @dttDate AND PPS.DATE <= @dtMDate
JOIN TPSTab_1 PPSL ON PPSL.NO = PPS.NO AND
PPSL.PID = PPS.PID AND ISNULL(PPSL.Ex,'false') <> 'true'
AND C.Meth = N''DEF''
AND PPS.No = C.NO AND C.stat = ''Active'' AND PPS.Rstat = N'A' AND
C.RECORD_STATUS = N''RCACT''
AND PPS.AMNT1 + PPS.AMNT2 + PPS.AMNT3 - ISNULL(PPSL.amnt4,0) + PPS.amnt5 > 0 INNER JOIN TSD_TAB CSD ON
CSD.Col = 112345
AND C.NO = CSD.NO
JOIN TTC_TAb2 CLCL ON CLCL.NO = CSD._NO AND CLCL.SPD = 'XYZ'
WHERE
(
1 = CASE
WHEN PPS.DATE =
(
SELECT MIN(P.DATE)
FROM TPSTab P
WHERE P.No = C.No
AND (P.Amnt1 + P.AMNT2 + P.AMNT3) > 0
AND CLCL.FPM ='ABC'
)
THEN 2
ELSE 1
END
)
The where clause is taking lot of time on this, how can i improve this query, any pointers or suggestions would help.
Also there are enough indexes on the tables, so creating a index is not a solution for now.
And this query is executed from code and not via SP, will converting into a SP will fasten this? as we can make use of temp tables.
Regards.
GCIT
Can you post the Actual Execution Plan as a .sqlplan attachment please? Also ddl for the tables.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 16, 2015 at 4:21 am
Hi,
Unfortunately I do not have the execution plans handy , so I am not able to share it.
June 16, 2015 at 4:25 am
GonnaCatchIT (6/16/2015)
Hi,Unfortunately I do not have the execution plans handy , so I am not able to share it.
So if someone posts up some fantastic code for you, you're unable to test it?
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 16, 2015 at 4:35 am
-- Here's your query with some structural changes which I've made to
-- assist tuning.
SELECT
PPS.[No],
PPS.PID,
PPS.[Date],
PPS.Amnt1 + PPS.AMNT2 + PPS.Amnt3 - ISNULL(PPSL.Amnt4,0) + PPS.Amnt5
AS Amnts
FROM TPSTab PPS
INNER JOIN TTCTAB C
ON C.[NO] = PPS.[No]
JOIN TPSTab_1 PPSL
ON PPSL.[NO] = PPS.[NO]
AND PPSL.PID = PPS.PID
INNER JOIN TSD_TAB CSD
ON CSD.[NO] = PPS.[NO]
JOIN TTC_TAb2 CLCL
ON CLCL.[NO] = CSD._NO
OUTER APPLY (
SELECT
MIN_P_DATE = MIN(P.[DATE])
FROM TPSTab P
WHERE
P.[No] = PPS.[No] -- outer ref
AND (P.Amnt1 + P.AMNT2 + P.AMNT3) > 0
AND CLCL.FPM = 'ABC' -- outer ref
) x
WHERE
PPS.[DATE] <= @dtMDate
AND PPS.[Date] > @dttDate
AND PPS.[DATE] <> x.MIN_P_DATE
AND PPS.Rstat = N'A'
AND PPS.AMNT1 + PPS.AMNT2 + PPS.AMNT3 - ISNULL(PPSL.amnt4,0) + PPS.amnt5 > 0
AND CLCL.SPD = 'XYZ'
AND (PPSL.Ex = 'false' OR PPSL.Ex IS NULL)
AND C.Meth = N'DEF'
AND C.stat = 'Active'
AND C.RECORD_STATUS = N'RCACT'
AND CSD.Col = 112345
-- This is a "show and tell" of an alternative method to the correlated subquery filter which you have
-- in your WHERE clause. It's not necessary to read the TPSTab table twice, you could incorporate
-- a MIN() OVER() similar to this into your query and filter on the result in an outer SELECT.
-- "GETDATE()+300" is an arbitrary spoof max date, use something sensible for your data.
SELECT
*,
MIN_P_DATE = MIN(CASE WHEN Amnt1 > 0 AND FPM = 'ABC' THEN [DATE] ELSE (GETDATE()+300) END)
OVER (PARTITION BY [No])
FROM (VALUES
(7, 1, GETDATE()-0, 10, 'ABC'),
(7, 2, GETDATE()-1, 10, 'ABC'),
(7, 3, GETDATE()-2, 10, 'NO'),
(7, 4, GETDATE()-3, 10, 'ABC'),
(7, 5, GETDATE()-4, 10, 'ABC'), -- Choose this row, earliest with Amnt1 > 0 AND FPM = 'ABC'
(7, 6, GETDATE()-5, -10, 'ABC'),
(7, 7, GETDATE()-6, 10, 'CBA')
) d ([No], Pid, [DATE], Amnt1, FPM)
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
June 16, 2015 at 5:00 am
Thanks Chris, I will check and build my query based on this , will provide you the results. Thank you
June 16, 2015 at 5:02 am
GonnaCatchIT (6/16/2015)
Thanks Chris, I will check and build my query based on this , will provide you the results. Thank you
It's a long shot without the execution plan and table ddl.
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply