optimize SQL select in tableadapter fill method

Question

I have used following query to fill my table adapter but when my application run it is so slow ,but when I run in sql management its fast! let me know the problem of my select and how could be more optimized !!!

SELECT (
        SELECT FullName AS Expr1
        FROM Sales.CustomerInfo
        WHERE (AccountFK = Ordering.Orders.CustomerFK)
        ) AS Customer
    ,Ordering.Orders.OrderID
    ,Ordering.Orders.OrderDate
    ,Ordering.Orders.OrderWayBill
    ,Ordering.Orders.ExpireDate
    ,Ordering.ShipperInfo.ShipperCompany
    ,Production.Store.StoreName
    ,Production.Product.ProductName
    ,Ordering.Orders.Quantity
    ,Ordering.Orders.Price
    ,Ordering.Orders.ShipAddress
    ,Ordering.Orders.Description1
    ,Ordering.Orders.Description2
    ,Ordering.Orders.Discount
    ,Ordering.OrderStatus.Description
    ,Sales.PaymentTerm.Description AS PaymentTerm
    ,Ordering.Orders.CustomerFK
    ,Ordering.Orders.ShipperFK
    ,Ordering.Orders.StoreFK
    ,Ordering.Orders.ProductCategoryFK
    ,Ordering.Orders.ProductFK
    ,Ordering.Orders.OrderStatusFK
    ,Ordering.Orders.PaymentTermFK
    ,Ordering.Orders.FinancialPeriodFK
    ,Ordering.Orders.CompanyInfoFK
    ,DueDate = (
        SELECT TOP 1 duedate
        FROM (
            SELECT duedate = DATE
            FROM banking.receivedcash
            WHERE orderfk = Ordering.Orders.OrderID

            UNION ALL

            SELECT duedate = duedate
            FROM banking.receivedcheque
            WHERE orderfk = Ordering.Orders.OrderID
            ) AS a
        ORDER BY duedate DESC
        )
FROM Ordering.Orders
LEFT OUTER JOIN Ordering.ShipperInfo 
  ON Ordering.Orders.ShipperFK = Ordering.ShipperInfo.ShipperInfoID
LEFT OUTER JOIN Production.Product 
  ON Ordering.Orders.ProductFK = Production.Product.ProductID
LEFT OUTER JOIN Production.Store 
  ON Ordering.Orders.StoreFK = Production.Store.StoreID
LEFT OUTER JOIN Ordering.OrderStatus 
  ON Ordering.Orders.OrderStatusFK = Ordering.OrderStatus.OrderStatusID
LEFT OUTER JOIN Sales.PaymentTerm 
  ON Ordering.Orders.PaymentTermFK = Sales.PaymentTerm.PaymentTermID

Answer 1

Your query is slow because the two subselects are executed for every row. It will be faster if you rewrite them as joins.