Hello Dave,

Thank you very much for reply.

I was wondering about the fact that you noticed. I tried to execute a similar query in our different client's TEST environment

Here is the code(just to emphasize that the queries are similar)

SELECT

datename(mm, dbo.Trade.TradeDate),

dbo.DataSource.Name,

datename(yy, dbo.Trade.TradeDate),

sum(dbo.Trade.TradeValueCcy),

SUM(dbo.Trade.SalesCreditCcy),

sum(dbo.Trade.TradeVolumeCcy),

SUM(dbo.Trade.MarginValue),

TradingDepartment.Description,

TradingDesk.Description,

SalesDepartment.Description,

SalesDesk.Description,

Customer.Description,

ParentProduct.Description,

Product.Description

FROM

dbo.Trade

INNER JOIN dbo.TradeMember ON dbo.Trade.TradeID=dbo.TradeMember.TradeID

INNER JOIN dbo.Member Product ON dbo.TradeMember.ProductID=Product.MemberID

INNER JOIN dbo.Dimension ParentProduct ON Product.ParentDimensionID=ParentProduct.DimensionID

INNER JOIN dbo.Member SalesDesk ON SalesDesk.MemberID=TradeMember.SalesDeskID

INNER JOIN dbo.Dimension SalesDepartment ON SalesDepartment.DimensionID=SalesDesk.ParentDimensionID

INNER JOIN dbo.Member Customer ON Customer.MemberID=dbo.TradeMember.CustomerID

INNER JOIN dbo.CustomerMemberDetail TradeCustomer ON TradeCustomer.MemberID=Customer.MemberID

INNER JOIN dbo.Member TradingDesk ON TradingDesk.MemberID=dbo.TradeMember.TradingDeskID

INNER JOIN dbo.Dimension TradingDepartment ON TradingDepartment.DimensionID=TradingDesk.ParentDimensionID

INNER JOIN dbo.DataSource ON dbo.DataSource.DataSourceID=dbo.Trade.DataSourceID

WHERE

(

( dbo.Trade.TradeDate >= DATEADD(DD, -1 * DAY(GETDATE()), DATEADD(MM, -1 * MONTH(GETDATE()) + 1, GETDATE())))

)

GROUP BY

datename(mm, dbo.Trade.TradeDate),

dbo.DataSource.Name,

datename(yy, dbo.Trade.TradeDate),

TradingDepartment.Description,

TradingDesk.Description,

SalesDepartment.Description,

SalesDesk.Description,

Customer.Description,

ParentProduct.Description,

Product.Description

And the execution plan proves that your idea might be right. Please find it attached.

Here are the IO stats:

Table 'DataSource'. Scan count 1, logical reads 2, physical reads 1, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Dimension'. Scan count 9, logical reads 5694, physical reads 4, read-ahead reads 1715, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Member'. Scan count 12, logical reads 11492, physical reads 4, read-ahead reads 2620, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'TradeMember'. Scan count 3, logical reads 224456, physical reads 398, read-ahead reads 204206, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Trade'. Scan count 3, logical reads 2451785, physical reads 485, read-ahead reads 2176232, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I appreciate that the databases and queries are different and the performance difference can be caused by that.

Sorry for probably stupid question, but how to let SQL engine know that there are 465,000 rows? I rebuilt all the indexes(thought it might help), but had no luck with it.

And one not related question: what program do you use to view SQL executions plans? Really liked number of estimated rows displayed next to the edge.

Thanks a lot once again.

Small update: the query I'm working on is generated by Business objects(reporting tool), so can't really use a temporary table or modify SQL(this is not 100 correct, I can change joins in BOBJ designer, which affects the way it generates SQL)

Igor