Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 


 
     
Recent Posts
   
Popular Topics
Home   
Search
    Members    Calendar    Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » query optimizing
Add to briefcase
27 posts, Page 1 of 3
123»»»

query optimizing Expand / Collapse
Rate Topic
Display Mode
Topic Options
Author
Message
srathna77
Posted Wednesday, October 07, 2009 12:05 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Thursday, September 08, 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
Lynn Pettis
Posted Wednesday, October 07, 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 @ 9:22 AM
Points: 21,599, Visits: 27,419
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
LutzM
Posted Wednesday, October 07, 2009 12:33 PM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Wednesday, April 24, 2013 3:17 PM
Points: 6,731, Visits: 12,131
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
srathna77
Posted Wednesday, October 07, 2009 12:45 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

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

Thanks
Post #799440
Lynn Pettis
Posted Wednesday, October 07, 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 @ 9:22 AM
Points: 21,599, Visits: 27,419
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
Mike McQueen
Posted Wednesday, October 07, 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: 318, 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
Jeff Moden
Posted Wednesday, October 07, 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 @ 6:23 AM
Points: 32,903, Visits: 26,784
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."

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Post #799598
Mike McQueen
Posted Thursday, October 08, 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: 318, 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
mtassin
Posted Thursday, October 08, 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: Today @ 9:16 AM
Points: 3,226, Visits: 64,167
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
Mike McQueen
Posted Thursday, October 08, 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: 318, 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
27 posts, Page 1 of 3
123»»»

Permissions Expand / Collapse

 
 
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved. Privacy Policy. Terms of Use. Report Abuse.