Query taking to long to run - 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)

Query taking to long to run

Author

Topic

uxphreak
Starting Member

38 Posts

Posted - 2005-04-05 : 11:44:09

Good morning,

I have a query that uses a function to display the quantity OnOrder and quantity ToBeOrdered for each Sku at each Location. The problem is the query is taking very long to run for the small amount of data returned (it takes 10+ minutes to return about 380 records).

If someone could take a look at the code for the function and the query to see if there's anything that could be slowing things down I would really appreciate it. I've been working on this problem for so long I can't clearly look at it anymore.

Thanks in advance.

D,

=====================================
= Function to calculate ToBeOrdered =
=====================================

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

-- This function returns the quantity to order by Sku for each location.

ALTER function dbo.GetQtyToOrderBySkuLocation
(
@Skuid nvarchar(20)
, @locid nvarchar(2)
, @DateToCheck datetime
)
RETURNS Int
AS
BEGIN
Declare
@ToOrder int
, @Min int
, @Max int
, @OnOrder int
, @qty int
, @override int
, @AlreadyGot int
Set
@override = 0
Select
@Min = MinLvl
, @Max = MaxLvl
, @qty = qty
, @Override = Overrideqty
From
Productlocation
where
skuid = @skuid
and
locid = @locid
-- If the value of OverrideQty is greater than 0, then return that value
If
@override > 0
BEGIN
RETURN
@override
END
-- If the Qty (quantity on hand) is greater than MinLvl, then return 0
ELSE If
@qty > @Min
BEGIN
Return
0
END
-- If the Qty is less than or equal to MinLvl, then subtract MaxLvl
-- from Qty, then subtract what is already OnOrder, and finally add
-- AlreadyGot (what has been received). This value will be returned
-- ToOrder
ELSE
BEGIN
-- Select what is OnOrder for all SkuID's where the order
-- CancelDate is greater than the value of @DateToCheck
Select
@onorder = IsNull(Sum(rl.qty),0)
From
reqline rl
inner join
request
on
rl.reqid = request.req_id
Where
request.Canceldate > @DateToCheck
and
skuid = @skuid
and
locid = @locid
-- If OnOrder > 0 for each Sku, then calculate AlreadyGot (quantity that has
-- been received for that Sku) where the order CancelDate is greater than
-- the value of @DateToCheck
If
@onorder > 0
BEGIN
Select
@AlreadyGot = IsNull(Sum(rcl.qty),0)
From
recline rcl
inner join
request
on
rcl.reqid = 'T' + request.req_id
where
request.canceldate > @DateToCheck
and
skuid = @skuid
and
locid = @locid
END
-- If OnOrder is not > 0 then set AlreadyGot to 0
ELSE
BEGIN
Set
@AlreadyGot = 0
END
-- Set the value of ToOrder to MaxLvl - Qty - OnOrder + AlreadyGot
Set @ToOrder = IsNull(@Max,0) - IsNull(@qty,0) - IsNull(@OnOrder,0) + IsNull(@AlreadyGot,0)
If
@ToOrder < 0
BEGIN
Return 0
END
ELSE
BEGIN
RETURN @ToOrder
END
END
RETURN @ToOrder
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

==============================================================
= Query to display Skus with qty OnOrder and qty ToBeOrdered =
==============================================================

-- This query does three things: (1) create a temporary table consisting of
-- SKUs for each location (LocID) where the StatusID = 1 and where MaxLvl <> 0
-- or Qty <= minlvl or OverrideQty > 0, (2) Display all Skus for each location
-- where they have a quantity OnOrder or have a quantity in ToBeOrdered, and (3)
-- drop the temporary table after completion.

-- Create the temporary table

CREATE TABLE
#ProdLoc
(
SkuID char(16)
, LocID char(16)
, Description varchar(64)
, Qty int
, MinLvl int
, MaxLvl int
, OverrideQty int
)
INSERT
#ProdLoc
(
SkuID
, LocID
, Description
, Qty
, MinLvl
, MaxLvl
, OverrideQty
)
SELECT
pl.SkuID
, pl.LocID
, p.Description
, pl.Qty
, pl.MinLvl
, pl.MaxLvl
, pl.OverrideQty
FROM
productlocation pl
INNER JOIN
product p
ON
p.skuid = pl.skuid
WHERE
pl.locid in ('1','2','3,','4','6','7','8')
AND
pl.statusid = '1'
AND
(
pl.maxlvl <> 0
OR
pl.Qty <= pl.minlvl
OR
pl.OverrideQty > 0
)
-- Show Skus for each location in the temporary table and the values of
-- OnOrder and ToBeOrdered.
Select
p.locid
, p.skuid
, p.description
, p.qty
, p.minlvl
, p.maxlvl
, IsNull(intran,0) - isnull(qtyreceived,0) as onorder
, dbo.GetQtyToOrderBySkuLocation(p.skuid, p.locid, getdate()) as tobeordered
, p.Overrideqty
from
#prodloc p
left join
(
Select
request.locid
, rl.skuid
, IsNull(Sum(rl.qty),0) as intran
From
reqline rl
inner join
request
on
rl.reqid = request.req_id
Where
request.Canceldate > getdate()
group by
request.locid
, rl.skuid
) rskus
on
p.locid = rskus.locid
and
p.skuid = rskus.skuid
Left Join
(
Select
Left(Rec_ID,1) as locid
, Skuid
, IsNull(Sum(qty),0) as qtyreceived
From
recline
inner join
request
on
'T' + request.req_id = reqid
Where
request.canceldate > getdate()
Group By
Left(Rec_ID,1)
, skuid
) as received
on
p.locid = received.locid
and
p.skuid = received.skuid
where
(
IsNull(intran,0) - isnull(qtyreceived,0) > 0
OR
dbo.GetQtyToOrderBySkuLocation(p.skuid, p.locid, getdate()) > 0
)

DROP TABLE
#PRODLOC

uxphreak
Starting Member

38 Posts

Posted - 2005-04-05 : 15:45:40

I wanted to add that I've just tried the function and query on another copy of the database I'm using, on a totally different server, and the query is still taking about 10 minutes to run.

D

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-06 : 05:18:40

Functions that do SELECTS perform far worse that those that don't do SELECT's....becasue the SELECT needs to be called on a ROW-by-ROW basis....nearly as bad as a CURSOR.

Try to design the function 'as if it had received all required information as parameters'...ie MOVE the SELECT OUTSIDE the function...and pass all required value in.

Other discussions on this topic have come up (very) recently. Search for FUNCTION + PERFORMANCE (SLOW) and see if the links clarify a solution.

There is a presumption in the above that all APPROPRIATE indexes to maximise performance are in place....posting DDL and an EXECUTION PLAN would be useful.

uxphreak
Starting Member

38 Posts

Posted - 2005-04-06 : 11:09:27

Thanks AndrewMurphy,

If SELECTS perform poorly in Functions, should I create a Stored Procedure instead of the Function, or create Stored Procedures that the Function will use? I'm confused, because I need SELECTS to gather values of @AlreadyGot, @OnOrder, etc.

D

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2005-04-06 : 11:19:06

You're moving into areas beyond my practical experience (most of what i;'ve written comes from memory of reading about these issues here, rather than from real-life examples that have affected me)....but others here nr, robvolk, merkin, tduggan, etc seem to have "been-there, done-that"

The advice I've read (here) from memory suggests that you move the SELECT outside the FUNCTION and pass in the values into the FUNCTION. Search here and BOL for previous discussions.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-04-06 : 11:29:22

right off the bat, joining on:

'T' + request.req_id = reqid

will be very inefficient and cannot use proper foreign keys or indexes.

Also, filtering on the results of a function such as:

dbo.GetQtyToOrderBySkuLocation(p.skuid, p.locid, getdate()) >

Also in general will be very inefficient as no indexes can be used and this function MUST be evaluated over and over for all possible rows that this query might return.

Finally, make sure that you have proper indexes on the various columns you are joining on and filtering on.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources