October 2, 2013 at 3:20 am
Hi
When I am runnng the below query it is taking long time. Before it was fine but when i added or condition in the join clause in rd_market_price it is taking long time
DECLARE @JOB_ID INTEGER
exec @job_id= oss_mi_get_job_id
SELECT DISTINCT
YEAR(G.VESSEL_VOYAGE_DT) AS [YEAR]
,MONTH(G.VESSEL_VOYAGE_DT) AS [MONTH]
,g.price_dt
,G.VESSEL_VOYAGE_DT
,L.LOCAL_BENCHMARK_TXT
,BA.WEIGHTING_PRCT
,G.BENCHMARK_FLEET_CD AS VESSELCLASS
,G.SUBCLASS_GROUP_NM AS CLASS
,SC.MIS_SUB_CLASS_CD AS SUB_CLASS
,G.SUBCLASS_TXT AS VESSEL_REF
,ROUND((BA.TONS_NBR /BA.VOYAGE_DURATION_DAYS) * BA.WEIGHTING_PRCT,2) AS TONS
, L.LOCAL_BENCHMARK_VAL AS MTM
,CONVERT(VARCHAR(8), GETDATE(), 112) AS [DATE STAMP]
,G.SUBCLASS_GROUP_NM AS PRODUCT
,MIN(FPD.QUOTE_VAL) AS FREIGHT_RATE
,MIN(FOPD.QUOTE_VAL) AS BUNKER_RATE
,MIN(GOPD.QUOTE_VAL) AS DIESEL_RATE
FROM OSS_DAILY_BENCHMARK_LOCAL L
JOIN OSS_DAILY_BENCHMARK_GLOBAL G
ON L.DAILY_BENCHMARK_GLOBAL_SEQ= G.DAILY_BENCHMARK_GLOBAL_SEQ
JOIN OSS_BENCHMARK_PRICE_REFERENCE PR
ON L.DAILY_BENCHMARK_LOCAL_SEQ=PR.DAILY_BENCHMARK_LOCAL_SEQ
JOIN RD_MARKET_PRICE MP
ON MP.MARKET_PRICE_SEQ=PR.MARKET_PRICE_SEQ
OR MP.MARKET_PRICE_SEQ=PR.FUEL_MARKET_PRICE_SEQ
OR MP.MARKET_PRICE_SEQ=PR.GAS_MARKET_PRICE_SEQ
JOIN OSS_BENCHMARK_ASSUMPTION_SNAPSHOT BA
ON L.BENCHMARK_ASSUMPTION_SNAPSHOT_SEQ=BA.BENCHMARK_ASSUMPTION_SNAPSHOT_SEQ
JOIN OSS_BENCHMARK_ASSUMPTION OBA
ON OBA.BENCHMARK_ASSUMPTION_SEQ=BA.BENCHMARK_ASSUMPTION_SEQ
INNER JOIN OSS_BENCHMARK_SUBCLASS_PERIOD SP
ON SP.BENCHMARK_SUBCLASS_PERIOD_SEQ = OBA.BENCHMARK_SUBCLASS_PERIOD_SEQ
INNER JOIN OSS_BENCHMARK_SUBCLASS S
ON SP.BENCHMARK_SUBCLASS_SEQ = S.BENCHMARK_SUBCLASS_SEQ
JOIN OSS_BENCHMARK_SHIP_CONTROL sc --OSS_MIS_BENCHMARK_SHIP_CONTROL_view SC
ON SC.FULL_BENCHMARK_CD = S.SUBCLASS_NM
LEFT JOIN OSS_MARKET_PRICE_DAILY FPD
ON MP.MARKET_PRICE_SEQ=FPD.MARKET_PRICE_SEQ
AND MP.PRICE_TYPE_CD ='FREIGHT'
AND FPD.OPTIMISATION_JOB_SEQ= @JOB_ID
AND CONVERT(VARCHAR(8), FPD.APPLICABLE_DT, 112)= CONVERT(VARCHAR(8), G.price_dt, 112)
LEFT JOIN OSS_MARKET_PRICE_DAILY FOPD
ON MP.MARKET_PRICE_SEQ=FOPD.MARKET_PRICE_SEQ
AND MP.PRICE_TYPE_CD ='FUEL OIL'
AND FOPD.OPTIMISATION_JOB_SEQ= @JOB_ID
AND CONVERT(VARCHAR(8), FOPD.APPLICABLE_DT, 112)= CONVERT(VARCHAR(8), G.price_dt, 112)
LEFT JOIN OSS_MARKET_PRICE_DAILY GOPD
ON MP.MARKET_PRICE_SEQ=GOPD.MARKET_PRICE_SEQ
AND MP.PRICE_TYPE_CD ='GAS OIL'
AND GOPD.OPTIMISATION_JOB_SEQ= @JOB_ID
AND CONVERT(VARCHAR(8), GOPD.APPLICABLE_DT, 112)= CONVERT(VARCHAR(8), G.price_dt, 112)
WHERE G.SUBCLASS_DISPLAY_NM in (select vout from OSS_MI_SPLITTING (@VESSEL_REF))
AND G.VESSEL_VOYAGE_DT>=@VESSEL_VOYAGE_START_DATE AND G.VESSEL_VOYAGE_DT<=@VESSEL_VOYAGE_END_DATE
--AND G.PRICE_DT>=@VESSEL_VOYAGE_START_DATE AND G.PRICE_DT<=@VESSEL_VOYAGE_END_DATE
AND G.OPTIMISATION_JOB_SEQ=@JOB_ID
and substring(g.BENCHMARK_FLEET_CD,1,2)=substring(@VESSEL_REF,1,2)
GROUP BY YEAR(G.VESSEL_VOYAGE_DT), MONTH(G.VESSEL_VOYAGE_DT),g.price_dt ,
G.VESSEL_VOYAGE_DT,L.LOCAL_BENCHMARK_TXT,BA.WEIGHTING_PRCT,
G.BENCHMARK_FLEET_CD,G.SUBCLASS_GROUP_NM,SC.MIS_SUB_CLASS_CD,
G.SUBCLASS_TXT,BA.TONS_NBR,BA.VOYAGE_DURATION_DAYS,L.LOCAL_BENCHMARK_VAL,G.SUBCLASS_GROUP_NM
ORDER BY 3,4 DESC
Can any one help me out in this
Regards
Naveen
October 2, 2013 at 6:04 am
Table definitions, index definitions and execution plan please. Also the definition of all the functns used in there will help.
Is that distinct really necessary?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 2, 2013 at 6:21 am
As Gail states.
You may get some mileage from this:
-- Extract this chunk of your query separately
--------------------------------------------------------------------
JOIN OSS_BENCHMARK_PRICE_REFERENCE PR
ON L.DAILY_BENCHMARK_LOCAL_SEQ=PR.DAILY_BENCHMARK_LOCAL_SEQ
JOIN RD_MARKET_PRICE MP
ON MP.MARKET_PRICE_SEQ=PR.MARKET_PRICE_SEQ
OR MP.MARKET_PRICE_SEQ=PR.FUEL_MARKET_PRICE_SEQ
OR MP.MARKET_PRICE_SEQ=PR.GAS_MARKET_PRICE_SEQ
LEFT JOIN OSS_MARKET_PRICE_DAILY FPD
ON MP.MARKET_PRICE_SEQ=FPD.MARKET_PRICE_SEQ
AND MP.PRICE_TYPE_CD ='FREIGHT'
AND FPD.OPTIMISATION_JOB_SEQ= @JOB_ID
AND CONVERT(VARCHAR(8), FPD.APPLICABLE_DT, 112)= CONVERT(VARCHAR(8), G.price_dt, 112)
LEFT JOIN OSS_MARKET_PRICE_DAILY FOPD
ON MP.MARKET_PRICE_SEQ=FOPD.MARKET_PRICE_SEQ
AND MP.PRICE_TYPE_CD ='FUEL OIL'
AND FOPD.OPTIMISATION_JOB_SEQ= @JOB_ID
AND CONVERT(VARCHAR(8), FOPD.APPLICABLE_DT, 112)= CONVERT(VARCHAR(8), G.price_dt, 112)
LEFT JOIN OSS_MARKET_PRICE_DAILY GOPD
ON MP.MARKET_PRICE_SEQ=GOPD.MARKET_PRICE_SEQ
AND MP.PRICE_TYPE_CD ='GAS OIL'
AND GOPD.OPTIMISATION_JOB_SEQ = @JOB_ID
AND CONVERT(VARCHAR(8), GOPD.APPLICABLE_DT, 112)= CONVERT(VARCHAR(8), G.price_dt, 112)
-------------------------------------------------------------------
-- something like this. Set it up either as a CTE, a derived table,
-- or (probably faster) a #temporary table
SELECT
PR.DAILY_BENCHMARK_LOCAL_SEQ,
APPLICABLE_DT = CONVERT(VARCHAR(8), FPD.APPLICABLE_DT, 112),
FREIGHT_RATE = CASE WHEN MP.PRICE_TYPE_CD ='FREIGHT' THEN FPD.QUOTE_VAL ELSE 0 END,
BUNKER_RATE = CASE WHEN MP.PRICE_TYPE_CD ='FUEL OIL' THEN FPD.QUOTE_VAL ELSE 0 END,
DIESEL_RATE = CASE WHEN MP.PRICE_TYPE_CD ='GAS OIL' THEN FPD.QUOTE_VAL ELSE 0 END
FROM OSS_BENCHMARK_PRICE_REFERENCE PR
INNER JOIN RD_MARKET_PRICE MP
ON MP.MARKET_PRICE_SEQ IN (PR.MARKET_PRICE_SEQ, PR.FUEL_MARKET_PRICE_SEQ, PR.GAS_MARKET_PRICE_SEQ)
LEFT JOIN OSS_MARKET_PRICE_DAILY FPD
ON MP.MARKET_PRICE_SEQ = FPD.MARKET_PRICE_SEQ
--AND MP.PRICE_TYPE_CD ='FREIGHT'
AND FPD.OPTIMISATION_JOB_SEQ = @JOB_ID
--AND CONVERT(VARCHAR(8), FPD.APPLICABLE_DT, 112)= CONVERT(VARCHAR(8), G.price_dt, 112)
GROUP BY
PR.DAILY_BENCHMARK_LOCAL_SEQ,
CONVERT(VARCHAR(8), FPD.APPLICABLE_DT, 112)
-- note that functions in JOINs and the WHERE clause are likely to render the search non-SARGable
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
October 24, 2013 at 6:48 am
select vout from OSS_MI_SPLITTING (@VESSEL_REF)
can also be shifted to CTE
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply