SP kills the CPU...Help? - 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)

SP kills the CPU...Help?

Author

Topic

SQLTim
Starting Member

21 Posts

Posted - 2006-03-29 : 13:28:24

I've tried to minimize function use here, but it is only marginally better than the original.
The code basically finds what the total could be sold for an item, then subtracts out of it actual sales, saved sales, and reserved items. It does this in all in a column query.

Can anyone suggest a better overall approach? Perhaps breaking it up into three seperate queries and subtracting them from the limit?

I've also learned that it only needs to return a single number qty_rem. What benefits could be gained by making this scalar in some way?

Many Thanks in Advance!!
Tim

ALTER procedure sp_GetQtyRemaining_By_DCI_base
@idate varchar(20), @dept char(10), @cat char(10), @item char(10), @tnSaleNo numeric(17,0)
as
set nocount on
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
declare @tDate datetime,
@nMax4Saved tinyint,
@iDateToday int,
@tOldestDate datetime,
@tGetdate datetime,
@cDate char(10),
@cNumMins char(12)

set @tDate = cast(@idate as datetime)
set @cDate = CONVERT(char(10), @tdate, 102)
set @cNumMins = CONVERT(char(12), @tDate, 14)
set @iDateToday = cast(@tDate as int) -1
set @tOldestDate = cast(@iDateToday as datetime)
set @tGetdate = GETDATE()

select top 1 @nMax4Saved = isnull(max4_saved,0) from prefs_sl

-- m.type<10 means that it's 1, 6, or 7 and therefore datetime
-- m.type>=10 means that it's 51,56,57 and therefore time only
-- m.type =1 or m.type=51 means that it's dci
-- m.type=6 or 56 means that its dept/cat
-- m.type=7 or 57 means that it's dept only

--for optimization purposes, get the oldest date we need to look at and use that in the query:
select top 1 @tOldestDate=m.start_time
from ((max4sale m LEFT OUTER JOIN departme d ON d.department=m.department)
LEFT OUTER JOIN category c ON c.department=m.department and c.category=m.category)
LEFT OUTER JOIN items i ON i.department=m.department and i.category=m.category and i.item=m.item
where (m.department=@dept) and (
(m.type=1 and i.ckmax4sale=1 and @tDate BETWEEN m.start_time and m.end_time and m.category=@cat and m.item=@item) or
(m.type=6 and c.ckmax4sale=1 and @tDate BETWEEN m.start_time and m.end_time and m.category=@cat) or
(m.type=7 and d.ckmax4sale=1 and @tDate BETWEEN m.start_time and m.end_time) )
order by m.start_time asc

-- Get the limits
select top 1
m.max_sale
--subtract out the number of things sold in transact.
- isnull((select sum(t.quantity) from transact t
where t.start_date >= @tOldestDate and
t.department=@dept and (m.type=7 or m.type=57 or t.category=@cat) and
(m.type IN(7 , 57, 6, 56) or t.item=@item) and
t.sale_no<>@tnSaleNo
and ( (m.type<10 and (t.start_date between m.start_time and m.end_time))
or ( (m.type>=10 and
( @cDate = CONVERT(char(8), t.start_date, 2) ) and
( CONVERT(char(12), t.start_date, 14) BETWEEN CONVERT(char(12), m.start_time, 14) and CONVERT(char(12), m.end_time, 14) )
))) ),0)
--subtract out the number of things in tr_save.
- isnull((select sum(tr.quantity) from tr_save tr LEFT OUTER JOIN sh_save sh ON tr.sale_no=sh.sale_no
where @nMax4Saved = 2 and tr.start_date >= @tOldestDate and
tr.department=@dept and (m.type=7 or m.type=57 or tr.category=@cat) and
(m.type IN(7 , 57, 6, 56) or tr.item=@item) and
tr.sale_no<>@tnSaleNo and sh.finalized=0 and tr.finalized=0
and ( (m.type<10 and (tr.start_date between m.start_time and m.end_time))
or ( (m.type>=10 and
( @cDate = CONVERT(char(8), tr.start_date, 2) ) and
( CONVERT(char(12), tr.start_date, 14) BETWEEN CONVERT(char(12), m.start_time, 14) and CONVERT(char(12), m.end_time, 14) )
))) ),0)
--subtract out real time inventory if necessary
- isnull((select sum(ir.quantity) from item_res ir
where i.ckm4s_rt =1 and ir.start_date >= @tOldestDate and
ir.department=@dept and (m.type=7 or m.type=57 or ir.category=@cat) and
(m.type IN(7 , 57, 6, 56) or ir.item=@item) and
ir.expires>= @tGetdate
and ( (m.type<10 and (ir.start_date between m.start_time and m.end_time))
or ( (m.type>=10 and
( @cDate = CONVERT(char(10), ir.start_date, 102) ) and
( CONVERT(char(12), ir.start_date, 14) BETWEEN CONVERT(char(12), m.start_time, 14) and CONVERT(char(12), m.end_time, 14) )
))) ),0)
as qty_rem
from ((max4sale m LEFT OUTER JOIN departme d ON d.department=m.department)
LEFT OUTER JOIN category c ON c.department=m.department and c.category=m.category)
LEFT OUTER JOIN items i ON i.department=m.department and i.category=m.category and i.item=m.item
where
m.department=@dept and (
(m.type=1 and i.ckmax4sale=1 and @tDate BETWEEN m.start_time and m.end_time and m.category=@cat and m.item=@item) or
(m.type=6 and c.ckmax4sale=1 and @tDate BETWEEN m.start_time and m.end_time and m.category=@cat) or
(m.type=7 and d.ckmax4sale=1 and @tDate BETWEEN m.start_time and m.end_time ) or
(m.type=51 and i.ckmax4sale=1 and m.category=@cat and m.item=@item
and @cNumMins between CONVERT(char(12), m.start_time, 14) and CONVERT(char(12), m.end_time, 14) ) or
(m.type=56 and c.ckmax4sale=1 and m.category=@cat
and @cNumMins between CONVERT(char(12), m.start_time, 14) and CONVERT(char(12), m.end_time, 14) ) or
(m.type=57 and d.ckmax4sale=1
and @cNumMins between CONVERT(char(12), m.start_time, 14) and CONVERT(char(12), m.end_time, 14) ) )
order by qty_rem asc
return (0)

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2006-03-29 : 13:45:06

read these 2 entries here
http://weblogs.sqlteam.com/jeffs/archive/2004/12.aspx

there's no way you can be using any indexes properly with this query.

Go with the flow & have fun! Else fight the flow

Blog thingie: [URL="weblogs.sqlteam.com/mladenp"]

SQLTim
Starting Member

21 Posts

Posted - 2006-03-30 : 13:32:40

Thanks Spirit1
Good point!

So I tried the following:

"tr.start_date between @cDate and @cEDate " This is where @cDate and @cEDate are a day apart.
instead of
"@cDate = CONVERT(char(8), tr.start_date, 2) "

"tr.start_date between convert(char(11),tr.start_date,20)+convert(char(8),m.start_time,14) and convert(char(11),tr.start_date,20)+convert(char(8),m.end_time,14)"
Instead of
"CONVERT(char(12), tr.start_date, 14) BETWEEN CONVERT(char(12), m.start_time, 14) and CONVERT(char(12), m.end_time, 14) "

I only care about the time portion so I'm borrowing the date part from the first field and prepending it to the time part of the second fields.

"convert(char(11),m.start_time,20)+@cTimePart between m.start_time and m.end_time "
instead of
"@cNumMins between CONVERT(char(12), m.start_time, 14) and CONVERT(char(12), m.end_time, 14) "

Same thing here, only it's reversed. Trying to make the larger table's columns searchable.

Even optimizing that code, the execution plan still puts these dates in filters. No indexes used.

Totally under the gun here and all eyes are on me.
Is there even another approach I should consider?

Many thanks in advance!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources