September 16, 2008 at 6:01 am
I'm using a derived table (see the following code) to retrieve a unique smalldatetime value from a static table. Using an outer join to this, i am then performing a date comparison to retrieve set rows. However the peformance seems poor, is this a good approach or are there any alternative methods i should be using ?
Thanks in advance for any help.
SELECT vw_BPMJDAAgedOrders.OrderNum, vw_BPMJDAAgedOrders.Component, vw_BPMJDAAgedOrders.WHID, vw_BPMJDAAgedOrders.ComponentQTY,
vw_BPMJDAAgedOrders.ComponentStatusID, CASE WHEN dtbl_BPMCutOffDate.CutOffDate > vw_BPMJDAAgedOrders.LatDelDate THEN 1 ELSE 0 END AS [Append]
FROM tbl_BPMJDAStatus INNER JOIN
vw_BPMJDAAgedOrders ON tbl_BPMJDAStatus.StatusID = vw_BPMJDAAgedOrders.ComponentStatusID INNER JOIN
tbl_BPMStockDelays ON vw_BPMJDAAgedOrders.Component = tbl_BPMStockDelays.Component AND
vw_BPMJDAAgedOrders.WHID = tbl_BPMStockDelays.WHID LEFT OUTER JOIN
(SELECT 1 AS bCutOffDate, CutOffDate
FROM tbl_BPMCutOffDate AS tbl_BPMCutOffDate_1) AS dtbl_BPMCutOffDate ON
tbl_BPMStockDelays.bCutOffDate = dtbl_BPMCutOffDate.bCutOffDate
WHERE (tbl_BPMJDAStatus.UseForAged = 1)
September 16, 2008 at 6:35 am
You are joining on:
tbl_BPMStockDelays.bCutOffDate = dtbl_BPMCutOffDate.bCutOffDate
dtbl_BPMCutOffDate.bCutOffDate is in your sub query as: "SELECT 1 AS bCutOffDate"
So why wouldn't you just use a simple WHERE tbl_BPMStockDelays.bCutOffDate = 1?
Your sub-query does not have any kind of filter or other complication, so joining to it vs. joining just to the table is going to give you the same result. The optimizer should be able to figure this out. The real issue would be that the field you are joining on is not a real field, it is defined as "1" in the select statement. So, it could not possibly have an index or any statistics.
September 16, 2008 at 6:44 am
Thanks for the response.
I don't use : WHERE tbl_BPMStockDelays.bCutOffDate = 1 as where bCutOffDate i want to retrieve a value to use as criteria in a where clause at row level.
I think you're on the right track with the fact that i join on a non indexed field.
September 16, 2008 at 7:08 am
Compare the execution plan from your query with the one from this query. It eliminates the join and uses a query in the case statement. I think the optimizer will know to only get the CutOffDate once.
[font="Courier New"]SELECT
AO.OrderNum
, AO.Component
, AO.WHID
, AO.ComponentQTY
, AO.ComponentStatusID
, CASE
WHEN SD.bCutOffDate = 1 AND AO.LatDelDate <= (SELECT TOP 1 CutOffDate FROM tbl_BPMCutOffDate) THEN 1
ELSE 0
END AS [Append]
FROM
tbl_BPMJDAStatus S
INNER JOIN vw_BPMJDAAgedOrders AO ON S.StatusID = AO.ComponentStatusID
INNER JOIN tbl_BPMStockDelays SD ON AO.Component = SD.Component
AND AO.WHID = SD.WHID
WHERE
(S.UseForAged = 1)[/font]
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply