I have a view, which left outer join's 3 tables together yet it it being incredibly slow (as in, over a minute to return 300 transactions). Last week, it was working fine though.
Anyone got any ideas what could be causing it?
Here is the code:
Basically, it gets all records in 'Transactions' which haven't got a record in 'ParentMailTransactionsSent' and also which are newer than the date in ParentMailAccount.
SELECT dbo.Transactions.ID, dbo.Transactions.PupilID, dbo.Transactions.TransactionType, dbo.Transactions.Amount, dbo.Transactions.DateIn, dbo.Transactions.Operator, dbo.Transactions.AuthMethod, dbo.Transactions.Till, dbo.Transactions.FSM, dbo.Transactions.Complimentary_Meal,
dbo.Transactions.[User], dbo.Transactions.Void, dbo.Transactions.Balance, dbo.Transactions.PurseType, dbo.ParentMailTransactionsSent.ID AS Expr1,
FROM dbo.Transactions LEFT OUTER JOIN
dbo.ParentMailTransactionsSent ON dbo.Transactions.ID = dbo.ParentMailTransactionsSent.TransactionID LEFT OUTER JOIN
dbo.ParentMailAccount ON dbo.Transactions.PupilID = dbo.ParentMailAccount.PupilID
WHERE (dbo.ParentMailTransactionsSent.TransactionID IS NULL) AND (dbo.ParentMailAccount.ParentMailAccountID IS NOT NULL) AND
(dbo.ParentMailAccount.DateTime < dbo.Transactions.DateIn)
I know Left Outer Joins are a bit slow, but not this bad!