SQL Server Performance Forum – Threads Archive
Complex Query Fine Tuning
The following query takes a lot of time to execute.Can anybody help me identify the bottleneck?how to check whether correct indexes are used by the query? SELECTBT.ROW_ID,BT.PR_POSTN_ID,BT.LAST_UPD,BT.LAST_UPD_BY,BT.MODIFICATION_NUM,
BT.ROW_ID,
(CASE WHEN BT.ROW_ID IN
(SELECTT_POSTN_CON_CON_ID C1
FROMdbo.EIM_CONTACT1, dbo.S_CONTACT BT
WHERE(
CON_PR_POSTN = ‘Y’ AND T_POSTN_CON_CON_ID IS NOT NULL AND
IF_ROW_BATCH_NUM = 100 AND T_POSTN_CON__STA = 0 AND
T_POSTN_CON__RID IS NOT NULL)
GROUP BY T_POSTN_CON_CON_ID
) THEN
(SELECTMIN(T_POSTN_CON_POSTNI) C1 FROM dbo.EIM_CONTACT1, dbo.S_CONTACT BT
WHERE(
T_POSTN_CON_CON_ID = BT.ROW_ID AND CON_PR_POSTN = ‘Y’ AND
T_POSTN_CON_CON_ID IS NOT NULL AND IF_ROW_BATCH_NUM = 100 AND
T_POSTN_CON__STA = 0 AND T_POSTN_CON__RID IS NOT NULL)
)
ELSE
(CASE WHEN BT.PR_POSTN_ID IS NULL OR BT.PR_POSTN_ID = ‘No Match Row Id’ THEN
(SELECT TOP 1 POSTN_ID
FROMdbo.S_POSTN_CON
WHERE (CON_ID = BT.ROW_ID))
ELSE NULL END)
END)
FROMdbo.S_CONTACT BT,
(SELECTT_POSTN_CON_CON_ID C1
FROMdbo.EIM_CONTACT1
, dbo.S_CONTACT BT
WHERE(
CON_PR_POSTN = ‘Y’ AND
T_POSTN_CON_CON_ID IS NOT NULL AND IF_ROW_BATCH_NUM = 100 AND
T_POSTN_CON__STA = 0 AND T_POSTN_CON__RID IS NOT NULL)
GROUP BY T_POSTN_CON_CON_ID
UNION
SELECTT_POSTN_CON_CON_ID C1
FROMdbo.EIM_CONTACT1
WHERE(
T_POSTN_CON_CON_ID IS NOT NULL AND IF_ROW_BATCH_NUM = 100 AND
T_POSTN_CON__STA = 0 AND T_POSTN_CON__EXS = ‘N’ AND
T_POSTN_CON__UNQ = ‘Y’ AND T_POSTN_CON__RID IS NOT NULL)) SQ
WHERE BT.ROW_ID = SQ.C1
Go
It may depend on the number of records in the tables Madhivanan
Take help of PROFILER tool while running this query and submit captured trace to INDEX TUNING WIZARD for any index recommendations. For the optimum performance always run intermittent UPDATE STATISTICS on the involved tables and regular execution of DBCC DBREINDEX to reduce fragmentation. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Well, there are quite a few things. All of those subqueries you have in the SELECT clause are going to kill you. They will run for EVERY row you have. You need to get the full result set of those, put them in derived tables or temp tables, and join to them. You can then filter out results based on your CASE statements. Second, avoid UNION clauses and IN clauses whenever you can. An IN clause is basically a big, nasty OR clause. Again, you can use a temp table, table variable, or derived table to perform the same logic. Other than that, it’s pretty hard to troubleshoot something like this online. It helps if you post DDL and DML so we can run samples and examine the execution plan.
quote:
SELECT
BT.ROW_ID,
BT.PR_POSTN_ID,
BT.LAST_UPD,
BT.LAST_UPD_BY,
BT.MODIFICATION_NUM,
BT.ROW_ID,
(CASE WHEN BT.ROW_ID IN ( –Get rid of this. You need to make an INNER JOIN to a derived table.
SELECT T_POSTN_CON_CON_ID C1
FROM
dbo.EIM_CONTACT1,
dbo.S_CONTACT BT
WHERE(
CON_PR_POSTN = ‘Y’
AND T_POSTN_CON_CON_ID IS NOT NULL
AND IF_ROW_BATCH_NUM = 100
AND T_POSTN_CON__STA = 0
AND T_POSTN_CON__RID IS NOT NULL)
GROUP BY T_POSTN_CON_CON_ID)
THEN(
SELECT MIN(T_POSTN_CON_POSTNI) C1 –Same with this. Use the CASE logic against the derived table.
FROM
dbo.EIM_CONTACT1,
dbo.S_CONTACT BT
WHERE(
T_POSTN_CON_CON_ID = BT.ROW_ID
AND CON_PR_POSTN = ‘Y’
AND T_POSTN_CON_CON_ID IS NOT NULL
AND IF_ROW_BATCH_NUM = 100
AND T_POSTN_CON__STA = 0
AND T_POSTN_CON__RID IS NOT NULL))
ELSE –Using logic like this is going to cause recompiles really bad.
(CASE WHEN BT.PR_POSTN_ID IS NULL OR BT.PR_POSTN_ID = ‘No Match Row Id’ THEN (
SELECT TOP 1 POSTN_ID
FROM dbo.S_POSTN_CON
WHERE (CON_ID = BT.ROW_ID))
ELSE NULL END)
END)
FROM
dbo.S_CONTACT BT,
(SELECT T_POSTN_CON_CON_ID C1
FROM
dbo.EIM_CONTACT1,
dbo.S_CONTACT BT
WHERE(
CON_PR_POSTN = ‘Y’
AND T_POSTN_CON_CON_ID IS NOT NULL
AND IF_ROW_BATCH_NUM = 100
AND T_POSTN_CON__STA = 0
AND T_POSTN_CON__RID IS NOT NULL)
GROUP BY
T_POSTN_CON_CON_ID
UNION –Avoid UNION when you can. Could this be a UNION ALL? If not, make a temp table and join to it.
SELECT T_POSTN_CON_CON_ID C1
FROM dbo.EIM_CONTACT1
WHERE(
T_POSTN_CON_CON_ID IS NOT NULL
AND IF_ROW_BATCH_NUM = 100
AND T_POSTN_CON__STA = 0
AND T_POSTN_CON__EXS = ‘N’
AND T_POSTN_CON__UNQ = ‘Y’
AND T_POSTN_CON__RID IS NOT NULL)) SQ
WHERE BT.ROW_ID = SQ.C1
Go
SELECT
BT.ROW_ID,
BT.PR_POSTN_ID,
BT.LAST_UPD,
BT.LAST_UPD_BY,
BT.MODIFICATION_NUM,
BT.ROW_ID,
(CASE WHEN BT.ROW_ID IN ( –Get rid of this. You need to make an INNER JOIN to a derived table.
SELECT T_POSTN_CON_CON_ID C1
FROM
dbo.EIM_CONTACT1,
dbo.S_CONTACT BT
WHERE(
CON_PR_POSTN = ‘Y’
AND T_POSTN_CON_CON_ID IS NOT NULL
AND IF_ROW_BATCH_NUM = 100
AND T_POSTN_CON__STA = 0
AND T_POSTN_CON__RID IS NOT NULL)
GROUP BY T_POSTN_CON_CON_ID)
THEN(
SELECT MIN(T_POSTN_CON_POSTNI) C1 –Same with this. Use the CASE logic against the derived table.
FROM
dbo.EIM_CONTACT1,
dbo.S_CONTACT BT
WHERE(
T_POSTN_CON_CON_ID = BT.ROW_ID
AND CON_PR_POSTN = ‘Y’
AND T_POSTN_CON_CON_ID IS NOT NULL
AND IF_ROW_BATCH_NUM = 100
AND T_POSTN_CON__STA = 0
AND T_POSTN_CON__RID IS NOT NULL))
ELSE –Using logic like this is going to cause recompiles really bad.
(CASE WHEN BT.PR_POSTN_ID IS NULL OR BT.PR_POSTN_ID = ‘No Match Row Id’ THEN (
SELECT TOP 1 POSTN_ID
FROM dbo.S_POSTN_CON
WHERE (CON_ID = BT.ROW_ID))
ELSE NULL END)
END)
FROM
dbo.S_CONTACT BT,
(SELECT T_POSTN_CON_CON_ID C1
FROM
dbo.EIM_CONTACT1,
dbo.S_CONTACT BT
WHERE(
CON_PR_POSTN = ‘Y’
AND T_POSTN_CON_CON_ID IS NOT NULL
AND IF_ROW_BATCH_NUM = 100
AND T_POSTN_CON__STA = 0
AND T_POSTN_CON__RID IS NOT NULL)
GROUP BY
T_POSTN_CON_CON_ID
UNION –Avoid UNION when you can. Could this be a UNION ALL? If not, make a temp table and join to it.
SELECT T_POSTN_CON_CON_ID C1
FROM dbo.EIM_CONTACT1
WHERE(
T_POSTN_CON_CON_ID IS NOT NULL
AND IF_ROW_BATCH_NUM = 100
AND T_POSTN_CON__STA = 0
AND T_POSTN_CON__EXS = ‘N’
AND T_POSTN_CON__UNQ = ‘Y’
AND T_POSTN_CON__RID IS NOT NULL)) SQ
WHERE BT.ROW_ID = SQ.C1
Go
MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Thanks for your replies.This query is internal to siebel.
so actually does not know the purpose.
when executed from siebel the app takes long time or hangs while executing
this query.Siebel says problem with SQL server.
I want to know how to check for indexes and other things? Thanks
Use Profiler and the Index Tuning Wizard as Satya recommended then. There are several articles on this site andhttp://www.sqlteam.com to help you through the process. MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
]]>