time taking query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

time taking query

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-02 : 06:35:14

Dear All,
here i'm posting my query which is taking 3 minutes

please suggest me the best query

SELECT distinct INP.COLUMN001 REPORT_INPUT_ID, INP.COLUMN002 REPORT_ID, INP.COLUMN003 OPERATION_ID,
OPER.COLUMN004 OPERATION_CODE, OPER.COLUMN005 OPERATION_NAME, INP.COLUMN004 ITEM_ID,
CONVERT(NVARCHAR , INP.COLUMN005, 110) RECEIVED_DATE, INP.COLUMN006 LOT_NO, INP.COLUMN007 RECEIVED_QTY,
INP.COLUMN008 CONSUMED_QTY, (select CODE from view1 where item_id = INP.COLUMN004) my_val,
(select NAME from view1 where item_id = INP.COLUMN004) Item_Name, INP.COLUMN009 UOM_ID,
U.UOM_CODE, INP.COLUMN010 BASE_RECEIVED_QTY, INP.COLUMN011 BASE_CONSUMED_QTY,
case when INP.COLUMN012 ='1' then 'Progress' when INP.COLUMN012 ='2' then 'Closed' end OPERATION_STATUS,
case when INGDTL.COLUMN006 ='0' then 'Ingredient' when INGDTL.COLUMN006 ='1' then 'Intermediate' end INPUT_TYPE,
INP.COLUMNB01 COLUMNB01, INP.COLUMNB02 COLUMNB02, INP.COLUMNB03 COLUMNB03, INP.COLUMNB04 COLUMNB04,
INP.COLUMNB05 COLUMNB05, INP.COLUMNB06 COLUMNB06, INP.COLUMNB07 COLUMNB07, INP.COLUMNB08 COLUMNB08,
INP.COLUMNB09 COLUMNB09, INP.COLUMNB10 COLUMNB10, INP.COLUMND01 BRANCHID, INP.COLUMND02 COMPANYID, INP.COLUMND03 CREATEDBY,
INP.COLUMND04 CREATEDDATE, INP.COLUMND05 LASTUPDATEDBY, INP.COLUMND06 LASTUPDATEDDATE, INP.COLUMND07 ROWGUID,
INP.COLUMND08 UPDATEDSITE, INP.COLUMND09 LANGID, WC.COLUMN009 WIP_WAREHOUSE_ID,
(SELECT (sum(WIP.COLUMN011) - sum(wip.column010))
FROM TABLE066 WIP where wip.column008 = INP.column004 and WIP.COLUMN005 = '8cd741c7-1ac6-4839-88e7-df85518170f1' and wip.column006 = inp.column003 ) WIP_Qty ,
WIPM.Column005 WIP_ITEM_ID
FROM TABLE073 INP
left join view1 I on I.ITEM_ID = INP.COLUMN004
left join view2 U on U.UOM_ID = INP.COLUMN009
left join TABLE022 OPER ON OPER.COLUMN001 = INP.COLUMN003
left join TABLE066 WIP on WIP.column008 = INP.column004
left join TABLE015 WC on WC.COLUMN001 = OPER.COLUMN008
left JOIN TABLE040 INGDTL ON INGDTL.COLUMN002 = INP.COLUMN004 AND WIP.column008 = INGDTL.COLUMN002
left join TABLE065 WIPM on WIPM.column005 = INP.column004
where INP.COLUMN002 = '057f87aa-7884-43fa-8984-9b74c971da62' order by my_val

thank you very much

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-02 : 06:45:50

You already making join between TABLE073 and View1, so why again you using subqueries to fetch CODE and NAME inside main query? In my opinion, if you could rewrite those correlated subqueries as normal joins, it would be much faster.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-02 : 07:01:19

how can i replace them....
please give me some time.....harsh

Vinod
Even you learn 1%, Learn it with 100% confidence.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-02 : 07:11:27

Something like this, I mean:

