Sp takes longer than the actual select statement

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Sp takes longer than the actual select statement

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-05-05 : 14:51:46

reema rughani writes "Hi
I have a fairly large SP that takes about 5 min to return 289 rows but if i run the select and other bits outside of the sp (by declaring the parameters and settign them to the same values used to declare the sp) it takes 20 seconds. There is no difference between the two expect that one is the stored procedure.

Sp is below and below that is the sql i ran:

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[spsun_rp_COM001_PI_v1_OrdersShippedByBusiness]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[spsun_rp_COM001_PI_v1_OrdersShippedByBusiness]
GO

CREATE PROCEDURE spsun_rp_COM001_PI_v1_OrdersShippedByBusiness
(
@DateFrom varchar(8) ,
@DateTo varchar(8) ,
@Cosmetics Varchar(50),
@Pigments Varchar(50),
@Textiles Varchar(50),
@IDO Varchar(50),
@Interco Varchar(50),
@Merchant Varchar(50),
@Invoiced Varchar(50),
@NotInvoiced Varchar(50),
@Company varchar(30),
@Site varchar(4) ,
@SalesOrderRep varchar(15),
@SalesAgent varchar(30),
@TPName varchar(30),
@TPNetwork varchar(30),
@TPCountry varchar(30),
@GenResc varchar(35),
@ResourceForm varchar(15),
@Parm20 Varchar(50)
)
AS

/******************************************************************************
** File: spsun_rp_COM001_PI_v1_OrdersShippedByBusiness.sql
** Name: spsun_rp_COM001_PI_v1_OrdersShippedByBusiness
** Desc:
**
** Auth: Greg Bones
** Date: 1 May 2002
*******************************************************************************
** Change History
*******************************************************************************
** Date: Author: Description:
** -------- -------- ---------------------------------------
** 20020517 NyborgM Adjusted to PIreports
** 20030203 NyborgM ISNULL inserted in where statement
** 20030212 NyborgM New selection criteria on Currency
** 20030217 NyborgM Wrong join to Invoice corrected
** 20030326 NyborgM Order site changed to shipSite
*******************************************************************************/
/*
DECLARE @DateFrom varchar(8)
DECLARE @DateTo varchar(8)
DECLARE @Cosmetics Varchar(50)
DECLARE @Pigments Varchar(50)
DECLARE @Textiles Varchar(50)
DECLARE @IDO Varchar(50)
DECLARE @Interco Varchar(50)
DECLARE @Merchant Varchar(50)
DECLARE @Invoiced Varchar(50)
DECLARE @NotInvoiced Varchar(50)
DECLARE @Company varchar(30)
DECLARE @Site varchar(4)
DECLARE @SalesOrderRep varchar(15)
DECLARE @SalesAgent varchar(30)
DECLARE @TPName varchar(30)
DECLARE @TPNetwork varchar(30)
DECLARE @TPCountry varchar(30)
DECLARE @GenResc varchar(35)
DECLARE @ResourceForm varchar(15)
DECLARE @Parm20 Varchar(50)

SET @DateFrom = '20021001'
SET @DateTo = '20021231'
SET @Cosmetics = 'TRUE'
SET @Pigments = 'TRUE'
SET @Textiles = 'TRUE'
SET @IDO = 'TRUE'
SET @Interco = 'TRUE'
SET @Merchant = 'TRUE'
SET @Invoiced = 'TRUE'
SET @NotInvoiced = 'TRUE'
SET @Company = 'WAGB'
SET @Site = '%'
SET @SalesOrderRep = '%'
SET @SalesAgent = '%'
SET @TPName = '%'
SET @TPNetwork = '%'
SET @TPCountry = '%'
SET @GenResc = '%'
SET @ResourceForm = '%'
SET @Parm20 = '%'

DROP TABLE #Orders
DROP TABLE #Currency
*/
BEGIN

CREATE TABLE #Orders (
[ShipDate] [datetime] NULL ,
[Bus] [varchar] (15) NULL ,
[Co] [varchar] (30) NULL ,
[Site] [varchar] (4) NULL ,
[SunOrder] [varchar] (15) NULL ,
[OrderObjectID] [int] NULL ,
[Rep] [varchar] (15) NULL ,
[Agent] [varchar] (30) NULL ,
[TPObjectID] [int] NULL ,
[TP] [varchar] (30) NULL ,
[TPN

graz
Chief SQLTeam Crack Dealer

4149 Posts

Posted - 2004-05-05 : 14:51:46

And we truncate at 4,000 characters. You'll have to repost in this thread.

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-05-05 : 14:56:47

Have you tried recompiling the stored procedure?

Tara

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-05 : 19:31:24

It's hard to troubleshoot without then entire query. A guess though is that it's recompiling every single time it runs because of those temp tables. Just a guess though. It's also possible it's getting stuck with a bad plan though.

Have you looked at the execution plan both ways and seen where they are different?

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rughanir
Starting Member

4 Posts

Posted - 2004-05-06 : 03:21:39

Hi SP Reattached. we have tried optimising the sp (mainly the first select). how do i recomplile an sp? i have never done that.

Thanks Reema

CREATE PROCEDURE spsun_rp_COM001_PI_v1_OrdersShippedByBusiness
(
@DateFrom varchar(8) ,
@DateTo varchar(8) ,
@Cosmetics Varchar(50),
@Pigments Varchar(50),
@Textiles Varchar(50),
@IDO Varchar(50),
@Interco Varchar(50),
@Merchant Varchar(50),
@Invoiced Varchar(50),
@NotInvoiced Varchar(50),
@Company varchar(30),
@Site varchar(4) ,
@SalesOrderRep varchar(15),
@SalesAgent varchar(30),
@TPName varchar(30),
@TPNetwork varchar(30),
@TPCountry varchar(30),
@GenResc varchar(35),
@ResourceForm varchar(15),
@Parm20 Varchar(50)
)
AS

BEGIN

CREATE TABLE #Orders (
[ShipDate] [datetime] NULL ,
[Bus] [varchar] (15) NULL ,
[Co] [varchar] (30) NULL ,
[Site] [varchar] (4) NULL ,
[SunOrder] [varchar] (15) NULL ,
[OrderObjectID] [int] NULL ,
[Rep] [varchar] (15) NULL ,
[Agent] [varchar] (30) NULL ,
[TPObjectID] [int] NULL ,
[TP] [varchar] (30) NULL ,
[TPName] [varchar] (30) NULL ,
[TPNet] [varchar] (30) NULL ,
[TPCity] [varchar] (30) NULL ,
[TPCtry] [varchar] (30) NULL ,
[TPOrder] [varchar] (30) NULL ,
[GenResc] [varchar] (35) NULL ,
[ResourceObjectID] [int] NULL ,
[Resc] [varchar] (35) NULL ,
[RescSite] [varchar] (4) NULL ,
[LineSequenceNumber] [int] NULL ,
[DelivSequenceNumber] [int] NULL ,
[Lot] [varchar] (20) NULL ,
[Pkg] [varchar] (20) NULL ,
[RescForm] [varchar] (15) NULL ,
[TotPrice] [float] NULL ,
[TotPriceUSD] [float] NULL ,
[UnitPrice] [float] NULL ,
[Cur] [varchar] (3) NULL ,
[ExchaRate] [float] NULL ,
[OrderDelivObjectID] [int] NULL ,
[ShipKg] [float] NULL ,
[InvoiceDate] [datetime] NULL ,
[Invoice] [varchar] (15) NULL,
[TPType] [varchar] (10) NULL ,
[RescLen] [int] NULL

)

END
/**************************************************************
Orders for shipping
**************************************************************/
BEGIN

INSERT INTO #Orders
(
ShipDate ,
Bus ,
Co ,
Site ,
SunOrder ,
OrderObjectID ,
Rep ,
TPObjectID ,
TP ,
TPName ,
TPNet ,
TPCity ,
TPCtry ,
TPOrder ,
ResourceObjectID ,
Resc ,
Lot ,
RescSite ,
LineSequenceNumber ,
DelivSequenceNumber,
RescForm ,
TotPrice ,
Cur ,
OrderDelivObjectID ,
UnitPrice ,
ShipKg,
TPType,
RescLen
)
SELECT ShipLine.ActyDate ,
fdBasResc.CatCodesCode10 ,
coOrderSumry.SellCmpny ,
coOrderDeliv.ShipSite ,
coOrderSumry.OrderOrdName ,
coOrderSumry.ObjectID ,
fdTradingPartne.Description2 ,
fdTradingPartne.ObjectID ,
fdTradingPartne.TP ,
fdTradingPartne.Description ,
PrimaryNetTPNName ,
fdContact.PrimAddrCity ,
fdContact.PrimAddrCntry ,
coOrderDeliv.CustPONum ,
coOrderLineItem.OrdRescObjectID ,
coOrderLineItem.OrdResc ,
ShipLine.Lot ,
coOrderLineItem.OrdRescSite ,
coOrderLineItem.SequenceNumber ,
coOrderDeliv.SequenceNumber,
fdBasResc.CatCodesCode6 ,
0 ,
coOrderDeliv.UnitPriceCur ,
coOrderDeliv.ObjectID ,
coOrderDeliv.UnitPriceCurAmt ,
ShipLine.AltQty,
fdTPType.Description2 ,
ISNULL(AOCOSTFIELDS.aoResLen, 0)

FROM coOrderSumry (NOLOCK)
INNER JOIN coOrderLineItem (NOLOCK) ON coOrderSumry.ObjectID = coOrderLineItem.ParentObjectID
INNER JOIN coOrderDeliv (NOLOCK) ON coOrderLineItem.ObjectID = coOrderDeliv.ParentObjectID
INNER JOIN ( SELECT inShippingLineI.ActyDate ,
inShippingLineI.Lot ,
SUM(inShippingLineI.AltQty) AS AltQty ,
inShippingLineI.LogDocNum ,
inShippingLineI.LogDocRlsNum ,
inShippingLineI.LogDocLineNum
FROM inShippingLineI (NOLOCK)
WHERE CONVERT(varchar, inShippingLineI.ActyDate, 112) BETWEEN @DateFrom AND @DateTo
GROUP BY inShippingLineI.ActyDate ,
inShippingLineI.Lot ,
inShippingLineI.LogDocNum ,
inShippingLineI.LogDocRlsNum ,
inShippingLineI.LogDocLineNum ) ShipLine ON (ShipLine.LogDocNum = coOrderSumry.OrderOrdName
AND ShipLine.LogDocRlsNum = coOrderDeliv.SequenceNumber
AND CONVERT(int,ShipLine.LogDocLineNum) = coOrderLineItem.SequenceNumber)
INNER JOIN fdBasResc (NOLOCK) ON coOrderLineItem.OrdRescObjectID = fdBasResc.ObjectID
INNER JOIN fdAddOn (NOLOCK) ON fdAddOn.ParentObjectID = fdBasResc.ObjectID
INNER JOIN AOCOSTFIELDS (NOLOCK) ON AOCOSTFIELDS.fdAddOnID = fdAddOn.ObjectID
INNER JOIN fdTradingPartne (NOLOCK) ON coOrderDeliv.ShipToTPObjectID = fdTradingPartne.ObjectID
INNER JOIN fdTPType (NOLOCK) ON fdTPType.ObjectID = fdTradingPartne.TPTypeObjectID
INNER JOIN fdShipToRole (NOLOCK) ON fdTradingPartne.ObjectID = fdShipToRole.ParentObjectID
INNER JOIN fdContact (NOLOCK) ON fdShipToRole.ContactObjectID = fdContact.ObjectID

WHERE ISNULL(coOrderSumry.SellCmpny, '') LIKE @Company
AND ISNULL(coOrderDeliv.ShipSite, '') LIKE @Site
AND ISNULL(fdTradingPartne.Description2, '') LIKE @SalesOrderRep
AND ISNULL(fdTradingPartne.Description, '') LIKE @TPName
AND IsNULL(PrimaryNetTPNName, '') LIKE @TPNetwork
AND ISNULL(fdContact.PrimAddrCntry, '') LIKE @TPCountry
AND ISNULL(fdBasResc.CatCodesCode6, '') LIKE @ResourceForm
ORDER BY coOrderDeliv.ActShipDate

END
/**************************************************************
Orders for returns
**************************************************************/
BEGIN

INSERT INTO #Orders
(
ShipDate ,
Bus ,
Co ,
Site ,
SunOrder ,
OrderObjectID ,
Rep ,
TPObjectID ,
TP ,
TPName ,
TPNet ,
TPCity ,
TPCtry ,
TPOrder ,
ResourceObjectID ,
Resc ,
Lot ,
RescSite ,
LineSequenceNumber ,
DelivSequenceNumber,
RescForm ,
TotPrice ,
Cur ,
OrderDelivObjectID ,
UnitPrice ,
ShipKg,
TPType,
RescLen
)
SELECT RecLine.ActyDate ,
fdBasResc.CatCodesCode10 ,
coOrderSumry.SellCmpny ,
coOrderDeliv.ShipSite ,
coOrderSumry.OrderOrdName ,
coOrderSumry.ObjectID ,
fdTradingPartne.Description2 ,
fdTradingPartne.ObjectID ,
fdTradingPartne.TP ,
fdTradingPartne.Description ,
PrimaryNetTPNName ,
fdContact.PrimAddrCity ,
fdContact.PrimAddrCntry ,
coOrderDeliv.CustPONum ,
coOrderLineItem.OrdRescObjectID ,
coOrderLineItem.OrdResc ,
RecLine.Lot ,
coOrderLineItem.OrdRescSite ,
coOrderLineItem.SequenceNumber ,
coOrderDeliv.SequenceNumber,
fdBasResc.CatCodesCode6 ,
0 ,
coOrderDeliv.UnitPriceCur ,
coOrderDeliv.ObjectID ,
coOrderDeliv.UnitPriceCurAmt ,
RecLine.AltQty * -1,
fdTPType.Description2 ,
ISNULL(AOCOSTFIELDS.aoResLen, 0)

FROM coOrderSumry (NOLOCK)
INNER JOIN coOrderLineItem (NOLOCK) ON coOrderSumry.ObjectID = coOrderLineItem.ParentObjectID
INNER JOIN coOrderDeliv (NOLOCK) ON coOrderLineItem.ObjectID = coOrderDeliv.ParentObjectID
INNER JOIN ( SELECT inReceivingLine.ActyDate ,
inReceivingLine.Lot ,
SUM(inReceivingLine.AltQty) AS AltQty ,
inReceivingLine.LogDocNum ,
inReceivingLine.LogDocRelNum ,
inReceivingLine.LogDocLine
FROM inReceivingLine (NOLOCK)
WHERE CONVERT(varchar, inReceivingLine.ActyDate, 112) BETWEEN @DateFrom AND @DateTo
GROUP BY inReceivingLine.ActyDate ,
inReceivingLine.Lot ,
inReceivingLine.LogDocNum ,
inReceivingLine.LogDocRelNum ,
inReceivingLine.LogDocLine ) RecLine ON (RecLine.LogDocNum = coOrderSumry.OrderOrdName
AND RecLine.LogDocRelNum = coOrderDeliv.SequenceNumber
AND CONVERT(int,RecLine.LogDocLine) = coOrderLineItem.SequenceNumber)
INNER JOIN fdBasResc (NOLOCK) ON coOrderLineItem.OrdRescObjectID = fdBasResc.ObjectID
INNER JOIN fdAddOn (NOLOCK) ON fdAddOn.ParentObjectID = fdBasResc.ObjectID
INNER JOIN AOCOSTFIELDS (NOLOCK) ON AOCOSTFIELDS.fdAddOnID = fdAddOn.ObjectID
INNER JOIN fdTradingPartne (NOLOCK) ON coOrderDeliv.ShipToTPObjectID = fdTradingPartne.ObjectID
INNER JOIN fdTPType (NOLOCK) ON fdTPType.ObjectID = fdTradingPartne.TPTypeObjectID
INNER JOIN fdShipToRole (NOLOCK) ON fdTradingPartne.ObjectID = fdShipToRole.ParentObjectID
INNER JOIN fdContact (NOLOCK) ON fdShipToRole.ContactObjectID = fdContact.ObjectID
WHERE coOrderDeliv.delivtype = '3'
AND ISNULL(coOrderSumry.SellCmpny, '') LIKE @Company
AND ISNULL(coOrderDeliv.ShipSite, '') LIKE @Site
AND ISNULL(fdTradingPartne.Description2, '') LIKE @SalesOrderRep
AND ISNULL(fdTradingPartne.Description, '') LIKE @TPName
AND IsNULL(PrimaryNetTPNName, '') LIKE @TPNetwork
AND ISNULL(fdContact.PrimAddrCntry, '') LIKE @TPCountry
AND ISNULL(fdBasResc.CatCodesCode6, '') LIKE @ResourceForm
ORDER BY coOrderDeliv.ActShipDate

END
/*****************************************************************************
Updating with Resource Length for resources not in FGNP
*****************************************************************************/
BEGIN

UPDATE #Orders
SET RescLen = CHARINDEX('_',#Orders.Resc)-1
FROM #Orders
WHERE CHARINDEX('_',#Orders.Resc) <> 0
AND #Orders.RescLen = 0

END
/*****************************************************************************
Updating with Genericresource
*****************************************************************************/
BEGIN

UPDATE #Orders
SET GenResc = SUBSTRING(#Orders.Resc,1,RescLen)
FROM #Orders
WHERE #Orders.RescLen <> 0

END
/*****************************************************************************
Delete lines if not equal to parm in GenResc
*****************************************************************************/
BEGIN

DELETE
FROM #Orders
WHERE NOT #Orders.GenResc LIKE @GenResc

END
/**************************************************************
Update Total Price
**************************************************************/
BEGIN

UPDATE #Orders
SET TotPrice = UnitPrice * ShipKg
FROM #Orders

END
/**************************************************************
Update Sales Agent Information
**************************************************************/
BEGIN

UPDATE #Orders
SET Agent = fdContact.Description
FROM #Orders
INNER JOIN fdTradingPartne (NOLOCK) ON #Orders.TPObjectID = fdTradingPartne.ObjectID
INNER JOIN fdCommissionsRo (NOLOCK) ON fdTradingPartne.CommisRoleObjectID = fdCommissionsRo.ObjectID
INNER JOIN fdContact (NOLOCK) ON fdCommissionsRo.ContactObjectID = fdContact.ObjectID

END
/**************************************************************
Update Packaging Information
**************************************************************/
BEGIN

UPDATE #Orders
SET Pkg = RIGHT(fdBasResc.ResourceUK, aoCostFields.aoPackLen)
FROM #Orders
INNER JOIN fdBasResc (NOLOCK) ON #Orders.ResourceObjectID = fdBasResc.ObjectID
INNER JOIN aoCostFields (NOLOCK) ON fdBasResc.ObjectID = aoCostFields.ParentObjectID

END
/**************************************************************
Update Invoice Information
**************************************************************/
BEGIN

UPDATE #Orders
SET InvoiceDate = coInvcSumry.InvcDate,
Invoice = coInvcSumry.InvcUKInvc
FROM #Orders
INNER JOIN coInvcOrder (NOLOCK) ON #Orders.OrderObjectID = coInvcOrder.OrderUKObjectID
INNER JOIN coInvcSumry (NOLOCK) ON coInvcSumry.ObjectID = coInvcOrder.ParentObjectID
INNER JOIN coInvcLine (NOLOCK) ON (coInvcOrder.ObjectID = coInvcLine.ParentObjectID
AND #Orders.LineSequenceNumber = coInvcLine.OrdLin
AND #Orders.DelivSequenceNumber = coInvcLine.OrdDel)

END
/**************************************************************
Calculate Exchange Rate into USD
**************************************************************/
BEGIN

SELECT Ord.OrderDelivObjectID ,
Ord.Cur ,
bcCurrencyConv.CurConvToCur ,
ISNULL(Ord.ShipDate, GetDate()) 'ShipDate',
ISNULL(bcCurrConvRate.ConvRate,1) 'ConvRate'
INTO #Currency
FROM #Orders Ord
INNER JOIN bcCurrencyConv (NOLOCK) ON Ord.Cur = bcCurrencyConv.CurConvFromCur
INNER JOIN bcCurrConvRate (NOLOCK) ON bcCurrencyConv.ObjectID = bcCurrConvRate.ParentObjectID
WHERE bcCurrencyConv.CurConvToCur = 'USD'
AND bcCurrConvRate.StartDate =
(SELECT MAX(bcCurrConvRate.StartDate)
FROM #Orders
INNER JOIN bcCurrencyConv (NOLOCK) ON #Orders.Cur = bcCurrencyConv.CurConvFromCur
INNER JOIN bcCurrConvRate (NOLOCK) ON bcCurrencyConv.ObjectID = bcCurrConvRate.ParentObjectID
WHERE bcCurrencyConv.CurConvToCur = 'USD'
AND Ord.OrderDelivObjectID = #Orders.OrderDelivObjectID
AND bcCurrConvRate.StartDate < ISNULL(#Orders.ShipDate, GetDate())
GROUP BY bcCurrencyConv.CurConvToCur)

GROUP BY Ord.OrderDelivObjectID ,
Ord.Cur ,
bcCurrencyConv.CurConvToCur ,
ISNULL(Ord.ShipDate, GetDate()),
ISNULL(bcCurrConvRate.ConvRate,1)

END
/**************************************************************
Set ExchaRate to USD
**************************************************************/
BEGIN

UPDATE #Orders
SET ExchaRate = #Currency.ConvRate
FROM #Orders
INNER JOIN #Currency ON #Orders.OrderDelivObjectID = #Currency.OrderDelivObjectID

END
/**************************************************************
Set ExchaRate to 1 for USD
**************************************************************/
BEGIN

UPDATE #Orders
SET ExchaRate = 1
WHERE Cur = 'USD'

END
/**************************************************************
Calculate TotPrice for USD
**************************************************************/
BEGIN

UPDATE #Orders
SET TotPriceUSD = TotPrice * ExchaRate

END
/**************************************************************
Select to list
**************************************************************/
BEGIN

SELECT ShipDate,
Bus,
Co,
Site,
SunOrder,
Rep,
Agent,
TP,
TPName,
TPNet,
TPCity,
TPCtry,
TPOrder,
GenResc,
Resc,
RescSite,
Lot,
Pkg,
RescForm,
TotPrice,
TotPriceUSD,
UnitPrice,
Cur,
ExchaRate,
ShipKg,
InvoiceDate,
Invoice
FROM #Orders
WHERE IsNull(Agent, ' ') LIKE @SalesAgent
AND ((not InvoiceDate IS NULL and @Invoiced = 'TRUE')
or (InvoiceDate IS NULL and @NotInvoiced = 'TRUE'))
AND ((TPType = 'IC' AND @Interco = 'TRUE')
OR (TPType = 'ID' AND @IDO = 'TRUE')
OR (TPType = 'TR' AND @Merchant = 'TRUE'))
AND ((Bus = 'COSMETICS' AND @COSMETICS = 'TRUE')
or (Bus = 'TEXTILES' AND @TEXTILEs = 'TRUE')
or (Bus = 'PIGMENTS' AND @PIGMENTS = 'TRUE'))

ORDER BY Bus,
TPCtry,
TP,
Resc,
ShipDate
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

GRANT EXECUTE ON [dbo].[spsun_rp_COM001_PI_v1_OrdersShippedByBusiness] TO [public]
GO

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-06 : 03:31:16

how do i recomplile an sp? i have never done that.

EXEC sp_recompile 'proc_name'

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

rughanir
Starting Member

4 Posts

Posted - 2004-05-06 : 04:08:23

Hi

I have tried recompiling but i have had no luck. The Sp itself still takes over 5 min to return 289 rows. And againt the direct sql with the same parameters declared and set, returns after 30 seconds.

i am so stuck on this! Hope someone can help.

Reema

mohdowais
Sheikh of Yak Knowledge

1456 Posts

Posted - 2004-05-06 : 07:44:03

Hi Reema, you might want to try running DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS to make sure the old execution plan is flushed from memory before you run it again. Have you compared the execution plans of the proc and the query, perhaps there is something very obviously different between the two? I've seen some similar issues with parallelism on servers with more than one processor.

Also, try replacing the temp table with a table variable, that is a lot less likely to cause a recompile every time the proc is run.

OS

rughanir
Starting Member

4 Posts

Posted - 2004-05-06 : 11:41:05

HI

I have tried everythign now. I have compared the direct sql exec plan to the sp exec plan and there is virtually no difference. I have also tried DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS but again that made no difference. I will now try to change the temp table to a table variable (which should be interesting seeign as i have never done one).

Thanks for all your help and advise. I will re-post with any results, good or bad.. he he!

R

kselvia
Aged Yak Warrior

526 Posts

Posted - 2004-05-06 : 12:09:23

Try

CREATE PROCEDURE spsun_rp_COM001_PI_v1_OrdersShippedByBusiness (...) WITH RECOMPILE AS

That will cause the proc to be recompiled every time it is run. Usually that is not a good idea, but the query plan is generated for a proc the first time it is executed. That plan is based on the values passed at the time it is run. If different parameters are used for subsequent calls, the original query plan may no longer be optimal. WITH RECOMPILE will add overhead when invoking it but may help in the long run.

You might also try changing:

CONVERT(varchar, inShippingLineI.ActyDate, 112) BETWEEN @DateFrom AND @DateTo

to

inShippingLineI.ActyDate BETWEEN convert(datetime,@DateFrom) AND convert(datetime,@DateTo)

in the 2 insert statements. Converting ActyDate to varchar prevents an index on ActyDate from being used.

Cheers

derrickleggett
Pointy Haired Yak DBA

4184 Posts

Posted - 2004-05-06 : 14:48:22

quote:
Originally posted by rughanir

HI

I have tried everythign now. I have compared the direct sql exec plan to the sp exec plan and there is virtually no difference. I have also tried DBCC FREEPROCCACHE and DBCC DROPCLEANBUFFERS but again that made no difference. I will now try to change the temp table to a table variable (which should be interesting seeign as i have never done one).

Thanks for all your help and advise. I will re-post with any results, good or bad.. he he!

R

It's a piece of cake:

DECLARE @table TABLE(
column1 INT,
column2 VARCHAR(55))

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources