Помогите оптимализировать процедуру - Базы данных

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

M>добрый день,

M>есть процедура, выполняется около 50-60мин, наибольшая задержка на шаге 6 и 7 по 20-25 минут. Условия в шаге 6 и 7 одинаковые происходит апдейт двух столбцов одной и той же временной таблицы #tmp_data. Все шаги используют индексы, проблема в количестве дат на апдейт. Поэтому хотелось бы апдейтнуть оба столбца за один шаг.

M>Любые советы подсказки как оптимализировать шаг 6 и 7 приветствуются...
M>Заранее спасибо.

Хотелось бы узнать примерные объемы данных,и соответствуют ли эти объемы железу.

Далее о процедуре:

M>IF OBJECT_ID('tempdb..#tmp_package') IS NOT NULL DROP TABLE #tmp_package
M>IF OBJECT_ID('tempdb..#tmp_prices') IS NOT NULL DROP TABLE #tmp_prices
M>IF OBJECT_ID('tempdb..#tmp_data') IS NOT NULL DROP TABLE #tmp_data
M>IF OBJECT_ID('tempdb..#tmp_tour') IS NOT NULL DROP TABLE #tmp_tour

Это имеет смысл только если эта процедура будет вызвана из другой, которая создала таблицы с этими же именами.
А посколько таблицы перетрутся, то это тем более врядли имеет смысл.

Далее шаг 6 и 7.

Общие мысли:
1.
Поскольку в запросе используются только поля
#tmp_data.from_date
#tmp_data.to_date
#tmp_data.CODE_STRING
#tmp_data.BOOKINGCODE
#tmp_data.KATALOG_CODE
то имеет смысл создать индекс со всеми ними — это позволит использовать только индекс не прибегая к запросу данных физического хранилища.
2.
Если я все правильно понимаю, то этот тяжеловесный запрос будет выполняться столько раз, сколько записей в таблице #tmp_data. Этого надо постараться избежать.
Например так:
(заранее предупреждаю, что этот код может не работать т.к. DDL и DML для теста не представлен)

    Update #tmp_data 
    set FREE_CAPACITY_ROOM = tmp.FREE_CAPACITY_ROOM,
        RELEASE=tmp.RELEASE
    from
        #tmp_data inner join
(        
        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
        FROM    ALLOTMENT_DAYS,
            ALLOTMENT_PRIMARY_HEADERS,
            ALLOTMENTS,
            SERVICE_TYPES,
            SERVICES,
            PACKAGE_SERVICES,
            PACKAGES
inner join #tmp_data
on
            SERVICE_TYPES.CODE_STRING = #tmp_data.CODE_STRING
            AND PACKAGES.BOOKINGCODE = #tmp_data.BOOKINGCODE
            AND PACKAGES.KATALOG_CODE = #tmp_data.KATALOG_CODE
            AND ALLOTMENT_DAYS.DATUM >= #tmp_data.from_date
            AND ALLOTMENT_DAYS.DATUM < #tmp_data.to_date
        WHERE
            ALLOTMENT_PRIMARY_HEADERS.ORIG_ID = ALLOTMENT_DAYS.ORIG_HEADER_ID
            AND ALLOTMENTS.ORIG_ID = ALLOTMENT_PRIMARY_HEADERS.ALLOTMENTS_ID
            AND SERVICE_TYPES.ALLOTMENT_ID = ALLOTMENTS.ORIG_ID
            AND ISNULL(SERVICE_TYPES.SUBALLOTMENT_ID, ALLOTMENT_PRIMARY_HEADERS.ORIG_ID) 
                = ALLOTMENT_PRIMARY_HEADERS.ORIG_ID
            AND SERVICES.ORIG_ID = SERVICE_TYPES.PACKAGE_ID
            AND PACKAGE_SERVICES.SERVICE_ID = SERVICES.ORIG_ID
            AND PACKAGES.ORIG_ID = PACKAGE_SERVICES.PACKAGE_ID
            AND SERVICES.TYPE = 'H'
            AND ALLOTMENT_DAYS.VERSION_NR = @versionNR
            and ALLOTMENT_PRIMARY_HEADERS.VERSION_NR = @versionNR
            AND ALLOTMENTS.VERSION_NR = @versionNR
            AND SERVICE_TYPES.VERSION_NR = @versionNR
            AND SERVICES.VERSION_NR = @versionNR
            AND PACKAGE_SERVICES.VERSION_NR = @versionNR
            AND PACKAGES.VERSION_NR = @versionNR
) tmp
on #tmp_data.tmp_tour_id=tmp.id

Это позволит выполнить тяжелый запрос только 1 раз и значительно поднять производительность.

Надеюсь, что помог

добрый день,

есть процедура, выполняется около 50-60мин, наибольшая задержка на шаге 6 и 7 по 20-25 минут. Условия в шаге 6 и 7 одинаковые происходит апдейт двух столбцов одной и той же временной таблицы #tmp_data. Все шаги используют индексы, проблема в количестве дат на апдейт. Поэтому хотелось бы апдейтнуть оба столбца за один шаг.

Любые советы подсказки как оптимализировать шаг 6 и 7 приветствуются...
Заранее спасибо.

CREATE Procedure dbo.sp_import_tour
    @versionNR         int,
    @dateFrom         datetime,
    @dateTo         datetime
As
begin

print '1 start termins ' + convert(varchar(20), getdate())

IF OBJECT_ID('tempdb..#tmp_package') IS NOT NULL DROP TABLE #tmp_package
IF OBJECT_ID('tempdb..#tmp_prices') IS NOT NULL DROP TABLE #tmp_prices
IF OBJECT_ID('tempdb..#tmp_data') IS NOT NULL DROP TABLE #tmp_data
IF OBJECT_ID('tempdb..#tmp_tour') IS NOT NULL DROP TABLE #tmp_tour

select a.*, t.from_date, t.to_date, p.name oblast_kod
Into #tmp_package
from dbo.PACKAGES a, dbo.termines t, PRODUCT_TYPES p
where 
a.orig_id = t.package_id
and a.PRODUKT_TYPE = p.CODE
and a.VERSION_NR = t.VERSION_NR
and a.VERSION_NR = @versionNR
and t.VERSION_NR = @versionNR
and t.FROM_DATE >= @dateFrom
and t.FROM_DATE <= @dateTo

print '2 start prices ' + convert(varchar(20), getdate())

SELECT  PRICETABLE.ORIG_ID,
    PRICE_SPLIT.PRICE,
    PRICEHEADER.CHILD_PRICE,
    PRICE_DEF.PRICE_CODE,
    PRICE_DEF.DAYS,
    SEASON_TERMS.DATE_FROM,
    SEASON_TERMS.DATE_TO
Into #tmp_prices
FROM
    PRICETABLE,
    PRICEHEADER,
    PRICE_DEF,
    PRICE_SPLIT,
    SEASON_TERMS
WHERE
    PRICEHEADER.PRICETABLE_ID = PRICETABLE.ORIG_ID
    And PRICE_DEF.PRICEHEADER_ID = PRICEHEADER.ORIG_ID
    And PRICE_SPLIT.PRICE_DEF_ID = PRICE_DEF.ORIG_ID

    AND SEASON_TERMS.SEASONTABLE = PRICETABLE.SEASONTABLE
    AND SEASON_TERMS.SEASONTABLE = PRICEHEADER.SEASONTABLE
    AND SEASON_TERMS.SEASONCODE = PRICE_DEF.SEASONCODE
    AND (PRICETABLE.VERSION_NR = @versionNR) 
    AND (PRICEHEADER.VERSION_NR = @versionNR) 
    AND (PRICE_DEF.VERSION_NR = @versionNR) 
    AND (PRICE_SPLIT.VERSION_NR = @versionNR)
    AND (SEASON_TERMS.VERSION_NR = @versionNR)

print '3 start join termins a prices ' + convert(varchar(20), getdate())

create index idx_tmp_prices_s0 on #tmp_prices (orig_id, date_from)
create index idx_tmp_prices_s1 on #tmp_prices (date_from, date_to)
create index idx_tmp_packages_s0 on #tmp_package (FROM_DATE)
create index idx_tmp_packages_s1 on #tmp_package (LM_PRICETABLE_ID)
create index idx_tmp_packages_s2 on #tmp_package (PRICETABLE_ID)
print '3.1 index created for termins a prices  ' + convert(varchar(20), getdate())

SELECT    FREE_CAPACITY_ROOM = 0,
    FREE_CAPACITY_FLIGHT = 0,
    RELEASE = 0,
    CHILD_FREE_PLACES = 0,
    MINIMAL_PRICE = 0 ,
    identity(int, 1,1) tmp_tour_id,
    getdate() valid_from,
    isnull(PACKAGES.PRIORITY,0) NAS_TIP,
    isnull(PACKAGES.LM_PRICETABLE_ID, 0) IS_LM,
    rtrim(PACKAGES.BOOKINGCODE) BOOKINGCODE,
    rtrim(PACKAGES.KATALOG_CODE) KATALOG_CODE,
    rtrim(PACKAGES.LAND) LAND,
    PACKAGES.DESTINATION DESTINATION,
    rtrim(PACKAGES.REGION) REGION,
    rtrim(PACKAGES.oblast_kod) OBLAST,
    PACKAGES.FROM_DATE FROM_DATE,
    PACKAGES.TO_DATE TO_DATE,
    PRICES.DAYS DAYS,
    SERVICE_TYPES.CODE_STRING CODE_STRING,
    cast(PRICES.PRICE as decimal) PRICE,
    cast(PRICES.CHILD_PRICE as decimal) CHILD_PRICE,
    (
        SELECT TOP 1 BOOKINGCODE 
        FROM SERVICES
            INNER JOIN PACKAGE_SERVICES ON PACKAGE_SERVICES.SERVICE_ID = SERVICES.ORIG_ID
        WHERE 
                TYPE = 'F'
            AND PACKAGE_SERVICES.STARTDAY = 1 
            AND PACKAGE_SERVICES.PACKAGE_ID = PACKAGES.ORIG_ID            
    ) AS FLIGHT_FROM,
    (
        SELECT  TOP 1 BOOKINGCODE 
        FROM SERVICES
            INNER JOIN PACKAGE_SERVICES ON PACKAGE_SERVICES.SERVICE_ID = SERVICES.ORIG_ID
        WHERE 
                TYPE = 'F'
            AND PACKAGE_SERVICES.STARTDAY = 2 
            AND PACKAGE_SERVICES.PACKAGE_ID = PACKAGES.ORIG_ID
    ) AS FLIGHT_TO,
    (
        SELECT  TOP 1 BOOKINGCODE
        FROM SERVICES
            INNER JOIN PACKAGE_SERVICES ON PACKAGE_SERVICES.SERVICE_ID = SERVICES.ORIG_ID
        WHERE 
                TYPE = 'H'
            AND PACKAGE_SERVICES.STARTDAY = 1 

            AND PACKAGE_SERVICES.PACKAGE_ID = PACKAGES.ORIG_ID
    ) AS HOTEL_CODE,
    (
        SELECT  TOP 1 HOTEL_CATEGORY
        FROM SERVICES
            INNER JOIN PACKAGE_SERVICES ON PACKAGE_SERVICES.SERVICE_ID = SERVICES.ORIG_ID
        WHERE 
                TYPE = 'H'
            AND PACKAGE_SERVICES.STARTDAY = 1 

            AND PACKAGE_SERVICES.PACKAGE_ID = PACKAGES.ORIG_ID
    ) AS HOTEL_CATEGORY,
    (
        SELECT  TOP 1 AGE_PROFILE
        FROM SERVICES
            INNER JOIN PACKAGE_SERVICES ON PACKAGE_SERVICES.SERVICE_ID = SERVICES.ORIG_ID
        WHERE 
                TYPE = 'H'
            AND PACKAGE_SERVICES.STARTDAY = 1 

            AND PACKAGE_SERVICES.PACKAGE_ID = PACKAGES.ORIG_ID
    ) AS AGE_PROFILE
Into #tmp_data
FROM
    #tmp_package PACKAGES,
    #tmp_prices PRICES,
    SERVICE_TYPES
WHERE
    PACKAGES.FROM_DATE > @dateFrom AND PACKAGES.FROM_DATE < @dateTo 
    And SERVICE_TYPES.PACKAGE_ID = PACKAGES.ORIG_ID
    And PRICES.ORIG_ID = isnull(PACKAGES.LM_PRICETABLE_ID, PACKAGES.PRICETABLE_ID)
    AND PRICES.PRICE_CODE = SERVICE_TYPES.CODE_STRING
    AND PRICES.DAYS = DATEDIFF(DAY, PACKAGES.FROM_DATE, PACKAGES.TO_DATE)
    AND PRICES.DATE_FROM <= PACKAGES.FROM_DATE 
    AND PRICES.DATE_TO >=  PACKAGES.FROM_DATE
    AND (PACKAGES.VERSION_NR = @versionNR) 
    AND (SERVICE_TYPES.VERSION_NR = @versionNR)
Order by price

print '4.1 start index for tmp_data ' + convert(varchar(20), getdate())
create index idx_tmp_data_s0 on #tmp_data (bookingcode)
create index idx_tmp_data_s1 on #tmp_data (from_date, to_date)
print '4.2 end index fro tmp_data ' + convert(varchar(20), getdate())


print '6 start update capacity room ' + convert(varchar(20), getdate())
    Update #tmp_data 
    set FREE_CAPACITY_ROOM = 
    (
        SELECT    isnull( min(ALLOTMENT_DAYS.FREEUNITS), 0)
        FROM    ALLOTMENT_DAYS,
            ALLOTMENT_PRIMARY_HEADERS,
            ALLOTMENTS,
            SERVICE_TYPES,
            SERVICES,
            PACKAGE_SERVICES,
            PACKAGES
        WHERE
            ALLOTMENT_PRIMARY_HEADERS.ORIG_ID = ALLOTMENT_DAYS.ORIG_HEADER_ID
            AND ALLOTMENTS.ORIG_ID = ALLOTMENT_PRIMARY_HEADERS.ALLOTMENTS_ID
            AND SERVICE_TYPES.ALLOTMENT_ID = ALLOTMENTS.ORIG_ID
            AND ISNULL(SERVICE_TYPES.SUBALLOTMENT_ID, ALLOTMENT_PRIMARY_HEADERS.ORIG_ID) 
                = ALLOTMENT_PRIMARY_HEADERS.ORIG_ID
            AND SERVICES.ORIG_ID = SERVICE_TYPES.PACKAGE_ID
            AND PACKAGE_SERVICES.SERVICE_ID = SERVICES.ORIG_ID
            AND PACKAGES.ORIG_ID = PACKAGE_SERVICES.PACKAGE_ID    
            AND ALLOTMENT_DAYS.DATUM >= #tmp_data.from_date
            AND ALLOTMENT_DAYS.DATUM < #tmp_data.to_date
            AND SERVICES.TYPE = 'H'
            AND SERVICE_TYPES.CODE_STRING = #tmp_data.CODE_STRING
            AND PACKAGES.BOOKINGCODE = #tmp_data.BOOKINGCODE
            AND PACKAGES.KATALOG_CODE = #tmp_data.KATALOG_CODE
            AND ALLOTMENT_DAYS.VERSION_NR = @versionNR
            and ALLOTMENT_PRIMARY_HEADERS.VERSION_NR = @versionNR
            AND ALLOTMENTS.VERSION_NR = @versionNR
            AND SERVICE_TYPES.VERSION_NR = @versionNR
            AND SERVICES.VERSION_NR = @versionNR
            AND PACKAGE_SERVICES.VERSION_NR = @versionNR
            AND PACKAGES.VERSION_NR = @versionNR
    )
print '7 start update release room ' + convert(varchar(20), getdate())

    Update #tmp_data 
    set RELEASE = 
    (
        SELECT    isnull( max(cast(ALLOTMENT_DAYS.RELEASE as int)), 0)
        FROM    ALLOTMENT_DAYS,
            ALLOTMENT_PRIMARY_HEADERS,
            ALLOTMENTS,
            SERVICE_TYPES,
            SERVICES,
            PACKAGE_SERVICES,
            PACKAGES
        WHERE
            ALLOTMENT_PRIMARY_HEADERS.ORIG_ID = ALLOTMENT_DAYS.ORIG_HEADER_ID
            AND ALLOTMENTS.ORIG_ID = ALLOTMENT_PRIMARY_HEADERS.ALLOTMENTS_ID
            AND SERVICE_TYPES.ALLOTMENT_ID = ALLOTMENTS.ORIG_ID
            AND ISNULL(SERVICE_TYPES.SUBALLOTMENT_ID, ALLOTMENT_PRIMARY_HEADERS.ORIG_ID) 
                = ALLOTMENT_PRIMARY_HEADERS.ORIG_ID
            AND SERVICES.ORIG_ID = SERVICE_TYPES.PACKAGE_ID
            AND PACKAGE_SERVICES.SERVICE_ID = SERVICES.ORIG_ID
            AND PACKAGES.ORIG_ID = PACKAGE_SERVICES.PACKAGE_ID
        
            AND ALLOTMENT_DAYS.DATUM >= #tmp_data.from_date
            AND ALLOTMENT_DAYS.DATUM < #tmp_data.to_date
            AND SERVICES.TYPE = 'H'
            AND SERVICE_TYPES.CODE_STRING = #tmp_data.CODE_STRING
            AND PACKAGES.BOOKINGCODE = #tmp_data.BOOKINGCODE
            AND PACKAGES.KATALOG_CODE = #tmp_data.KATALOG_CODE
            AND ALLOTMENT_DAYS.VERSION_NR = @versionNR
            and ALLOTMENT_PRIMARY_HEADERS.VERSION_NR = @versionNR
            AND ALLOTMENTS.VERSION_NR = @versionNR
            AND SERVICE_TYPES.VERSION_NR = @versionNR
            AND SERVICES.VERSION_NR = @versionNR
            AND PACKAGE_SERVICES.VERSION_NR = @versionNR
            AND PACKAGES.VERSION_NR = @versionNR
    )
print '8 start update capacity flight ' + convert(varchar(20), getdate())

    Update #tmp_data 
    set FREE_CAPACITY_FLIGHT = 
    (
        SELECT    isnull( min(ALLOTMENT_DAYS.FREEUNITS), 0)
        FROM    ALLOTMENT_DAYS,
            ALLOTMENT_PRIMARY_HEADERS,
            ALLOTMENTS,
            SERVICE_TYPES,
            SERVICES,
            PACKAGE_SERVICES,
            PACKAGES
        WHERE
            ALLOTMENT_PRIMARY_HEADERS.ORIG_ID = ALLOTMENT_DAYS.ORIG_HEADER_ID
            AND ALLOTMENTS.ORIG_ID = ALLOTMENT_PRIMARY_HEADERS.ALLOTMENTS_ID
            AND SERVICE_TYPES.ALLOTMENT_ID = ALLOTMENTS.ORIG_ID
            AND ISNULL(SERVICE_TYPES.SUBALLOTMENT_ID, ALLOTMENT_PRIMARY_HEADERS.ORIG_ID) 
                = ALLOTMENT_PRIMARY_HEADERS.ORIG_ID
            AND SERVICES.ORIG_ID = SERVICE_TYPES.PACKAGE_ID
            AND PACKAGE_SERVICES.SERVICE_ID = SERVICES.ORIG_ID
            AND PACKAGES.ORIG_ID = PACKAGE_SERVICES.PACKAGE_ID
        
            AND (ALLOTMENT_DAYS.DATUM = #tmp_data.from_date
            or ALLOTMENT_DAYS.DATUM = #tmp_data.to_date)
            AND SERVICES.TYPE = 'F'
            AND SERVICE_TYPES.CODE_STRING = 'Y'
            AND (PACKAGE_SERVICES.STARTDAY = 1 
            or PACKAGE_SERVICES.STARTDAY = 2 )
            AND PACKAGES.BOOKINGCODE = #tmp_data.BOOKINGCODE
            AND PACKAGES.KATALOG_CODE = #tmp_data.KATALOG_CODE
            AND ALLOTMENT_DAYS.VERSION_NR = @versionNR
            and ALLOTMENT_PRIMARY_HEADERS.VERSION_NR = @versionNR
            AND ALLOTMENTS.VERSION_NR = @versionNR
            AND SERVICE_TYPES.VERSION_NR = @versionNR
            AND SERVICES.VERSION_NR = @versionNR
            AND PACKAGE_SERVICES.VERSION_NR = @versionNR
            AND PACKAGES.VERSION_NR = @versionNR
    )

print '8.1 start update child free places ' + convert(varchar(20), getdate())

    Update #tmp_data 
    set CHILD_FREE_PLACES = 
    (
        SELECT    isnull( min(ALLOTMENT_DAYS.FREEUNITS), 0)
        FROM    ALLOTMENT_DAYS,
            ALLOTMENT_PRIMARY_HEADERS,
            ALLOTMENTS,
            SERVICE_TYPES,
            SERVICES,
            PACKAGE_SERVICES,
            PACKAGES
        WHERE
            ALLOTMENT_PRIMARY_HEADERS.ORIG_ID = ALLOTMENT_DAYS.ORIG_HEADER_ID
            AND ALLOTMENTS.ORIG_ID = ALLOTMENT_PRIMARY_HEADERS.ALLOTMENTS_ID
            AND SERVICE_TYPES.ALLOTMENT_ID = ALLOTMENTS.ORIG_ID
            AND ISNULL(SERVICE_TYPES.SUBALLOTMENT_ID, ALLOTMENT_PRIMARY_HEADERS.ORIG_ID) 
                = ALLOTMENT_PRIMARY_HEADERS.ORIG_ID
            AND SERVICES.ORIG_ID = SERVICE_TYPES.PACKAGE_ID
            AND PACKAGE_SERVICES.SERVICE_ID = SERVICES.ORIG_ID
            AND PACKAGES.ORIG_ID = PACKAGE_SERVICES.PACKAGE_ID
        
            AND (ALLOTMENT_DAYS.DATUM = #tmp_data.from_date
            or ALLOTMENT_DAYS.DATUM = #tmp_data.to_date)
            AND SERVICES.TYPE = 'F'
            AND SERVICE_TYPES.CODE_STRING = 'P'
            AND (PACKAGE_SERVICES.STARTDAY = 1 
            or PACKAGE_SERVICES.STARTDAY = 2 )
            AND PACKAGES.BOOKINGCODE = #tmp_data.BOOKINGCODE
            AND PACKAGES.KATALOG_CODE = #tmp_data.KATALOG_CODE
            AND ALLOTMENT_DAYS.VERSION_NR = @versionNR
            and ALLOTMENT_PRIMARY_HEADERS.VERSION_NR = @versionNR
            AND ALLOTMENTS.VERSION_NR = @versionNR
            AND SERVICE_TYPES.VERSION_NR = @versionNR
            AND SERVICES.VERSION_NR = @versionNR
            AND PACKAGE_SERVICES.VERSION_NR = @versionNR
            AND PACKAGES.VERSION_NR = @versionNR
    )

-- skiped select * from #tmp_data into mytable

print '11 end ' + convert(varchar(20), getdate())
end

Есть одно предложение:
попробуй условия в WHERE-блоке заменить на CASE в SELECT-блоке. Т.е:

select isnull("нужные поля", o) from ...
where "условие"

приводим к виду

select 
    case     when "условие"
        then "нужные поля"
        else 0
    end
from ...

синтаксис подгонишь под СУБД

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

_>Есть одно предложение:
_>попробуй условия в WHERE-блоке заменить на CASE в SELECT-блоке. Т.е:

Не понимаю как мне это поможет?

Здравствуйте, 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.

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

Здравствуйте, 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

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

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

приведите план выполения
может понятнее станет что не так.
хотя обновление разом такого объема может и должно выполняться такое время (сомневаюсь)

Здравствуйте, tarasich, Огромное спасибо

шаг 6 и 7 обьединенный теперь пролетает за 2 минуты вместо 40-50 мин.

единственное почему то потеряется апдейт на нескольких строчках?
из 670526 апдейтнет 619088

3 start join termins a prices Mar  7 2007 10:40AM
3.1 index created for termins a prices Mar  7 2007 10:40AM

(670526 row(s) affected)

4.1 start index for tmp_data Mar  7 2007 10:44AM
4.2 end index fro tmp_data Mar  7 2007 10:44AM
6 start update capacity room and release Mar  7 2007 10:44AM

(619088 row(s) affected)

8 start update capacity flight Mar  7 2007 10:46AM

(670526 row(s) affected)

8.1 start update child free places Mar  7 2007 10:48AM

(670526 row(s) affected)

обьединенный шаг выглядит так (изменил немного запись inner join вложенного):

Update #tmp_data 
    set FREE_CAPACITY_ROOM = tmp.FREE_CAPACITY_ROOM,
        RELEASE=tmp.RELEASE
    from
        #tmp_data inner join
    (        
            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
            FROM    ALLOTMENT_DAYS,
                ALLOTMENT_PRIMARY_HEADERS,
                ALLOTMENTS,
                SERVICE_TYPES,
                SERVICES,
                PACKAGE_SERVICES,
                PACKAGES,
                    #tmp_data
            WHERE
                SERVICE_TYPES.CODE_STRING = #tmp_data.CODE_STRING
                AND PACKAGES.BOOKINGCODE = #tmp_data.BOOKINGCODE
                AND PACKAGES.KATALOG_CODE = #tmp_data.KATALOG_CODE
                AND ALLOTMENT_DAYS.DATUM >= #tmp_data.from_date
                AND ALLOTMENT_DAYS.DATUM < #tmp_data.to_date
                AND ALLOTMENT_PRIMARY_HEADERS.ORIG_ID = ALLOTMENT_DAYS.ORIG_HEADER_ID
                AND ALLOTMENTS.ORIG_ID = ALLOTMENT_PRIMARY_HEADERS.ALLOTMENTS_ID
                AND SERVICE_TYPES.ALLOTMENT_ID = ALLOTMENTS.ORIG_ID
                AND ISNULL(SERVICE_TYPES.SUBALLOTMENT_ID, ALLOTMENT_PRIMARY_HEADERS.ORIG_ID) 
                    = ALLOTMENT_PRIMARY_HEADERS.ORIG_ID
                AND SERVICES.ORIG_ID = SERVICE_TYPES.PACKAGE_ID
                AND PACKAGE_SERVICES.SERVICE_ID = SERVICES.ORIG_ID
                AND PACKAGES.ORIG_ID = PACKAGE_SERVICES.PACKAGE_ID
                AND SERVICES.TYPE = 'H'
                AND ALLOTMENT_DAYS.VERSION_NR = @versionNR
                and ALLOTMENT_PRIMARY_HEADERS.VERSION_NR = @versionNR
                AND ALLOTMENTS.VERSION_NR = @versionNR
                AND SERVICE_TYPES.VERSION_NR = @versionNR
                AND SERVICES.VERSION_NR = @versionNR
                AND PACKAGE_SERVICES.VERSION_NR = @versionNR
                AND PACKAGES.VERSION_NR = @versionNR
        Group By #tmp_data.tmp_tour_id
    ) tmp
    on #tmp_data.tmp_tour_id=tmp.id

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

M>Здравствуйте, tarasich, Огромное спасибо

M>шаг 6 и 7 обьединенный теперь пролетает за 2 минуты вместо 40-50 мин.

Всегда пожалуйста

M>единственное почему то потеряется апдейт на нескольких строчках?
M>из 670526 апдейтнет 619088

Ну очевидно это те где связка inner не работает
для начала проверь правильные ли значения ли получаются
если да — меняй inner на left outer и tmp.FREE_CAPACITY_ROOM на isnull(tmp.FREE_CAPACITY_ROOM,0) (то же для релиз)

А на будущее — обязательно смотри на планы выполнения — если какой-то запрос выполняется много тысяч раз, то от него обязательно набо избавляться

Удачи

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

M>Здравствуйте, tarasich, Огромное спасибо

M>шаг 6 и 7 обьединенный теперь пролетает за 2 минуты вместо 40-50 мин.

M>

M>3 start join termins a prices Mar  7 2007 10:40AM
M>3.1 index created for termins a prices Mar  7 2007 10:40AM

M>(670526 row(s) affected)

M>4.1 start index for tmp_data Mar  7 2007 10:44AM
M>4.2 end index fro tmp_data Mar  7 2007 10:44AM
M>6 start update capacity room and release Mar  7 2007 10:44AM

M>(619088 row(s) affected)

M>8 start update capacity flight Mar  7 2007 10:46AM

M>(670526 row(s) affected)

M>8.1 start update child free places Mar  7 2007 10:48AM

M>(670526 row(s) affected)
M>

На две недели процедуру отложили, а сейчас пришла новая база данных и

, обьемы данных почти без изменений (на 10% больше), индексы все созданы, но шаг 6 и 7 обьединенный выполняется 120 минут.

Куда копать? как выложить план выполнения?

SET SHOWPLAN_TEXT ON
на обьединенный шаг 6 и 7 дает такой результат(не знаю почему QA обрезает строки справа):

|--Table Update(OBJECT:([tempdb].[dbo].[#tmp_data___________________________________________________________________________________________________________00000000005C]), SET:([#tmp_data].[RELEASE]=RaiseIfNull([Expr1021]), [#tmp_data].[FREE_CAPACITY_ROOM
       |--Top(ROWCOUNT est 0)
            |--Compute Scalar(DEFINE:([Expr1020]=isnull([Expr1016], 0), [Expr1021]=isnull([Expr1017], 0)))
                 |--Hash Match(Inner Join, HASH:([#tmp_data].[tmp_tour_id])=([#tmp_data].[tmp_tour_id]))
                      |--Stream Aggregate(GROUP BY:([#tmp_data].[tmp_tour_id]) DEFINE:([Expr1016]=MIN([ALLOTMENT_DAYS].[FREEUNITS]), [Expr1017]=MAX(Convert([ALLOTMENT_DAYS].[RELEASE]))))
                      |    |--Nested Loops(Inner Join, OUTER REFERENCES:([SERVICE_TYPES].[ALLOTMENT_ID]) WITH PREFETCH)
                      |         |--Filter(WHERE:([SERVICES].[ORIG_ID]=[PACKAGE_SERVICES].[SERVICE_ID]))
                      |         |    |--Bookmark Lookup(BOOKMARK:([Bmk1010]), OBJECT:([DAVINCI_WWW].[dbo].[PACKAGE_SERVICES]))
                      |         |         |--Nested Loops(Inner Join, OUTER REFERENCES:([PACKAGES].[ORIG_ID]) WITH PREFETCH)
                      |         |              |--Sort(ORDER BY:([#tmp_data].[tmp_tour_id] ASC))
                      |         |              |    |--Filter(WHERE:([PACKAGES].[KATALOG_CODE]=[#tmp_data].[KATALOG_CODE]))
                      |         |              |         |--Bookmark Lookup(BOOKMARK:([Bmk1012]), OBJECT:([DAVINCI_WWW].[dbo].[PACKAGES]))
                      |         |              |              |--Nested Loops(Inner Join, OUTER REFERENCES:([#tmp_data].[BOOKINGCODE]) WITH PREFETCH)
                      |         |              |                   |--Bookmark Lookup(BOOKMARK:([Bmk1014]), OBJECT:([tempdb].[dbo].[#tmp_data___________________________________________________________________________________________________________00000000
                      |         |              |                   |    |--Nested Loops(Inner Join, OUTER REFERENCES:([ALLOTMENT_DAYS].[DATUM], [SERVICE_TYPES].[CODE_STRING]))
                      |         |              |                   |         |--Nested Loops(Inner Join, OUTER REFERENCES:([ALLOTMENT_PRIMARY_HEADERS].[ORIG_ID]))
                      |         |              |                   |         |    |--Hash Match(Inner Join, HASH:([ALLOTMENT_PRIMARY_HEADERS].[ALLOTMENTS_ID])=([SERVICE_TYPES].[ALLOTMENT_ID]), RESIDUAL:([ALLOTMENT_PRIMARY_HEADERS].[ALLOTMENTS_ID]=[SERVICE_
                      |         |              |                   |         |    |    |--Index Scan(OBJECT:([DAVINCI_WWW].[dbo].[ALLOTMENT_PRIMARY_HEADERS].[idx_ALLOTMENT_PRIMARY_HEADERS_s1]),  WHERE:([ALLOTMENT_PRIMARY_HEADERS].[VERSION_NR]=[@versionNR])
                      |         |              |                   |         |    |    |--Hash Match(Inner Join, HASH:([SERVICES].[ORIG_ID])=([SERVICE_TYPES].[PACKAGE_ID]), RESIDUAL:([SERVICE_TYPES].[PACKAGE_ID]=[SERVICES].[ORIG_ID]))
                      |         |              |                   |         |    |         |--Clustered Index Scan(OBJECT:([DAVINCI_WWW].[dbo].[SERVICES].[PK__SERVICES__2DB1C7EE]), WHERE:([SERVICES].[VERSION_NR]=[@versionNR] AND [SERVICES].[TYPE]='H'))
                      |         |              |                   |         |    |         |--Clustered Index Scan(OBJECT:([DAVINCI_WWW].[dbo].[SERVICE_TYPES].[PK__SERVICE_TYPES__16CE6296]), WHERE:([SERVICE_TYPES].[VERSION_NR]=[@versionNR]))
                      |         |              |                   |         |    |--Clustered Index Seek(OBJECT:([DAVINCI_WWW].[dbo].[ALLOTMENT_DAYS].[PK__ALLOTMENT_DAYS__1C873BEC]), SEEK:([ALLOTMENT_DAYS].[ORIG_HEADER_ID]=[ALLOTMENT_PRIMARY_HEADERS].[ORI
                      |         |              |                   |         |--Index Seek(OBJECT:([tempdb].[dbo].[#tmp_data___________________________________________________________________________________________________________00000000005C].[idx_tmp_da
                      |         |              |                   |--Index Seek(OBJECT:([DAVINCI_WWW].[dbo].[PACKAGES].[idx_PACKAGES_s0]), SEEK:([PACKAGES].[BOOKINGCODE]=[#tmp_data].[BOOKINGCODE] AND [PACKAGES].[VERSION_NR]=[@versionNR]) ORDERED FORWARD)
                      |         |              |--Index Seek(OBJECT:([DAVINCI_WWW].[dbo].[PACKAGE_SERVICES].[idx_PACKAGE_SERVICES_s1]), SEEK:([PACKAGE_SERVICES].[PACKAGE_ID]=[PACKAGES].[ORIG_ID] AND [PACKAGE_SERVICES].[VERSION_NR]=[@versionNR]) ORDERED FOR
                      |         |--Index Seek(OBJECT:([DAVINCI_WWW].[dbo].[ALLOTMENTS].[idx_ALLOTMENTS_s0]), SEEK:([ALLOTMENTS].[ORIG_ID]=[SERVICE_TYPES].[ALLOTMENT_ID] AND [ALLOTMENTS].[VERSION_NR]=[@versionNR]) ORDERED FORWARD)
                      |--Table Scan(OBJECT:([tempdb].[dbo].[#tmp_data___________________________________________________________________________________________________________00000000005C]))

	От:	tarasich
	Дата:	06.03.07 13:12
	Оценка:	3 (1)

	От:	Malchik
	Дата:	06.03.07 11:46
	Оценка:

	От:	ser_gunya
	Дата:	06.03.07 12:05
	Оценка:

	От:	Malchik
	Дата:	06.03.07 12:59
	Оценка:

	От:	Malchik
	Дата:	06.03.07 15:31
	Оценка:

	От:	shelkovnikov
	Дата:	07.03.07 07:05
	Оценка: