February 29, 2008 at 5:25 am
What this query does is:
1) Select Ads that are to be posted on locations - incomplete work orders come with what Ad has to be posted on a location (Work Order Detail table references Ad Master).
2) Select Ads that are already posted on those locations on which the incomplete work orders exist (Work Order Detail table also references Location Master).
Which means, i see currently posted as well as to-be-posted Ads in the below query only for Locations on which Incomplete Work Orders exist.. :
SELECTAD_NAME,
AD_Image AS AD_HH_IMAGE
FROM TBL_AD_MASTER
WHERE AD_NAME IN
(
SELECT DISTINCT( ADM.AD_NAME)
FROM TBL_AD_MASTER AS ADM
INNER JOIN TBL_WORK_ORDER_DETAIL AS WODTL ON WODTL.Ad_Key=ADM.Ad_Key
INNER JOIN TBL_WORK_ORDER_HEADER AS WOHDR ON WOHDR.WO_Hdr_Key=WODTL.WO_Hdr_Key
WHERE WODTL.TASK_SURR_KEY =1 --INDICATES A TYPE OF TASK
AND WOHDR.WO_STATUS = 0 --INCOMPLETE STATUS
AND WOHDR.DELETED_FLAG =0 -- NOT DELETED
)
OR
AD_NAME IN
(
SELECT DISTINCT( ADM.AD_NAME)
FROM TBL_AD_MASTER AS ADM
INNER JOIN TBL_LOCATION_MASTER AS LM ON LM.Ad_Key=ADM.Ad_Key
INNER JOIN TBL_WORK_ORDER_DETAIL AS WODTL ON WODTL.Location_Key=LM.Location_Key
INNER JOIN TBL_WORK_ORDER_HEADER AS WOHDR ON WOHDR.WO_Hdr_Key=WODTL.WO_Hdr_Key
WHERE WODTL.TASK_SURR_KEY =1
AND WOHDR.WO_STATUS = 0
AND WOHDR.DELETED_FLAG =0
)
This query takes FIVE seconds! And that is just so terrible.
How else can this be written, people?
If there's something unclear in my post, please ask me for clarifications!
Thanks a ton... in advance.
February 29, 2008 at 5:43 am
Hi,
SELECT
AD_NAME,
AD_Image AS AD_HH_IMAGE
FROM
TBL_AD_MASTER
INNER JOIN
(
SELECT
ADM.AD_NAME
FROM
TBL_AD_MASTER AS ADM
INNER JOIN
TBL_WORK_ORDER_DETAIL AS WODTL ON WODTL.Ad_Key=ADM.Ad_Key
INNER JOIN
TBL_WORK_ORDER_HEADER AS WOHDR ON WOHDR.WO_Hdr_Key=WODTL.WO_Hdr_Key
WHERE
WODTL.TASK_SURR_KEY =1
AND WOHDR.WO_STATUS = 0
AND WOHDR.DELETED_FLAG =0
UNION
SELECT
ADM.AD_NAME
FROM
TBL_AD_MASTER AS ADM
INNER JOIN
TBL_LOCATION_MASTER AS LM ON LM.Ad_Key=ADM.Ad_Key
INNER JOIN
TBL_WORK_ORDER_DETAIL AS WODTL ON WODTL.Location_Key=LM.Location_Key
INNER JOIN
TBL_WORK_ORDER_HEADER AS WOHDR ON WOHDR.WO_Hdr_Key=WODTL.WO_Hdr_Key
WHERE
WODTL.TASK_SURR_KEY =1
AND WOHDR.WO_STATUS = 0
AND WOHDR.DELETED_FLAG =0
) A ON A.ad_name = TBL_AD MASTER.ad_name
Try this way ..
Question .. do you have an index on the ad_name column?
Regards,
Oana.
February 29, 2008 at 5:43 am
I think this might work better (at first glance anyway):
SELECT AD_NAME,AD_Image AS AD_HH_IMAGE
FROM TBL_AD_MASTER AS ADM
INNER JOIN TBL_WORK_ORDER_DETAIL AS WODTL ON WODTL.Ad_Key=ADM.Ad_Key
INNER JOIN TBL_WORK_ORDER_HEADER AS WOHDR ON WOHDR.WO_Hdr_Key=WODTL.WO_Hdr_Key
WHERE WODTL.TASK_SURR_KEY =1 --INDICATES A TYPE OF TASK
AND WOHDR.WO_STATUS = 0 --INCOMPLETE STATUS
AND WOHDR.DELETED_FLAG =0 -- NOT DELETED
UNION
SELECT AD_NAME,AD_Image AS AD_HH_IMAGE
FROM TBL_AD_MASTER AS ADM
INNER JOIN TBL_LOCATION_MASTER AS LM ON LM.Ad_Key=ADM.Ad_Key
INNER JOIN TBL_WORK_ORDER_DETAIL AS WODTL ON WODTL.Location_Key=LM.Location_Key
INNER JOIN TBL_WORK_ORDER_HEADER AS WOHDR ON WOHDR.WO_Hdr_Key=WODTL.WO_Hdr_Key
WHERE WODTL.TASK_SURR_KEY =1
AND WOHDR.WO_STATUS = 0
AND WOHDR.DELETED_FLAG =0
February 29, 2008 at 8:23 am
For a quick "free" boost in performance - since there logically should be no overlap between the two - replace any of the UNION operations above with a UNION ALL. The extra effort to make sure everything is distinct sounds like it's superfluous.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 3, 2008 at 11:09 pm
I tried these earlier, people...
Problem is, the result set of this query is to be returned to the hand held device through an SP. And using the UNION operator in the query seems like a problem as the RDA.Pull can't seem to pull the data! It does not even create the table schema from this query, so the question of pulling data doesn't even arise.
Any other suggestions, guys?
March 3, 2008 at 11:12 pm
Matt Miller (2/29/2008)
For a quick "free" boost in performance - since there logically should be no overlap between the two - replace any of the UNION operations above with a UNION ALL. The extra effort to make sure everything is distinct sounds like it's superfluous.
But there could be an overlap...
There could be a Pepsi ad on Location1 (with image of a can). There could be a Work Order created for same location to post same ad, same image as the current one is faded, frayed, damaged... or any such factors that require re-posting...
March 4, 2008 at 1:19 pm
hmm - Not quite seeing it - but ok. It looked to me you might pull the same ad_image, but different ad_names in that case (since you can't have a work order be both not started AND started but not complete at the same time)
a few more thoughts - going back to your initial query:
- there's no reason to have the DISTINCT in the IN clauses. I don't think it will buy you much, and I think it it likely hurting perf.
- have you tried rewriting those to use EXISTS instead of IN? Tend to run faster when doing that.
- what's your indexing look like?
- are you seeing performance differences between calling the SP locally vs from the PDA?
The EXISTs would be:
SELECTAD_NAME,
AD_Image AS AD_HH_IMAGE
FROM TBL_AD_MASTER ADM_OUT
WHERE Exists(
SELECT NULL
FROM TBL_AD_MASTER AS ADM
INNER JOIN TBL_WORK_ORDER_DETAIL AS WODTL ON WODTL.Ad_Key=ADM.Ad_Key
INNER JOIN TBL_WORK_ORDER_HEADER AS WOHDR ON
WOHDR.WO_Hdr_Key=WODTL.WO_Hdr_Key
WHERE WODTL.TASK_SURR_KEY =1 --INDICATES A TYPE OF TASK
AND WOHDR.WO_STATUS = 0 --INCOMPLETE STATUS
AND WOHDR.DELETED_FLAG =0 -- NOT DELETED
and ADM.AD_NAME=ADM_OUT.AD_NAME
)
OR
Exists
(
SELECT Null
FROM TBL_AD_MASTER AS ADM
INNER JOIN TBL_LOCATION_MASTER AS LM ON LM.Ad_Key=ADM.Ad_Key
INNER JOIN TBL_WORK_ORDER_DETAIL AS WODTL ON
WODTL.Location_Key=LM.Location_Key
INNER JOIN TBL_WORK_ORDER_HEADER AS WOHDR ON
WOHDR.WO_Hdr_Key=WODTL.WO_Hdr_Key
WHERE WODTL.TASK_SURR_KEY =1
AND WOHDR.WO_STATUS = 0
AND WOHDR.DELETED_FLAG =0
and ADM.AD_NAME=ADM_OUT.AD_NAME
)
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
March 4, 2008 at 1:45 pm
Not sure either, and with no DDL, sample data, and expected results I can't test what I have, so give this a try with no promises that it will work:
with cteWorkOrder (
AD_NAME
) as (
SELECT DISTINCT
ADM.AD_NAME
FROM
TBL_AD_MASTER AS ADM
INNER JOIN TBL_WORK_ORDER_DETAIL AS WODTL
ON (WODTL.Ad_Key = ADM.Ad_Key)
INNER JOIN TBL_WORK_ORDER_HEADER AS WOHDR
ON WOHDR.WO_Hdr_Key = WODTL.WO_Hdr_Key
WHERE
WODTL.TASK_SURR_KEY = 1 --INDICATES A TYPE OF TASK
AND WOHDR.WO_STATUS = 0 --INCOMPLETE STATUS
AND WOHDR.DELETED_FLAG =0 -- NOT DELETED
), cteLocation (
AD_NAME
) as (
SELECT DISTINCT
ADM.AD_NAME
FROM
TBL_AD_MASTER AS ADM
INNER JOIN TBL_LOCATION_MASTER AS LM
ON (LM.Ad_Key = ADM.Ad_Key)
INNER JOIN TBL_WORK_ORDER_DETAIL AS WODTL
ON (WODTL.Location_Key = LM.Location_Key)
INNER JOIN TBL_WORK_ORDER_HEADER AS WOHDR
ON (WOHDR.WO_Hdr_Key = WODTL.WO_Hdr_Key)
WHERE
WODTL.TASK_SURR_KEY = 1
AND WOHDR.WO_STATUS = 0
AND WOHDR.DELETED_FLAG =0
)
SELECT
tam.AD_NAME,
tam.AD_Image AS AD_HH_IMAGE
FROM
TBL_AD_MASTER tam
WHERE
exists (select
1
from
cteWorkOrder wo
where
wo.AD_NAME = tam.AD_NAME
)
or exists (select
1
from
cteLocation loc
where
loc.AD_NAME = tam.AD_NAME
)
😎
March 4, 2008 at 2:59 pm
Try this:
;WITH
Ad_Keys1 (Ad_Key) as
(SELECT Ad_Key
FROM TBL_WORK_ORDER_DETAIL AS WODTL
INNER JOIN TBL_WORK_ORDER_HEADER AS WOHDR
ON WOHDR.WO_Hdr_Key=WODTL.WO_Hdr_Key
WHERE WODTL.TASK_SURR_KEY =1
AND WOHDR.WO_STATUS = 0
AND WOHDR.DELETED_FLAG =0),
Ad_Keys2 (Ad_Key) as
(select Ad_Key
FROM TBL_LOCATION_MASTER AS LM
INNER JOIN TBL_WORK_ORDER_DETAIL AS WODTL
ON WODTL.Location_Key=LM.Location_Key
INNER JOIN TBL_WORK_ORDER_HEADER AS WOHDR
ON WOHDR.WO_Hdr_Key=WODTL.WO_Hdr_Key
WHERE WODTL.TASK_SURR_KEY =1
AND WOHDR.WO_STATUS = 0
AND WOHDR.DELETED_FLAG =0)
SELECTAD_NAME,
AD_Image AS AD_HH_IMAGE
FROM TBL_AD_MASTER
WHERE Ad_Key in
(select ad_key
from ad_keys1
union all
select ad_key
from ad_keys2)
You might also want to make sure there are indexes on the columns used in the where and join clauses of the two CTEs, as well as the referenced ad_key column.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
March 5, 2008 at 12:10 am
Superb, guys! Thank you soooo much, Matt, Lynn and Gsquared!!! Now I just need to decide which suits me better! Tough choice!
Matt Miller (3/4/2008)
hmm - Not quite seeing it - but ok. It looked to me you might pull the same ad_image, but different ad_names in that case (since you can't have a work order be both not started AND started but not complete at the same time)
Matt, let me see if I can explain this better. Do check this attachment.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply