Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

query optimizing Expand / Collapse
Author
Message
Posted Wednesday, October 7, 2009 12:05 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 8, 2011 7:12 PM
Points: 72, Visits: 176
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
Post #799409
Posted Wednesday, October 7, 2009 12:11 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 20,729, Visits: 32,489
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)




Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #799412
Posted Wednesday, October 7, 2009 12:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 1:52 PM
Points: 6,840, Visits: 13,354
Assuming @A will always be a positive number you can change
where ((avg_cost - std_cost)*in_stock >= @A 
or (avg_cost - std_cost)*in_stock <= -1* @A)

to
where (ABS((avg_cost - std_cost)*in_stock) &gt;= @A) 





Lutz
A pessimist is an optimist with experience.

How to get fast answers to your question
How to post performance related questions
Links for Tally Table , Cross Tabs and Dynamic Cross Tabs , Delimited Split Function
Post #799424
Posted Wednesday, October 7, 2009 12:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 8, 2011 7:12 PM
Points: 72, Visits: 176
Yes you are correct. any more suggestions plz

Thanks
Post #799440
Posted Wednesday, October 7, 2009 12:49 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:25 PM
Points: 20,729, Visits: 32,489
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #799444
Posted Wednesday, October 7, 2009 1:10 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 16, 2011 12:39 PM
Points: 313, Visits: 841
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 )
Post #799468
Posted Wednesday, October 7, 2009 4:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 10:47 AM
Points: 35,347, Visits: 31,882
Mike McQueen (10/7/2009)
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 )


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
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #799598
Posted Thursday, October 8, 2009 8:03 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 16, 2011 12:39 PM
Points: 313, Visits: 841
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?
Post #800029
Posted Thursday, October 8, 2009 9:16 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Tuesday, September 30, 2014 7:42 AM
Points: 3,688, Visits: 72,435
It's not the Order by so much as the ABS function in the Order by I'm pretty sure.



--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link
For tips on how to post your problems
Post #800099
Posted Thursday, October 8, 2009 9:20 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 16, 2011 12:39 PM
Points: 313, Visits: 841
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?
Post #800102
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse