Very slow query, with nested loops in execution plan. Please help

Question

Very slow query, with nested loops in execution plan. Please help

evifra

SSC-Addicted

Points: 412
More actions
August 14, 2008 at 6:00 am

#382244

My query turns very slow, that it takes 45 seconds, and it has these 3 nested loops in execution plan,as shown below
The query below takes 45 seconds in 1455 rows
SELECT
scs.Sale_Id,
scs.Sale_No,
scs.Sale_Date,
scs.Customer_Id,
scs.Customer_Code,
scs.Customer_Name,
scs.Area_Id,
scs.Area_Code,
scs.Area_Name,
scs.Dispatched_By_Id,
scs.Dispatched_By_Employee_No,
scs.Dispatched_By_Employee_Name,
scs.Terms,
scs.Due_Date,
scs.Sale_Amount,
scs.Return_Sale_Amount,
scs.Special_Collection_Discount_Amount,
scs.Collection_Amount,
scs.Number_Of_Days_Due,
scs.Sale_Balance,
CASE WHEN DATEDIFF(DAY, scs.Due_Date, GETDATE()) < 1 THEN scs.Sale_Balance ELSE 0 END AS Amount_Current,
CASE WHEN DATEDIFF(DAY, scs.Due_Date, GETDATE()) BETWEEN 1 AND 15 THEN scs.Sale_Balance ELSE 0 END AS Amount_1_15,
CASE WHEN DATEDIFF(DAY, scs.Due_Date, GETDATE()) BETWEEN 16 AND 30 THEN scs.Sale_Balance ELSE 0 END AS Amount_16_30,
CASE WHEN DATEDIFF(DAY, scs.Due_Date, GETDATE()) BETWEEN 31 AND 45 THEN scs.Sale_Balance ELSE 0 END AS Amount_31_45,
CASE WHEN DATEDIFF(DAY, scs.Due_Date, GETDATE()) BETWEEN 46 AND 60 THEN scs.Sale_Balance ELSE 0 END AS Amount_46_60,
CASE WHEN DATEDIFF(DAY, scs.Due_Date, GETDATE()) > 60 THEN scs.Sale_Balance ELSE 0 END AS Amount_Over_60
FROM
(
SELECT
s.Sale_Id,
s.[Sale_No],
s.[Sale_Date],
s.Dispatched_By_Id,
s.[Terms],
e.Employee_No AS Dispatched_By_Employee_No,
e.Name AS Dispatched_By_Employee_Name,
tc.[Customer_Id],
tc.[Code] AS Customer_Code,
tc.[Name] AS Customer_Name,
ta.[Area_Id],
ta.[Code] AS Area_Code,
ta.[Name] AS Area_Name,
DATEADD(DAY, s.Terms, s.Sale_Date) AS Due_Date,
ISNULL(s.Sale_Amount, 0) AS Sale_Amount,
ISNULL(rs.Return_Sale_Amount, 0) AS Return_Sale_Amount,
ISNULL(c.Special_Discount, 0) AS Special_Collection_Discount_Amount,
ISNULL(c.Collection_Amount, 0) AS Collection_Amount,
ISNULL(s.Sale_Amount, 0)
- ISNULL(rs.Return_Sale_Amount, 0)
- ISNULL(c.Special_Discount, 0)
- ISNULL(c.Collection_Amount, 0) AS Sale_Balance,
CASE WHEN DATEADD(DAY, s.Terms, s.Sale_Date) < GETDATE() THEN DATEDIFF(DAY, DATEADD(DAY, s.Terms, s.Sale_Date), GETDATE()) ELSE 0 END AS Number_Of_Days_Due
FROM
dbo.tblSale AS s LEFT OUTER JOIN
(
SELECT
Reference_Sale_Id AS Sale_Id,
SUM(Return_Sale_Amount) AS Return_Sale_Amount
FROM
dbo.tblReturn_Sale
GROUP BY Reference_Sale_Id
) AS rs ON rs.Sale_Id = s.Sale_Id LEFT OUTER JOIN
(
SELECT
tc.Sale_Id,
SUM(ISNULL(tc.Special_Discount, 0)) AS Special_Discount,
SUM(ISNULL(tc.[Collection_Amount],0)) AS Collection_Amount
FROM
dbo.tblCollection AS tc
GROUP BY
tc.Sale_Id
) AS c ON s.Sale_Id = c.Sale_Id
INNER JOIN dbo.tblEmployee AS e ON e.Employee_Id = s.Dispatched_By_Id
INNER JOIN dbo.tblCustomer AS tc ON tc.Customer_Id = s.Customer_Id
INNER JOIN dbo.tblArea AS ta ON tc.Area_Id = ta.Area_Id
WHERE
s.Terms > 0
AND ISNULL(s.Sale_Amount, 0)
- ISNULL(rs.Return_Sale_Amount, 0)
- ISNULL(c.Special_Discount, 0)
- ISNULL(c.Collection_Amount, 0) != 0
) AS scs
But when i removed the INNER JOIN to tblArea, the three nested loops was gone and so the performance was superb,
as shown in the images below
So i thought the problem was this tblArea,
the DML below is tblArea
CREATE TABLE [dbo].[tblArea](
[Area_Id] [int] IDENTITY(1,1) NOT NULL,
[Code] [nvarchar](10) NOT NULL,
[Name] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_tblArea] PRIMARY KEY CLUSTERED
(
[Area_Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
as you can see, the primary is index as CLUSTERED, so I decided to change this to the opposite, NON CLUSTERED
executing the code below
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.tblArea
DROP CONSTRAINT PK_tblArea
GO
ALTER TABLE dbo.tblArea ADD CONSTRAINT
PK_tblArea PRIMARY KEY NONCLUSTERED
(
Area_Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
after this alter and re-execute my query now including tblArea that i once removed before, the performance works well. the execution plan has no three nested loops at all, and so i thought the problem is clustered index, for my curiosity to satisfy I returned again the non-clustered to clustered index, and check the execution plan, but then there is no three nested loops in my execution plan, i thought i will see the three nested loops because of clustered index but i am wrong..
BUT I WANNA KNOW WHY THIS HAPPENS??
BUT I WANNA KNOW WHY THIS HAPPENS??
BUT I WANNA KNOW WHY THIS HAPPENS??
BUT I WANNA KNOW WHY THIS HAPPENS??

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply

evifra SSC-Addicted Points: 412 More actions · Answer 1

Sorry, don't bother about this post.. I already solved my problem by rebuilding the index.. But thank you SQL Server Central from the perfect timing of posting the article this day (2008-08-14) http://technet.microsoft.com/en-us/magazine/cc671165.aspx