Здравствуйте, Malchik, Вы писали:

M>примерные обьемы в #tmp_data 800000, в ALLOTMENT_DAYS 1000000, железо 4cpu intel xeon 3.20Ghz

Ну с железом проблем нет

M>здесь проблема с
M>

M>SELECT    #tmp_data.tmp_tour_id as Id,isnull( min(ALLOTMENT_DAYS.FREEUNITS), 0) as FREE_CAPACITY_ROOM,
M>          isnull( max(cast(ALLOTMENT_DAYS.RELEASE as int)), 0) as RELEASE
M>

M>Column '#tmp_data.tmp_tour_id' is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause.

M>или же я что то не допонял...

Ну тут отсутствует блок Group By
надо добавить как минимум

Group By #tmp_data.tmp_tour_id

Может еще на что ругнется — тоже добавь в группировку

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