Здравствуйте, tarasich, спасибо за ответ,
Вы писали:
T>Хотелось бы узнать примерные объемы данных,и соответствуют ли эти объемы железу.
примерные обьемы в #tmp_data 800000, в ALLOTMENT_DAYS 1000000, железо 4cpu intel xeon 3.20Ghz
T>Далее шаг 6 и 7.
T>Общие мысли:
T>1.
T>то имеет смысл создать индекс со всеми ними — это позволит использовать только индекс не прибегая к запросу данных физического хранилища.
создал
T>2.
T>T> Update #tmp_data
T> set FREE_CAPACITY_ROOM = tmp.FREE_CAPACITY_ROOM,
T> RELEASE=tmp.RELEASE
T> from
T> #tmp_data inner join
T>(
T> SELECT #tmp_data.tmp_tour_id as Id,isnull( min(ALLOTMENT_DAYS.FREEUNITS), 0) as FREE_CAPACITY_ROOM,
T> isnull( max(cast(ALLOTMENT_DAYS.RELEASE as int)), 0) as RELEASE
T> FROM ALLOTMENT_DAYS,
T> ALLOTMENT_PRIMARY_HEADERS,
T> ALLOTMENTS,
T> SERVICE_TYPES,
T> SERVICES,
T> PACKAGE_SERVICES,
T> PACKAGES
T>inner join #tmp_data
T>on
T> SERVICE_TYPES.CODE_STRING = #tmp_data.CODE_STRING
T> AND PACKAGES.BOOKINGCODE = #tmp_data.BOOKINGCODE
T> AND PACKAGES.KATALOG_CODE = #tmp_data.KATALOG_CODE
T> AND ALLOTMENT_DAYS.DATUM >= #tmp_data.from_date
T> AND ALLOTMENT_DAYS.DATUM < #tmp_data.to_date
T> WHERE
T> ALLOTMENT_PRIMARY_HEADERS.ORIG_ID = ALLOTMENT_DAYS.ORIG_HEADER_ID
T> AND ALLOTMENTS.ORIG_ID = ALLOTMENT_PRIMARY_HEADERS.ALLOTMENTS_ID
T> AND SERVICE_TYPES.ALLOTMENT_ID = ALLOTMENTS.ORIG_ID
T> AND ISNULL(SERVICE_TYPES.SUBALLOTMENT_ID, ALLOTMENT_PRIMARY_HEADERS.ORIG_ID)
T> = ALLOTMENT_PRIMARY_HEADERS.ORIG_ID
T> AND SERVICES.ORIG_ID = SERVICE_TYPES.PACKAGE_ID
T> AND PACKAGE_SERVICES.SERVICE_ID = SERVICES.ORIG_ID
T> AND PACKAGES.ORIG_ID = PACKAGE_SERVICES.PACKAGE_ID
T> AND SERVICES.TYPE = 'H'
T> AND ALLOTMENT_DAYS.VERSION_NR = @versionNR
T> and ALLOTMENT_PRIMARY_HEADERS.VERSION_NR = @versionNR
T> AND ALLOTMENTS.VERSION_NR = @versionNR
T> AND SERVICE_TYPES.VERSION_NR = @versionNR
T> AND SERVICES.VERSION_NR = @versionNR
T> AND PACKAGE_SERVICES.VERSION_NR = @versionNR
T> AND PACKAGES.VERSION_NR = @versionNR
T>) tmp
T>on #tmp_data.tmp_tour_id=tmp.id
T>
здесь проблема с
SELECT #tmp_data.tmp_tour_id as Id,isnull( min(ALLOTMENT_DAYS.FREEUNITS), 0) as FREE_CAPACITY_ROOM,
isnull( max(cast(ALLOTMENT_DAYS.RELEASE as int)), 0) as RELEASE
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.
или же я что то не допонял...
