Question: I have to run two queries in my code to get my tenants balance. However, these queries are too slow.

First query, I get all the tenants and it's unit name:

```
SELECT t.TenantID 
  FROM Tenants t
  JOIN Units u
    ON t.UnitID = u.UnitID
 Where t.Prospect = 2 
   AND t.PropertyID = 8
 ORDER 
    BY CONCAT(Left(Replace(UnitName,'-',''),2),
              REPEAT('0', (10-CHAR_LENGTH(UnitName))),
              Right(Replace(UnitName,'-',''),
                    CHAR_LENGTH(Replace(UnitName,'-',''))-2
             )     )

```

It returns 500 rows

Then I get the balances in 4 conditions. This query will be inside of first query loop:

```
Select
        SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID != 6 THEN TransactionAmount ELSE 0 END) AS TotalDebit,
        SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID = 6 THEN TransactionAmount ELSE 0 END) AS HousingDebit,
        SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID != 6 THEN TransactionAmount ELSE 0 END) AS TotalCredit,
        SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID = 6 THEN TransactionAmount ELSE 0 END) AS HousingCredit
    From TenantTransactions
    Where TenantID= FirstQuery.TenantID

```

Am I doing the queries wrong? It's taking like 1 minute to run.

Answer: 
Do this in a single query with `GROUP BY`. 

Try something like this:

```
SELECT t.TenantID, TotalDebit, HousingDebit, TotalCredit, HousingCredit
  FROM Tenants t
  JOIN Units u ON t.UnitID = u.UnitID
  LEFT JOIN (
        Select
          TenantID,
          SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID != 6 THEN TransactionAmount ELSE 0 END) AS TotalDebit,
          SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID = 6 THEN TransactionAmount ELSE 0 END) AS HousingDebit,
          SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID != 6 THEN TransactionAmount ELSE 0 END) AS TotalCredit,
          SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID = 6 THEN TransactionAmount ELSE 0 END) AS HousingCredit
        From TenantTransactions
       Group By TenantID
       ) sums ON sums.TenantID = t.TenantID
 Where t.Prospect = 2 
   AND t.PropertyID = 8
 ORDER 
    BY CONCAT(Left(Replace(UnitName,'-',''),2),REPEAT('0', (10-CHAR_LENGTH(UnitName))),Right(Replace(UnitName,'-',''),CHAR_LENGTH(Replace(UnitName,'-',''))-2))

```

The inner query may still run for a while but it will only run once.

Try a compound covering index on `TenantTransactions` containing these columns: `(TenantID, TransactionTypeID, ChargeTypeID, TransactionAmount)` to optimize the query with the SUMs in it. 

Try a compound index on `Tenants` with the columns `(PropertyID, Prospect)` in it.
Here's another way to do it with a subquery. You know, the performance problem might not be database performance, but the back and forth between your database and application server. So that is where a single query will help.

```
SELECT t.TenantID,
  (SELECT SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID != 6 THEN TransactionAmount ELSE 0 END) From TenantTransactions TT WHERE TT.TenantID=t.TenantID) AS TotalDebit,
  (SELECT SUM(CASE WHEN TransactionTypeID = 1 AND ChargeTypeID = 6 THEN TransactionAmount ELSE 0 END) From TenantTransactions TT WHERE TT.TenantID=t.TenantID) AS HousingDebit,
  (SELECT SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID != 6 THEN TransactionAmount ELSE 0 END) From TenantTransactions TT WHERE TT.TenantID=t.TenantID) AS TotalCredit,
  (SELECT SUM(CASE WHEN TransactionTypeID = 2 AND ChargeTypeID = 6 THEN TransactionAmount ELSE 0 END) From TenantTransactions TT WHERE TT.TenantID=t.TenantID) AS HousingCredit  
  FROM Tenants t
  JOIN Units u
    ON t.UnitID = u.UnitID
 Where t.Prospect = 2 
   AND t.PropertyID = 8
 ORDER 
    BY CONCAT(Left(Replace(UnitName,'-',''),2),REPEAT('0', (10-CHAR_LENGTH(UnitName))),Right(Replace(UnitName,'-',''),CHAR_LENGTH(Replace(UnitName,'-',''))-2))

```