Solved

stand-alone_query_faster_then_sp

Posted on 2007-03-22
3
152 Views
Last Modified: 2010-03-19
I have a query that runs on SQL Server 2000 SP4.  When I run it as a stand-alone query in SQL Query Analyzer, it executes in 0.3 sec, but when I make the query a stored procedure, the stored procedure takes 19 seconds to execute.  
0
Comment
Question by:technoscheidt
  • 2
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 500 total points
ID: 18773972
do the following
- compare the execution plans for both
- use sql server profiler to see whether any recompilation goesduring the execution of each procedure call
- make sure that there are no locks on the table
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 18773977
also if possible, please post the Query
0
 

Author Comment

by:technoscheidt
ID: 18774521

--CREATE PROCEDURE  dbo.FiveQuartersReportProdTime_2_5_3
DECLARE @myitem char(5),@myitemsecond char(5),@timeinput char(12), @monthly int, @history int, @reporting int, @months int, @weeks int
 --AS
set @myitem='764420'

set @myitemsecond='766009'
set @reporting=0
set @timeinput='03/21/2007'

 DECLARE  
 @timeframe1 datetime
,@timeframe2 datetime
,@timeframe3 datetime
,@timeframe4 datetime
,@timeframe5 datetime
,@timeframe6 datetime
,@timeframe7 datetime
,@timeframe8 datetime
,@timeframe9 datetime
,@timeframe10 datetime
,@timeframe11 datetime
,@timeframe12 datetime
,@timeframe13 datetime
,@timeframe14 datetime
,@timeframe15 datetime
,@timeframe16 datetime
,@timeframe17 datetime
,@timeframe18 datetime
,@timeframe19 datetime
,@timeframe20 datetime
,@timeframe21 datetime
,@timeframe22 datetime

DECLARE @firstnum int,@totalrecords int,@secnum int, @ee decimal(9,4)
DECLARE @itemnum int
DECLARE @timeparam char(40),@itemnu2 int
DECLARE @whs_num int,@SoldUnits int

if @monthly is null
begin

     select @timeframe1 = @timeinput
      select @timeframe2 = convert(char (10),DATEADD(day,-90,@timeframe1), 101)
      select @timeframe3 =convert(char (10),DATEADD(day,-1,@timeframe2),101)
      select @timeframe4 =convert(char (10),DATEADD(day,-90,@timeframe3),101)
      select @timeframe5 = convert(char (10),DATEADD(day,-1,@timeframe4), 101)
      select @timeframe6 = convert(char (10),DATEADD(day,-90,@timeframe5), 101)
      select @timeframe7 =convert(char (10),DATEADD(day,-1,@timeframe6),101)
      select @timeframe8 =convert(char (10),DATEADD(day,-90,@timeframe7),101)
      select @timeframe9 =convert(char (10),DATEADD(day,-1,@timeframe8),101)
      select @timeframe10 =convert(char (10),DATEADD(day,-90,@timeframe9),101)

      select @timeframe11 = '01/01/2100'
      select @timeframe12 = '01/01/2100'
      select @timeframe13 = '01/01/2100'
      select @timeframe14 = '01/01/2100'
      select @timeframe15 = '01/01/2100'
      select @timeframe16 = '01/01/2100'
      select @timeframe17 = '01/01/2100'
      select @timeframe18 = '01/01/2100'
      select @timeframe19 = '01/01/2100'
      select @timeframe20 = '01/01/2100'
      select @timeframe21 = '01/01/2100'

      select @timeframe22 =@timeframe10
end
else
begin
      select @timeframe1 = @timeinput
        select @timeframe2 = convert(char (10),DATEADD(day,-29,@timeframe1),101)
      select @timeframe3 =convert(char (10),DATEADD(day,-1,@timeframe2),101)
      select @timeframe4 =convert(char (10),DATEADD(day,-29,@timeframe3),101)
      select @timeframe5 = convert(char (10),DATEADD(day,-1,@timeframe4), 101)
      select @timeframe6 = convert(char (10),DATEADD(day,-30,@timeframe5), 101)
      select @timeframe7 =convert(char (10),DATEADD(day,-1,@timeframe6),101)
      select @timeframe8 =convert(char (10),DATEADD(day,-29,@timeframe7),101)
      select @timeframe9 =convert(char (10),DATEADD(day,-1,@timeframe8),101)
      select @timeframe10 =convert(char (10),DATEADD(day,-29,@timeframe9),101)
      select @timeframe11 = convert(char (10),DATEADD(day,-1,@timeframe10), 101)
      select @timeframe12 = convert(char (10),DATEADD(day,-30,@timeframe11), 101)
      select @timeframe13 =convert(char (10),DATEADD(day,-1,@timeframe12),101)
      select @timeframe14 =convert(char (10),DATEADD(day,-29,@timeframe13),101)
      select @timeframe15 = convert(char (10),DATEADD(day,-1,@timeframe14), 101)
      select @timeframe16 = convert(char (10),DATEADD(day,-29,@timeframe15), 101)
      select @timeframe17 =convert(char (10),DATEADD(day,-1,@timeframe16),101)
      select @timeframe18 =convert(char (10),DATEADD(day,-30,@timeframe17),101)
      select @timeframe19 =convert(char (10),DATEADD(day,-1,@timeframe18),101)
      select @timeframe20 =convert(char (10),DATEADD(day,-90,@timeframe19),101)
      select @timeframe21 =convert(char (10),DATEADD(day,-1,@timeframe20),101)
      select @timeframe22 =convert(char (10),DATEADD(day,-90,@timeframe21),101)
end



if @history is null
begin

      select         
       inventory.item_num
      , coalesce(sold.warehouse_num_r,available.warehouse_num_r,instructed.dest_whse_num_r,0) whs
      , coalesce(sold.aquarte,0) timeperiod
      , coalesce(sold.sold ,0) sold
      , inventory.[description]
      , inventory.standard_pack_qty
      , inventory.pack_description
      , coalesce(cast(TotalAfloat as int),0)as instructed
      , coalesce(cast(TotalAlloc as int),0) as allocated
      , coalesce(cast(TotalCases as int),0) as onhand
      , coalesce(cast(TotalUninstr as int),0)as uninstructed
      from
      
      
      (
            select
             sales.item_num_r
            ,sales.warehouse_num_r
            ,sum(sales.qty)/standard_pack as sold
            ,sales.aquarte
            from
            (
                  select       
                   s.item_num_r,
                    (select case
                        when s.invoice_num > 90000000
                               then s.unit_quantity * (-1)
                        else
                               s.unit_quantity  
                    end)   as qty
                  ,inv.standard_pack_qty as standard_pack
                  ,WSpace.dbo.fn_GetReportingWhse(@reporting,s.warehouse_num_r) as warehouse_num_r      
                  ,case
                              WHEN s.invoice_date between @timeframe2 and @timeframe1 THEN 1
                                    WHEN s.invoice_date between @timeframe4 and @timeframe3 THEN 2
                                    WHEN s.invoice_date between @timeframe6 and @timeframe5 THEN 3
                                    WHEN s.invoice_date between @timeframe8 and @timeframe7 THEN 4
                                    WHEN s.invoice_date between @timeframe10 and @timeframe9 THEN 5
                                      WHEN s.invoice_date between @timeframe12 and @timeframe11 THEN 6
                                    WHEN s.invoice_date between @timeframe14 and @timeframe13 THEN 7
                                    WHEN s.invoice_date between @timeframe16 and @timeframe15 THEN 8
                                    WHEN s.invoice_date between @timeframe18 and @timeframe17 THEN 9
                                    WHEN s.invoice_date between @timeframe20 and @timeframe19 THEN 10
                                      WHEN s.invoice_date between @timeframe22 and @timeframe21 THEN 11
                   END as aquarte
      
                  from
                   Wspace.dbo.inventory_master inv
                   inner join
                   ProdDbo.dbo.sales as s
                   on
                                  
                   inv.item_num between @myitem and @myitemsecond
                  and  inv.standard_pack_qty <>0
                  and  s.item_num_r/10 =inv.item_num
                  and (s.invoice_date between @timeframe22 and @timeframe1 )
                  and  s.order_type<>'X'
                  and  s.order_type<>'W'
                    and  s.ship_account_num_r not in (701482,505735,600015,021030,062182,513960,599985,033142)
                  and ((s.unit_price <> 0) or (s.unit_price = 0 and s.ship_account_num_r not in (799049,602102,502559,015206)))
                    and  s.warehouse_num_r not in (select whgd.whse_num_r
                                                        from Wspace.dbo.warehouse_group_detail whgd,Wspace.dbo.warehouse_group wg
                                                        where wg.description='EXCLUDEFROMFIVEQUARTER'
                                                        and wg.id_code=whgd.whse_group_num_r)
                  inner join
                  ProdDbo.dbo.warehouse w
                  on w.warehouse_num = s.warehouse_num_r


            ) sales
            group by  sales.item_num_r, sales.aquarte, sales.warehouse_num_r ,sales.standard_pack      
            
      ) sold

      
      full outer join
      
      (
            select
             ld.item_num_r
            ,WSpace.dbo.fn_GetReportingWhse(@reporting,ld.warehouse_num_r) as warehouse_num_r
            ,sum((ld.units_onhand_qty-ld.units_allocated_qty))/inv.standard_pack_qty as TotalCases
            ,sum(COALESCE(ld.units_allocated_qty,0)/inv.standard_pack_qty)as TotalAlloc
            from
            Wspace.dbo.inventory_master inv
            inner join
            ProdDbo.dbo.lot_data ld
            on
                inv.item_num between @myitem and @myitemsecond
            and ld.item_num_r/10=inv.item_num
            and inv.standard_pack_qty<>0
           --------------------------------------------------
            and ld.warehouse_num_r not in (select whgd.whse_num_r
                                                        from Wspace.dbo.warehouse_group_detail whgd,Wspace.dbo.warehouse_group wg
                                                        where wg.description='EXCLUDEFROMFIVEQUARTER'
                                                        and wg.id_code=whgd.whse_group_num_r)

            and ld.lot_num >0
            inner join
            ProdDbo.dbo.warehouse w
            on w.warehouse_num = ld.warehouse_num_r
              group by WSpace.dbo.fn_GetReportingWhse(@reporting,ld.warehouse_num_r),ld.item_num_r,inv.standard_pack_qty
      ) available
      
      on
      sold.item_num_r = available.item_num_r
      and sold.warehouse_num_r = available.warehouse_num_r
      
      full outer join
      (
        select
           sum((((pd.order_paid_qty + pd.order_free_qty)-pd.received_qty)*(pd.pack1*pd.pack2))) /inv.standard_pack_qty as TotalAfloat
         ,WSpace.dbo.fn_GetReportingWhse(@reporting,pd.dest_whse_num_r) as dest_whse_num_r
         ,pd.item_num_r
         from
         Wspace.dbo.inventory_master inv
         inner join
         ProdDbo.dbo.purchases_detail pd
         on
         inv.item_num between @myitem and @myitemsecond
                   and pd.item_num_r/10=inv.item_num
                 and pd.dest_whse_num_r not in(select whgd.whse_num_r
                                         from Wspace.dbo.warehouse_group_detail whgd,Wspace.dbo.warehouse_group wg
                                         where wg.description='EXCLUDEFROMFIVEQUARTER'
                                         and wg.id_code=whgd.whse_group_num_r)
         and pd.dest_whse_num_r <> 0
         and pd.lot_num > 0
         and (pd.order_paid_qty + pd.order_free_qty)>0
         and (pd.received_qty=0 or(pd.received_qty<(pd.order_paid_qty + pd.order_free_qty)))
         inner join
         ProdDbo.dbo.warehouse w
         on w.warehouse_num = pd.dest_whse_num_r
         group by dest_whse_num_r,pd.item_num_r, inv.standard_pack_qty
      ) instructed
      on
          sold.item_num_r = instructed.item_num_r
          and instructed.dest_whse_num_r = sold.warehouse_num_r
      
      full outer join
      (

      select  pd.item_num_r
        ,sum(((pd.order_paid_qty + pd.order_free_qty)-pd.allocated_qty)*(pd.pack1*pd.pack2)) /inv.standard_pack_qty as TotalUninstr
                  from
        Wspace.dbo.inventory_master inv
         inner join
         ProdDbo.dbo.purchases_detail pd
         on
             inv.item_num between @myitem and @myitemsecond
         and pd.item_num_r/10=inv.item_num
         and (pd.contract_si_num=0 or(pd.contract_si_num >0 and pd.dest_whse_num_r=0))
         group by pd.item_num_r, inv.standard_pack_qty
      ) uninstructed
      on
          sold.item_num_r = uninstructed.item_num_r
      
      inner JOIN
      Wspace.dbo.inventory_master inventory
      on
      
      inventory.item_num = coalesce(sold.item_num_r/10, available.item_num_r/10, instructed.item_num_r/10, uninstructed.item_num_r/10 )
            and  (sold.sold <> 0 or coalesce(cast(TotalAfloat as int),0)  <> 0 or coalesce(cast(TotalAlloc as int),0)   <> 0 or  coalesce(cast(TotalCases as int),0) <>0 or  coalesce(cast(TotalUninstr as int),0)<>0 )
      
      order by 1,2,3
end
else
begin
      select         
       inventory.item_num
      , coalesce(sold.warehouse_num_r,available.warehouse_num_r,instructed.dest_whse_num_r,0) whs
      , coalesce(sold.aquarte,0) timeperiod
      , coalesce(sold.sold ,0) sold
      , inventory.[description]
      , inventory.standard_pack_qty
      , inventory.pack_description
      , coalesce(cast(TotalAfloat as int),0) as instructed
      , coalesce(cast(TotalAlloc as int),0) as allocated
      , coalesce(cast(TotalCases as int),0) as onhand
      , coalesce(cast(TotalUninstr as int),0) as uninstructed
      from
      
      
      (
            select
             sales.item_num_r

            ,sales.warehouse_num_r
            ,sum(sales.qty)/standard_pack as sold
            ,sales.aquarte
            from
            (
                  select       
                   s.item_num_r
                  ,
                    (select case
                        when s.invoice_num > 90000000
                               then s.unit_quantity * (-1)
                        else
                               s.unit_quantity  
                    end)   as qty
                  ,inv.standard_pack_qty as standard_pack
                  ,WSpace.dbo.fn_GetReportingWhse(@reporting,s.warehouse_num_r) as warehouse_num_r
      
                  ,case
                              WHEN s.invoice_date between @timeframe2 and @timeframe1 THEN 1
                                    WHEN s.invoice_date between @timeframe4 and @timeframe3 THEN 2
                                    WHEN s.invoice_date between @timeframe6 and @timeframe5 THEN 3
                                    WHEN s.invoice_date between @timeframe8 and @timeframe7 THEN 4
                                    WHEN s.invoice_date between @timeframe10 and @timeframe9 THEN 5
                                      WHEN s.invoice_date between @timeframe12 and @timeframe11 THEN 6
                                    WHEN s.invoice_date between @timeframe14 and @timeframe13 THEN 7
                                    WHEN s.invoice_date between @timeframe16 and @timeframe15 THEN 8
                                    WHEN s.invoice_date between @timeframe18 and @timeframe17 THEN 9
                                    WHEN s.invoice_date between @timeframe20 and @timeframe19 THEN 10
                                      WHEN s.invoice_date between @timeframe22 and @timeframe21 THEN 11
                   END as aquarte
      
                  from
                   Wspace.dbo.inventory_master inv
                   inner join
                   ProdDbo.dbo.sales_history as s
                   on
                       inv.item_num between @myitem and @myitemsecond
                  and  inv.standard_pack_qty <>0
                  and  s.item_num_r/10 =inv.item_num
                  and (s.invoice_date between @timeframe22 and @timeframe1 )
                  and  s.order_type<>'X'
                  and  s.order_type<>'W'
                  and  s.ship_account_num_r not in (701482,505735,600015,021030,062182,513960,599985,033142)
                  and ((s.unit_price <> 0) or (s.unit_price = 0 and s.ship_account_num_r not in (799049,602102,502559,015206)))
                  and  s.warehouse_num_r not in (select whgd.whse_num_r
                                                        from Wspace.dbo.warehouse_group_detail whgd,Wspace.dbo.warehouse_group wg
                                                        where wg.description='EXCLUDEFROMFIVEQUARTER'
                                                        and wg.id_code=whgd.whse_group_num_r)
                  inner join
                  ProdDbo.dbo.warehouse w
                  on w.warehouse_num = s.warehouse_num_r
            ) sales
            group by  sales.item_num_r, sales.aquarte, sales.warehouse_num_r ,sales.standard_pack      
            
      ) sold

      
      full outer join
      
      (
            select
             ld.item_num_r
            ,WSpace.dbo.fn_GetReportingWhse(@reporting,ld.warehouse_num_r) as warehouse_num_r
            ,sum((ld.units_onhand_qty-ld.units_allocated_qty))/inv.standard_pack_qty as TotalCases
            ,sum(COALESCE(ld.units_allocated_qty,0)/inv.standard_pack_qty)as TotalAlloc
            from
            Wspace.dbo.inventory_master inv
            inner join
            ProdDbo.dbo.lot_data ld
            on
                inv.item_num between @myitem and @myitemsecond
      
            and ld.item_num_r/10=inv.item_num
            and inv.standard_pack_qty<>0
                and ld.warehouse_num_r not in (select whgd.whse_num_r
                                                        from Wspace.dbo.warehouse_group_detail whgd,Wspace.dbo.warehouse_group wg
                                                        where wg.description='EXCLUDEFROMFIVEQUARTER'
                                                        and wg.id_code=whgd.whse_group_num_r)
            and ld.lot_num >0
            inner join
            ProdDbo.dbo.warehouse w
            on w.warehouse_num = ld.warehouse_num_r
            group by warehouse_num_r,ld.item_num_r,inv.standard_pack_qty
      ) available      
      on
          sold.item_num_r = available.item_num_r
      and sold.warehouse_num_r = available.warehouse_num_r
      
      full outer join
      (
        select   sum((((pd.order_paid_qty + pd.order_free_qty)-pd.received_qty)*(pd.pack1*pd.pack2))) /inv.standard_pack_qty as TotalAfloat
           ,WSpace.dbo.fn_GetReportingWhse(@reporting,pd.dest_whse_num_r) as dest_whse_num_r
         ,pd.item_num_r
         from
         Wspace.dbo.inventory_master inv
         inner join
         ProdDbo.dbo.purchases_detail pd
         on
             inv.item_num between @myitem and @myitemsecond
      
         and pd.item_num_r/10=inv.item_num
           and pd.dest_whse_num_r not in(select whgd.whse_num_r
                                                        from Wspace.dbo.warehouse_group_detail whgd,Wspace.dbo.warehouse_group wg
                                                        where wg.description='EXCLUDEFROMFIVEQUARTER'
                                                        and wg.id_code=whgd.whse_group_num_r)
         and pd.dest_whse_num_r <> 0
         and pd.lot_num > 0
         and (pd.order_paid_qty + pd.order_free_qty)>0
         and (pd.received_qty=0 or(pd.received_qty<(pd.order_paid_qty + pd.order_free_qty)))
         inner join
         ProdDbo.dbo.warehouse w
         on w.warehouse_num = pd.dest_whse_num_r
         group by dest_whse_num_r,pd.item_num_r, inv.standard_pack_qty
      ) instructed
      on
          sold.item_num_r = instructed.item_num_r
      and instructed.dest_whse_num_r = sold.warehouse_num_r
      
      full outer join
      (
      select  pd.item_num_r
        ,sum(((pd.order_paid_qty + pd.order_free_qty)-pd.allocated_qty)*(pd.pack1*pd.pack2)) /inv.standard_pack_qty as TotalUninstr
                  from
         Wspace.dbo.inventory_master inv
         inner join
         ProdDbo.dbo.purchases_detail pd
         on
             inv.item_num between @myitem and @myitemsecond
         and pd.item_num_r/10=inv.item_num
         and (pd.contract_si_num=0 or (pd.contract_si_num >0 and pd.dest_whse_num_r=0))
         group by pd.item_num_r, inv.standard_pack_qty
      ) uninstructed
      on
          sold.item_num_r = uninstructed.item_num_r
      
      inner JOIN
      Wspace.dbo.inventory_master inventory
      on

       inventory.item_num = coalesce(sold.item_num_r/10, available.item_num_r/10, instructed.item_num_r/10, uninstructed.item_num_r/10 )
              and  (sold.sold <> 0 or coalesce(cast(TotalAfloat as int),0)  <> 0 or coalesce(cast(TotalAlloc as int),0)   <> 0 or  coalesce(cast(TotalCases as int),0) <>0 or  coalesce(cast(TotalUninstr as int),0)<>0 )
      
      order by 1,2,3
end
GO
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction SQL Server Integration Services can read XML files, that’s known by every BI developer.  (If you didn’t, don’t worry, I’m aiming this article at newcomers as well.) But how far can you go?  When does the XML Source component become …
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now