SQL Server Performance

Stored procedure tuning help #2

Discussion in 'Performance Tuning for DBAs' started by korzon, Jan 21, 2004.

  1. korzon New Member

    Hey, <br />I have yet another strange query that takes 20 seconds to run. I've beaten my head against the wall for a few days with this one too, and consulted two other DBAs. I'm sure the answer is quite simple, which is what is so anoying <img src='/community/emoticons/emotion-6.gif' alt=':(' /><br />Anyway, here is the info, would appreciate any suggestions:<br /><br />SP called with:<br />Exec Spd_LoadSadranTest '01/19/2004','01/19/2004',1,Null,Null,0,0,'00:00','23:59','00:00','23:59',Null<br /><br />SP code:<br /><br />************ START SP CODE ***************<br /><br />CREATE PROCEDURE spd_LoadSadranTest<br />@FromDate DateTime,<br />@ToDate DateTime,<br />@AreaId Int,<br />@VisaNumber Int,<br />@VisaType Int,<br />@IsGrouped Int,<br />@IsSplitted bit, <br />@FromStartTime char(5),<br />@ToStartTime char(5),<br />@FromEndTime char(5), <br />@ToEndTime char(5),<br />@ClientId Int<br /><br />AS<br />set nocount on<br /><br />--Locate all UnUssign Orders <br />select * into #V_UnassignedOrders from V_UnassignedOrders where VEndDate&gt;=@FromDate and VSTartDate &lt;= @ToDate and @AreaId = VCurrentArea2 and vOperatorTypeId=1<br />Update #V_UnassignedOrders set VGroupno = case VReOccur when 0 then VgroupNo else case isnull(vGroupNo,0) when 0 then 0 else vGroupNo * dbo.FxIsGroupable(VVisa, VstartDate) end end<br /><br /><br />delete from #v_unassignedorders where vreoccur = 1 and vstartdate&lt; @FromDate<br /><br /><br />create table #Results (OrderId int,VGroupNo int)<br /><br />if @IsGrouped=0 and @IsSplitted=0 and @FromStartTime='00:00' and @ToEndTime='23:59' and @ClientId is null and @VisaType is null and @VisaNumber is null<br /><br /><br />begin<br />insert into #Results<br />Select VOrderId,VGroupNo<br />from #V_UnAssignedOrders<br />end<br />else<br />begin<br />--Locate only assign for search<br />insert into #Results<br />Select OrderId,VGroupNo<br />from #V_UnAssignedOrders<br />Inner Join Orders on VorderId=Orders.OrderId<br />Where<br />(@VisaNumber is null or @VisaNumber=VVisa) and <br />(@VisaType is null or @VisaType=Visatype) and <br />(<br />(@IsGrouped=0) or<br />(@IsGrouped=1 and VGroupno&lt;&gt;0) or<br />(@IsGrouped=2 and VGroupno&lt;&gt;0 and Vreoccur=0) or <br />(@IsGrouped=3 and VGroupno&lt;&gt;0 and Vreoccur=1) or<br />(@IsGrouped=4 and VGroupNo=0) <br />) and <br />(<br />(@IsSplitted=0) or (@IsSplitted=1 and dbo.FxIsSplitted(Orders.OrderId)=1)<br />)and <br />(@FromStartTime &lt;=VstartTime or @FromStartTime is null) and <br />(@ToStartTime &gt;=VstartTime or @ToStartTime is null) and <br />(@FromEndTime &lt;=VEndTime or @FromEndTime is null) and <br />(@ToEndTime &gt;= VEndTime or @ToEndTime is null) and <br />(@Clientid is null or @ClientId = clientId) <br /><br />end<br /><br />Select #V_UnassignedOrders .* ,Orders.*,Styles.*,Source.CityName FromCityName,target.CityName ToCityName<br />from <br />#V_UnAssignedOrders Inner Join Orders on #V_UnAssignedOrders.VorderId=Orders.OrderId<br />Left Join City as Source ON fromCityId=Source.CityId <br />Left Join City as Target ON toCityId = Target.CityId <br />left join styles on Orders.VisaType = Styles.Styleid<br />where <br />#V_UnAssignedOrders.VOrderId in (select OrderId from #Results ) or<br />#V_UnAssignedOrders.VGroupNo in (select VGroupNo from #Results where VGroupNo&lt;&gt;0) <br />and Orders.OperatorTypeId =1<br />order by #V_UnassignedOrders.vGroupNo,VisaType,case orders.reoccur when 1 then #V_UnAssignedOrders.VStartDate else orders.Fromdate end,vstartTime<br /><br />return<br />GO<br /><br />**********END SP CODE**************<br /><br />View V_UnAssignedOrders:<br /><br />*********START VIEW CODE **********<br />CREATE VIEW dbo.V_UnAssignedOrders<br />AS<br />Select <br />V_SpanOrders .*,AssignId,<br />VCurrentArea2 = case Assigns.Cancelcode when 2 then Assigns.CurrentAreaId else V_spanOrders.VCurrentArea end,<br />PstartDate = case Assigns.Cancelcode when 2 then Assigns.FromDate else V_spanOrders.VStartDate end,<br />PstartTime = case Assigns.Cancelcode when 2 then Assigns.FromTime else V_spanOrders.VStartTime end,<br />PEndDate = case Assigns.Cancelcode when 2 then Assigns.ToDate else V_spanOrders.VEndDate end,<br />PEndTime = case Assigns.Cancelcode when 2 then Assigns.ToTime else V_spanOrders.VEndTime end,<br />PGroupNo = case Assigns.Cancelcode when 2 then Assigns.GroupNo else V_spanOrders.VGroupNo end<br /><br />from <br />V_SpanOrders<br />Left Join Assigns ON VorderId=Assigns.OrderId and vstartDate=isnull(Assigns.OriginalFromDate,assigns.fromDate)<br />Where (Assigns.OrderId is null or Assigns.CancelCode=2) and VisCancled=0 and VIsConfirmed=1<br />*********** END VIEW CODE ***********<br /><br />Again, the tables involved are all rather small (Less than 100K rows) and are properly indexed. Tuning wizard makes no suggestions and many people have went over them many times.<br />
  2. ChrisFretwell New Member

    Can you try a little mini-debugging? Throw in some select getdate() statements between each process to see if its just one of the steps creating making it slower.

    Without spending more time looking at it, have you tried to combine this condition
    delete from #v_unassignedorders where vreoccur = 1 and vstartdate< @FromDate

    into the creation of the temp table so that they dont get put into it at all? I dont know if it will help or not, but its worth trying.

    Also keep in mind that your view and each of your temp tables is unindexed, so anything you do against them will be impacted, even in smaller tables.

    You first temp table is created via a select into. This requres locks on some system resources. The process can be slowed down if it has to wait to get these temporary resources.

    Chris
  3. joechang New Member

    if you can run this on an isolated development system,<br />run profiler with StoredProcedures SP<img src='/community/emoticons/emotion-7.gif' alt=':S' />tmtCompleted as an alternative to putting in GETDATE()<br /><br />also monitor S::Recompile, as you will be getting recompiles periodically on insterts of more than 6 rows and 500 rows<br /><br />also run this with SET STATISTICS PROFILE ON<br />so we can see how many rows were involved in each step, and what that operation was

Share This Page