SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 how to choose best columns to create indexes
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

neellotus
Starting Member

India
14 Posts

Posted - 02/22/2013 :  05:09:51  Show Profile  Reply with Quote
Hi All,

I have created view as per below query but it is taking huge time to show all result so i want to create indexes on this view, pls help me which columns need to create indexes:-

CREATE VIEW STK_DETAIL AS
SELECT 1 AS ROWID,E.CODE,E.A_CODE,E.I_CODE,E.ZZSF_LOT,E.MRN_NO,E.MRN_CAT,E.URATE,E.QTY,E.QTY1,(E.QTY-E.QTY1) PEND_QTY FROM
(SELECT C.CODE,C.A_CODE,C.I_CODE,C.ZZSF_LOT,C.MRN_NO,C.MRN_CAT,AVG(C.URATE) URATE,SUM(C.QTY) QTY,SUM(C.QTY1) QTY1 FROM
(SELECT A.DOC_CAT,A.DOC_NO,A.DOC_DT,A.GD_CD,A.A_CODE,A.I_CODE,A.UNIT,A.ZZSF_LOT,A.MRN_CAT,A.MRN_NO,A.URATE,a.QTY1 AS QTY, 0 AS QTY1,A.CODE
FROM PENDING A
WHERE A.DOC_CAT='ISV1' AND a.DOC_CLASS='06' and a.qty_ytdc=0
UNION ALL
SELECT A.DOC_CAT,A.DOC_NO,A.DOC_DT,'' GD_CD,A.A_CODE,A.I_CODE,A.UNIT,A.ZZSF_LOT,A.MRN_CAT,A.MRN_NO,A.URATE,a.QTY1 AS QTY, 0 AS QTY1,A.CODE
FROM PEND_FISL A
WHERE A.DOC_CAT='FISL' AND a.DOC_CLASS='06' and a.qty_ytdc=0
UNION ALL
SELECT A.DOC_CAT,A.DOC_NO,A.DOC_DT,a.GD_CD,A.A_CODE,A.I_CODE,A.UNIT,a.ZZSF_LOT,a.MRN_CAT,A.MRN_NO,A.URATE,A.QTY3 AS QTY, 0 AS QTY1,A.CODE
FROM ISSUE A
WHERE A.DOC_CAT='FIS1' AND a.DOC_CLASS='06'
UNION ALL
SELECT A.DOC_CAT,A.DOC_NO,A.DOC_DT,'' GD_CD,A.A_CODE,A.I_CODE,A.UNIT,a.ZZSF_LOT,a.MRN_CAT,A.MRN_NO,A.URATE,A.QTY AS QTY, 0 AS QTY1,A.CODE
FROM CTP_B A
WHERE A.DOC_CAT='MTPR'
UNION ALL
SELECT B.DOC_CAT,B.DOC_NO,B.DOC_DT,'' GD_CD,B.A_CODE,B.I_CODE,B.UNIT,D.ZZSF_LOT,D.MRN_CAT,D.MRN_NO,D.URATE,0 AS QTY,D.QTY AS QTY1, b.CODE
FROM PTM_A B, PTM_B D WHERE B.DOC_CAT+B.DOC_NO=D.DOC_CAT+D.DOC_NO
UNION ALL
sELECT B.DOC_CAT,B.DOC_NO,B.DOC_DT,'' GD_CD,B.A_CODE,B.I_CODE,B.UNIT,D.ZZSF_LOT,D.MRN_CAT,D.MRN_NO,D.URATE,0 AS QTY,D.QTY AS QTY1, b.CODE
FROM DIPF_A B, DIPF_B D WHERE B.DOC_CAT+B.DOC_NO=D.DOC_CAT+D.DOC_NO AND NOT ISNULL(D.ZZSF_LOT,'')='') C
GROUP BY C.CODE,C.A_CODE,C.I_CODE,C.ZZSF_LOT,C.MRN_NO,C.MRN_CAT) E
WHERE (E.QTY-E.QTY1) >0


Pls tell me i will be very thankful to you.

Neel

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 02/23/2013 :  02:07:33  Show Profile  Reply with Quote
see

http://msdn.microsoft.com/en-us/library/ms187864(v=sql.105).aspx

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000