How to improve SQL Database Select query's performance ?

See more:

SQL-Server

Hi,
i have a SQL database select Query which brings 60 columns from 10 tables via join. i need to fetch 300000(3 Lacs) rows from database. It takes 18-19 seconds to fetch 300000 rows. I want to decrease this execution time to 10-12 secs.

I used DATABASE ENGINE TUNING ADVISORof SQL SERVER 2008 R2, it suggested me 2 create some Indexes and statistics. I have created all this, but could not improved the performance of my query.

can anybody suggest me right solution ???

This is my Query :-

select
i.IssueId, i.IssueNumber, i.TeamId, t.Name AS TeamName, i.IssueStatus, p1.TextValue AS StatusText, p1.SortNumber AS StatusOrder, i.Substatus, 
                      p2.TextValue AS SubstatusText, p2.SortNumber AS SubstatusOrder, i.CreatedBy, cc.FullName AS CreatedByFullName, i.CreatedAt, i.UpdatedBy, 
                      cu.FullName AS UpdatedByFullName, i.UpdatedAt, i.ReportedBy, cReported.FullName AS ReportedByFullName, i.ReportedAt, i.Title, i.Description, i.Severity, 
                      pSeverity.TextValue AS SeverityText, i.Priority, pPriority.TextValue AS PriorityText, i.IssueType, pType.TextValue AS TypeText, pType.SortNumber AS TypeOrder, 
                      i.Subtype1, pST1.TextValue AS SubType1Text, pST1.SortNumber AS SubType1Order, i.Subtype2, pST2.TextValue AS SubType2Text, 
                      pST2.SortNumber AS SubType2Order, i.ProjectId, pr.Name AS ProjectName, i.LocationId, lc.Name AS LocationName, i.DepartmentId, dp.Name AS DepartmentName, 
                      i.Escalated, i.AssignedTo, ucAssign.FullName AS AssignedToFullName, i.AssignedAt, i.DueAt, i.ClosedAt, i.ClosedBy, udf.UDFString1, udf.UDFBool1, udf.UDFInt1, 
                      udf.UDFUserId1, udfc1.FullName AS UDFUser1Name, udf.UDFPickList1, udfp1.TextValue AS UDFPickList1Text, udfp1.SortNumber AS UDFPickList1Order, t.TenantId, 
                      uc.LoginEmail, uc.UserId, DATEDIFF(d, i.CreatedAt, GETDATE()) AS Age, i.AttachmentCount
FROM         dbo.ITIssue AS i INNER JOIN
                      dbo.PicklistItem AS p1 ON i.IssueStatus = p1.PicklistItemId LEFT OUTER JOIN
                      dbo.PicklistItem AS p2 ON i.Substatus = p2.PicklistItemId INNER JOIN
                      dbo.PicklistItem AS pType ON i.IssueType = pType.PicklistItemId LEFT OUTER JOIN
                      dbo.PicklistItem AS pST1 ON i.Subtype1 = pST1.PicklistItemId LEFT OUTER JOIN
                      dbo.PicklistItem AS pST2 ON i.Subtype2 = pST2.PicklistItemId LEFT OUTER JOIN
                      dbo.PicklistItem AS pSeverity ON i.Severity = pSeverity.PicklistItemId LEFT OUTER JOIN
                      dbo.PicklistItem AS pPriority ON i.Priority = pPriority.PicklistItemId LEFT OUTER JOIN
                      dbo.Project AS pr ON i.ProjectId = pr.ProjectId LEFT OUTER JOIN
                      dbo.Location AS lc ON i.LocationId = lc.LocationId LEFT OUTER JOIN
                      dbo.Department AS dp ON i.DepartmentId = dp.DepartmentId INNER JOIN
                      dbo.TenantUser AS uc ON i.CreatedBy = uc.UserId LEFT OUTER JOIN
                      dbo.TenantUser AS uu ON i.UpdatedBy = uu.UserId LEFT OUTER JOIN
                      dbo.TenantUser AS uAssign ON i.AssignedTo = uAssign.UserId LEFT OUTER JOIN
                      dbo.Contact AS ucAssign ON uAssign.ContactId = ucAssign.ContactId LEFT OUTER JOIN
                      dbo.Contact AS cc ON uc.ContactId = cc.ContactId LEFT OUTER JOIN
                      dbo.Contact AS cu ON uu.ContactId = cu.ContactId LEFT OUTER JOIN
                      dbo.UDFValues AS udf ON udf.EntityId = i.IssueId LEFT OUTER JOIN
                      dbo.TenantUser AS udfu1 ON udfu1.UserId = udf.UDFUserId1 LEFT OUTER JOIN
                      dbo.Contact AS udfc1 ON udfu1.ContactId = udfc1.ContactId LEFT OUTER JOIN
                      dbo.PicklistItem AS udfp1 ON udfp1.PicklistItemId = udf.UDFPickList1 INNER JOIN
                      dbo.Team AS t ON t.TeamId = i.TeamId LEFT OUTER JOIN
                      dbo.TenantUser AS uRepored ON i.ReportedBy = uRepored.UserId LEFT OUTER JOIN
                      dbo.Contact AS cReported ON cReported.ContactId = uRepored.ContactId