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

Откуда:
Сообщений: 204
у меня есть функция f_v_port_eq, которая возвращает таблицу. Работает она 3-4 секунды, что для моей задачи много. Чтоб можно было сделать , чтоб функция отрабатывала за 1-1.5 секунды?

текст функции:
+
CREATE FUNCTION [oss].[f_v_port_eq](@vequip varchar(50),@organization int)

RETURNS @tbl TABLE(id              int,
                   chassis_id      int, 
                   card_id         int, 
                   subcard_id      int,  
                   card_model_code varchar(100), 
                   code_port       varchar(40),
				   card_model_id   int,
				   card_model      varchar(100),
				   subcard_model_id int, 
				   subcard_model   varchar(100),
				   connector_model varchar(100),
				   connector_model_id int,
				   port_mode       varchar(50),
				   port_mode_id    int,
				   port_model      varchar(100),
				   port_model_id   int,
				   formfactor_id   int,
				   formfactor      varchar(50),
				   prefix          varchar(50),
				   service         int,    
				   chassis_model_id int,
				   chassis_model    varchar(100),
				   vid_port_id     int,
				   vid_port        varchar(50),
				   port_subtype    varchar(50),
				   port_subtype_id int,	
				   module_model_id int,
				   module_model    varchar(100),
				   module_id       int,
				   formfactor_func int,
				   owner_          varchar(50),
				   equip           int,
				   organization_id int,
				   organization    varchar(100),
				   state_id        int,
				   state           varchar(50),
				   slot            varchar(5),
				   slot_full       varchar(5),
				   card_subslot    varchar(5),
				   code            varchar(100)
				   , nullable int,sort int) 
AS
begin
declare @vid int,@equip varchar(50),@cc varchar(1);
-- 1 - CHASSIS, 2 - CARD,  3 - SUBCARD,   4 - MODULE
set @cc=substring(@vequip,1,1);
if @cc='1' set @equip='CHASSIS';
if @cc='2' set @equip='CARD';
if @cc='3' set @equip='SUBCARD';
if @cc='4' set @equip='MODULE';

set @vid=substring(@vequip,2,len(@vequip));
	with t_ports(id,
                 chassis_id, 
                 card_id, 
                 subcard_id,  
                 card_model_code, 
                 code_port,
			     card_model_id,
			     card_model,
			     subcard_model_id, 
			     subcard_model,
				 connector_model,
			     connector_model_id,
			     port_mode,
			     port_mode_id,
			     port_model,
			     port_model_id,
			     formfactor_id,
			     formfactor,
                 prefix,
			     service,    
			     chassis_model_id,
			     chassis_model,
			     vid_port_id,
			     vid_port,
			     port_subtype,
			 	 port_subtype_id,	
				 module_model_id,
				 module_model,
				 module_id,
				 formfactor_func,
			     owner_,
			     equip,
				 organization_id,
				 organization ,
				 state_id,
				 state,
				 slot,
				 slot_full,
				 card_subslot,
				 code
			     , nullable,sort) as
	(select i.*,
		ROW_NUMBER() over(order by --prefix,
		cast(i.slot_full as int),
                         cast(card_subslot as int),
                          cast(i.CODE_port as int)) as sort   from (
select distinct 
   s.id,
   isnull(isnull(isnull(s.chassis,c.CHASSIS),sc.CHASSIS),ll.CHASSIS) as chassis_id,
   isnull(isnull(s.card,sc.card),ll.CARD)                               card_id,
   s.subcard                                                            subcard_id, 
   ccmm1.code                                                           card_model_code,
   p.code                                                               code_port,
   p.card_model_id,
   p.card_model,
   p.subcard_model_id, 
   p.subcard_model,
   isnull(cn.code,p.connector_model)  connector_model,
   isnull(cn.id,p.connector_model_id) connector_model_id,
   null port_mode,
   null port_mode_id,
   --s.port_mode,
   --s.port_mode_id,
   p.port_model,
   p.port_model_id,
   p.formfactor_id,
   p.formfactor,
   ty.code                              prefix,
   --isnull((select 1 where exists (select 1 from service sv where sv.port=s.id)),0) as service,    
   null as service,
   p.chassis_model_id,
   p.chassis_model,
   p.vid_port_id,
   p.vid_port,
   ps.CODE                               port_subtype,
   ps.id                                 port_subtype_id,	
   mm.ID                                 module_model_id,
   mm.CODE                               module_model,
   m.id                               as module_id,
   case when isnull(p.formfactor_id,0)>0 then 1 else 11 end AS formfactor_func,
   s.owner_,
   cast(@cc as varchar(1))+cast(m.id as varchar(50)) equip,
   null                             as organization_id,
   null                             as organization ,
   sa.id state_id,
   sa.code state,
   isnull(sl.CODE ,ll.chassis_slot) slot,
   ISNULL(cast(isnull(sl.CODE ,ll.chassis_slot) as int),cast(mf.slot_number as int)) slot_full,
 --  ISNULL(cast(isnull(sl.CODE ,ll.chassis_slot) as int),cast(mf.slot_number as int)) slot_full,
   isnull(sl1.code ,isnull(ll.card_subslot,sl2.CODE)) card_subslot,
   isnull(cast(ty.code as varchar(10)),'')+
       case when isnull(cast(ty.code as varchar(10)),'')<>'' then ' ' else '' end+       
        oss.f_concat('/',ISNULL( isnull(sl.CODE ,ll.chassis_slot),
        cast(p.prefix_slot as varchar(200))),
         isnull(cast(isnull(sl1.code ,isnull(ll.card_subslot,sl2.CODE))  as varchar(10)),cast(p.prefix_subslot as varchar(200))) ,cast(p.CODE as varchar(200)),'','')
        --isnull(cast(isnull(isnull(sl1.code ,isnull(ll.card_subslot,sl2.CODE)),'') as varchar(10)),cast(mf.port_number as varchar(200))) ,cast(p.CODE as varchar(200)),'','')        
              AS code
   ,null as nullable
   from  (select * from oss.f_v_seat_port_client(@vid,@equip,@organization)) s
--from  (select * from [oss].[v_seat_port_client] 
--where 
--    ((@equip='CHASSIS' and (chassis=@vid or card in (select id from oss.card where chassis=@vid)        
--			or subcard in (select id from oss.subcard where card in (select id from oss.card where chassis=@vid))
--		or subcard in (select id from oss.subcard where chassis=@vid))) or
--    (@equip='CARD' and (card=@vid or subcard in (select id from oss.subcard where card=@vid ))) or
--    (@equip='SUBCARD' and subcard=@vid)) 
--   ) s

left join v_port_seat_position p on 
( p.id= case when isnull(s.seat_position,0)>0 then s.seat_position else s.port_position end)
--( p.id=isnull(s.port_position,s.seat_position))
-- and s.owner_=p.owner_
and s.owner_id=p.owner_id
left join card                 c on c.ID=s.card
left join subcard             sc on sc.ID=s.subcard

left join (select dd.id subcard,dd.card,vv.CHASSIS,gg.code card_subslot,bb.CODE chassis_slot from subcard dd 
             left join card vv on vv.ID=dd.CARD 
             left join card_slot gg on gg.ID=dd.card_slot 
             left join chassis_slot bb on bb.ID=vv.CHASSIS_SLOT) ll on ll.subcard=s.subcard
             

left join chassis_slot  sl on sl.ID=c.CHASSIS_SLOT
left join chassis_slot sl1 on sl1.ID=sc.CHASSIS_SLOT
left join card_slot    sl2 on sl2.ID=sc.card_slot

left join module     m        on m.id=s.module and ((ISNULL(s.seat_position,0)>0 and s.owner_='port,seat')
                              or s.owner_='combo')
left join module_model mm     on mm.ID=isnull(m.MODEL ,p.module_model_id)
left join connector_model  cn on cn.ID=mm.connector_model

left join abs_types         a on a.ID=sl.type_slot
left join abs_types ty        on ty.ID=p.prefix and isnull(p.visible_prefix,0)>0
left join abs_types ps        on s.port_subtype = ps.id
left join abs_types sa        on sa.id=s.state
left join abs_manufacturer mf on mf.ID=p.manufacturer_id 
left join card cc11           on cc11.ID=isnull(isnull(s.card,sc.card),ll.CARD)
left join card_model ccmm1    on ccmm1.ID=cc11.MODEL
where ((@equip='CHASSIS' and isnull(isnull(isnull(s.chassis,c.CHASSIS),sc.CHASSIS),ll.CHASSIS)=@vid )
                                  or (@equip='CARD' and isnull(isnull(s.card,sc.card),ll.CARD)=@vid)
                                  or (@equip='SUBCARD' and s.subcard=@vid)
                                  or (@equip='MODULE' and m.id=@vid)) -- and       isnull(s.organization_id,0) =isnull(@organization,0)
                                  )i
where ISNULL(i.code,'')<>'' )
  INSERT INTO @tbl    
      select 
      id,
   chassis_id, card_id, subcard_id,  card_model_code, code_port,
   card_model_id,
   card_model,
   subcard_model_id, 
   subcard_model,
     connector_model,
    connector_model_id,
   port_mode,
   port_mode_id,
   port_model,
   port_model_id,
   formfactor_id,
   formfactor,
     prefix,
   service,    
   chassis_model_id,
   chassis_model,
   vid_port_id,
  vid_port,
    port_subtype,
    port_subtype_id,	
       module_model_id,
        module_model,
    module_id,
    formfactor_func,
   owner_,
   equip,
    organization_id,
    organization ,
    state_id,
    state,
    slot,
    slot_full,
    card_subslot,
    code
   , nullable,sort
      from t_ports;
      return;
      end;


текст вложенной вьюшки в функцию:
+
CREATE FUNCTION [oss].[f_v_port_eq](@vequip varchar(50),@organization int)

RETURNS @tbl TABLE(id              int,
                   chassis_id      int, 
                   card_id         int, 
                   subcard_id      int,  
                   card_model_code varchar(100), 
                   code_port       varchar(40),
				   card_model_id   int,
				   card_model      varchar(100),
				   subcard_model_id int, 
				   subcard_model   varchar(100),
				   connector_model varchar(100),
				   connector_model_id int,
				   port_mode       varchar(50),
				   port_mode_id    int,
				   port_model      varchar(100),
				   port_model_id   int,
				   formfactor_id   int,
				   formfactor      varchar(50),
				   prefix          varchar(50),
				   service         int,    
				   chassis_model_id int,
				   chassis_model    varchar(100),
				   vid_port_id     int,
				   vid_port        varchar(50),
				   port_subtype    varchar(50),
				   port_subtype_id int,	
				   module_model_id int,
				   module_model    varchar(100),
				   module_id       int,
				   formfactor_func int,
				   owner_          varchar(50),
				   equip           int,
				   organization_id int,
				   organization    varchar(100),
				   state_id        int,
				   state           varchar(50),
				   slot            varchar(5),
				   slot_full       varchar(5),
				   card_subslot    varchar(5),
				   code            varchar(100)
				   , nullable int,sort int) 
AS
begin
declare @vid int,@equip varchar(50),@cc varchar(1);
-- 1 - CHASSIS, 2 - CARD,  3 - SUBCARD,   4 - MODULE
set @cc=substring(@vequip,1,1);
if @cc='1' set @equip='CHASSIS';
if @cc='2' set @equip='CARD';
if @cc='3' set @equip='SUBCARD';
if @cc='4' set @equip='MODULE';

set @vid=substring(@vequip,2,len(@vequip));
	with t_ports(id,
                 chassis_id, 
                 card_id, 
                 subcard_id,  
                 card_model_code, 
                 code_port,
			     card_model_id,
			     card_model,
			     subcard_model_id, 
			     subcard_model,
				 connector_model,
			     connector_model_id,
			     port_mode,
			     port_mode_id,
			     port_model,
			     port_model_id,
			     formfactor_id,
			     formfactor,
                 prefix,
			     service,    
			     chassis_model_id,
			     chassis_model,
			     vid_port_id,
			     vid_port,
			     port_subtype,
			 	 port_subtype_id,	
				 module_model_id,
				 module_model,
				 module_id,
				 formfactor_func,
			     owner_,
			     equip,
				 organization_id,
				 organization ,
				 state_id,
				 state,
				 slot,
				 slot_full,
				 card_subslot,
				 code
			     , nullable,sort) as
	(select i.*,
		ROW_NUMBER() over(order by --prefix,
		cast(i.slot_full as int),
                         cast(card_subslot as int),
                          cast(i.CODE_port as int)) as sort   from (
select distinct 
   s.id,
   isnull(isnull(isnull(s.chassis,c.CHASSIS),sc.CHASSIS),ll.CHASSIS) as chassis_id,
   isnull(isnull(s.card,sc.card),ll.CARD)                               card_id,
   s.subcard                                                            subcard_id, 
   ccmm1.code                                                           card_model_code,
   p.code                                                               code_port,
   p.card_model_id,
   p.card_model,
   p.subcard_model_id, 
   p.subcard_model,
   isnull(cn.code,p.connector_model)  connector_model,
   isnull(cn.id,p.connector_model_id) connector_model_id,
   null port_mode,
   null port_mode_id,
   --s.port_mode,
   --s.port_mode_id,
   p.port_model,
   p.port_model_id,
   p.formfactor_id,
   p.formfactor,
   ty.code                              prefix,
   --isnull((select 1 where exists (select 1 from service sv where sv.port=s.id)),0) as service,    
   null as service,
   p.chassis_model_id,
   p.chassis_model,
   p.vid_port_id,
   p.vid_port,
   ps.CODE                               port_subtype,
   ps.id                                 port_subtype_id,	
   mm.ID                                 module_model_id,
   mm.CODE                               module_model,
   m.id                               as module_id,
   case when isnull(p.formfactor_id,0)>0 then 1 else 11 end AS formfactor_func,
   s.owner_,
   cast(@cc as varchar(1))+cast(m.id as varchar(50)) equip,
   null                             as organization_id,
   null                             as organization ,
   sa.id state_id,
   sa.code state,
   isnull(sl.CODE ,ll.chassis_slot) slot,
   ISNULL(cast(isnull(sl.CODE ,ll.chassis_slot) as int),cast(mf.slot_number as int)) slot_full,
 --  ISNULL(cast(isnull(sl.CODE ,ll.chassis_slot) as int),cast(mf.slot_number as int)) slot_full,
   isnull(sl1.code ,isnull(ll.card_subslot,sl2.CODE)) card_subslot,
   isnull(cast(ty.code as varchar(10)),'')+
       case when isnull(cast(ty.code as varchar(10)),'')<>'' then ' ' else '' end+       
        oss.f_concat('/',ISNULL( isnull(sl.CODE ,ll.chassis_slot),
        cast(p.prefix_slot as varchar(200))),
         isnull(cast(isnull(sl1.code ,isnull(ll.card_subslot,sl2.CODE))  as varchar(10)),cast(p.prefix_subslot as varchar(200))) ,cast(p.CODE as varchar(200)),'','')
        --isnull(cast(isnull(isnull(sl1.code ,isnull(ll.card_subslot,sl2.CODE)),'') as varchar(10)),cast(mf.port_number as varchar(200))) ,cast(p.CODE as varchar(200)),'','')        
              AS code
   ,null as nullable
   from  (select * from oss.f_v_seat_port_client(@vid,@equip,@organization)) s
--from  (select * from [oss].[v_seat_port_client] 
--where 
--    ((@equip='CHASSIS' and (chassis=@vid or card in (select id from oss.card where chassis=@vid)        
--			or subcard in (select id from oss.subcard where card in (select id from oss.card where chassis=@vid))
--		or subcard in (select id from oss.subcard where chassis=@vid))) or
--    (@equip='CARD' and (card=@vid or subcard in (select id from oss.subcard where card=@vid ))) or
--    (@equip='SUBCARD' and subcard=@vid)) 
--   ) s

left join v_port_seat_position p on 
( p.id= case when isnull(s.seat_position,0)>0 then s.seat_position else s.port_position end)
--( p.id=isnull(s.port_position,s.seat_position))
-- and s.owner_=p.owner_
and s.owner_id=p.owner_id
left join card                 c on c.ID=s.card
left join subcard             sc on sc.ID=s.subcard

left join (select dd.id subcard,dd.card,vv.CHASSIS,gg.code card_subslot,bb.CODE chassis_slot from subcard dd 
             left join card vv on vv.ID=dd.CARD 
             left join card_slot gg on gg.ID=dd.card_slot 
             left join chassis_slot bb on bb.ID=vv.CHASSIS_SLOT) ll on ll.subcard=s.subcard
             

left join chassis_slot  sl on sl.ID=c.CHASSIS_SLOT
left join chassis_slot sl1 on sl1.ID=sc.CHASSIS_SLOT
left join card_slot    sl2 on sl2.ID=sc.card_slot

left join module     m        on m.id=s.module and ((ISNULL(s.seat_position,0)>0 and s.owner_='port,seat')
                              or s.owner_='combo')
left join module_model mm     on mm.ID=isnull(m.MODEL ,p.module_model_id)
left join connector_model  cn on cn.ID=mm.connector_model

left join abs_types         a on a.ID=sl.type_slot
left join abs_types ty        on ty.ID=p.prefix and isnull(p.visible_prefix,0)>0
left join abs_types ps        on s.port_subtype = ps.id
left join abs_types sa        on sa.id=s.state
left join abs_manufacturer mf on mf.ID=p.manufacturer_id 
left join card cc11           on cc11.ID=isnull(isnull(s.card,sc.card),ll.CARD)
left join card_model ccmm1    on ccmm1.ID=cc11.MODEL
where ((@equip='CHASSIS' and isnull(isnull(isnull(s.chassis,c.CHASSIS),sc.CHASSIS),ll.CHASSIS)=@vid )
                                  or (@equip='CARD' and isnull(isnull(s.card,sc.card),ll.CARD)=@vid)
                                  or (@equip='SUBCARD' and s.subcard=@vid)
                                  or (@equip='MODULE' and m.id=@vid)) -- and       isnull(s.organization_id,0) =isnull(@organization,0)
                                  )i
where ISNULL(i.code,'')<>'' )
  INSERT INTO @tbl    
      select 
      id,
   chassis_id, card_id, subcard_id,  card_model_code, code_port,
   card_model_id,
   card_model,
   subcard_model_id, 
   subcard_model,
     connector_model,
    connector_model_id,
   port_mode,
   port_mode_id,
   port_model,
   port_model_id,
   formfactor_id,
   formfactor,
     prefix,
   service,    
   chassis_model_id,
   chassis_model,
   vid_port_id,
  vid_port,
    port_subtype,
    port_subtype_id,	
       module_model_id,
        module_model,
    module_id,
    formfactor_func,
   owner_,
   equip,
    organization_id,
    organization ,
    state_id,
    state,
    slot,
    slot_full,
    card_subslot,
    code
   , nullable,sort
      from t_ports;
      return;
      end;

Модератор: Не разрывайте мониторы километровыми текстами вашего кода. Используйте тег spoiler


Сообщение было отредактировано: 19 янв 15, 18:37
19 янв 15, 18:14    [17139461]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
relav
Member

Откуда:
Сообщений: 204
сорри, текст вложенной вьюшки не правильно выложил.
Правильный вариант:
+
CREATE FUNCTION [oss].[f_v_seat_port_client](@vid int,@vequip varchar(10),@organization int)

RETURNS @tbl TABLE(id              int NOT NULL PRIMARY KEY CLUSTERED,
                   card    int,
                   subcard int,
                   chassis int,
                   port_position   int,
                   module          int,
                   seat_position   int,
                   organization_id int,
                   invisible    int,
                   port_subtype int,
                   port_mode_id int,
                   port_mode    varchar(20),
                   seat_type    int,
                   owner_       varchar(9),
                   owner_id int,
                   state        int)     
AS
begin

	with t_ports(id          ,
                   card    ,
                   subcard ,
                   chassis ,
                   port_position ,
                   module ,
                   seat_position ,
                   organization_id ,
                   invisible ,
                   port_subtype ,
                   port_mode_id,
                   port_mode,
                   seat_type,
                   owner_ ,
                   owner_id,
                   state ) as
	(  select          p.ID,
						card,
						subcard,
						chassis,
						seat_position,
					
						p.module,
						seat_position,
	                    organization,						
						null,
						port_subtype,
						null,
						null,
						ss.seat_type,
						'port,seat',
					   3,						
						null 
		from seats p
		left join seat_position ss on p.seat_position=ss.id
		where --ISNULL(invisible,0)=0 and 
		--isnull(organization,0)=isnull(@organization,0)
		--and ss.seat_type<>50000397
		--and    
		((@vequip='CHASSIS' and (chassis=@vid or card in (select id from card where chassis=@vid) 
			or subcard in (select id from subcard where card in (select id from card where chassis=@vid))
		or subcard in (select id from subcard where chassis=@vid))) or
    (@vequip='CARD' and (card=@vid or subcard in (select id from subcard where card=@vid ))) or
    (@vequip='SUBCARD' and subcard=@vid))
		UNION all
		select ID,
			   card,
			   subcard,
			   chassis,
			   combo_position,
			   module,
			   combo_position,
			   organization_id,
			   null,
			   port_subtype,
			   null,
			   null,
			   50000397,
			   'combo' ,
			   	   2,
			   null
			   from combo 
			   where ISNULL(invisible,0)=0 --and isnull(organization_id,0)=isnull(@organization,0)
		and    ((@vequip='CHASSIS' and (chassis=@vid or card in (select id from card where chassis=@vid) 
			or subcard in (select id from subcard where card in (select id from card where chassis=@vid))
		or subcard in (select id from subcard where chassis=@vid))) or
    (@vequip='CARD' and (card=@vid or subcard in (select id from subcard where card=@vid ))) or
    (@vequip='SUBCARD' and subcard=@vid))
    
    union all
    SELECT distinct p.id,
                card,
                subcard,
                chassis,
                port_position,
                module,
                isnull(p.seat_position,p.combo_position) seat_position,
                organization_id,
                invisible,
                p.port_subtype,
                isnull(pp.port_mode,cp.port_mode) port_mode_id,
              
                aa.code port_mode,
                p.port_subtype seat_type,
                case when p.port_subtype=50000397 then 'combo' else 'port,seat' end as owner_,
                 case when p.port_subtype=50000397 then 2 else 3 end as owner_id,
                p.state
  
		from port p
		left join port_position pp on pp.id=p.port_position
		left join combo_position cp on cp.id=p.combo_position
		left join abs_types aa on aa.id=isnull(pp.port_mode,cp.port_mode)
		
		where ISNULL(invisible,0)=0 --and isnull(p.organization_id,0)=isnull(@organization,0)
		and pp.seat_type<>50000397 and isnull(p.module,0)=0
		and    ((@vequip='CHASSIS' and (p.chassis=@vid or p.card in (select id from card where chassis=@vid) 
		or p.subcard in (select id from subcard where card in (select id from card where chassis=@vid))
		                             or p.subcard in (select id from subcard where chassis=@vid))) or
    (@vequip='CARD' and (p.card=@vid or p.subcard in (select id from subcard where card=@vid ))) or
    (@vequip='SUBCARD' and p.subcard=@vid)))
    
    
		  INSERT INTO @tbl    
			  select 
			 id          ,
						   card    ,
						   subcard ,
						   chassis ,
						   port_position ,
						   module ,
						   seat_position ,
						   organization_id ,
						   invisible ,
						   port_subtype ,
						   port_mode_id,
						   port_mode,
						   seat_type,
						   owner_ ,
						   owner_id,
						   state 
      from t_ports;
          
      return;
      end;


Сообщение было отредактировано: 19 янв 15, 18:38
19 янв 15, 18:17    [17139477]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Добрый Э - Эх
Guest
relav,

медаль тому, кто согласится ковыряться в твоей портянке...
19 янв 15, 19:13    [17139699]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31949
Добрый Э - Эх
relav,

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

Нужны реальные данные, планы запросов, нужно смотреть планы и искать проблемы вначале не в форме функции, а в отдельных запросах.

Кстати, "текст вложенной вьюшки" не увидел, там тоже функция.
19 янв 15, 23:16    [17140438]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
aleks2
Guest
alexeyvg
Добрый Э - Эх
relav,

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

Нужны реальные данные, планы запросов, нужно смотреть планы и искать проблемы вначале не в форме функции, а в отдельных запросах.

Кстати, "текст вложенной вьюшки" не увидел, там тоже функция.


Да хоть бы хны.

1. Нафига UNION? Лучше три инсерта.

2. В жопу такие условия: where ISNULL(invisible,0)=0.

3. (select id from card where chassis=@vid) вынести в промежуточную времянку с индексом.
20 янв 15, 07:07    [17140902]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
relav
Member

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

исправил на 3-и инсерта, но почему то вылетает ошибка: Invalid object name 't_ports2'

текст переделанной функции привожу:
+
CREATE FUNCTION [oss].[f_v_seat_port_client](@vid int,@vequip varchar(10),@organization int)

RETURNS @tbl TABLE(id              int NOT NULL PRIMARY KEY CLUSTERED,
                   card    int,
                   subcard int,
                   chassis int,
                   port_position   int,
                   module          int,
                   seat_position   int,
                   organization_id int,
                   invisible    int,
                   port_subtype int,
                   port_mode_id int,
                   port_mode    varchar(20),
                   seat_type    int,
                   owner_       varchar(9),
                   owner_id int,
                   state        int)     
AS
begin

	with t_ports1(id          ,
                   card    ,
                   subcard ,
                   chassis ,
                   port_position ,
                   module ,
                   seat_position ,
                   organization_id ,
                   invisible ,
                   port_subtype ,
                   port_mode_id,
                   port_mode,
                   seat_type,
                   owner_ ,
                   owner_id,
                   state ) as
	(  select          p.ID,
						card,
						subcard,
						chassis,
						seat_position,
					
						p.module,
						seat_position,
	                    organization,						
						null,
						port_subtype,
						null,
						null,
						ss.seat_type,
						'port,seat',
					   3,						
						null 
		from seats p
		left join seat_position ss on p.seat_position=ss.id
		where --ISNULL(invisible,0)=0 and 
		--isnull(organization,0)=isnull(@organization,0)
		--and ss.seat_type<>50000397
		--and    
		((@vequip='CHASSIS' and (chassis=@vid or card in (select id from card where chassis=@vid) 
			or subcard in (select id from subcard where card in (select id from card where chassis=@vid))
		or subcard in (select id from subcard where chassis=@vid))) or
    (@vequip='CARD' and (card=@vid or subcard in (select id from subcard where card=@vid ))) or
    (@vequip='SUBCARD' and subcard=@vid))),

 t_ports2(id          ,
                   card    ,
                   subcard ,
                   chassis ,
                   port_position ,
                   module ,
                   seat_position ,
                   organization_id ,
                   invisible ,
                   port_subtype ,
                   port_mode_id,
                   port_mode,
                   seat_type,
                   owner_ ,
                   owner_id,
                   state ) as
		( select ID,
			   card,
			   subcard,
			   chassis,
			   combo_position,
			   module,
			   combo_position,
			   organization_id,
			   null,
			   port_subtype,
			   null,
			   null,
			   50000397,
			   'combo' ,
			   	   2,
			   null
			   from combo 
			   where ISNULL(invisible,0)=0 --and isnull(organization_id,0)=isnull(@organization,0)
		and    ((@vequip='CHASSIS' and (chassis=@vid or card in (select id from card where chassis=@vid) 
			or subcard in (select id from subcard where card in (select id from card where chassis=@vid))
		or subcard in (select id from subcard where chassis=@vid))) or
    (@vequip='CARD' and (card=@vid or subcard in (select id from subcard where card=@vid ))) or
    (@vequip='SUBCARD' and subcard=@vid))),
    
    
 t_ports3(id          ,
                   card    ,
                   subcard ,
                   chassis ,
                   port_position ,
                   module ,
                   seat_position ,
                   organization_id ,
                   invisible ,
                   port_subtype ,
                   port_mode_id,
                   port_mode,
                   seat_type,
                   owner_ ,
                   owner_id,
                   state ) as    
       (SELECT distinct p.id,
                card,
                subcard,
                chassis,
                port_position,
                module,
                isnull(p.seat_position,p.combo_position) seat_position,
                organization_id,
                invisible,
                p.port_subtype,
                isnull(pp.port_mode,cp.port_mode) port_mode_id,
              
                aa.code port_mode,
                p.port_subtype seat_type,
                case when p.port_subtype=50000397 then 'combo' else 'port,seat' end as owner_,
                 case when p.port_subtype=50000397 then 2 else 3 end as owner_id,
                p.state
  
		from port p
		left join port_position pp on pp.id=p.port_position
		left join combo_position cp on cp.id=p.combo_position
		left join abs_types aa on aa.id=isnull(pp.port_mode,cp.port_mode)
		
		where ISNULL(invisible,0)=0 --and isnull(p.organization_id,0)=isnull(@organization,0)
		and pp.seat_type<>50000397 and isnull(p.module,0)=0
		and    ((@vequip='CHASSIS' and (p.chassis=@vid or p.card in (select id from card where chassis=@vid) 
		or p.subcard in (select id from subcard where card in (select id from card where chassis=@vid))
		                             or p.subcard in (select id from subcard where chassis=@vid))) or
    (@vequip='CARD' and (p.card=@vid or p.subcard in (select id from subcard where card=@vid ))) or
    (@vequip='SUBCARD' and p.subcard=@vid)))
    
    
		  INSERT INTO @tbl    
			  select 
			 id          ,
						   card    ,
						   subcard ,
						   chassis ,
						   port_position ,
						   module ,
						   seat_position ,
						   organization_id ,
						   invisible ,
						   port_subtype ,
						   port_mode_id,
						   port_mode,
						   seat_type,
						   owner_ ,
						   owner_id,
						   state 
      from t_ports1;

	  INSERT INTO @tbl    
			  select 
			 id          ,
						   card    ,
						   subcard ,
						   chassis ,
						   port_position ,
						   module ,
						   seat_position ,
						   organization_id ,
						   invisible ,
						   port_subtype ,
						   port_mode_id,
						   port_mode,
						   seat_type,
						   owner_ ,
						   owner_id,
						   state 
      from t_ports2;
          
	  INSERT INTO @tbl    
			  select 
			 id          ,
						   card    ,
						   subcard ,
						   chassis ,
						   port_position ,
						   module ,
						   seat_position ,
						   organization_id ,
						   invisible ,
						   port_subtype ,
						   port_mode_id,
						   port_mode,
						   seat_type,
						   owner_ ,
						   owner_id,
						   state 
      from t_ports3;
      return;
      end;


где ошибка?
20 янв 15, 13:38    [17142706]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
iap
Member

Откуда: Москва
Сообщений: 47142
relav,

а AS у CTE кто писать будет?
CTE - это часть одного-единственного INSERT!
Как это Вы его пытаетесь использовать в нескольких?
Извольте для каждого INSERT определять свои CTE.

Глянул одним глазом - какой ужас в спойлере!!
20 янв 15, 13:48    [17142778]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
relav
Member

Откуда:
Сообщений: 204
замена union all на insert производительность не улучшила, как было 3.5 секунд, так и осталось. Что можно еще сделать в плане производительности?
20 янв 15, 14:07    [17142909]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
komrad
Member

Откуда:
Сообщений: 5735
relav
замена union all на insert производительность не улучшила, как было 3.5 секунд, так и осталось. Что можно еще сделать в плане производительности?

для начала показать план "медленного" запроса
20 янв 15, 14:52    [17143174]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Maxx
Member [скрыт]

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

как минимум 1 раз прочитать where и запихнуть результат во времянку ,и потом джойнить основные запросы.. вы 3 раза выполняетет одни и теже запросы для where. Помоему,ето вам и писал aleks2 изначально
20 янв 15, 14:55    [17143210]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
relav
Member

Откуда:
Сообщений: 204
переделал функцию с временными таблицами, но выдается ошибка:
Must declare the scalar variable "@t_tbl".
Must declare the scalar variable "@t_tbl".
Must declare the scalar variable "@t_tbl1".
Must declare the scalar variable "@t_tbl2".
Must declare the scalar variable "@t_tbl".
Must declare the scalar variable "@t_tbl".

функция:
+
CREATE FUNCTION [oss].[f_v_seat_port_client](@vid int,@vequip varchar(10),@organization int)

RETURNS @tbl TABLE(id              int NOT NULL PRIMARY KEY CLUSTERED,
                   card    int,
                   subcard int,
                   chassis int,
                   port_position   int,
                   module          int,
                   seat_position   int,
                   organization_id int,
                   invisible    int,
                   port_subtype int,
                   port_mode_id int,
                   port_mode    varchar(20),
                   seat_type    int,
                   owner_       varchar(9),
                   owner_id int,
                   state        int)     
AS
begin
 declare @t_tbl TABLE (id int NOT NULL PRIMARY KEY);
 declare @t_tbl1 TABLE (id int NOT NULL PRIMARY KEY);
 declare @t_tbl2 TABLE (id int NOT NULL PRIMARY KEY);
  
 insert into @t_tbl select id from card where chassis=@vid;
 insert into @t_tbl1 select id from subcard where chassis=@vid;
 insert into @t_tbl2 select id from subcard where card=@vid;

 
	with t_ports1(id          ,
                   card    ,
                   subcard ,
                   chassis ,
                   port_position ,
                   module ,
                   seat_position ,
                   organization_id ,
                   invisible ,
                   port_subtype ,
                   port_mode_id,
                   port_mode,
                   seat_type,
                   owner_ ,
                   owner_id,
                   state ) as
	(  select          p.ID,
						card,
						subcard,
						chassis,
						seat_position,
					
						p.module,
						seat_position,
	                    organization,						
						null,
						port_subtype,
						null,
						null,
						ss.seat_type,
						'port,seat',
					   3,						
						null 
		from seats p
		left join seat_position ss on p.seat_position=ss.id
		where --ISNULL(invisible,0)=0 and 
		--isnull(organization,0)=isnull(@organization,0)
		--and ss.seat_type<>50000397
		--and    
		((@vequip='CHASSIS' and (chassis=@vid or card in (@t_tbl) 
			or subcard in (select id from subcard where card in (@t_tbl))
		or subcard in (@t_tbl1))) or
    (@vequip='CARD' and (card=@vid or subcard in (@t_tbl2))) or
    (@vequip='SUBCARD' and subcard=@vid)))
    
    INSERT INTO @tbl    
			  select 
			 id          ,
						   card    ,
						   subcard ,
						   chassis ,
						   port_position ,
						   module ,
						   seat_position ,
						   organization_id ,
						   invisible ,
						   port_subtype ,
						   port_mode_id,
						   port_mode,
						   seat_type,
						   owner_ ,
						   owner_id,
						   state 
      from t_ports1;

 with t_ports2(id          ,
                   card    ,
                   subcard ,
                   chassis ,
                   port_position ,
                   module ,
                   seat_position ,
                   organization_id ,
                   invisible ,
                   port_subtype ,
                   port_mode_id,
                   port_mode,
                   seat_type,
                   owner_ ,
                   owner_id,
                   state ) as
		( select ID,
			   card,
			   subcard,
			   chassis,
			   combo_position,
			   module,
			   combo_position,
			   organization_id,
			   null,
			   port_subtype,
			   null,
			   null,
			   50000397,
			   'combo' ,
			   	   2,
			   null
			   from combo 
			   where ISNULL(invisible,0)=0 --and isnull(organization_id,0)=isnull(@organization,0)
		and    ((@vequip='CHASSIS' and (chassis=@vid or card in (@t_tbl) 
			or subcard in (select id from subcard where card in (@t_tbl))
		or subcard in (@t_tbl1))) or
    (@vequip='CARD' and (card=@vid or subcard in (@t_tbl2))) or
    (@vequip='SUBCARD' and subcard=@vid)))
    
    INSERT INTO @tbl    
			  select 
			 id          ,
						   card    ,
						   subcard ,
						   chassis ,
						   port_position ,
						   module ,
						   seat_position ,
						   organization_id ,
						   invisible ,
						   port_subtype ,
						   port_mode_id,
						   port_mode,
						   seat_type,
						   owner_ ,
						   owner_id,
						   state 
      from t_ports2;
    
 with t_ports3(id          ,
                   card    ,
                   subcard ,
                   chassis ,
                   port_position ,
                   module ,
                   seat_position ,
                   organization_id ,
                   invisible ,
                   port_subtype ,
                   port_mode_id,
                   port_mode,
                   seat_type,
                   owner_ ,
                   owner_id,
                   state ) as    
       (SELECT distinct p.id,
                card,
                subcard,
                chassis,
                port_position,
                module,
                isnull(p.seat_position,p.combo_position) seat_position,
                organization_id,
                invisible,
                p.port_subtype,
                isnull(pp.port_mode,cp.port_mode) port_mode_id,
              
                aa.code port_mode,
                p.port_subtype seat_type,
                case when p.port_subtype=50000397 then 'combo' else 'port,seat' end as owner_,
                 case when p.port_subtype=50000397 then 2 else 3 end as owner_id,
                p.state
  
		from port p
		left join port_position pp on pp.id=p.port_position
		left join combo_position cp on cp.id=p.combo_position
		left join abs_types aa on aa.id=isnull(pp.port_mode,cp.port_mode)
		
		where ISNULL(invisible,0)=0 --and isnull(p.organization_id,0)=isnull(@organization,0)
		and pp.seat_type<>50000397 and isnull(p.module,0)=0
		and    ((@vequip='CHASSIS' and (p.chassis=@vid or p.card in (@t_tbl) 
		or p.subcard in (select id from subcard where card in (@t_tbl))
		                             or p.subcard in (@t_tbl1))) or
    (@vequip='CARD' and (p.card=@vid or p.subcard in (@t_tbl2))) or
    (@vequip='SUBCARD' and p.subcard=@vid)))
    
    
		  INSERT INTO @tbl    
			  select 
			 id          ,
						   card    ,
						   subcard ,
						   chassis ,
						   port_position ,
						   module ,
						   seat_position ,
						   organization_id ,
						   invisible ,
						   port_subtype ,
						   port_mode_id,
						   port_mode,
						   seat_type,
						   owner_ ,
						   owner_id,
						   state 
      from t_ports3;

	
      return;
      end;
20 янв 15, 16:21    [17143768]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
а чего вы решили что так можно использовать переменые табличного типа ?
 or card in (@t_tbl) 
20 янв 15, 16:28    [17143829]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
relav
Member

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

а как ее написать?
20 янв 15, 16:29    [17143841]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
or card in (select id from @t_tbl)
да и для етого у вас есть готовый результат уже
or subcard in (select id from subcard where card in (@t_tbl))
20 янв 15, 16:33    [17143869]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
relav
Member

Откуда:
Сообщений: 204
ничего не поменялось 3.5 секунды.
20 янв 15, 16:54    [17144015]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Maxx
Member [скрыт]

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

ну уберайте сте ..они тут вообще не нужны по факту при таком подходе
20 янв 15, 16:58    [17144037]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
relav
Member

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

убрал cte, заменил на 3 -и инсерта, но время не поменялось все так же 3.5 секунды.

+
CREATE FUNCTION [oss].[f_v_seat_port_client](@vid int,@vequip varchar(10),@organization int)

RETURNS @tbl TABLE(id              int NOT NULL PRIMARY KEY CLUSTERED,
                   card    int,
                   subcard int,
                   chassis int,
                   port_position   int,
                   module          int,
                   seat_position   int,
                   organization_id int,
                   invisible    int,
                   port_subtype int,
                   port_mode_id int,
                   port_mode    varchar(20),
                   seat_type    int,
                   owner_       varchar(9),
                   owner_id int,
                   state        int)     
AS
begin
 declare @t_tbl  TABLE (id int NOT NULL PRIMARY KEY);
 declare @t_tbl1 TABLE (id int NOT NULL PRIMARY KEY);
 declare @t_tbl2 TABLE (id int NOT NULL PRIMARY KEY);
  
 insert into @t_tbl  select id from card where chassis=@vid;
 insert into @t_tbl1 select id from subcard where chassis=@vid;
 insert into @t_tbl2 select id from subcard where card=@vid;

 
   INSERT INTO @tbl    
			select          p.ID,
						card,
						subcard,
						chassis,
						seat_position,
					
						p.module,
						seat_position,
	                    organization,						
						null,
						port_subtype,
						null,
						null,
						ss.seat_type,
						'port,seat',
					   3,						
						null 
		from seats p
		left join seat_position ss on p.seat_position=ss.id
		where --ISNULL(invisible,0)=0 and 
		--isnull(organization,0)=isnull(@organization,0)
		--and ss.seat_type<>50000397
		--and    
		((@vequip='CHASSIS' and (chassis=@vid or card in (select id from @t_tbl) 
			or subcard in (select id from subcard where card in (select id from @t_tbl))
		or subcard in (select id from @t_tbl1))) or
    (@vequip='CARD' and (card=@vid or subcard in (select id from @t_tbl2))) or
    (@vequip='SUBCARD' and subcard=@vid))

 
    INSERT INTO @tbl    
			  select ID,
			   card,
			   subcard,
			   chassis,
			   combo_position,
			   module,
			   combo_position,
			   organization_id,
			   null,
			   port_subtype,
			   null,
			   null,
			   50000397,
			   'combo' ,
			   	   2,
			   null
			   from combo 
			   where ISNULL(invisible,0)=0 --and isnull(organization_id,0)=isnull(@organization,0)
		and    ((@vequip='CHASSIS' and (chassis=@vid or card in (select id from @t_tbl) 
			or subcard in (select id from subcard where card in (select id from @t_tbl))
		or subcard in (select id from @t_tbl1))) or
    (@vequip='CARD' and (card=@vid or subcard in (select id from @t_tbl2))) or
    (@vequip='SUBCARD' and subcard=@vid))
    
   
		  INSERT INTO @tbl    
			 SELECT distinct p.id,
                card,
                subcard,
                chassis,
                port_position,
                module,
                isnull(p.seat_position,p.combo_position) seat_position,
                organization_id,
                invisible,
                p.port_subtype,
                isnull(pp.port_mode,cp.port_mode) port_mode_id,
              
                aa.code port_mode,
                p.port_subtype seat_type,
                case when p.port_subtype=50000397 then 'combo' else 'port,seat' end as owner_,
                 case when p.port_subtype=50000397 then 2 else 3 end as owner_id,
                p.state
  
		from port p
		left join port_position pp on pp.id=p.port_position
		left join combo_position cp on cp.id=p.combo_position
		left join abs_types aa on aa.id=isnull(pp.port_mode,cp.port_mode)
		
		where ISNULL(invisible,0)=0 --and isnull(p.organization_id,0)=isnull(@organization,0)
		and pp.seat_type<>50000397 and isnull(p.module,0)=0
		and    ((@vequip='CHASSIS' and (p.chassis=@vid or p.card in (select id from @t_tbl) 
		or p.subcard in (select id from subcard where card in (select id from @t_tbl))
		                             or p.subcard in (select id from @t_tbl1))) or
    (@vequip='CARD' and (p.card=@vid or p.subcard in (select id from @t_tbl2))) or
    (@vequip='SUBCARD' and p.subcard=@vid))
	
      return;
      end;
20 янв 15, 17:29    [17144182]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Maxx
Member [скрыт]

Откуда:
Сообщений: 24290
план хотябы первого запроса покажите
20 янв 15, 17:40    [17144254]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
Mind
Member

Откуда: Лучший город на Земле
Сообщений: 2322
relav
Maxx,

убрал cte, заменил на 3 -и инсерта, но время не поменялось все так же 3.5 секунды.
Вы форумом ошиблись. Вам к экстрасенсам надо.
20 янв 15, 20:56    [17145089]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
relav
Member

Откуда:
Сообщений: 204
план первого запроса.

К сообщению приложен файл (plan.html - 48Kb) cкачать
21 янв 15, 11:06    [17147167]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
хмхмхм
Guest
relav,

у вас оценочный план, покажите актуальный
21 янв 15, 11:14    [17147235]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
relav
Member

Откуда:
Сообщений: 204
вроде актуальный план

К сообщению приложен файл (plan1.html - 38Kb) cкачать
21 янв 15, 12:33    [17147931]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
хмхмхм
Guest
relav,

Какие индексы на таблице seats? Есть ли индексы по card, subcard, seat_position? Какие индексы на seat_position?

У вас в условиях запроса много or, не пробовали разделить запросы на три ветки if по значению @vequip? Что даст такое разделение?
21 янв 15, 12:48    [17148019]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
relav
Member

Откуда:
Сообщений: 204
хмхмхм,

индекс есть на временную таблицу tbl на поле id и на таблицы t_tbln на поле id.
21 янв 15, 14:29    [17148811]     Ответить | Цитировать Сообщить модератору
 Re: оптимизация запроса  [new]
хмхмхм
Guest
relav,

т.е. у вас на таблицах seats и seat_position никаких индексов нет?
Что на счет разбиения запроса по значению @vequip?
21 янв 15, 15:48    [17149506]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить