Query taking a long time - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Query taking a long time

Author

Topic

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-07-28 : 13:35:16

I have this query that takes 1 hour to execute, this query is pretty old and no one has worked on it before .Most of the time is spent on the select case. Im not too sure of the business logic but have been asked to tune this sp.What can be done to simplfy this query, where can I replace with temp tables ?

Temp Table #O & #D gets created before the below code.
I am unable to share more info then this, Ive identified this SELECT Case as taking the longest by using SQLSENTRY

-****************************************************************-
"Other Business Logic"

UNION

SELECT DISTINCT B.I_KEY, B.A_T_KEY, B.PRODUCT_KEY
,EFFEC_DT =
CASE
WHEN (( SELECT COUNT(*)
FROM #O IA
WHERE A.I_KEY = IA.I_KEY
AND A.A_T_KEY = IA.A_T_KEY
AND (ISNULL(B.PRODUCT_KEY, 0) = ISNULL(IA.PRODUCT_KEY, 0) OR ISNULL(IA.PRODUCT_KEY, 0) = 0)
AND IA.EFFEC_DT < A.EFFEC_DT
AND IA.END_DT IS NOT NULL) > 0
)
THEN
(SELECT MAX(IA.END_DT)
FROM #O IA
WHERE A.I_KEY = IA.I_KEY
AND A.A_T_KEY = IA.A_T_KEY
AND (ISNULL(B.PRODUCT_KEY, 0) = ISNULL(IA.PRODUCT_KEY, 0) OR ISNULL(IA.PRODUCT_KEY, 0) = 0)
AND IA.EFFEC_DT < A.EFFEC_DT
AND IA.END_DT IS NOT NULL
) + 1
ELSE (
SELECT MAX(IA.EFFEC_DT)
FROM #D IA
WHERE A.I_KEY = IA.I_KEY
AND A.A_T_KEY = IA.A_T_KEY
AND (
ISNULL(B.PRODUCT_KEY, 0) = ISNULL(IA.PRODUCT_KEY, 0)
OR ISNULL(IA.PRODUCT_KEY, 0) = 0
)
AND IA.EFFEC_DT < A.EFFEC_DT
AND ISNULL(IA.END_DT, '12/31/9999') > A.EFFEC_DT
)
END
, END_DT = CASE
WHEN A.EFFEC_DT = '1/1/1753'
THEN A.EFFEC_DT
ELSE (SELECT MIN(IA.EFFEC_DT) FROM #O IA WHERE A.I_KEY = IA.I_KEY AND A.A_T_KEY = IA.A_T_KEY AND (ISNULL(B.PRODUCT_KEY, 0) = ISNULL(IA.PRODUCT_KEY, 0) OR ISNULL(b.PRODUCT_KEY, 0) = 0 OR ISNULL(ia.PRODUCT_KEY, 0) = 0) AND IA.EFFEC_DT > B.EFFEC_DT) - 1
END
FROM #O A
INNER JOIN #D B
ON A.I_KEY = B.I_KEY
AND A.A_T_KEY = B.A_T_KEY
AND A.EFFEC_DT BETWEEN B.EFFEC_DT AND ISNULL(B.END_DT, A.EFFEC_DT)
AND A.EFFEC_DT <> B.EFFEC_DT

AND (CASE
WHEN (( SELECT COUNT(*)
FROM #O IA
WHERE A.I_KEY = IA.I_KEY
AND A.A_T_KEY = IA.A_T_KEY
AND (ISNULL(B.PRODUCT_KEY, 0) = ISNULL(IA.PRODUCT_KEY, 0) OR ISNULL(IA.PRODUCT_KEY, 0) = 0)
AND IA.EFFEC_DT < A.EFFEC_DT
AND IA.END_DT IS NOT NULL) > 0 )

THEN
(SELECT MAX(IA.END_DT)
FROM #O IA WHERE A.I_KEY = IA.I_KEY
AND A.A_T_KEY = IA.A_T_KEY
AND (ISNULL(B.PRODUCT_KEY, 0) = ISNULL(IA.PRODUCT_KEY, 0) OR ISNULL(IA.PRODUCT_KEY, 0) = 0)
AND IA.EFFEC_DT < A.EFFEC_DT
AND IA.END_DT IS NOT NULL
) + 1
ELSE (
SELECT MAX(IA.EFFEC_DT)
FROM #D IA
WHERE A.I_KEY = IA.I_KEY
AND A.A_T_KEY = IA.A_T_KEY
AND (ISNULL(B.PRODUCT_KEY, 0) = ISNULL(IA.PRODUCT_KEY, 0) OR ISNULL(IA.PRODUCT_KEY, 0) = 0)
AND IA.EFFEC_DT < A.EFFEC_DT
AND ISNULL(IA.END_DT, '12/31/9999') > A.EFFEC_DT)
END) < (CASE
WHEN A.EFFEC_DT = '1/1/1753'
THEN A.EFFEC_DT
ELSE (SELECT MIN(IA.EFFEC_DT) FROM #ORG_SRC IA WHERE A.I_KEY = IA.I_KEY AND A.A_T_KEY = IA.A_T_KEY AND (ISNULL(B.PRODUCT_KEY, 0) = ISNULL(IA.PRODUCT_KEY, 0) OR ISNULL(b.PRODUCT_KEY, 0) = 0 OR ISNULL(ia.PRODUCT_KEY, 0) = 0) AND IA.EFFEC_DT > B.EFFEC_DT) - 1
END)

UNION

"Other Business Logic"

-****************************************************************-

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-28 : 14:12:44

It's not the CASE that's slow, it's the multiple subqueries inside them that's slow.

Can you describe what all this is trying to accomplish? I honestly can't tell what this is doing, and am surprised that it actually parses.

I'd ask if you could post sample data and expected output, because it will be impossible to construct a working alternative without it.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-07-28 : 14:53:58

Its a rather complicated business logic, and it has been running without any issue for many years.Only problem is the amount of time that it is consuming is about 1 hour for just this part and we would like to minimize that time.

Im getting the table and some sample data information ready and will post my updates.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-07-28 : 15:20:07

Time for some gasoline and a match. I'd run the query to see what the result set produces and then ask myself, "How do I get that?" And start out refresh, without somebody else's working kludge preventing you from writing a better query.

Jim

Everyday I learn something that somebody else already knew

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-28 : 15:23:37

quote:
Originally posted by jimf

Time for some gasoline and a match. I'd run the query to see what the result set produces and then ask myself, "How do I get that?" And start out refresh, without somebody else's working kludge preventing you from writing a better query.

I agree. I've used up all the Kleenex I had trying to stop my eyes from bleeding.

LOOKUP_BI
Constraint Violating Yak Guru

295 Posts

Posted - 2011-07-28 : 15:49:49

I agree, Im dissecting the query and will repost once I figure out more or If I hit a dead end

Thanks Folks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources