Используйте временную таблицу или табличную переменную для хранимой процедуры, которая возвращает более 100 строк.

Хорошо, в основном я создаю хранимую процедуру, которая будет возвращать данные для нашего поиска мощности холодного синтеза.

Я создал представление для хранения данных из нескольких таблиц с теми же именами столбцов, которые были возвращены, конечно.

Затем в моей хранимой процедуре я создал простую временную таблицу, подобную этой....

    CREATE TABLE #TempSearchResults
(
    search_id int identity,
    id integer,
    type varchar(20),
    title varchar(50),
    url varchar(250),
    rank integer
)

Затем я добавил к нему индекс, по моему, возможно, ограниченному опыту, как способ повысить производительность.

CREATE UNIQUE INDEX idx on #TempSearchResults (search_id)

Затем я сделал свой выбор в массивном запросе

insert into #TempSearchResults
select id, type, title, url, rank + 1200 as rank
from my view
where company_id = @company_id
and title like @keyword
union all
select id, type, title, url, rank + 1100 as rank
from my view
where company_id = @company_id
and title like @keyword
and description like @keyword

и так далее, имея разные математические значения ранга для того, где он нашел ключевое слово в таблицах.

И в конце получается...

select id, type, title, url, rank
from #TempSearchResults
group by id, type, title, url, rank
order by rank desc, title asc;

Теперь, когда я тестирую эту хранимую процедуру в coldfusion, кажется, что это занимает очень много времени.

Поэтому я думаю, что либо я использую временные таблицы неправильно, либо не полностью для оптимальной производительности.

Или, возможно, мне следует переключиться на табличные переменные...

Но я просто читал...Временные таблицы и табличные переменные

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

Я надеюсь на оптимальную производительность....

Спасибо...

Ниже приведена основная логика или код для представления, которое я использую.

select some field as id, some field as title, some field as description, 'url link' as url, 1 as rank
from table a
union
select some field as id, some field as title, some field as description, 'url link' as url, 1 as rank
from table b
union
select some field as id, some field as title, some field as description, 'url link' as url, 1 as rank
from table c

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


stored-procedures sql-server-2008 temp-tables coldfusion-8
person crosenblum    schedule 18.02.2011    source источник
comment
Сколько времени занимает выполнение вашего оператора вставки? Бьюсь об заклад, это медленная часть... запустите ее в одиночку в качестве выбора и посмотрите, сколько времени там потрачено. Нужно выяснить, ЧТО нам нужно оптимизировать в первую очередь!   -  person Wavel    schedule 18.02.2011
comment
При запуске cfstoredproc в coldfusion он показывает 5312 мс. В SSMS указано общее время выполнения 171,00.   -  person crosenblum    schedule 18.02.2011
comment
Можете ли вы дать больше информации о представлении.   -  person bernd_k    schedule 18.02.2011
comment
Само представление представляет собой группу выбора для разных таблиц в объединении. Это облегчает мне поиск одного и того же набора таблиц разными способами и облегчает чтение. Один вопрос, в моей компании нет dba, только я, еще один программист и наш начальник. Помогло бы, если бы я запустил UPDATE STATISTICS для таблиц, используемых в представлениях/процедурах?   -  person crosenblum    schedule 18.02.2011
comment
Я добавил больше деталей о представлении, по крайней мере, с точки зрения базовой логики.   -  person crosenblum    schedule 18.02.2011
comment
Почему вы используете UNION в представлении, а не UNION ALL, когда вы все равно удаляете дубликаты в окончательном выборе?   -  person bernd_k    schedule 18.02.2011
comment
Потому что я не знал этого заранее. Я должен был создать окончательный sql и убедиться, что результаты доставлены точно так, как хотел мой менеджер, как только все это будет сделано, я смогу повторно отредактировать представление и процедуру ... Но вы сделали очень хорошее замечание.   -  person crosenblum    schedule 19.02.2011

Ответы (2)


arrow_upward
2
arrow_downward

Я вообще не вижу необходимости использовать временную таблицу или табличную переменную. Вы можете просто написать

select id, type, title, url, rank
from (
    select id, type, title, url, rank + 1200 as rank 
    from my view 
    where company_id = @company_id and title like @keyword 

    union all 

    select id, type, title, url, rank + 1100 as rank 
    from my view 
    where company_id = @company_id and title like @keyword and description like @keyword
) as t
group by id, type, title, url, rank
order by rank desc, title asc;

Изменить:

Заменив UNION ALL на UNION, это можно упростить до

select id, type, title, url, rank + 1200 as rank 
from my view 
where company_id = @company_id and title like @keyword 

union 

select id, type, title, url, rank + 1100 as rank 
from my view 
where company_id = @company_id and title like @keyword and description like @keyword

order by rank desc, title asc;
person bernd_k    schedule 18.02.2011
comment
Я знаком с этим подходом, это лучшая производительность, чем временная таблица? - person crosenblum; 18.02.2011
comment
Я бы поспорил, что это так, но я не знаю точно. - person bernd_k; 18.02.2011
comment
Выполняя изменения в coldfusion, это 5265 мс. Первоначально я перешел от объединения к объединению всех, потому что объединение больше влияет на производительность, потому что оно различает результаты. - person crosenblum; 18.02.2011
comment
Изменение с union all на union увеличило производительность холодного синтеза до 5515 мс. - person crosenblum; 18.02.2011
comment
Если вы знаете, что у вас нет дубликатов, вы можете использовать UNION ALL и обойтись без группы. Эта внешняя группа эквивалентна отличительной черте во внешнем выборе. - person bernd_k; 18.02.2011
comment
Я точно знаю, что у меня будут дубликаты, потому что я ищу одни и те же таблицы разными способами и имею другое значение ранга для того, где был найден результат. Любые другие предложения? - person crosenblum; 18.02.2011
comment
Очень хороший и полезный ответ. Большое спасибо! - person crosenblum; 21.02.2011

arrow_upward
0
arrow_downward

Используйте подсказку with (Nolock) для таблиц, где бы вы ни выбирали данные; это может повысить производительность, если ваше приложение допускает грязное чтение.

person Vinod    schedule 18.07.2012