Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Подскажите, в чем проблема  [new]
Bobby
Member

Откуда:
Сообщений: 638
Всем доброго дня!
Вот такая проблема. Есть две вьюхи. Ни чем не приметные:


 --web_TERMINALS_CURRENT_1   
  SELECT
    t.[ID] as [TERMINALS_ID] -- Unique record ID; Autoincrement; Also stands for DP-PackageInstanceId
    ,t.[TECHNOLOGY] as [TECHNOLOGY] -- Terminal technology (package type) - R-BGAN, BGAN. See SYS_TECHNOLOGIES.
    ,t.[CHANGES_PENDING] as [CHANGES_PENDING] -- ID of the pending changes request; NULL if no changes are pending
    ,t.[CHANGES_PENDING_WHAT] as [CHANGES_PENDING_WHAT] -- Type of the pending changes. Redundant; Reserved. May probably store pending status or barring reason?
    ,t.[DATE_ACT] as [DATE_ACT] -- Terminal (package) activation date (UTC). Differs from DATE_CRE slightly due to time consumed for activation process
    ,tb.[ID] as [TERMINAL_BASE_ID] -- Unique record ID
    ,tb.[MSISDN] as [MSISDN] -- Master MSISDN number. Stands for Terminal Number
    ,tb.[STATUS] as [STATUS] -- Terminal (package) status - active, suspended etc.  (See SYS_TERM_STATUSES)
    ,tb.[NAME] as [NAME] -- Terminal (package) name/comments
    ,tb.[INM_CUSTOMER_ID] as [INM_CUSTOMER_ID] -- Inmarsat customer. Link to BSS_CUSTOMERS.ID
    ,tb.[INM_PACKAGE_PLAN_ID] as [INM_PACKAGE_PLAN_ID] -- Inmarsat Package Plan ID (See SYS_INM_PACKAGE_PLANS)
    ,tb.[INM_RATE_PLAN_ID] as [INM_RATE_PLAN_ID] -- Inmarsat Package Rate Plan ID (See SYS_INM_RATE_PLANS)
    ,tb.[INM_MARKET_SECTOR_ID] as [INM_MARKET_SECTOR_ID] -- Market Sector for Inmarsat; from list: Enterprise, Maritime, Aeronautical (See SYS_BSS_MARKETS)
    ,tb.[INM_INDUSTRY_ID] as [INM_INDUSTRY_ID] -- Industry for Inmarsat; from list, e.g., Military Government, Manufacturing, Broadcast Media, etc.  (See SYS_BSS_INDUSTRY)
    ,tb.[ICCID] as [ICCID] -- SIM card ICC ID number
    ,tb.[IMSI] as [IMSI] -- SIM card IMSI number
    ,tb.[IMEI] as [IMEI] -- Hardware IMEI number
    ,tb.[UT_SERIAL] as [UT_SERIAL] -- BGAN Hardware Serial number / Dummy UT for RBGAN (optional)
    ,tb.[MODEL_ID] as [MODEL_ID] -- Hardware Model Type - updated by Inmarsat only. See SYS_UT_MODELS for details. Used in processing GetData requests.
    ,tb.[OBO] as [OBO] -- Operator Determined Barring - Outbound; Yes / No Checkbox
    ,tb.[OBI] as [OBI] -- Operator Determined Barring - Inbound; Yes / No Checkbox
    ,tb.[OBR] as [OBR] -- Operator Determined Barring - Roaming; Yes / No Checkbox
    ,tb.[CLIP] as [CLIP] -- Calling Line Identifictation Presentation; Yes / No Checkbox
    ,tb.[SOCLIP] as [SOCLIP] -- Subscription Option Calling Line Identification Presentation ["otmena zapreta vydachi svoego nomera" - anti-anti-AON]; 0 = without override category (default); 1 = without override category;
    ,tb.[CLIR] as [CLIR] -- Calling Line Identifictation Restriction; Yes / No Checkbox;
    ,tb.[SOCLIR] as [SOCLIR] -- Subscription Option Calling Line Identification Restriction [Anti-AON po zhelaniyu - nabrat` #31# i nomer kuda zvonit`]; 0 = permanent mode; 1 = temporary mode, peresentation restricted; 2 = temporary mode, presentation allowed (default)
    ,tb.[COLP] as [COLP] -- Connected Line Identifictation Presentation; Presentation of the actual number reached (if number called was diverted to another number); Yes / No Checkbox;
    ,tb.[SOCOLP] as [SOCOLP] -- Subscription Option Connected Line Identifictation Presentation; 0 = without override category (default); 1 = without override category; 
    ,tb.[COLR] as [COLR] -- Connected Line Identifictation Restriction; Presentation of the original number called by the calling party (if the call was diverted to this cellphone); Yes / No Checkbox
    ,tb.[EMLPP] as [EMLPP] -- Enhanced Multi-Level Precedence and Pre-Emption; Yes / No Checkbox
    ,tb.[PREPAID_POLICY] as [PREPAID_POLICY] -- Prepaid policy used on SIM activation. Null - no prepaid policy has been used. Linked to PREPAID_POLICY.ID.
    ,tb.[ATTACHED_SERVICES] as [ATTACHED_SERVICES] -- Redundant; List of services attached; See SYS_TERM_SERVICES_CORE; Example: 1,2,3,10
    ,tb.[PARAMS] as [PARAMS] -- Extra parameters in XML format.
    ,tb.[PARAMS_FORMAT] as [PARAMS_FORMAT] -- Format of data in PARAMS - XML/BLOB/Text. For details see SYS_PARAMS_DATA_FORMATS.
    ,tb.[PARAMS_VERSION] as [PARAMS_VERSION] -- Version of format used in PARAMS; For example - 1, 2, etc
    ,tb.[PROGRAMME] as [PROGRAMME] -- Activation programme - Free Use, Commercial, Beta Tests, etc. See SYS_ACT_PROGRAMS for details.
    ,tb.[INM_PREPAID] as [INM_PREPAID] -- Whether SIM is prepaid for Inmarsat of not. 0/1 = no/yes
    ,tb.[INM_PREP_CLASS] as [INM_PREP_CLASS] -- Inmarsat prepaid class. Only "Gold" is available so far (default). See SYS_INM_PREP_CLASSES().
    ,tb.[PREP_INITIAL_AMOUNT] as [PREP_INITIAL_AMOUNT] -- Initial prepaid balance in units. There are two modes of use: (1) Inm prepaid is on; (2) Inm prepaid is off, MVS prepaid is on.
    ,tb.[PREP_RATE_PLAN_ID] as [PREP_RATE_PLAN_ID] -- Rate plan for for prepaid policy`s 1st account. Overrides policy and account values. (Null - use policy/account values)
    ,tb.[PREP_LIMITS_ADJUSTMENT] as [PREP_LIMITS_ADJUSTMENT] -- Limits for prepaid policy`s 1st account will be shifted by this value. Overrides policy and account values. (Null - use policy/account values)
    ,tb.[PREP_TRANSLATION_RATE] as [PREP_TRANSLATION_RATE] -- Rate of conversion of units to display units for prepaid policy`s 1st account. Overrides values in policy and account. (Null - use policy/account values)
    ,ta.DATE_START as [LAST_CHANGE_DATE] -- Last change date
    ,ta.TYPE as [LAST_CHANGE_TYPE] -- What SIM parameters has been changed last time. (Type of action code; See SYS_TERM_ACTION_TYPES for details).
  FROM 
    dbo.TERMINALS t 
    INNER JOIN dbo.TERMINAL_BASE tb ON tb.PACKAGE_INSTANCE_ID = t.ID 
    INNER JOIN dbo.vTERMINAL_ACTIONS_CURRENT ta ON ta.ID = tb.ID 

и
--vTERMINAL_ACTIONS_STATUS_RELATED_CURRENT_EXT
  SELECT
    t.[ID] as [TERMINALS_ID] -- Unique record ID; Autoincrement; Also stands for DP-PackageInstanceId
    ,tas.DATE_START as [LAST_CHANGE_STATUS_DATE] -- Last change status date
    ,tas.CHA_STAT_REASON as [LAST_CHANGE_STATUS_REASON] -- Change status reason. So far: 0=user submited; 100=submitted by prepaid system. For details see SYS_TERM_CHA_STAT_REASONS.
    ,dbo.fn_IsTerminalDirty(t.ID) as [IS_TERMINAL_DIRTY] -- Whether TERMINAL has changes pending.
    
  FROM 
    dbo.TERMINALS t 
    INNER JOIN dbo.vTERMINAL_ACTIONS_STATUS_RELATED_CURRENT tas ON tas.PACKAGE_INSTANCE_ID = t.ID   

Обе отрабатывают по отдельности за секунду
Но как только я хочу их заджоинить:

SELECT w1.* 
,w2.LAST_CHANGE_STATUS_DATE
,w2.LAST_CHANGE_STATUS_REASON
,w2.IS_TERMINAL_DIRTY

from  
web_TERMINALS_CURRENT_1  w1
INNER JOIN  vTERMINAL_ACTIONS_STATUS_RELATED_CURRENT_EXT w2 on w1.TERMINALS_ID=w2.TERMINALS_ID 

Все замирает почти на пять минут. Подскажите, плз, как можно все это дело ускорить и из-за чего могут быть такие тормоза. На таблицах живых индексы и статистики есть, тьюнинг эдвайзер говорит, что все у них замечательно и никаких рекомендаций не выдает.
Заранее благодарен
22 июл 11, 16:41    [11013954]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, в чем проблема  [new]
igor2222
Member

Откуда: Харків
Сообщений: 1233
Bobby, а Вы уверены, что [TERMINALS_ID] в пределах вьюх уникальны?
22 июл 11, 17:41    [11014396]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, в чем проблема  [new]
adv
Member

Откуда:
Сообщений: 3333
Bobby,
а в плане выполнения причину тормозов не видно?
22 июл 11, 17:54    [11014482]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, в чем проблема  [new]
stimpi
Member

Откуда: Киев, Украина
Сообщений: 662
как же ничем не приментные

явно колонки во вьюха писать надо, а не звездочки тыкать
автор
SELECT w1.* 


планы в студию
22 июл 11, 18:30    [11014685]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, в чем проблема  [new]
Bobby
Member

Откуда:
Сообщений: 638
вот запрос без звездочек:
SELECT 

    w1.[TERMINALS_ID] -- Unique record ID; Autoincrement; Also stands for DP-PackageInstanceId
    ,w1.[TECHNOLOGY] as [TECHNOLOGY] -- Terminal technology (package type) - R-BGAN, BGAN. See SYS_TECHNOLOGIES.
    ,w1.[CHANGES_PENDING] as [CHANGES_PENDING] -- ID of the pending changes request; NULL if no changes are pending
    ,w1.[CHANGES_PENDING_WHAT] as [CHANGES_PENDING_WHAT] -- Type of the pending changes. Redundant; Reserved. May probably store pending status or barring reason?
    ,w1.[DATE_ACT] as [DATE_ACT] -- Terminal (package) activation date (UTC). Differs from DATE_CRE slightly due to time consumed for activation process
    ,w1.[TERMINAL_BASE_ID] -- Unique record ID
    ,w1.[MSISDN] as [MSISDN] -- Master MSISDN number. Stands for Terminal Number
    ,w1.[STATUS] as [STATUS] -- Terminal (package) status - active, suspended etc.  (See SYS_TERM_STATUSES)
    ,w1.[NAME] as [NAME] -- Terminal (package) name/comments
    ,w1.[INM_CUSTOMER_ID] as [INM_CUSTOMER_ID] -- Inmarsat customer. Link to BSS_CUSTOMERS.ID
    ,w1.[INM_PACKAGE_PLAN_ID] as [INM_PACKAGE_PLAN_ID] -- Inmarsat Package Plan ID (See SYS_INM_PACKAGE_PLANS)
    ,w1.[INM_RATE_PLAN_ID] as [INM_RATE_PLAN_ID] -- Inmarsat Package Rate Plan ID (See SYS_INM_RATE_PLANS)
    ,w1.[INM_MARKET_SECTOR_ID] as [INM_MARKET_SECTOR_ID] -- Market Sector for Inmarsat; from list: Enterprise, Maritime, Aeronautical (See SYS_BSS_MARKETS)
    ,w1.[INM_INDUSTRY_ID] as [INM_INDUSTRY_ID] -- Industry for Inmarsat; from list, e.g., Military Government, Manufacturing, Broadcast Media, etc.  (See SYS_BSS_INDUSTRY)
    ,w1.[ICCID] as [ICCID] -- SIM card ICC ID number
    ,w1.[IMSI] as [IMSI] -- SIM card IMSI number
    ,w1.[IMEI] as [IMEI] -- Hardware IMEI number
    ,w1.[UT_SERIAL] as [UT_SERIAL] -- BGAN Hardware Serial number / Dummy UT for RBGAN (optional)
    ,w1.[MODEL_ID] as [MODEL_ID] -- Hardware Model Type - updated by Inmarsat only. See SYS_UT_MODELS for details. Used in processing GetData requests.
    ,w1.[OBO] as [OBO] -- Operator Determined Barring - Outbound; Yes / No Checkbox
    ,w1.[OBI] as [OBI] -- Operator Determined Barring - Inbound; Yes / No Checkbox
    ,w1.[OBR] as [OBR] -- Operator Determined Barring - Roaming; Yes / No Checkbox
    ,w1.[CLIP] as [CLIP] -- Calling Line Identifictation Presentation; Yes / No Checkbox
    ,w1.[SOCLIP] as [SOCLIP] -- Subscription Option Calling Line Identification Presentation ["otmena zapreta vydachi svoego nomera" - anti-anti-AON]; 0 = without override category (default); 1 = without override category;
    ,w1.[CLIR] as [CLIR] -- Calling Line Identifictation Restriction; Yes / No Checkbox;
    ,w1.[SOCLIR] as [SOCLIR] -- Subscription Option Calling Line Identification Restriction [Anti-AON po zhelaniyu - nabrat` #31# i nomer kuda zvonit`]; 0 = permanent mode; 1 = temporary mode, peresentation restricted; 2 = temporary mode, presentation allowed (default)
    ,w1.[COLP] as [COLP] -- Connected Line Identifictation Presentation; Presentation of the actual number reached (if number called was diverted to another number); Yes / No Checkbox;
    ,w1.[SOCOLP] as [SOCOLP] -- Subscription Option Connected Line Identifictation Presentation; 0 = without override category (default); 1 = without override category; 
    ,w1.[COLR] as [COLR] -- Connected Line Identifictation Restriction; Presentation of the original number called by the calling party (if the call was diverted to this cellphone); Yes / No Checkbox
    ,w1.[EMLPP] as [EMLPP] -- Enhanced Multi-Level Precedence and Pre-Emption; Yes / No Checkbox
    ,w1.[PREPAID_POLICY] as [PREPAID_POLICY] -- Prepaid policy used on SIM activation. Null - no prepaid policy has been used. Linked to PREPAID_POLICY.ID.
    ,w1.[ATTACHED_SERVICES] as [ATTACHED_SERVICES] -- Redundant; List of services attached; See SYS_TERM_SERVICES_CORE; Example: 1,2,3,10
    ,w1.[PARAMS] as [PARAMS] -- Extra parameters in XML format.
    ,w1.[PARAMS_FORMAT] as [PARAMS_FORMAT] -- Format of data in PARAMS - XML/BLOB/Text. For details see SYS_PARAMS_DATA_FORMATS.
    ,w1.[PARAMS_VERSION] as [PARAMS_VERSION] -- Version of format used in PARAMS; For example - 1, 2, etc
    ,w1.[PROGRAMME] as [PROGRAMME] -- Activation programme - Free Use, Commercial, Beta Tests, etc. See SYS_ACT_PROGRAMS for details.
    ,w1.[INM_PREPAID] as [INM_PREPAID] -- Whether SIM is prepaid for Inmarsat of not. 0/1 = no/yes
    ,w1.[INM_PREP_CLASS] as [INM_PREP_CLASS] -- Inmarsat prepaid class. Only "Gold" is available so far (default). See SYS_INM_PREP_CLASSES().
    ,w1.[PREP_INITIAL_AMOUNT] as [PREP_INITIAL_AMOUNT] -- Initial prepaid balance in units. There are two modes of use: (1) Inm prepaid is on; (2) Inm prepaid is off, MVS prepaid is on.
    ,w1.[PREP_RATE_PLAN_ID] as [PREP_RATE_PLAN_ID] -- Rate plan for for prepaid policy`s 1st account. Overrides policy and account values. (Null - use policy/account values)
    ,w1.[PREP_LIMITS_ADJUSTMENT] as [PREP_LIMITS_ADJUSTMENT] -- Limits for prepaid policy`s 1st account will be shifted by this value. Overrides policy and account values. (Null - use policy/account values)
    ,w1.[PREP_TRANSLATION_RATE] as [PREP_TRANSLATION_RATE] -- Rate of conversion of units to display units for prepaid policy`s 1st account. Overrides values in policy and account. (Null - use policy/account values)
    ,w1.[LAST_CHANGE_DATE] -- Last change date
    ,w1.[LAST_CHANGE_TYPE]

,w2.LAST_CHANGE_STATUS_DATE
,w2.LAST_CHANGE_STATUS_REASON
,w2.IS_TERMINAL_DIRTY

from  
web_TERMINALS_CURRENT_1  w1
INNER JOIN  vTERMINAL_ACTIONS_STATUS_RELATED_CURRENT_EXT w2 on w1.TERMINALS_ID=w2.TERMINALS_ID

план во вложении в архиве. в чистом виде не влез
[TERMINALS_ID] в пределах вьюх уникальны.

К сообщению приложен файл (plan.rar - 10Kb) cкачать
23 июл 11, 10:29    [11016284]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, в чем проблема  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31961
Bobby
[TERMINALS_ID] в пределах вьюх уникальны.
Сомневаюсь что то... Сужя по названиям таблиц и по плану, не должны быть уникальны.

Вы выполните запросы, чтоб убедиться:

SELECT TERMINALS_ID from web_TERMINALS_CURRENT_1
SELECT TERMINALS_ID from vTERMINAL_ACTIONS_STATUS_RELATED_CURRENT_EXT
SELECT distinct TERMINALS_ID from web_TERMINALS_CURRENT_1
SELECT distinct TERMINALS_ID from vTERMINAL_ACTIONS_STATUS_RELATED_CURRENT_EXT
24 июл 11, 10:17    [11018720]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, в чем проблема  [new]
Bobby
Member

Откуда:
Сообщений: 638
alexeyvg,

сделал предложенные проверки, все 4 запроса вернули одинаковое количество записей.
еще сделал

select TERMINALS_ID from web_TERMINALS_CURRENT_1 where TERMINALS_ID not in (SELECT distinct TERMINALS_ID from vTERMINAL_ACTIONS_STATUS_RELATED_CURRENT_EXT)

вернулось 0 записей
24 июл 11, 13:08    [11018957]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, в чем проблема  [new]
Bobby
Member

Откуда:
Сообщений: 638
Всем доброе утро! Нашел нетривиальное решение проблемы. Попинайте, если не лень.
В виду того, что обе вьюхи по отдельности отрабатывают довольно-таки быстро (в пределах 1-2 секунд), создал table-valued функцию. В этой функции в качестве RETURN создал memo-таблицу, куда сливался результат объединения. Выборку из первой вьюхи слил в таблицу @t1, из второй -- в @t2. А результат оформил вот таким способом
INSERT @t_out
select w1.* 
,w2.LAST_CHANGE_STATUS_DATE
,w2.LAST_CHANGE_STATUS_REASON
,w2.IS_TERMINAL_DIRTY
from @t1 w1
inner join @t2 w2 on w1.TERMINALS_ID=w2.TERMINALS_ID
И итоговую выборку делал уже из этой функции. Результат работы -- 3-4 секунды против 5-ти минут
25 июл 11, 09:54    [11021122]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, в чем проблема  [new]
EvAlex
Member

Откуда: Israel
Сообщений: 1001
а где execution plan?!
25 июл 11, 10:21    [11021262]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, в чем проблема  [new]
Bobby
Member

Откуда:
Сообщений: 638
EvAlex,

выше смотрите, есть план там. в виде раровского файла
25 июл 11, 11:09    [11021545]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, в чем проблема  [new]
stimpi
Member

Откуда: Киев, Украина
Сообщений: 662
попробуйте сделать 1 запрос, вместо вьюх
25 июл 11, 11:32    [11021700]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, в чем проблема  [new]
Bobby
Member

Откуда:
Сообщений: 638
stimpi
попробуйте сделать 1 запрос, вместо вьюх


Это не реально, т.к. эти вьюхи тянут за собой еще гору вьюх, и все это хозяйство разложить на исходные таблицы просто убийственно.
25 июл 11, 12:00    [11021978]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, в чем проблема  [new]
stimpi
Member

Откуда: Киев, Украина
Сообщений: 662
сколько записей возвращает каждая вьюха по отдельности и вместе ?
25 июл 11, 13:05    [11022474]     Ответить | Цитировать Сообщить модератору
 Re: Подскажите, в чем проблема  [new]
Bobby
Member

Откуда:
Сообщений: 638
stimpi
сколько записей возвращает каждая вьюха по отдельности и вместе ?

в районе ста тысяч каждая. ну и итоговая столько же. эти две вьюхи тупо джоинятся по terminals_id. Повторюсь, по terminals_id они идентичны, проверялось. В принципе, задача решена приведенным выше способом (через table-valued функцию), просто интересно, может есть более красивые решения
25 июл 11, 14:29    [11023136]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить