How to improve SQL Database Select query's performance ?
-
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 ADVISER of 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 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 O
-
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 ADVISER of 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 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 O
Count your blessings. I'd be ecstatic if some of the queries I'm working on currently took only twenty seconds. One of the techniques I found that improved some of the worst (similar to yours) from several hours to about twenty minutes is the use of Common Table Expressions. Instead of SELECT ... FROM A INNER JOIN B... INNER JOIN C... INNER JOIN D... You can do WITH a AS ( SELECT ... FROM A INNER JOIN B ... ) , b AS ( SELECT ... FROM a INNER JOIN C ... ) SELECT ... FROM b INNER JOIN D ...
-
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 ADVISER of 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 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 O
I would strip your query down to the INNER JOINs and execute into a temp table. Optimize this if necessary. Then I would apply the LEFT JOIN lookups to the temp table. I have found that complex queries can sometimes be sped up by a huge factor when broken up logically. In this case, you have the engine joining and executing numerous correlated subqueries (in the form of LEFT JOINS) at the same time.
-
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 ADVISER of 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 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 O
In addition to Michael Potters suggestion, identify the inner join or where clause that reduces the quantity by the most records, this is the same as MPs suggestion except more focused. Shove that into either a temp table or table var. A temp table may have indexes applied if required.
Never underestimate the power of human stupidity RAH
-
Count your blessings. I'd be ecstatic if some of the queries I'm working on currently took only twenty seconds. One of the techniques I found that improved some of the worst (similar to yours) from several hours to about twenty minutes is the use of Common Table Expressions. Instead of SELECT ... FROM A INNER JOIN B... INNER JOIN C... INNER JOIN D... You can do WITH a AS ( SELECT ... FROM A INNER JOIN B ... ) , b AS ( SELECT ... FROM a INNER JOIN C ... ) SELECT ... FROM b INNER JOIN D ...
PIEBALDconsult wrote:
Count your blessings
I agree. 18-19 seconds on a query that looks like its working with a good number of tables, and joins which is returning 300k rows does seem to horrible. Good luck to him at shaving down anymore time on it.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
-
PIEBALDconsult wrote:
Count your blessings
I agree. 18-19 seconds on a query that looks like its working with a good number of tables, and joins which is returning 300k rows does seem to horrible. Good luck to him at shaving down anymore time on it.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
I agree with the performance observed. A query which returns 300K rows is typically not a online transaction where a user is waiting for the results to display, so what does it matter if you shave a few seconds off the execution time? (rhetorical question here) Also noted is that there is no "where clause", so each time this query is run it will take longer and longer. Right? More and more data will be created each day... If this is an online transaction, then maybe a strategy where summary tables are populated as transactions are being generated might be something to investigate.