SELECT 
	distinct INP.COLUMN001 REPORT_INPUT_ID, INP.COLUMN002 REPORT_ID, INP.COLUMN003 OPERATION_ID, 
	OPER.COLUMN004 OPERATION_CODE, OPER.COLUMN005 OPERATION_NAME, INP.COLUMN004 ITEM_ID, 
	CONVERT(NVARCHAR , INP.COLUMN005, 110) RECEIVED_DATE, INP.COLUMN006 LOT_NO, INP.COLUMN007 RECEIVED_QTY, 
	INP.COLUMN008 CONSUMED_QTY, I.CODE as my_val, 
	I.NAME as Item_Name, INP.COLUMN009 UOM_ID, 
	U.UOM_CODE, INP.COLUMN010 BASE_RECEIVED_QTY, INP.COLUMN011 BASE_CONSUMED_QTY, 
	case when INP.COLUMN012 ='1' then 'Progress' when INP.COLUMN012 ='2' then 'Closed' end OPERATION_STATUS, 
	case when INGDTL.COLUMN006 ='0' then 'Ingredient' when INGDTL.COLUMN006 ='1' then 'Intermediate' end INPUT_TYPE, 
	INP.COLUMNB01 COLUMNB01, INP.COLUMNB02 COLUMNB02, INP.COLUMNB03 COLUMNB03, INP.COLUMNB04 COLUMNB04, 
	INP.COLUMNB05 COLUMNB05, INP.COLUMNB06 COLUMNB06, INP.COLUMNB07 COLUMNB07, INP.COLUMNB08 COLUMNB08, 
	INP.COLUMNB09 COLUMNB09, INP.COLUMNB10 COLUMNB10, INP.COLUMND01 BRANCHID, INP.COLUMND02 COMPANYID, INP.COLUMND03 CREATEDBY, 
	INP.COLUMND04 CREATEDDATE, INP.COLUMND05 LASTUPDATEDBY, INP.COLUMND06 LASTUPDATEDDATE, INP.COLUMND07 ROWGUID, 
	INP.COLUMND08 UPDATEDSITE, INP.COLUMND09 LANGID, WC.COLUMN009 WIP_WAREHOUSE_ID, 
	wip1.WIP_Qty, 
	WIPM.Column005 WIP_ITEM_ID 
FROM TABLE073 INP join
(
	SELECT column008, column006, (sum(COLUMN011) - sum(column010)) as WIP_Qty
	FROM TABLE066 where COLUMN005 = '8cd741c7-1ac6-4839-88e7-df85518170f1'
	Group by column008, column006
) wip1 on wip1.column008 = INP.column004 and wip1.column006 = inp.column003
left join view1 I on I.ITEM_ID = INP.COLUMN004 
left join view2 U on U.UOM_ID = INP.COLUMN009 
left join TABLE022 OPER ON OPER.COLUMN001 = INP.COLUMN003 
left join TABLE066 WIP on WIP.column008 = INP.column004 
left join TABLE015 WC on WC.COLUMN001 = OPER.COLUMN008 
left JOIN TABLE040 INGDTL ON INGDTL.COLUMN002 = INP.COLUMN004 AND WIP.column008 = INGDTL.COLUMN002 
left join TABLE065 WIPM on WIPM.column005 = INP.column004 
where INP.COLUMN002 = '057f87aa-7884-43fa-8984-9b74c971da62' order by my_val

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-02 : 07:53:50

dear Harsh,
i'm getting this error

Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "WIP.COLUMN011" could not be bound.
Msg 4104, Level 16, State 1, Line 2
The multi-part identifier "wip.column010" could not be bound.
Msg 1011, Level 16, State 1, Line 2
The correlation name 'WIP' is specified multiple times in a FROM clause.

Vinod
Even you learn 1%, Learn it with 100% confidence.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-11-02 : 07:58:12

Vinod, Please try now. I edited my post.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-11-02 : 08:12:19

Dear Harsh,
now the query is working and it is taking 1 minute.....
thank you very much and is there any chance of making the time much less? actually the query is returning only four rows......

thank you for the great help

Vinod
Even you learn 1%, Learn it with 100% confidence.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2007-11-02 : 08:27:31

The performance problem isn't related to just returning 4 rows - you haven't told us how many rows are in the other tables which have to be searched first to find the 4 rows. If it was mega-millions or rows then 1 minute isn't bad performance.

Also can you post the query execution plan? Maybe you have poor indices installed (for this query).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources