I have a query that collects customer data. I select a lot of correlated data from different tables. I want to have only ONE result row for each customer.
The query looks like that:
SELECT c.Id
,c.NAME
,(
SELECT TOP 1 p.NAME
FROM ContactPerson p
WHERE p.CustomerId = c.Id
AND p.Priority = 1
)
,(
SELECT TOP 1 p.NAME
FROM ContactPerson p
WHERE p.CustomerId = c.Id
AND p.Priority = 2
)
,(
SELECT TOP 1 p.NAME
FROM ContactPerson p
WHERE p.CustomerId = c.Id
AND p.Priority = 3
)
,(
SELECT TOP 1 p.NAME
FROM ContactPerson p
WHERE p.CustomerId = c.Id
AND p.Priority = 4
)
,(
SELECT TOP 1 b.AccountNumber
FROM BankAccounts b
WHERE b.CustomerId = c.Id
AND b.STATUS = 'active'
)
FROM Customers c
WHERE c.STATUS = 'active'
This is a simplified version of the query. I have multiple subqueries, often referring to the same table under different conditions. If I didn't use subqueries I would get many rows for one customer but I need just one row with all the available data.
How can I rewrite this query so it runs fast? The current version generates high CPU usage, over 200MB/s IO operations and it takes around 6 hours to collect data about 70 000 customers.
Aucun commentaire:
Enregistrer un commentaire