Solved

stand-alone_query_faster_then_sp

Posted on 2007-03-22
3
163 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

624 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