October 7, 2009 at 12:05 pm
Declare
@a money,
@Picklist varchar(10)
-- for testing
SELECT
@a = 1000,
@Picklist
= 'Exclude' -- 'Include'
IF
(@Picklist = 'Exclude')
BEGIN
select acct_code,
part_no
,location
,description
,sku_no
,list_status
,pub_date
,pub_status
,succession_status
,void
,in_stock
,avg_cost
,std_cost
,((avg_cost - std_cost)/avg_cost)*100 as unit_cost_var
,(avg_cost - std_cost)*in_stock as extended_diff
,in_stock*avg_cost as extended_avg_cost
from dfi_inv_master_list with (NOLOCK)
where ((avg_cost - std_cost)*in_stock >= @a
or (avg_cost - std_cost)*in_stock <= -1* @a)
and avg_cost <> 0
and in_stock <> 0
order by ABS((avg_cost - std_cost)*in_stock) desc
END
ELSE
BEGIN
select acct_code, part_no
, location
, description, sku_no
, list_status
, pub_date
, pub_status
, succession_status
, void
, in_stock
, avg_cost
, std_cost
, ((avg_cost - std_cost)/avg_cost)*100 as unit_cost_var
, (avg_cost - std_cost)*in_stock as extended_diff
, in_stock*avg_cost as extended_avg_cost
from
dfi_inv_master_list with (NOLOCK)
where ((avg_cost - std_cost)*in_stock >= @a
or (avg_cost - std_cost)*in_stock <= -1* @a)
and (avg_cost <> 0 OR avg_cost = 0)
and (in_stock <> 0 OR in_stock = 0)
order by ABS((avg_cost - std_cost)*in_stock) desc
END
October 7, 2009 at 12:11 pm
Why don't you just delete this part from the second query, it is always true:
and (avg_cost <> 0 OR avg_cost = 0)
and (in_stock <> 0 OR in_stock = 0)
October 7, 2009 at 12:45 pm
Yes you are correct. any more suggestions plz
Thanks
October 7, 2009 at 12:49 pm
Here is a big one. If these queries are inside a stored procedure, I'd seperate them into their own procedure and use a master stored procedure to call the approriate procedure based on the inputs to the master stored procedure.
October 7, 2009 at 1:10 pm
where ((avg_cost - std_cost)*in_stock >= @a
or (avg_cost - std_cost)*in_stock <= -1* @a)
will cause a scan every time, as well as the computation.
I don't know that much about computed columns (so please, don't be shy to correct me, gurus- and I wouldn't take my word as the end-all-be-all, OP) but I know you can have a PERSISTED computed column which stores and retains the calculated value. This will at least get rid of having to do the math every single time, plus you might even be able to get a seek out of it (PERSISTED computed columns can be indexed).
Like I said, I don't have all that much experience with these so I can't speak as to how they affect mass INSERTS and UPDATES. Someone please elaborate (or feel free to tell me my idea is terrible :hehe:)
October 7, 2009 at 4:20 pm
Mike McQueen (10/7/2009)
where ((avg_cost - std_cost)*in_stock >= @aor (avg_cost - std_cost)*in_stock <= -1* @a)
will cause a scan every time, as well as the computation.
I don't know that much about computed columns (so please, don't be shy to correct me, gurus- and I wouldn't take my word as the end-all-be-all, OP) but I know you can have a PERSISTED computed column which stores and retains the calculated value. This will at least get rid of having to do the math every single time, plus you might even be able to get a seek out of it (PERSISTED computed columns can be indexed).
Like I said, I don't have all that much experience with these so I can't speak as to how they affect mass INSERTS and UPDATES. Someone please elaborate (or feel free to tell me my idea is terrible :hehe:)
Actually, a computed column that isn't persisted can be indexed. It just needs to be deterministic. But, your basic premise is spot on.
Also, regardless of that suggestion, the following will also force a full table scan or, at best, a full index scan...
order by ABS((avg_cost - std_cost)*in_stock) desc
You need to use the ol' "Divide'n'Conquer" methods to get any performance out of this code... Can you use a stored procedure? Also, can you use temp tables?
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2009 at 8:03 am
Jeff- Thanks for the follow up, good to know I wasn't too far off 🙂
I do have a question though- why would the order by cause a scan on the entire table?
October 8, 2009 at 9:16 am
It's not the Order by so much as the ABS function in the Order by I'm pretty sure.
October 8, 2009 at 9:20 am
mtassin (10/8/2009)
It's not the Order by so much as the ABS function in the Order by I'm pretty sure.
But wouldn't it only need to ABS() the rows that meet the WHERE criteria?
October 8, 2009 at 9:35 am
I'd leave Jeff to explain exactly because I sometimes get confused as to why SCANs creep up in situations like this but.
As I recall the order of operations in a SQL statement goes something like this
FROM
WHERE
SELECT
GROUP BY
ORDER BY
If the ABS function was a calculated field in the query above (I did a quick look and didn't see it) and the ORDER BY referenced that calculated field by name, I'd think it would sort by the result set.
i.e.
SELECT
valuechange = ABS((endingstock - startingstock)*Cost)
FROM
inventory
ORDER BY
valuechange desc
You might get a sort based on the result set. But because the ORDER BY is instead
ORDER BY ABS((endingstock - startingstock)*Cost)
SQL basically has to go back after the data again to get the values for endingstock, startingstock and cost.
Again I'm probably off a bit in this description, but this is kind of what I think is going on.
October 8, 2009 at 9:43 am
I definately need Jeff to explain because I've confused myself.
I made a (VERY) limited test.
create table #inventory(itemcode varchar(10),startlevel int, endlevel int, cost money)
create index funfun on #inventory(itemcode,startlevel,endlevel,cost)
insert into #inventory
select
'Apples',
100,
50,
.35
UNION ALL
SELECT
'Bannanas',
75,
50,
.25
UNION ALL
SELECt
'Peaches',
80,
20,
.10
SELECT
itemcode,
stockvalue = ABS((endlevel - startlevel)*Cost)
FROM
#inventory
WHERE
itemcode = 'Apples'
order by
ABS((endlevel - startlevel)*Cost)
SELECT
itemcode,
stockvalue = ABS((endlevel - startlevel)*Cost)
FROM
#inventory
WHERE
itemcode = 'Apples'
order by
stockvalue
And I get index seeks on both... so there's more to this than I thought. 🙂
Jeff?
October 8, 2009 at 10:31 am
Heh... you've confused me, as well. 😛 I'll have to study that a bit... I'll be back.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2009 at 10:40 am
Ah... got it. If you gen the actual execution plans for both queries, you'll see that the INDEX SEEK is used to resolve the predicate "WHERE itemcode = 'Apples'" and that the sort is taking 78% of the total workload (careful folks... the execution plan isn't ALWAYS right on something like this). And it's not just the ABS that's causing the wayward sort time... it's the fact that it's sorting a calculation of columns rather than columns.
--Jeff Moden
Change is inevitable... Change for the better is not.
October 8, 2009 at 10:48 am
Let's take this one step further. In a lot of cases, if an index is used by the sort, the sort operation in the execution plan will frequently not even appear (not ALWAYS true but IS TRUE in this particular case). Run the following code with the actual execution plan turned on to see what I mean. I've added a third query that shows this case-in-point...
drop table #inventory
go
create table #inventory(itemcode varchar(10),startlevel int, endlevel int, cost money)
create index funfun on #inventory(itemcode,startlevel,endlevel,cost)
insert into #inventory
select
'Apples',
100,
50,
.35
UNION ALL
SELECT
'Bannanas',
75,
50,
.25
UNION ALL
SELECt
'Peaches',
80,
20,
.10
SELECT
itemcode,
stockvalue = ABS((endlevel - startlevel)*Cost)
FROM
#inventory
WHERE
itemcode = 'Apples'
order by
ABS((endlevel - startlevel)*Cost)
SELECT
itemcode,
stockvalue = ABS((endlevel - startlevel)*Cost)
FROM
#inventory
WHERE
itemcode = 'Apples'
order by
stockvalue
--===== Additional code shows that an index was used
-- by the ORDER BY because of the absence of the
-- "SORT" operation in the execution plan. It
-- doesn't always work out this way, but this is
-- a good example of when it does.
SELECT
itemcode,
stockvalue = ABS((endlevel - startlevel)*Cost)
FROM
#inventory
WHERE
itemcode = 'Apples'
order by
StartLevel, EndLevel, Cost
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply