Create index on long query for report SQL Server

Question

the following query takes few minutes to generate 100000+ records from db, had tried creating index but it is with view, getting compiling errors,,

WITH OrderedOrders AS
(

select ROW_NUMBER() OVER (ORDER BY a.fSONo) AS 'RowNumber', 
a.fSONo as orderno, a.fSODate as orderdate, MONTH(a.fsodate) as OM, Day(a.fsodate) as OD, YEAR(A.fSODate) as OY,
b.fTypeDesc as app_type, c.fCustRgsName as custname, 
d.fMemCode as HPCode, d.fMemName,a.fSODeptCode as DeptCode, a.fSOGrpCode as GrpCode, a.fSOOrgCode as OrgCode,
a.fSOTotalPV as PV, a.fSOPVMonth as PV_Mth, a.fSOPVYear as PV_Year, c.fCustIsCompany as IsCompany, c.fCustGender as Gender, e.fTypeDesc as Race,
f.fStatusName as orderstatus, g.fBranchName as KeyInLocation, i.fBranchName as DSC,i.fBranchCode as DSCCode , (r.fInstallSerialNo) SerialNo , (R.fInstallSirimNo) SirimNo,
j.fCustAdd1 as InstAdd1, j.fCustAdd2 as InstAdd2, j.fCustAdd3 as InstAdd3, j.fCustAdd4 as InstAdd4,
a.fSOMthRentAmt as MonthlyRentalFees, a.fSOTotalAmt as RPF_Out_Ins_Amt, v.fRentAmount-(v.total+v.reverse) as RPF_YS,k.OUT_INS_YSAmount,
m.fStkCode as StkCode, m.fStkDesc as StkDescription, o.fStatusName as callstatus,
q.fStatusName as installstatus, p.fInstallDate as PreInstallDate, s.fReasonDesc as FailInstallReasons, t.fInstallDate as ComInstDate,
u.fpromocode as Promocode, u.fPromoDesc as PromoDesc , (us1.fUserName) KeyInUser ,
isnull(t.fInstallDocRefNo1,'') as Ref_No1 ,
isnull(t.fInstallDocRefNo2,'') as Ref_No2

from tSalesOrderM a
left join tSupTypeCode b on a.fSOAppTypeID = b.fTypeID
left join tCustomer c on a.fSOCustID = c.fCustID
left join tmember d on a.fSOMemID = d.fMemID
left join tSupTypeCode e on c.fCustRaceID = e.fTypeID
left join tSupStatusCode f on a.fSOStatusID = f.fStatusID
left join tSupBranch g on a.fSOBranchID = g.fBranchID
left join tInstallation h on a.fSOID = h.fInstSOID
left join tSupBranch i on h.fInstBranchID = i.fBranchID
left join tCustAddress j on h.fInstAddID = j.fCustAddID
left join (select fTradeSOID, SUM(fTradeAmount) as OUT_INS_YSAmount
			from tAccTradeLedger
			group by fTradeSOID) k on a.fSOID = k.fTradeSOID
inner join tSalesOrderD l on a.fSOID = l.fSOID
left join tInvStock m on l.fSOItemStkID = m.fStkID
left join (select fcallsoid, fcallstatusid, fcallentryid
			from tCallEntry
			where fCallEntryID in (
									select MAX(fcallentryid)
									from tCallEntry
									group by fCallSOID)) n on a.fSOID = n.fCallSOID
left join tSupStatusCode o on n.fCallStatusID = o.fStatusID
left join (select fSOID, fInstallStatusID, fInstallEntryID, fInstallDate
			from tInstallEntry 
			where fInstallEntryID in (
										select max(fInstallEntryID)
										from tInstallEntry
										group by fSOID)) p on a.fSOID = p.fSOID
left join tSupStatusCode q on p.fInstallStatusID = q.fStatusID
left join tInstallResult r on p.fInstallEntryID = r.fInstallEntryID
left join tSupReasonCode s on r.fInstallFailID = s.fReasonID
left join tInstallResult t on p.fInstallEntryID = t.fInstallEntryID and t.fInstallStatusID = 4
left join tSalesPromoM u on a.fSOPromoID = u.fPromoID
left join tUser us1 on us1.fUserID = a.fSOCreateBy
left join (select s.fsoid, r.fRentAmount, isnull(sum(m1.fpaytotalamt),0) as total, isnull(sum(m2.fpaytotalamt),0) as reverse
			from tAccRentLedger r
			inner join tSalesOrderM s on r.fRentSOID = s.fSOID and r.fRentDocNo = s.fSONo
			left join tpaym m1 on r.fRentSOID = m1.fpaysoid and m1.fpaytypeid = 98
			left join tpaym m2 on m1.fpayid = m2.fpaymatchid
			where r.frentdoctypeid = 161
			group by s.fsoid, r.fRentAmount	)v on a.fSOID = v.fSOID
where a.fsoapptypeid in (68,67,66,145) 


)
SELECT * 
FROM OrderedOrders 
WHERE RowNumber BETWEEN 1  AND 15000

Answer 1

Wow, 100,000 rows report? Can you show us an execution plan of that query?

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Showing the query plan (In the Menu Query-Show estimated Execution Plan) will also give a suggested index hint if it thinks of one in ssms 2008

---

Best Wishes, The Redman; If something helps, please help show it by voting, if it solves, bonus!

Answer 3

besides creating view index, is there any other way of creating the query with index?  0.o

Answer 4

It will be possible to say after  you show us the execution plan

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 5

what is execution plan?

From the message tab besides results, noticed theres a message saying :

Warning: Null value is eliminated by an aggregate or other SET operation.

(15000 row(s) affected)

Answer 6

There's a button which when you hover over say's something like show actual execution plan.
What this will do is show the physical operations the optimizer chose to implement the logical conditions in your query.
As it was said before there may be index hints displayed if using Management studio on a 2008 or later instance.

What you will be looking for are things like "scans" which is where the optimizer has chosen to look through every record in a table. Ideally you want to change this by adding an index to "seek" for records which is a lot less intensive on IO and RAM.

---

If you find this helpful, please mark the post as helpful,
If you think this solves the problem, please propose or mark it an an answer.

Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

Richard Douglas
My Blog: Http://SQL.RichardDouglas.co.uk
Twitter: @SQLRich

Answer 7

i guess i got it,

having mentioned that,

1. Index Scan (NonClustered) [tSalesOrderM].[tSalesOrderM_uq] [a] Cost: 0%

2. Key Lookup (Clustered) [tSalesOrderM].[tSalesOrderM_pk] [a] Cost : 0%

3. Index Seek (NonClustered) [tSalesOrderD].[tSalesOrderD fSOID_idk [l]  Cost : 1%

4. Clustered Index Seek (Clustered) [tSupTypeCode].[tSupTypeCode_pk] [b] Cost : 0%

5. Index Seek (NonClustered) [tCustomer].[tCustomer_idx] [c] Cost : 0%

6. Key Lookup (Clustered) [tCustomer].[PK_tCustome_A802C29F640DD89F] [c]

7. Clustered Index Seek (Clustered) [tMember].[PK_tMember_875A4B1D21B6055D] [d]

8. Clustered Index Seek (Clustered) [tSupTypeCode].[tSupTypeCode_pk] [e] Cost : 0%

9. Clustered Index Seek (Clustered) [tSupStatusCode].[PK_tSupStat_1243CB6E47DBAE45] [f] Cost : 0%.

10. Clustered Index Scan (Clustered) [tSupBranch].[PK_tSupBran_4F9458CE300424B4] [g]

11. Index Seek (NonClustered) [tInstallation].[tInstallation_idx] [h] Cost : 0%

12. RID Lookup (Heap) [tInstallation] [h] Cost: 0%

13. Clustered Index Scan (Clustered) [tSupBranch].[PK_tSupBran_4F9458CE300424B4] [i]
14. Clustered Index Seek (Clustered) [tCustAddress].[tCustAddress_pk] [j] Cost : 0 %

15. Index Seek (NonClustered) [tAccTradeLedger].[tAccTradeLedgerSOID_idx]

16. Clustered Index Seek (Clustered) [tInvStock].[tInvStock].[tInvStock_pk] [m]

17. Index Scan (NonClustered) [tCallEntry].[tCallEntry_fCallTypeID_fCallStatusID] Cost: 5%

18. Index Scan (NonClustered) [tCallEntry].[tCallEntry_fCallTypeID_fCallStatusID] Cost: 5%

19. Clustered Index Seek (Clustered) [tSupStatusCode].[PK_tSupStat_1243CB6E47DBAE45] [o] Cost : 0%

20. Index Seek (NonClustered) [tInstallEntry].[tInstallEntrySOID_idx] Cost : 1%

21. Key Lookup (Clustered) [tInstallEntry].[tInstallEntry_pk] Cost : 1%

22. Index Scan (NonClustered) [tInstallEntry].[tInstallEntrySOID_idx] Cost : 0%

23. Clustered Index Seek (Clustered) [tSupStatusCode].[PK_tSupStat_1243CB6E47DBAE45] [q]

24. Index Seek (NonClustered) [tInstallResult].[tInstallResultEntryID_udx] [r] Cost : 1%

25. Key Lookup (Clustered) [tInstallResult].[tInstallResult_pk] [r]

26. Clustered Index Seek (Clustered) [tSupReasonCode].[PK_tSupReasonCode].[PK_tSupReas_9DAFDA4806ED0088] [s]

27. Index Seek (NonClustered) [tInstallResult].[tInstallResultEntryID_udx] [t] Cost : 1%

28. Key Lookup (Clustered) [tInstallResult].[tInstallResult_pk] [t]

29. Clustered Index Seek (Clustered) [tSalesPromoM].[PK_tSalesPr_5C3B99BE42E1EEFE] [u]

30. Clustered Index Seek (Clustered) [tUser].[PK_tUser_A48DC6F309353BAD] [us1] Cost: 1%

31. Clustered Index Seek (Clustered) [tSalesOrderM].[tSalesOrderM_pk] [s] Cost : 1%

32. Index Seek (NonClustered) [tAccRentLedger].[tAccRentLedger_idx4] [r] Cost : 1%

33. Index Seek (NonClustered) [tPayM].[PK_tPayM_6A25003D59C55456] [m1] Cost : 17%

34. Index Seek (NonClustered) [tPayM].[tPayM_idx5] [m2] Cost : 0%

35. Key Lookup (Clustered) [tPayM].[PK_tPayM_6A25003D59C55456] [m2] Cost : 1%

 

Answer 8

You can move intermediate result of the query into a temporary table and having an index on row_number column perform filter ....

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 9

You have a lot of key lookups there, and also an RID Lookup which can be bad for performance. I wrote a blog post on Key Lookups some time back, take a look: http://sql.richarddouglas.co.uk/archive/2011/10/t-sql-tuesday-23-key-lookups.html

---

If you find this helpful, please mark the post as helpful,
If you think this solves the problem, please propose or mark it an an answer.

Please provide details on your SQL Server environment such as version and edition, also DDL statements for tables when posting T-SQL issues

Richard Douglas
My Blog: Http://SQL.RichardDouglas.co.uk
Twitter: @SQLRich