Query taking long time
-
Hi,
The below query is taking around 1000 Seconds.
SELECT ar.MOVE_QTY,ar.DM12,ar.AL_OR,ar.OR_CODE,ar.CAI_CODE,ar.CAI_DESCRIPTION,ar.CST_CODE,ar.AL_NO AL_NO,ar.OPEN_QTY,ar.WH_CODE,ar.ETA_DATE,
ar.CATEGORY,ar.AL_DATE,ar.OD_CODE,
ar.SHIPTO_CODE,ar.AL_LINE AL_LINE,ar.ORIG_QTY,ar.CNCL_QTY,b.SHIP_QTY SHIP_QTY,ar.LOCAL_CODE,0 AL_TXT_LINE,b.AVIEXP_NO,
b.UC_NUMBER,
b.AVIEXP_DATE,b.ETD_DATE,b.ETW_DATE,b.DISCHARGE_PORT,b.VESSEL_NAME,b.CONTAINER_NO,b.SHIPPING_CO,b.TRANSPORT_TYPE,b.WH_NAME,
ar.AL_SET,ar.IS_AUTO,ar.MKT_CODE,ar.BRAND_CODE,ar.INVOIC_QTY
FROM VW_RPT_EXTRACT_OPENAL ar left outer join VW_SHIPPED_AL_RPT b on ar.or_code=b.or_code and ar.cai_code=b.cai_code
and ar.al_no=b.al_no
WHERE ar.OR_CODE IN ('AU','JP','J1','TD','KE','KO','MY','ID','VN','SI')
order by ar.or_code,ar.cai_code,ar.al_noCould you please let me know where is the problem and how we can improve it.
Thanks
Naveen -
The explain plan is as below:
-
Just a (unformatted) query isn't going to tell us anything about why your query is performing badly, and pictures of your query plan aren't useful (we can't interact with them). Have a read of Jeff's article on How to Post Performance Problems and post back please. The .sqlplan file will be much more useful.
Also, here's a more legible T-SQL Query, rather than just a large blob of text:
SELECT ar.MOVE_QTY, ar.DM12, ar.AL_OR,
ar.OR_CODE, ar.CAI_CODE,
ar.CAI_DESCRIPTION, ar.CST_CODE,
ar.AL_NO AL_NO, ar.OPEN_QTY,
ar.WH_CODE, ar.ETA_DATE,
ar.CATEGORY, ar.AL_DATE, ar.OD_CODE,
ar.SHIPTO_CODE, ar.AL_LINE AL_LINE,
ar.ORIG_QTY, ar.CNCL_QTY,
b.SHIP_QTY SHIP_QTY, ar.LOCAL_CODE,
0 AS AL_TXT_LINE, b.AVIEXP_NO,
b.UC_NUMBER,
b.AVIEXP_DATE, b.ETD_DATE,
b.ETW_DATE, b.DISCHARGE_PORT,
b.VESSEL_NAME, b.CONTAINER_NO,
b.SHIPPING_CO, b.TRANSPORT_TYPE,
b.WH_NAME,
ar.AL_SET, ar.IS_AUTO,
ar.MKT_CODE, ar.BRAND_CODE,
ar.INVOIC_QTY
FROM VW_RPT_EXTRACT_OPENAL ar
LEFT OUTER JOIN VW_SHIPPED_AL_RPT b ON ar.or_code = b.or_code
AND ar.cai_code = b.cai_code
AND ar.al_no = b.al_no
WHERE ar.OR_CODE IN ('AU','JP','J1','TD','KE','KO','MY','ID','VN','SI')
ORDER BY ar.or_code, ar.cai_code, ar.al_no;
Edit: Tone change.Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
Naveen
Your query is joining (what looks like) two views, and from the picture you've posted, I'd say that each of those views in turn contains several joins.
This is probably a bigger problem than can be covered in a single forum topic. You need to look at the design and indexing of your database and the definitions of the individual views. Get someone in to help you if you don't have the expertise in house.
Just a couple of things that jump out at me from looking at the code, though: I assume this is for a report? Do you really need all of those columns? Can your presentation layer not handle the sorting and adding the constant "0" for AL_TXT_LINE?
John
-
@thom-2, the modified query is also taking long time around 900 seconds
@Mitchell, Yes it is using views and have joins in that . When i tried to run the view seperately, each view is completing in 6 seconds. When i joined with inner join it is completing in 60 sec but as per the requirement i need to join with left join.
Also i will see if any indexes required or so. Meanwhile if you have any better option pls let me knowThanks
Naveen - ekknaveen - Wednesday, October 25, 2017 5:15 AM
There's not a linear relationship. Just because each view runs in six seconds, doesn't mean it'll run in 12 if you combine them.
Meanwhile if you have any better option pls let me know
Unless you follow Thom's advice above, there's not much more we can say. And, as I said before, this may be too complex for a forum thread even if you do that.
@Thom, the modified query is also taking long time around 900 seconds
Yes, it's the same query but laid out to make it readable in order to help people to help you. It won't perform any better.John
- ekknaveen - Wednesday, October 25, 2017 1:21 AMThe explain plan is as below:
Looks like an Oracle Explain Plan - is this for Oracle or SQL Server?
Sue
-
It is oracle query. need to find where it is taking long time and how we can improve it
- ekknaveen - Monday, November 20, 2017 5:43 AM
You realise you've posted on a SQL Server (2005) forum, right? Although some principles are the same, SQL Server and Oracle are fundamentally different products. You'd be better asking somewhere devoted to Oracle for support on an Oracle database.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk -
This is a Microsoft SQL Server forum, you probably won't find Oracle query tuning people here.
Maybe try an Oracle-focused forum?Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass - ekknaveen - Monday, November 20, 2017 5:43 AM
SQL Server Furum notwithstanding, you don't need that order by clause.
Your oracle query is joining two views. See how they're performing individually before you join them. That's where I'd start."I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."-- Itzik Ben-Gan 2001
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply