"Подзапрос имеет несколько строк для сравнения

Question

У меня есть одна таблица, приведенная ниже.

В следующем запросе внешний запрос включается в like сравнение на tag столбец с подзапросом.

 SELECT top 6 *
  FROM [piarchive].[picomp2]
  WHERE tag Like
  (
  Select distinct left(tag,19) + '%' 
  from (SELECT  *
  FROM [piarchive].[picomp2]
  WHERE tag like '%CPU_Active' and  time between '2014/10/02 15:13:08'and'2014/10/02 15:18:37'
  and value=-524289 order by time desc) as t1
  )  
  and tag not like '%CPU_Active' and tag not like '%Program%' and time between '2014/10/02    
  15:13:08'and'2014/10/02 15:18:37'  order by time desc

Но этот подзапрос возвращает несколько строк, вызывая следующую ошибку:

Ошибка: "При использовании в качестве выражения подзапрос может возвращать не более одной строки".

Answer 1

Заменить where tag like (...) (где ... это подзапрос, опущенный здесь для краткости) расстаться с where exists (...)и принести like сравнение в подзапрос.

---

select top 6
    *
from
    [piarchive].[picomp2] t0
where
    exists
    (
        select
            *
        from
            (
                select
                    *
                from
                    [piarchive].[picomp2]
                where
                    tag like '%cpu_active' and time between '2014/10/02 15:13:08' and '2014/10/02 15:18:37'
                    and
                    value = -524289
            )
            as t1
        where
            t0.tag like left(t1.tag, 19) + '%' 
    ) 
    and
    tag not like '%cpu_active'
    and
    tag not like '%program%'
    and
    time between '2014/10/02 15:13:08' and '2014/10/02 15:18:37'
order by
    time desc;

Я добавил псевдоним таблицы во внешний запрос, чтобы устранить неоднозначность tag столбцы, но вы можете увидеть like сравнение смещено в подзапрос.

Я не могу поручиться за то, как это будет работать с большими наборами данных, но это другая тема. Лично я бы искал способ полностью избавиться от подзапроса, так как он все запрашивает одну и ту же таблицу.

Подробнее об оптимизации

Это не будет легко оптимизировать, и индексы здесь будут бесполезны по следующим причинам:

• Критерии объединения (t0.tag like left(t1.tag, 19) + '%') не является простым, и оптимизатору запросов может быть сложно создать что-то лучше, чем вложенные циклы (т. е. выполнить подзапрос для каждой строки внешнего запроса). Это, вероятно, ваш самый большой убийца производительности прямо здесь.
• Ни один из like сравнения могут использовать табличные индексы, потому что они проверяют конец значения, а не начало.

Ваша единственная надежда может быть, если проверка диапазона дат является очень избирательной (исключает много записей). Так как та же проверка на time Поле выполняется как во внешнем, так и во внутреннем запросе, вы можете выбрать это во временной таблице:

select left(tag, 19) as key, *
  into #working
  from [piarchive].[picomp2]
 where [time] between '2014/10/02 15:13:08' and '2014/10/02 15:18:37';

#working теперь есть только записи за указанный период времени. Поскольку ваш примерный диапазон довольно узок (всего 5 с половиной минут), я бы поспорил, что это может выбить ~99% записей. Индекс на time значительно ускорит это. После того, как вы это сделаете, вы имеете дело только с крошечной частью данных.

Тогда, возможно (см. Позже) индекс key:

create clustered index cx_key on #working (key);

Затем выполните остальную часть запроса как:

select a.*
  from #working a
 where exists
       (
           select *
             from #working b
            where a.key = b.key and b.tag like '%cpu_active'
       )
       and
       a.tag not like '%program%'
       and
       a.tag not like '%cpu_active'

Я создал кластерный индекс по критериям объединения (первые 19 символов tag) оптимизировать подзапрос. Вам придется проверить это, так как это может не иметь никакого значения или даже замедлить процесс, если выигрыш перевесит стоимость создания индекса в первую очередь. Это будет зависеть от того, сколько данных у вас есть, и других факторов. Делая это, я получил только минимальный выигрыш (увеличение скорости примерно на 5%), хотя я использую только несколько сотен строк тестовых данных, которые я собрал. Чем больше у вас данных, тем эффективнее они должны быть.