This seems to be a query optimiser problem.
This selects data into a temp table, and then use the temp table in a join...
Select a.WidgetID, Sum(a.Amount * (b.Sign)) As WidgetValue Into #temp From WidgetTransaction As a inner JOIN TransactionType As b On a.TransactionTypeID = b.TransactionTypeID inner JOIN Widget As aass On a.WidgetID = aass.WidgetID inner JOIN CurrentPeriod As cp On aass.CompanyID = cp.CompanyID Where a.BookID = 1 And b.TransactionTypeClass In ('ADDITION') And cp.UserID = 'ADMIN' And (a.ApplyDate <= cp.EndDate OR (a.TransactionTypeID = 1 And aass.DatePurchased <= cp.EndDate)) And a.BudgetID > 0 Group By a.WidgetID
Select top 61 a.WidgetID,a.WidgetRef,a.Barcode,a.Widge
,atc3.WidgetValue As CurrencyOriginalValue
From Widget As a
left JOIN #temp As atc3 On a.WidgetID = atc3.WidgetID
Order By a.DatePurchased
... runs very fast and the query plan shows correct data volumes at each branch. Each join is a hash join .
When I try to combine the temp table into the main query via a subselect ....
Select top 61 a.WidgetID,a.WidgetRef,a.Barcode,a.Widge
,atc3.WidgetValue As CurrencyOriginalValue
From Widget As a
left JOIN (Select a.WidgetID, Sum(a.Amount * (b.Sign)) As WidgetValue From WidgetTransaction As a inner JOIN TransactionType As b On a.TransactionTypeID = b.TransactionTypeID inner JOIN Widget As aass On a.WidgetID = aass.WidgetID inner JOIN CurrentPeriod As cp On aass.CompanyID = cp.CompanyID Where a.BookID = 1 And b.TransactionTypeClass In ('ADDITION') And cp.UserID = 'ADMIN' And (a.ApplyDate <= cp.EndDate OR (a.TransactionTypeID = 1 And aass.DatePurchased <= cp.EndDate)) And a.BudgetID > 0 Group By a.WidgetID) As atc3 On a.WidgetID = atc3.WidgetID
Order By a.DatePurchased
... this runs relatively slowly (very slowly when you have four subtables). The final join (to pull in the subquery into the main select) is showing a "Table Spool/Lazy Spool" into a nested loops join, and the intermediate table spool itself has 139,000 rows in it. Even though when I run the inner select statement on its own it correctly produces 2300 records. With four subtables each "Table Spool/Lazy Spool" join-in has 5 million rows and the query takes 58 seconds.
The main Widget table has 2300 rows and the WidgetTransaction table has 21000 rows.
The problem doesn't occur when I cut down the inner query to a much simpler select statement (even though it selects more records)
When I force it to be a HASH join the problem goes away.
OK it sounds like I've answered my own question with forcing a hash join but I want to understand WHY sql server is choosing a table spool/lazy sppol into a holding table of 139000 or even 5 million rows and is there some logic problem in my sql thats kicked this off ?
The reason is that the optimiser can't accurately assess the number of rows being returned in the subquery and so it assumes a plan based on what it thinks the row count will be.
In your first set of queries the optimiser nows how many rows are in the temp table and so can optimise the query correctly.
It could well be due to the OR clause you have
Try this
Select top 61 a.WidgetID,a.WidgetRef,a.Barcode,a.WidgetDesc,a.DatePurchased
,atc3.WidgetValue + ISNULL(act4.WidgetValue,0) As CurrencyOriginalValue
From Widget As a
left JOIN (Select a.WidgetID, Sum(a.Amount * (b.Sign)) As WidgetValue
From WidgetTransaction As a
inner JOIN TransactionType As b On a.TransactionTypeID = b.TransactionTypeID
inner JOIN CurrentPeriod As cp On aass.CompanyID = cp.CompanyID
Where a.BookID = 1
And b.TransactionTypeClass In ('ADDITION')
And cp.UserID = 'ADMIN'
And (a.ApplyDate <= cp.EndDate
)
And a.BudgetID > 0
Group By a.WidgetID) As atc3 On a.WidgetID = atc3.WidgetID
left JOIN (Select a.WidgetID, Sum(a.Amount * (b.Sign)) As WidgetValue
From WidgetTransaction As a
inner JOIN TransactionType As b On a.TransactionTypeID = b.TransactionTypeID
inner JOIN Widget As aass On a.WidgetID = aass.WidgetID
inner JOIN CurrentPeriod As cp On aass.CompanyID = cp.CompanyID
Where a.BookID = 1
And b.TransactionTypeClass In ('ADDITION')
And cp.UserID = 'ADMIN'
And a.ApplyDate > cp.EndDate
AND a.TransactionTypeID = 1
And aass.DatePurchased <= cp.EndDate
And a.BudgetID > 0
Group By a.WidgetID) As atc4 On a.WidgetID = atc3.WidgetID
Order By a.DatePurchased
No comments:
Post a Comment