plq
asked on
Slow running query
Please don't comment on this question unless you have excellent knowledge of sql server query plans and query analysis. This seems to be a query optimiser problem.
This select 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.B arcode,a.W idgetDesc, a.DatePurc hased
,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.B arcode,a.W idgetDesc, a.DatePurc hased
,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. And the final join is a 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.
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 rows and is there some logic problem in my sql thats kicked this off ?
This select 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.B
,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.B
,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. And the final join is a 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.
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 rows and is there some logic problem in my sql thats kicked this off ?
ASKER
insert into... would be logged and therefore cause excessive transaction log usage on what are queries not updates - that wouldnt scale well. "Select into" is not logged in tempdb (or any db with selectinto/bulkcopy ticked). The temp table is narrow (two columns) so an index isn't going to be any better than a table scan.
The select statement you pasted has the sql source used to create the temp table joined in "in situ"
......left join #temp as atc3 on ..... vs
......left join (select .......) as atc3 on .....
The problem is that the query optimiser is choosing a very inefficient query plan when the temp table select statement is placed inside [......left join (select .......) as atc3 on .....] and I want to understand if there's anything in the sql thats triggering that.
Looking at merge join rules I wondered if I could convince the optimiser to choose a merge join by sorting the inner subquery. So I added an order by (and a top predicate to allow the order by to compile) but still its doing a loop join and a ridiculously large intermediary table in the query plan
The original query that brought this problem to my attention had four subqueries and off the same tables was spooling 5 million rows in each table loop intermediary.
thanks
The select statement you pasted has the sql source used to create the temp table joined in "in situ"
......left join #temp as atc3 on ..... vs
......left join (select .......) as atc3 on .....
The problem is that the query optimiser is choosing a very inefficient query plan when the temp table select statement is placed inside [......left join (select .......) as atc3 on .....] and I want to understand if there's anything in the sql thats triggering that.
Looking at merge join rules I wondered if I could convince the optimiser to choose a merge join by sorting the inner subquery. So I added an order by (and a top predicate to allow the order by to compile) but still its doing a loop join and a ridiculously large intermediary table in the query plan
The original query that brought this problem to my attention had four subqueries and off the same tables was spooling 5 million rows in each table loop intermediary.
thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
just my 2 cents:
Instead of Select .. Into you should use
Insert INTO ... SELECT
and before that create the temp table manually. this way you can set Indexes and primary key.
if your temp table is indexed correctly, the subselect should be much faster.
And one more thing about this statement: I can't see that you join on a temp table here. the tables you are using are normal tables
Select top 61 a.WidgetID,a.WidgetRef,a.B
,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
HTH
Andy