Performance Problem in SqlServer 2000

[steenbreker]

Hi,

I have this query, that should return some 2000 rows, but it takes up to 30 minutes to get them :

SELECT 	A.VOBnr, ...........
FROM 	dbo.SalesOrder A
	INNER JOIN dbo.Type B ON A.ModelGroup = B.ModelGroup and A.OrderModel = B.OrderModel 
	INNER JOIN dbo.Geography C ON A.CountryCode = C.CountryCode 
	INNER JOIN dbo.Dealer D ON A.BpNr = D.BpNr 
	INNER JOIN dbo.Market E ON A.MarketCode = E.MarketCode 
	INNER JOIN dbo.Cabin F ON A.CabinNr = F.CabinNr 
	INNER JOIN dbo.Colour G ON A.ColourCode = G.ColourCode 
	INNER JOIN dbo.DealerDirect H ON A.SoldOrigin = H.SoldOrigin 
	INNER JOIN dbo.FleetRetail I ON A.EndCustomerType = I.EndCustomerType 
	INNER JOIN dbo.Currency J ON A.CurrencyCode = J.CurrencyCode 
	INNER JOIN dbo.BpOrder K ON A.BpOrderNr = K.BpOrderNr 
	INNER JOIN dbo.BpInvoice L ON A.BpInvoiceNr = L.BpInvoiceNr 
	INNER JOIN dbo.EndCustomer M ON A.EndCustomerNr = M.EndCustomerNr

If however I delete the inner join on Dealer (D) or BpOrder (K), it will return the rows within 2 seconds. So I can use either the Dealer (D) or the BpOrder (K) inner join without significant performance drop, but using both will collapse performance.

Thanx for all hints

 

[nigelrivett]

Update the statistics on all tables

Look at the query plan and give index hints where needed.

Try spliting it up into smaller queries populating temp tables. You'll find it easier to optime and the server will probably do a better job.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.