Linq2SQL: оптимизировать запрос?

Могу ли я каким-либо образом оптимизировать следующий запрос:

Цель состоит в том, чтобы попытаться найти клиентов типа 4, который также существует как тип 2, на основе их НДС и электронной почты. У клиента может быть один или несколько клиентов-пользователей.

(У меня есть все соответствующие индексы, обещаю)

from e in Clients.Where(h => h.ClientTypeId==4)
 join u in ClientUsers on e.Id equals u.ClientId
 where
 e.DeleteFlag.Equals("n") &&
 (

     (!(e.VAT == null || e.VAT.Equals("")) && Clients.Any(f => f.ClientTypeId == 2 && f.VAT.Equals(e.VAT)))

     || (!(e.Email == null || e.Email.Equals("")) &&
         (
             Clients.Any(f => f.ClientTypeId == 2 && f.Email.ToLower().Equals(e.Email.ToLower()))
             || (from f in ClientUsers join q in Clients on f.ClientId equals q.Id where f.Email.Equals(e.Email.ToLower()) && q.ClientTypeId == 2 select f.Id).Any()
         )
     )

     || (!(u.Email == null || u.Email.Equals("")) &&
         (
             Clients.Any(f => f.ClientTypeId == 2 && f.Email.ToLower().Equals(u.Email.ToLower()))
             || (from f in ClientUsers join q in Clients on f.ClientId equals q.Id where f.Email.Equals(u.Email.ToLower()) && q.ClientTypeId == 2 select f.Id).Any()
         )
     )

 )
 select e

Результаты в:

-- Region Parameters
DECLARE @p0 VarChar(1) SET @p0 = 'n'
DECLARE @p1 NVarChar(1) SET @p1 = ''
DECLARE @p2 Int SET @p2 = 2
DECLARE @p3 NVarChar(1) SET @p3 = ''
DECLARE @p4 Int SET @p4 = 2
DECLARE @p5 Int SET @p5 = 2
DECLARE @p6 NVarChar(1) SET @p6 = ''
DECLARE @p7 Int SET @p7 = 2
DECLARE @p8 Int SET @p8 = 2
DECLARE @p9 Int SET @p9 = 4
-- EndRegion
SELECT [t0].[Id], [t0].[AccountId], [t0].[CompanyName], [t0].[Address], [t0].[Address2], [t0].[PostCode], [t0].[PostArea], [t0].[ContactPerson], [t0].[Phone], [t0].[Email], [t0].[VAT], [t0].[Webpage], [t0].[Description], [t0].[Active], [t0].[PreRegcode], [t0].[PreRegcheck], [t0].[AccountCreated], [t0].[UpdateTimeStamp], [t0].[DeleteFlag], [t0].[ClientTypeId], [t0].[CampaignCodeId], [t0].[PayexId], [t0].[ApiKey], [t0].[InvoiceName], [t0].[Source], [t0].[ProspectStatusId], [t0].[ProspectLastCommentId], [t0].[ProspectCallbackDate]
FROM [Clients] AS [t0]
INNER JOIN [ClientUsers] AS [t1] ON ([t0].[Id]) = [t1].[ClientId]
WHERE ([t0].[DeleteFlag] = @p0) AND (((NOT (([t0].[VAT] IS NULL) OR ([t0].[VAT] = @p1))) AND (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Clients] AS [t2]
    WHERE ([t2].[ClientTypeId] = @p2) AND ([t2].[VAT] = [t0].[VAT])
    ))) OR ((NOT (([t0].[Email] IS NULL) OR ([t0].[Email] = @p3))) AND ((EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Clients] AS [t3]
    WHERE ([t3].[ClientTypeId] = @p4) AND (LOWER([t3].[Email]) = LOWER([t0].[Email]))
    )) OR (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [ClientUsers] AS [t4]
    INNER JOIN [Clients] AS [t5] ON [t4].[ClientId] = ([t5].[Id])
    WHERE ([t4].[Email] = LOWER([t0].[Email])) AND ([t5].[ClientTypeId] = @p5)
    )))) OR ((NOT (([t1].[Email] IS NULL) OR ([t1].[Email] = @p6))) AND ((EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [Clients] AS [t6]
    WHERE ([t6].[ClientTypeId] = @p7) AND (LOWER([t6].[Email]) = LOWER([t1].[Email]))
    )) OR (EXISTS(
    SELECT NULL AS [EMPTY]
    FROM [ClientUsers] AS [t7]
    INNER JOIN [Clients] AS [t8] ON [t7].[ClientId] = ([t8].[Id])
    WHERE ([t7].[Email] = LOWER([t1].[Email])) AND ([t8].[ClientTypeId] = @p8)
    ))))) AND ([t0].[ClientTypeId] = @p9)

Запрос работает, но его выполнение занимает 5 минут.


.net query-optimization linq-to-sql
person Niels Bosma    schedule 30.09.2009    source источник
comment
54641 клиентов (сегодня у каждого клиента только один ClientUser). 4544 типа 2 и 30287 типа 4.   -  person Niels Bosma    schedule 30.09.2009
comment
Думаю, было бы легче помочь, если бы вы описали свои таблицы и то, что вы хотите делать именно вместо этого. Также убедитесь, что у вас есть индексы для этих столбцов.   -  person Runeborg    schedule 30.09.2009

Ответы (1)


arrow_upward
2
arrow_downward

Какую сортировку db вы используете? Запрос можно реструктурировать и немного упростить, но сначала может быть идея отбросить все .ToLower и т. Д., Если вы не используете сортировку с учетом регистра ...

Изменить: вы можете попробовать изменить свой запрос на кучу более мелких объединенных запросов, которые оставляют оптимизатору sql меньше свободы для творчества ... (многие условия 'или' в запросе, как правило, приводят к сканированию даже если есть индексы покрытия).

E.g.:

(

from e in dc.Clients
join u in dc.ClientUsers on e.Id equals u.ClientId
join vc in dc.Clients on new { ClientTypeId = 2, e.VAT } equals new { vc.ClientTypeId, vc.VAT }
where e.ClientTypeId == 4
  && e.DeleteFlag.Equals("n")
  && e.VAT != null
  && e.VAT != "" 
select e

).Union(

from e in dc.Clients
join u in dc.ClientUsers on e.Id equals u.ClientId
join ec in dc.Clients on new { ClientTypeId = 2, e.Email } equals new { ec.ClientTypeId, ec.Email }
where e.ClientTypeId == 4
&& e.DeleteFlag.Equals("n")
&& e.Email != null
&& e.Email != ""
select e 

).Union(

from e in dc.Clients
join u in dc.ClientUsers on e.Id equals u.ClientId
join c1u in dc.ClientUsers on e.Email equals new c1u.Email
join c1c in dc.Clients on new { ClientTypeId = 2, c1u.ClientId } equals new { c1c.ClientTypeId, ClientId = c1c.Id }
where e.ClientTypeId==4
&& e.DeleteFlag.Equals("n")
&& e.Email != null
&& e.Email != ""
select e

).Union(

from e in dc.Clients
join u in dc.ClientUsers on e.Id equals u.ClientId
join c2u in dc.ClientUsers on u.Email equals c2u.Email
join c2c in dc.Clients on new { ClientTypeId = 2, c2u.ClientId } equals new { c2c.ClientTypeId, ClientId = c2c.Id }
where e.ClientTypeId==4
&& e.DeleteFlag.Equals("n")
&& u.Email != null
&& u.Email != ""
select e

)
person KristoferA    schedule 30.09.2009
comment
SQL_SwedishStd_Pref_CP1_CI_AS - person Niels Bosma; 30.09.2009
comment
В порядке. Это сортировка без учета регистра, поэтому вы можете удалить вызовы .ToLower ... - person KristoferA; 30.09.2009
comment
Вы профессиональный оптимизатор! Удаление ToLower сократило исполнение до 3 сек. Но использование вашего Союза уменьшило казнь почти до нуля! - person Niels Bosma; 30.09.2009