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

Откуда:
Сообщений: 44
Добрый день!
Заколдованный круг получается. Без robust plan ошибка, что нужно с ним сделать запрос, используя robust plan , ошибка - не нужно использовать

select @@version


Microsoft SQL Server 2000 - 8.00.2039 (Intel X86) May 3 2005 23:18:38 Copyright (c) 1988-2003 Microsoft Corporation Developer Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

select * from table


Could not insert a row larger than the page size into a hash table. Resubmit the query with the ROBUST PLAN hint

select * from table 
OPTION (robust plan)

Warning: The query processor could not produce a query plan from the optimizer because the total length of all the columns in the GROUP BY or ORDER BY clause exceeds 8000 bytes. Resubmit your query without the ROBUST PLAN hint.

Причем в запросах не используются group or order
19 мар 12, 17:18    [12275646]     Ответить | Цитировать Сообщить модератору
 Re: Robust plan  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
А нефига такие группировки писать.
19 мар 12, 17:18    [12275651]     Ответить | Цитировать Сообщить модератору
 Re: Robust plan  [new]
Гавриленко Сергей Алексеевич
Member

Откуда:
Сообщений: 37254
sinnah
Причем в запросах не используются group or order
А вьюх там нет? Вообще, текст запроса покажите, что ли.
19 мар 12, 17:19    [12275662]     Ответить | Цитировать Сообщить модератору
 Re: Robust plan  [new]
sinnah
Member

Откуда:
Сообщений: 44
view есть но внутри них или на запросе к ним нет никаких групировок

SELECT      
[vw_distributor_sales].[acc_system_code] AS [acc_system_code],
     [vw_distributor_sales].[customer_code] AS [customer_code],
     [vw_distributor_sales].[customer_inn] AS [customer_inn],
     [vw_distributor_sales].[customer_name] AS [customer_name],
     [vw_distributor_sales].[customer_terr_city_code] AS [customer_terr_city_code],
     [vw_distributor_sales].[customer_terr_city_name] AS [customer_terr_city_name],
     [vw_distributor_sales].[customer_terr_country_code] AS [customer_terr_country_code],
     [vw_distributor_sales].[customer_terr_country_name] AS [customer_terr_country_name],
     [vw_distributor_sales].[customer_terr_district_code] AS [customer_terr_district_code],
     [vw_distributor_sales].[customer_terr_district_name] AS [customer_terr_district_name],
    [vw_distributor_sales].[customer_terr_region_code] AS [customer_terr_region_code],
     [vw_distributor_sales].[customer_terr_region_name] AS [customer_terr_region_name],
     [vw_distributor_sales].[distributor_code] AS [distributor_code],
     [vw_distributor_sales].[distributor_currency] AS [distributor_currency],
     [vw_distributor_sales].[distributor_lfirm_code] AS [distributor_lfirm_code],
     [vw_distributor_sales].[distributor_lfirm_name] AS [distributor_lfirm_name],
     [vw_distributor_sales].[distributor_name] AS [distributor_name],
     [vw_distributor_sales].[packed_product_code] AS [packed_product_code], 
    [vw_distributor_sales].[packed_product_name] AS [packed_product_name],
     [vw_distributor_sales].[packed_product_pack_measure] AS [packed_product_pack_measure], 
    [vw_distributor_sales].[packed_product_pack_size] AS [packed_product_pack_size], 
    [vw_distributor_sales].[packed_product_pack_size_ltres] AS [packed_product_pack_size_ltres], 
    [vw_distributor_sales].[packed_product_pack_type] AS [packed_product_pack_type],
     [vw_distributor_sales].[period] AS [period],  
   [vw_distributor_sales].[product_family_code] AS [product_family_code], 
    [vw_distributor_sales].[product_family_name] AS [product_family_name],  
   [vw_distributor_sales].[product_group_code] AS [product_group_code],   
  [vw_distributor_sales].[product_group_name] AS [product_group_name],   
  [vw_distributor_sales].[product_group_nameENG] AS [product_group_nameENG],   
  [vw_distributor_sales].[product_LoB_code] AS [product_LoB_code],  
   [vw_distributor_sales].[product_LoB_name] AS [product_LoB_name],  
   [vw_distributor_sales].[product_LoB2_code] AS [product_LoB2_code],   
  [vw_distributor_sales].[product_LoB2_name] AS [product_LoB2_name],   
  [vw_distributor_sales].[product_LoB2_nameENG] AS [product_LoB2_nameENG],  
   [vw_distributor_sales].[sale_terr_city_code] AS [sale_terr_city_code],  
   [vw_distributor_sales].[sale_terr_city_name] AS [sale_terr_city_name],   
  [vw_distributor_sales].[sale_terr_country_code] AS [sale_terr_country_code],  
   [vw_distributor_sales].[sale_terr_country_name] AS [sale_terr_country_name],  
   [vw_distributor_sales].[sale_terr_district_code] AS [sale_terr_district_code],  
   [vw_distributor_sales].[sale_terr_district_name] AS [sale_terr_district_name],   
  [vw_distributor_sales].[sale_terr_region_code] AS [sale_terr_region_code],  
   [vw_distributor_sales].[sale_terr_region_name] AS [sale_terr_region_name],   
  [vw_distributor_sales].[saleable_product_code] AS [saleable_product_code],   
  [vw_distributor_sales].[saleable_product_name] AS [saleable_product_name],   
  [vw_distributor_sales].[sales_ch1_code] AS [sales_ch1_code],  
   [vw_distributor_sales].[sales_ch1_name] AS [sales_ch1_name],   
  [vw_distributor_sales].[sales_ch1_nameENG] AS [sales_ch1_nameENG],  
   [vw_distributor_sales].[sales_ch2_code] AS [sales_ch2_code],   
  [vw_distributor_sales].[sales_ch2_name] AS [sales_ch2_name],   
  [vw_distributor_sales].[sales_ch2_nameENG] AS [sales_ch2_nameENG], 
    [vw_distributor_sales].[sales_ch3_code] AS [sales_ch3_code],   
  [vw_distributor_sales].[sales_ch3_name] AS [sales_ch3_name],  
   [vw_distributor_sales].[sales_ch3_nameENG] AS [sales_ch3_nameENG],  
   [vw_distributor_sales].[sales_proceed] AS [sales_proceed], 
    [vw_distributor_sales].[volume_items] AS [volume_items],   
  [vw_secur_sales_managers].[SM_code] AS [SM_code],  
  [vw_secur_sales_managers].[SM_name] AS [SM_name],   
  [vw_secur_sales_managers].[STL_code] AS [STL_code], 
    [vw_secur_sales_managers].[STL_name] AS [STL_name],  
   [vw_secur_sales_managers].[AM_code] AS [AM_code],    
 [vw_secur_sales_managers].[AM_name] AS [AM_name],   
  [vw_secur_sales_managers].[LoB_code] AS [LoB_code],  
   [vw_secur_sales_managers].[legal_firm_code] AS [legal_firm_code], 
    [vw_secur_sales_managers].[SM_olapcube_suffix] AS [SM_olapcube_suffix],   
  [vw_secur_sales_managers].[STL_olapcube_suffix] AS [STL_olapcube_suffix], 
    [vw_secur_sales_managers].[AM_olapcube_suffix] AS [AM_olapcube_suffix],  
   [vw_distributor_sales].[investment_prj_num] AS [investment_prj_num],  
   [vw_distributor_sales].[point_1] AS [point_1],  
   [vw_distributor_sales].[point_2] AS [point_2], 
  [vw_distributor_sales].[point_3] AS [point_3],   
  [vw_distributor_sales].[points_1] AS [points_1],  
   [vw_distributor_sales].[points_2] AS [points_2],  
   [vw_distributor_sales].[points_3] AS [points_3],   
  [vw_distributor_sales].[investment_prj_signing_date] AS [investment_prj_signing_date],  
   [vw_distributor_sales].[investment_prj_name] AS [investment_prj_name],  
   [vw_distributor_sales].[investment_prj_contractor_inn] AS [investment_prj_contractor_inn], 
    [vw_distributor_sales].[investment_prj_contractor] AS [investment_prj_contractor],  
   [vw_distributor_sales].[volume_litres] AS [volume_litres],  
   [vw_distributor_sales].[investment_prj_length] AS [investment_prj_length],
global_ipp as global, gsap as gsap 

FROM       
[vw_distributor_sales],   
  [vw_secur_sales_managers] 
 WHERE    
  (         [vw_distributor_sales].[product_LoB_code] = [vw_secur_sales_managers].[LoB_code]        
  AND          [vw_secur_sales_managers].[legal_firm_code] = [vw_distributor_sales].[distributor_lfirm_code] 
and period >= '2010-01-01'      ) 
19 мар 12, 17:22    [12275691]     Ответить | Цитировать Сообщить модератору
 Re: Robust plan  [new]
sinnah
Member

Откуда:
Сообщений: 44
А вот и я не прав, в одной из view

DISTINCT 
                      TOP 100 PERCENT 
19 мар 12, 17:27    [12275739]     Ответить | Цитировать Сообщить модератору
 Re: Robust plan  [new]
Prolog
Member

Откуда: Москва
Сообщений: 2793
Попробуйте в конце запроса добавить option(loop join). Но может сильно упасть производительность.
И обновите 2039 хотя бы до 2040.
19 мар 12, 17:55    [12276087]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить