Slow Proc - SQL Server Forums

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)

Slow Proc

Author

Topic

Togaspoon
Starting Member

42 Posts

Posted - 2003-04-22 : 10:49:26

Why would this version of a stored procedure

CREATE PROC TEST
	@EquipID varchar(5),
	@SDate datetime  = NULL,
	@EDate datetime = NULL

AS

IF @SDate IS NULL
	SET @SDate = DATEADD(yy,-1,GETDATE())

IF @EDate IS NULL
	SET @EDate = GETDATE()

SELECT SUM(LABOR_AMT_SCC) AS 'TotalCost'
FROM SERV_CALL_COST A INNER JOIN SERIAL_EQUIP B ON A.SHIP_TO = B.CUSTOMER_NUMBER AND A.SERIAL = B.SERIAL AND A.Model = B.Model
WHERE B.EQUIP_ID = @EquipID
 AND TRANS_DATE_SCC BETWEEN @SDate AND @EDate

Be slower then this version

CREATE PROC TEST
	@EquipID varchar(5),
	@SDate datetime  = '4/13/02',
	@EDate datetime = '4/13/03'

AS

SELECT SUM(LABOR_AMT_SCC) AS 'TotalCost'
FROM SERV_CALL_COST A INNER JOIN SERIAL_EQUIP B ON A.SHIP_TO = B.CUSTOMER_NUMBER AND A.SERIAL = B.SERIAL AND A.Model = B.Model
WHERE B.EQUIP_ID = @EquipID
 AND TRANS_DATE_SCC BETWEEN @SDate AND @EDate

NOTE: The Only difference is that in the 2nd one I've hard coded the default values for the date range.

The 2nd query is 4 times faster.

claire
Starting Member

19 Posts

Posted - 2003-04-22 : 11:03:53

How about changing all the "null" to "0".

I think the problem may come from
(1)default
(2)Getting getdate()
(3)Calculating dateadd()
(4)If condition

X002548
Not Just a Number

15586 Posts

Posted - 2003-04-22 : 12:19:09

I bet if you did a SHOW Execution PLAN on both that the slower one is performing a tablespace scan.

Maybe you can add Convert(datetime,@SDate,104) to the predicate?

If you are finding a table scan and the convert doesn't work, maybe you can force the index.

Just a thought..

good luck

Brett

8-)

Togaspoon
Starting Member

42 Posts

Posted - 2003-04-22 : 13:22:50

This version seems to work well.

Go figure...

CREATE PROC ps_SERIAL_EQUIP_SELECT_TotalServCostByEquipID
	@EquipID varchar(5),
	@SDate datetime  = NULL,
	@EDate datetime = NULL

AS

SELECT SUM(LABOR_AMT_SCC) AS 'TotalCost'
FROM SERV_CALL_COST A INNER JOIN SERIAL_EQUIP B 
			ON A.SHIP_TO = B.CUSTOMER_NUMBER AND A.SERIAL = B.SERIAL AND A.Model = B.Model
WHERE B.EQUIP_ID = @EquipID
 AND TRANS_DATE_SCC BETWEEN COALESCE(@SDate,DATEADD(yy,-1,GETDATE()))
			AND COALESCE(@EDate,GETDATE())

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources